ETL and ELT Pipelines Flashcards

What does data lineage describe in a pipeline context?

Answer: The path data takes from source through transformations to final outputs

Lineage traces how data flows and transforms across systems, aiding debugging and impact analysis.

Which strategy minimizes load on a production OLTP database during extraction?

Answer: Reading from a read replica or using CDC log mining

Replicas and log-based CDC offload extraction work without burdening the primary transactional system.

What is the main advantage of an event-driven pipeline triggered by file arrival versus a fixed cron schedule?

Answer: It processes data as soon as it's available, reducing latency

Event triggers react immediately to new data instead of waiting for the next scheduled window.

When normalizing inconsistent date formats across multiple sources, this step belongs to which pipeline phase?

Answer: Transform

Standardizing and cleaning formats is core transformation work applied to extracted data.

A fact table in a star schema typically contains what?

Answer: Numeric measures and foreign keys to dimensions

Fact tables store quantitative measures plus foreign keys linking to surrounding dimension tables.

Why might a team add a checkpoint or bookmark to an incremental pipeline?

Answer: To record the last processed position so the next run resumes correctly

Checkpoints persist the high-water mark so subsequent runs only fetch new records.

Which is a key trade-off of choosing ELT over ETL regarding data governance?

Answer: Raw sensitive data lands in the warehouse before any masking is applied

Because ELT loads raw data first, sensitive fields reside in the warehouse before transformation/masking, raising governance concerns.