The Living Table
A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.
- Domain
- Pipeline Design
- Difficulty
- medium
- Seniority
- L5
Problem
We're migrating our data platform to a lakehouse. Our source systems have a mix of reference data that changes over time: product catalog updates, customer profile changes, store-to-region assignments. Finance needs to be able to report historical revenue using the values that were in effect at the time of each sale. Design a data pipeline with proper CI/CD to land this data correctly.
Data lands continuously. History must survive every update.
Business requirements
- Finance reports historical revenue using prices, addresses, and store assignments as they were at the time of each sale; using today's values produces the wrong number.
- If a bad deploy corrupts a dimension table, the team has to restore it to a known-good state within minutes; reports can't wait days.
- Regulators require that every customer address change be auditable with who changed it and when; a silent overwrite is a compliance violation.
Worked solution and explanation
Why this problem exists in real interviews
An L5 lakehouse pipeline that's really about three properties together: as-of historical accuracy, fast rollback after a bad deploy, and an audit trail for every change. The trap is treating dimensions as overwritten tables and trusting that 'we'll restore from backup' will work fast enough on the day a bad deploy lands.
The default reach is to merge the source's latest values into a dimension table and report off it. Finance reports historical revenue and the numbers don't match what was actually charged because the dimension was overwritten. A bad deploy corrupts the dimension; the team finds the last clean backup and restores hours later. An auditor asks for a customer's address-change history and the answer is what the column says today.
Walk the requirements
Step 1: Slowly-changing dimensions so finance reports the right historical number
Each dimension (product, customer, store) writes a new row on each change, keyed on (entity_id, valid_from, valid_to). The sales fact joins on sale_date BETWEEN valid_from AND valid_to so a sale from last March picks up last March's price, address, and store assignment. A 'overwrite the dimension on every change' approach silently rewrites historical revenue; the SCD plus point-in-time join is the version that matches what was actually charged.
Step 2: Time-travel rollback so a bad deploy is recoverable in minutes
The lakehouse table format keeps versioned snapshots of the dimension; if a bad deploy writes a corrupted version, the rollback is RESTORE TABLE dim TO VERSION x (or equivalent) and the dimension reverts in minutes. A 'restore from nightly backup' approach takes hours and doesn't include the day's good changes; time-travel is the contract that makes the rollback fast and complete.
Step 3: Audit log for every change
Regulators require every address change be auditable. The SCD merge writes a row to an audit log on every change with (entity_id, column, source, timestamp, prior value, new value). Auditors query the log for the change history of any column. A 'check the dimension for current state' approach loses the trail because the SCD shows the row but not the source or the audit context; the dedicated log is the boundary that captures who and when.
The shape that fits
- source_systems (source, CDC)
- orchestrator (transform, Airflow); on error: alert; alerting: SCD merge failure or CI gate fail
- staging_lake (storage, S3); backfill: partition_overwrite
- scd_merge (transform, Spark); idempotency: staging_table
- dimension_lakehouse (storage, Delta Lake); freshness: < 24h; backfill: partition_overwrite
- audit_log (storage, S3); backfill: incremental
- finance_team (consumer, Tableau); freshness: < 24h
- auditors (consumer, Tableau); freshness: < 24h
- source_systems -> staging_lake
- orchestrator -> scd_merge
- staging_lake -> scd_merge
- scd_merge -> dimension_lakehouse
- scd_merge -> audit_log
- dimension_lakehouse -> finance_team
- audit_log -> auditors
- Source + stage: source_systems, staging_lake
- Merge + dimension: orchestrator, scd_merge, dimension_lakehouse
- Audit + consumers: audit_log, finance_team, auditors
Common follow-up questions
- A source's address change is corrected after the fact (the original was wrong). How does this design represent the correction without losing the prior history? (Tests whether the candidate sees that the correction writes a new SCD row with the corrected value and the prior incorrect row stays in place; the audit log captures the correction with its prior and new values. Historical sales joined to the address as-of-then now pick up either the original or the corrected value depending on what the design treats as the canonical story (a documented choice).)
- A CI test fails on a SCD merge change. What in this design blocks the deploy, and what does the team see? (Tests whether the candidate has a CI gate at the orchestrator: the merge job's tests run pre-deploy, a failed test blocks the merge into the dimension, and the team sees the test failure with the diff. Without CI, a bad deploy reaches production; with it, the test catches it before the dimension is touched.)