Data Engineering
Storage architectures, ETL versus ELT, data layering, and change data capture.
16 questions
JuniorTheoryVery commonWhy does an OLTP system normalize its tables while an analytics warehouse denormalizes them?
Why does an OLTP system normalize its tables while an analytics warehouse denormalizes them?
OLTP normalizes to remove redundancy so writes stay consistent and cheap — one fact lives in one place. Analytics denormalizes because it is read-heavy: pre-joining and duplicating context avoids expensive runtime joins, trading write simplicity for fast reads.
Common mistakes
- ✗Reversing which workload normalizes and which denormalizes
- ✗Claiming duplicating a column into a warehouse table is forbidden
- ✗Saying denormalization is about disk, not about avoiding joins
Follow-up questions
- →What write anomalies does normalization prevent in OLTP?
- →What is the cost of denormalization when a dimension attribute changes?
JuniorTheoryVery commonIn a star schema, how does a fact table differ from a dimension table, and why prefer it to OLTP?
In a star schema, how does a fact table differ from a dimension table, and why prefer it to OLTP?
A fact table holds events — one row per transaction, with numeric measures and foreign keys. Dimension tables hold descriptive context — customer, product, date. Analysts prefer the star for its few denormalized joins, simple and fast to aggregate.
Common mistakes
- ✗Swapping which table holds measures and which holds descriptive context
- ✗Thinking the star is just a renamed fully normalized OLTP schema
- ✗Believing analysts prefer it for storage, not for simpler joins
Follow-up questions
- →Why does a wide denormalized dimension speed up analytic queries?
- →What is the grain of a fact table, and why does it matter?
JuniorTheoryVery commonWhat is the difference between a Data Warehouse, a Data Lake, and a Lakehouse?
What is the difference between a Data Warehouse, a Data Lake, and a Lakehouse?
A Data Warehouse stores structured, curated data with schema-on-write — modelled and ready for BI. A Data Lake stores raw data of any format cheaply with schema-on-read, applying structure only when queried. A Lakehouse adds warehouse-like ACID transactions and table semantics on top of lake storage via Delta, Iceberg, or Hudi.
Common mistakes
- ✗Swapping schema-on-write and schema-on-read between warehouse and lake
- ✗Treating the lakehouse as just a smaller warehouse
- ✗Forgetting the lakehouse adds ACID/table semantics over lake storage
Follow-up questions
- →When would a lake's schema-on-read be a liability?
- →What problem do Delta or Iceberg solve over plain lake files?
JuniorTheoryCommonWhat does an analytics engineer do that neither the data engineer nor the analyst does?
What does an analytics engineer do that neither the data engineer nor the analyst does?
The analytics engineer owns the transformation layer — turning raw landed tables into clean, tested, documented models (staging to marts), typically in dbt. The data engineer lands the raw data; the analyst consumes the marts. The engineer bridges the two.
Common mistakes
- ✗Confusing the analytics engineer with a pure dashboard builder
- ✗Treating the three roles as interchangeable with no distinct layer
- ✗Giving marts modelling to the data engineer instead
Follow-up questions
- →Why did the analytics engineer role emerge with cloud warehouses and dbt?
- →How do tests and documentation in the model layer help downstream analysts?
JuniorTheoryCommonWhat actually forces a pipeline to stream instead of batch, and why is real-time often a want, not a need?
What actually forces a pipeline to stream instead of batch, and why is real-time often a want, not a need?
Stream only when a decision acts within seconds — fraud blocking, live alerting, personalization. Otherwise batch is simpler, cheaper, and easier to backfill. Most dashboards are read hourly or daily, so real-time is usually a preference, not a requirement.
Common mistakes
- ✗Choosing streaming by data volume rather than decision latency
- ✗Assuming streaming is always cheaper and simpler than batch
- ✗Treating every real-time request as a genuine hard requirement
Follow-up questions
- →Which questions reveal whether a stakeholder truly needs sub-minute latency?
- →Why is a batch pipeline usually easier to backfill and reprocess?
JuniorTheoryCommonWhat do you trade away choosing a star schema versus a snowflake versus one big table?
What do you trade away choosing a star schema versus a snowflake versus one big table?
A star keeps denormalized dimensions — few joins, some redundancy. A snowflake normalizes them into sub-tables — less redundancy, more joins. OBT pre-joins everything flat — zero joins and fastest reads, but the most duplication and hardest to maintain.
Common mistakes
- ✗Swapping which of star and snowflake is the normalized one
- ✗Claiming one big table has the least duplication
- ✗Believing the three shapes have identical read cost
Follow-up questions
- →When is a snowflake's extra normalization actually worth the joins?
- →What maintenance problems appear when a one-big-table source attribute changes?
MiddleTheoryCommonWhat does a data contract contain, and what does breaking it mean for the producing team?
What does a data contract contain, and what does breaking it mean for the producing team?
A data contract is a producer's guarantee about a dataset — schema, types, semantics, freshness/SLA, and allowed values. Breaking it means an incompatible change — a rename or a type or meaning change — without versioning or notice, so consumers silently break.
Common mistakes
- ✗Reducing a data contract to a legal or billing document
- ✗Putting the stability obligation on the consumer, not the producer
- ✗Thinking a type or meaning change is safely backward compatible
Follow-up questions
- →How does versioning let a producer evolve a schema without breaking consumers?
- →Which contract change is breaking even when the column name stays the same?
MiddleTheoryCommonWhat is the difference between ETL and ELT, and when does each fit?
What is the difference between ETL and ELT, and when does each fit?
ETL transforms data before loading it into the target — cleaning and reshaping on a separate engine, then writing the curated result. ELT loads raw data into the warehouse first and transforms it there, leveraging the warehouse's cheap, scalable MPP compute.
Common mistakes
- ✗Reversing which one transforms before vs after loading
- ✗Calling ELT the legacy approach when it is the modern one
- ✗Ignoring that ELT leans on the warehouse's own compute
Follow-up questions
- →Why does cheap warehouse compute make ELT attractive?
- →When is transforming before load (ETL) mandatory?
MiddleDesignCommonA daily job aggregates yesterday's orders into a revenue_daily mart by appending its results with INSERT. Occasionally the orchestrator retries the job after a transient failure, and on those days the revenue for that date comes out doubled. You must make the pipeline idempotent — re-running it for a date must leave the mart in exactly the same state as running it once, with no double counting and no manual cleanup. Which design achieves that?
A daily job aggregates yesterday's orders into a revenue_daily mart by appending its results with INSERT. Occasionally the orchestrator retries the job after a transient failure, and on those days the revenue for that date comes out doubled. You must make the pipeline idempotent — re-running it for a date must leave the mart in exactly the same state as running it once, with no double counting and no manual cleanup. Which design achieves that?
Make the write idempotent by keying on the date partition — delete-then-insert (or MERGE) the target date's rows each run, so a re-run replaces that date instead of appending. The output for a date becomes a pure function of the input, stable however many times it runs.
Common mistakes
- ✗Deduplicating the aggregated mart instead of replacing the date partition
- ✗Believing a lock alone makes an append-only INSERT idempotent
- ✗Fixing the doubling only at read time in downstream queries
Follow-up questions
- →Why does keying the write on the date partition make re-runs safe?
- →How do INSERT OVERWRITE or MERGE express the same idempotent replace?
MiddleDesignCommonEvery morning yesterday's revenue number on the dashboard changes, because events for a day keep arriving for a day or two afterward (mobile clients sync late, upstream jobs backfill). Stakeholders think the dashboard is lying. Design how the pipeline should handle this late-arriving data so the numbers stop silently shifting under people.
Every morning yesterday's revenue number on the dashboard changes, because events for a day keep arriving for a day or two afterward (mobile clients sync late, upstream jobs backfill). Stakeholders think the dashboard is lying. Design how the pipeline should handle this late-arriving data so the numbers stop silently shifting under people.
Reprocess a rolling lookback window (say the last 3 days) each run so late events land in their event-date partition, and flag recent dates as provisional until the window closes. The number still updates, but an 'as of' label shows it is expected, not broken.
Common mistakes
- ✗Dropping late events instead of reprocessing them into their date
- ✗Stamping events by processing time and misattributing the date
- ✗Treating expected late-data drift as a pipeline bug to freeze away
Follow-up questions
- →How wide should the lookback window be, and what sets that bound?
- →Why label recent dates provisional instead of hiding the change?
SeniorTheoryCommonWhat data layers do you operate with in a platform, and why do they exist?
What data layers do you operate with in a platform, and why do they exist?
A typical medallion-style layering is raw/staging (an exact landed copy of source data), then a normalized/cleansed layer (deduplicated, typed, conformed), then data marts (curated, aggregated, business-facing). The layers separate concerns so each stage can be reprocessed and audited independently.
Common mistakes
- ✗Reversing the raw → cleansed → marts order
- ✗Claiming layers only waste storage with no benefit
- ✗Doing all cleaning inside consumer queries instead of a layer
Follow-up questions
- →Why keep an exact raw copy you never expose to BI?
- →How does layering help when a source schema changes?
MiddleDesignOccasionalYou are adding data-quality tests to a revenue mart. The candidates are not-null, unique (primary key), accepted values (an enum like order status), referential integrity (every order's customer exists), freshness (data landed on time), and volume anomaly (row count within bounds). Which single test tends to catch the most real production bugs, and why?
You are adding data-quality tests to a revenue mart. The candidates are not-null, unique (primary key), accepted values (an enum like order status), referential integrity (every order's customer exists), freshness (data landed on time), and volume anomaly (row count within bounds). Which single test tends to catch the most real production bugs, and why?
The uniqueness / primary-key test catches the most: a broken join or a non-idempotent re-run fans out rows and silently doubles revenue, which no not-null or range check would notice. A duplicated key is the most common cause of wrong totals in a mart.
Common mistakes
- ✗Assuming SQL sums ignore duplicated rows from a join fan-out
- ✗Ranking not-null above the uniqueness test for total correctness
- ✗Treating every check as catching an equal share of bugs
Follow-up questions
- →How does a many-to-many join fan-out inflate a summed measure?
- →Why is freshness the other high-value check on a daily mart?
MiddleDebuggingOccasionalA daily job re-ran after a retry and that date's revenue doubled — find the design flaw, not the bad row
A daily job re-ran after a retry and that date's revenue doubled — find the design flaw, not the bad row
The load is append-only — a second INSERT for the same :run_date double-counts the date. Make it idempotent by replacing the date each run: DELETE the :run_date rows then INSERT, or MERGE — so a re-run overwrites instead of appending.
Common mistakes
- ✗Blaming a bad row instead of the append-only load design
- ✗Trying to cancel the doubling with AVG instead of replacing the date
- ✗Deduplicating at read time instead of making the write idempotent
Follow-up questions
- →Why does DELETE-then-INSERT of the date partition make the load idempotent?
- →How would a unique constraint on order_date have surfaced this bug sooner?
MiddleTheoryOccasionalA customer moves city. Under slowly-changing-dimension SCD Type 1 versus Type 2, what happens to last year's report?
A customer moves city. Under slowly-changing-dimension SCD Type 1 versus Type 2, what happens to last year's report?
Type 1 overwrites the city in place, so the whole history — including last year's report — shows the new city and the old value is lost. Type 2 adds a dated dimension row with date ranges, so last year's report keeps the old city and the new one applies forward.
Common mistakes
- ✗Reversing which type overwrites and which versions history
- ✗Thinking both types treat historical reports identically
- ✗Believing Type 2 deletes or duplicates the customer instead of versioning
Follow-up questions
- →How does a Type 2 row mark which version was active on a given date?
- →When is losing history (Type 1) actually the right choice?
SeniorTheoryRareWhat is Change Data Capture (CDC), and how do you implement it (Pull vs Push)?
What is Change Data Capture (CDC), and how do you implement it (Pull vs Push)?
CDC captures row-level changes (inserts, updates, deletes) from a source database so downstream systems stay in sync without full reloads. Log-based CDC reads the DB transaction log (WAL/binlog, e.g. Debezium) and streams changes — a push model with low lag.
Common mistakes
- ✗Equating CDC with a full table reload
- ✗Swapping which of pull/push is log-based and low-lag
- ✗Forgetting log-based CDC reads the DB transaction log
Follow-up questions
- →Why does log-based CDC load the source less than polling?
- →How does CDC handle deletes that polling can miss?
SeniorDesignRareAn upstream team renamed a column with no warning and 30 downstream dashboards broke overnight. Leadership asks you to design the process and the tests that make this class of failure impossible — not to patch the 30 dashboards. What combination of contract, versioning, and automated checks do you put in place so an incompatible upstream schema change is caught before it reaches production?
An upstream team renamed a column with no warning and 30 downstream dashboards broke overnight. Leadership asks you to design the process and the tests that make this class of failure impossible — not to patch the 30 dashboards. What combination of contract, versioning, and automated checks do you put in place so an incompatible upstream schema change is caught before it reaches production?
Put a data contract on the source with schema tests in the producer's CI, so a rename fails their build, not our dashboards. Breaking changes must ship versioned with a deprecation window, and a schema-diff check blocks the deploy — the break shifts left to the producer, before release.
Common mistakes
- ✗Fixing it reactively after release instead of shifting the check left
- ✗Trying to freeze the schema forever rather than versioning changes
- ✗Hardening the 30 consumers instead of contracting the producer
Follow-up questions
- →Why must the schema test run in the producer's CI, not only in yours?
- →How does a deprecation window let consumers migrate before a rename lands?
