Data Engineering ETL and ELT Pipelines 2 — Questions and Answers
Question 1: In an ELT pipeline, where does the bulk of data transformation logic execute?
- In the target data warehouse after loading (Correct answer)
- In a separate staging server before loading
- On the source system before extraction
- In the network transfer layer
Correct answer: In the target data warehouse after loading
ELT loads raw data into the warehouse first and uses the warehouse's compute to transform it.
Question 2: A pipeline reprocesses the entire source table every night instead of only changed rows. Which technique would reduce this load?
- Incremental loading with change data capture (Correct answer)
- Full truncate-and-reload
- Adding more network bandwidth
- Disabling indexes
Correct answer: Incremental loading with change data capture
Incremental loading via CDC processes only inserted, updated, or deleted records since the last run.
Question 3: Which scenario most favors ELT over traditional ETL?
- A cloud warehouse with elastic, scalable compute like Snowflake or BigQuery (Correct answer)
- A legacy on-prem server with fixed, limited CPU
- A real-time embedded device
- A system with no storage for raw data
Correct answer: A cloud warehouse with elastic, scalable compute like Snowflake or BigQuery
ELT thrives when the destination warehouse provides cheap, scalable compute to transform raw loaded data.
Question 4: What is the primary purpose of a staging area in an ETL pipeline?
- A temporary landing zone for raw data before transformation (Correct answer)
- The final reporting layer for business users
- A backup of production credentials
- A cache for dashboard queries
Correct answer: A temporary landing zone for raw data before transformation
Staging holds extracted raw data temporarily so transformations can run without touching source systems.
Question 5: An idempotent pipeline run guarantees what behavior?
- Running it multiple times produces the same final result (Correct answer)
- It runs faster on each subsequent execution
- It never reads from the source twice
- It always doubles the output rows
Correct answer: Running it multiple times produces the same final result
Idempotency means repeated executions yield identical state, preventing duplicates on retry.
Question 6: Which approach best handles late-arriving data in a batch pipeline?
- Reprocessing a lookback window of recent partitions (Correct answer)
- Ignoring any record past its scheduled window
- Restarting the entire historical load nightly
- Dropping the timestamp column
Correct answer: Reprocessing a lookback window of recent partitions
A lookback window reprocesses recent partitions so records that arrive late are still captured.
Question 7: What does schema drift refer to in a data pipeline?
- Unexpected changes to source column names, types, or structure over time (Correct answer)
- Data physically moving between disk partitions
- Gradual clock skew between servers
- Slow degradation of network throughput
Correct answer: Unexpected changes to source column names, types, or structure over time
Schema drift is when the source structure evolves, potentially breaking downstream transformations.
Data Engineering ETL and ELT Pipelines 2
In an ELT pipeline, where does the bulk of data transformation logic execute?