Sample questions
Data Pipeline DesignDifficulty 1
In an ETL pipeline, at which stage does data transformation happen relative to loading?
- aTransformation happens after the data is loaded into the target system
- bTransformation happens before the data reaches the target system✓
- cTransformation happens only if the source schema changes
- dTransformation is skipped entirely and raw data is loaded as-is
Explanation:ETL stands for Extract, Transform, Load: the data is transformed in a separate processing step before it ever reaches the target system. This is the defining difference from ELT, where transformation happens after loading, inside the target system.
Data Pipeline DesignDifficulty 1
What is the main practical reason a team might choose ELT over ETL when the target system is a powerful analytical database?
- aELT avoids the need for any data validation
- bELT guarantees the source system will never change its schema
- cELT removes the need to extract data from the source at all
- dELT lets the target system handle heavy transforms✓
Explanation:ELT loads raw data first and pushes transformation work into the target system, taking advantage of that system's own processing power for large-scale transformations, instead of doing the heavy lifting in a separate transform layer before loading.
Data Pipeline DesignDifficulty 1
What does it mean for a pipeline step to be "idempotent"?
- aRepeating it with the same input always gives one result✓
- bIt can only be run exactly one time, ever, for a given dataset
- cIt automatically retries itself until it succeeds
- dIt never produces any errors regardless of the input
Explanation:Idempotency means repeated executions with the same input leave the system in the same state as a single execution — no duplicate rows, no double-counted totals. It says nothing about retry mechanics or error-free behavior, and it does not forbid re-running a step.
Data Pipeline DesignDifficulty 2
A nightly pipeline fails halfway through loading yesterday's orders. An engineer simply re-runs the whole job from scratch. Why is this safe only if the load step is idempotent?
- aBecause re-running always makes the pipeline run faster the second time
- bBecause idempotency guarantees the source system will not have changed
- cRows loaded before the failure get duplicated✓
- dBecause idempotency removes the need to extract the data again
Explanation:If the load step already inserted some of yesterday's orders before failing, and it is not idempotent, re-running from scratch will insert those same rows again, causing duplicates and inflated totals. An idempotent load (e.g. upsert-style behavior conceptually) makes a full re-run safe.
Data Pipeline DesignDifficulty 1
What is the basic difference between a full extract and an incremental extract from a source system?
- aA full extract only pulls rows changed since the last run, an incremental extract pulls everything
- bA full extract pulls everything each time, an incremental extract pulls only changed rows✓
- cA full extract can only be used once per source table for its entire lifetime
- dThere is no meaningful difference, both always return identical row counts
Explanation:A full extract re-reads the entire source dataset on every run, regardless of what changed. An incremental extract identifies and pulls only the rows that are new or changed since the previous run, which is cheaper for large, slowly-changing tables.
Data Pipeline DesignDifficulty 2
Conceptually, what is the core idea behind change-data-capture style extraction, without reference to any specific tool?
- aDetecting and extracting only the rows that changed at the source✓
- bRe-running the transformation logic twice to double-check correctness
- cDeleting old data from the source system after it has been extracted
- dStoring every extract as a full snapshot regardless of what changed
Explanation:Change-data-capture style extraction is about identifying which rows changed (inserted, updated, deleted) since the last extraction and pulling only that delta, rather than re-scanning the entire source dataset every time.