ETL and ELT Pipelines Flashcards

Backfilling a pipeline refers to what activity?

Answer: Reprocessing historical date ranges to populate or correct past data

Backfilling runs the pipeline over past periods to fill gaps or apply corrected logic retroactively.

Which file format is columnar and well-suited for analytical ELT workloads?

Answer: Apache Parquet

Parquet stores data by column with compression and predicate pushdown, ideal for analytics.

What is the benefit of partitioning a large table by date in a warehouse?

Answer: Queries can prune irrelevant partitions, scanning less data

Date partitioning lets the engine skip partitions outside the query's range, cutting scan cost.

A watermark in stream processing is primarily used to do what?

Answer: Track event-time progress and decide when a window can close

Watermarks signal how far event-time has advanced, allowing windows to finalize despite out-of-order data.

Which delivery guarantee can produce duplicate records that downstream logic must deduplicate?

Answer: At-least-once delivery

At-least-once may redeliver messages on retry, so consumers must handle duplicates.

Why is storing transformation logic as version-controlled code (e.g., dbt models) preferred over manual SQL scripts?

Answer: It enables testing, code review, lineage, and reproducible deployments

Code-based transformations bring software engineering rigor—tests, reviews, lineage—to the warehouse layer.

A surrogate key in a data warehouse dimension is best described as what?

Answer: A system-generated identifier independent of business values

Surrogate keys are warehouse-assigned integers that stay stable even when natural business keys change.