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

Design the pipeline that loads our changing reference data into the new lakehouse every night: product catalog updates, customer profile changes and store-to-region assignments, which the source systems emit as change records stamped with who made each change and when. Finance's revenue reports and the compliance team's audits both read from it.

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

This is a dimension-history problem dressed up as a lakehouse migration. Anyone can land CDC rows in a table. The trap is merging the latest source values into the dimension, because that silently rewrites every past sale with today's price, address and store region. The same overwrite leaves nothing to restore after a bad deploy except a nightly backup, and nothing for an auditor to read except the current value.

Three requirements, three owners

Step 1: Version the dimension instead of overwriting it

Each change closes the open row and inserts a new one with valid_from and valid_to. Facts join where the sale date falls inside that window, so last March's sale gets last March's price, address and region. Make valid_to exclusive, or a sale on the boundary date matches two versions and double counts revenue.

Step 2: Keep the dimension in a table format with time travel

A bad merge becomes a restore to the last good table version. That takes minutes and keeps every good change made before the deploy. A nightly backup takes hours and drops the day's good updates. This is why history lives in dimension_lakehouse and not in plain files.

Step 3: Write one audit record per change

Versioned rows show what the value was, not who changed it or where it came from. The merge also writes to audit_log with source, timestamp, prior value and new value, and auditors query that directly.

Step 4: Let the orchestrator own the merge

Airflow schedules scd_transform after the batch has landed. Raw CDC rows stay in raw_landing, so a failed run replays from there and never re-reads the source systems. Rerunning the same batch must produce the same dimension.

Overwrite the dimension

Historical revenue is recomputed with today's attributes. A bad deploy means a backup restore that takes hours. The audit answer is whatever the column says now.

Versioned dimension plus time travel

Each sale joins the version in effect on its date. A bad deploy is a restore to a prior table version. The audit answer is a query on audit_log.

Common follow-up questions

• A source corrects an address that was wrong from the start. How do you record it without losing history? (Tests whether the candidate inserts a corrected version, logs prior and new values in `audit_log`, and can say which version past sales should use.)
• How do you stop a bad merge from reaching the dimension in the first place? (Tests CI gating: merge logic is tested before deploy and the orchestrator only runs a version that passed, with time travel as the safety net for what slips through.)