The Design Session

The six-step framework

Everything in chapters 02–08 is raw material. This chapter is the assembly: a single loop you run on any pipeline prompt so you're never staring at a blank board. It's the macro-version of the five-beat answer structure from Chapter 01, stretched across a full hour.

The scenario

Here's the prompt we'll walk the entire framework on — a near-verbatim version of what this loop tends to ask:

Three things in that prompt are load-bearing, and you should name them back to the interviewer so they know you heard them: (1) two sources with different semantics — a database you control and a provider you don't; (2) late-arriving events, called out explicitly; (3) a hard reconciliation requirement against an external source of truth. That third point is the tell: this is a financial pipeline, so correctness dominates latency. Anchor every later trade-off to that.

Steps 1–2 · Clarify & set SLAs

Open with a tight volley of clarifying questions. Below are the ones that actually change the design, each with a plausible answer the interviewer might give — internalize the shape so you can improvise when the numbers differ.

Notice you asked six questions, not twenty, and each one has a design consequence. Now write the success metrics on the board — this is Step 2, and it takes thirty seconds but frames the entire rest of the hour:

SUCCESS METRICS  (write these on the board, top-right)
  Freshness  : orders + payments marts ready by 06:00 local, T+1        (SLA)
  Complete.  : >= 99.9% of a day's events captured before daily close   (SLO)
  Correctness: daily gross revenue & net payout tie to processor
               settlement to within $0.00 (hard break if not)           (SLA)
  Restatement: allowed, but must be versioned + auditable
  Scale bar  : 2M orders/day, 5M pay-events/day, ~2x YoY

Step 3 · Sketch the architecture

Now draw — breadth first, left to right, one row for the happy path, with orchestration and observability as cross-cutting rails underneath. Reproduce this on the board roughly as-is. The medallion layering (landing → staging → marts) is straight from Chapter 02; the ingestion choices from Chapter 05.

  SOURCES                INGEST / CDC            LANDING (bronze)        TRANSFORM (dbt)          MARTS (gold)         SERVE
                                                 immutable, raw          staging -> intermediate
 ┌───────────────┐     ┌──────────────────┐    ┌──────────────────┐    ┌───────────────────┐    ┌──────────────┐    ┌──────────────┐
 │ App Postgres  │──①─▶│ log-based CDC    │───▶│ raw.orders       │───▶│ stg_orders        │─┐  │ fct_orders   │──▶ │ BI / dashbds │
 │ (orders)      │ ⚡  │ (Debezium)       │    │ (append, by _lsn)│    │ (typed, dedup)    │ │  │              │    │ (ops, hourly)│
 └───────────────┘     └──────────────────┘    └──────────────────┘    └───────────────────┘ ├─▶│ fct_payments │──▶ │ Finance close│
                                                                                              │  │              │    │ (T+1, exact) │
 ┌───────────────┐     ┌──────────────────┐    ┌──────────────────┐    ┌───────────────────┐ │  │ fct_revenue  │    └──────────────┘
 │ Payments API  │──②─▶│ webhook sink +   │───▶│ raw.pay_events   │───▶│ stg_payments      │─┘  │ (recon'd)    │──▶ ┌──────────────┐
 │ + webhooks    │ ⚡  │ poll reconciler  │    │ (append, dedup   │    │ int_settlements   │    │ dim_customer │    │ recon_report │
 │ (Stripe)      │     │ (idempotent key) │    │  by event_id)    │    │ (SCD2 refunds)    │    │ (SCD2)       │    │ (audit)      │
 └───────────────┘     └──────────────────┘    └──────────────────┘    └───────────────────┘    └──────────────┘    └──────────────┘
 ┌───────────────┐                                                            ▲    WAP: write to _staging, AUDIT (recon+tests), then PUBLISH swap
 │ Processor     │──③── daily settlement file (S&T truth) ───────────────────┘
 │ settlement    │
 └───────────────┘
 ══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
   ORCHESTRATION  │ Airflow: data-aware DAGs, hourly ops run + daily close run, retries+backoff, sensors on landing, backfill-safe
   OBSERVABILITY  │ freshness + volume + schema-drift monitors, recon dashboard, lineage, alerting -> block on hard breaks
 ══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
   ⚡ = most likely failure points (external API, dedup/merge boundary)      ①②③ = the three feeds; ③ is the reconciliation source of truth

Walk it in about ninety seconds: two operational feeds (orders via CDC, payment events via webhook) land immutably in bronze, dbt shapes them through staging and intermediate models into finance-grade marts, and a third feed — the processor's daily settlement file — is the independent source of truth you reconcile against. The Write-Audit-Publish gate sits right before the marts go live. Then stop and hand back control:

A few deliberate choices worth defending if asked. CDC for orders (Ch. 05) over a query-based updated_at pull, because at this volume and correctness bar you want completeness and ordering, and you need deletes. Immutable append-only landing keyed by event_id/_lsn, so re-runs never corrupt raw and every restatement is reproducible. dbt on the warehouse (ELT), because the transforms are set-based SQL and the warehouse is where reconciliation SQL naturally lives.

Deep dive · Late-arriving data

The interviewer pulls the first thread: "refunds and disputes land days or weeks after the original order — how do you handle that?" This is Chapter 04 made concrete. Answer in three moves, narrating the level climb as you go.

1 · Event time, not processing time. Every fact is stamped with the processor's event_time (when the money moved) and an ingested_at (when we saw it). All marts aggregate by event date, never by load date — otherwise a refund processed today silently corrupts today's revenue instead of correcting the original day's.

2 · Lookback window + idempotent merge. Each incremental run doesn't just process "since the last watermark". It re-scans a lookback window of arrivals (say 7 days, enough to survive a stalled run) and merges by natural key. Late events landing inside the window get folded into their correct event-date partition; the merge is idempotent so re-processing is a no-op when nothing changed.

-- dbt incremental model: absorbs late-arriving payment events by
-- re-scanning a lookback window and merging on the natural key.
{{ config(
    materialized = 'incremental',
    incremental_strategy = 'merge',
    unique_key = 'payment_event_id',
    on_schema_change = 'append_new_columns'
) }}

with src as (
    select
        payment_event_id,
        order_id,
        event_type,                       -- capture | refund | dispute | payout
        amount_cents,
        currency,
        event_time,                       -- processor's authoritative timestamp
        date(event_time) as event_date,
        ingested_at
    from {{ ref('stg_payments') }}
    {% if is_incremental() %}
      -- window on ARRIVAL (ingested_at), not event_time, so a refund that lands
      -- today with an old event_time is still re-read and merged into its own
      -- event_date. 7d of arrivals survives a stalled run; disputes use SCD2 below.
      where ingested_at >= (
          select dateadd('day', -7, max(ingested_at)) from {{ this }}
      )
    {% endif %}
)

select * from src
-- MERGE on payment_event_id => re-runs and late arrivals are idempotent:
-- a re-seen event overwrites in place, a new late event inserts into its event_date.

3 · SCD2 for anything that gets corrected. A dispute can flip an order's revenue-recognized status weeks later. Rather than mutate history in place, the customer and settlement dimensions are slowly-changing Type 2 — effective-dated rows so you can answer "what did we believe on the day of close?" (as-of) and "what's true now?" (current). That's what makes restatements auditable instead of destructive.

Deep dive · Reconciliation

This is the heart of the prompt, and the single highest-leverage deep dive. The interviewer asks: "Finance says the number has to tie out. How do you guarantee that?" Everything here is Chapter 06 applied to money.

The core idea: you have two independent representations of the same truth — your event-derived fct_payments, and the processor's daily settlement report (feed ③). Reconciliation is the daily proof that they agree, per day, per currency, to a tolerance. You compute control totals on both sides and diff them.

-- Source-vs-target control-total reconciliation, by settlement day + currency.
-- SOURCE OF TRUTH = processor settlement file; TARGET = our warehouse marts.
with target as (      -- what our pipeline says we collected
    select
        event_date              as settle_date,
        currency,
        count(*)                as target_rows,
        sum(amount_cents)       as target_amount_cents
    from {{ ref('fct_payments') }}
    where event_type in ('capture', 'refund')
    group by 1, 2
),
source as (           -- what the processor says we collected
    select
        settlement_date         as settle_date,
        currency,
        count(*)                as source_rows,
        sum(net_amount_cents)   as source_amount_cents
    from {{ ref('stg_settlement') }}
    group by 1, 2
),
recon as (
    select
        coalesce(s.settle_date, t.settle_date) as settle_date,
        coalesce(s.currency, t.currency)       as currency,
        s.source_rows,  t.target_rows,
        s.source_amount_cents,  t.target_amount_cents,
        coalesce(t.target_amount_cents, 0)
          - coalesce(s.source_amount_cents, 0) as variance_cents,
        coalesce(t.target_rows, 0)
          - coalesce(s.source_rows, 0)         as row_variance
    from source s
    full outer join target t
      on s.settle_date = t.settle_date and s.currency = t.currency
)
select
    *,
    case
        when abs(variance_cents) = 0 and row_variance = 0 then 'OK'
        when abs(variance_cents) <= 100                    then 'WARN'  -- <= $1.00 rounding tolerance
        else 'BREAK'                                                     -- hard: block publish
    end as recon_status
from recon
order by settle_date desc, currency;

Three design points to state as you show this:

• Tolerances are explicit and tiered. Zero-cent variance is OK; sub-dollar (FX rounding, fees) is a logged WARN; anything larger is a BREAK. Finance agreed the tolerance up front — you don't invent it silently.
• A full outer join, deliberately. It surfaces rows present on either side alone — a settlement with no matching events (we missed an ingest) or events with no settlement (a capture the processor hasn't settled yet). Those asymmetries are exactly the breaks you must catch.
• The recon runs inside Write-Audit-Publish. The marts are built into a staging schema, the recon query is the audit, and promotion to the live schema only happens if there are zero BREAK rows. A bad day never reaches finance.

# Airflow: Write-Audit-Publish for the daily finance close.
# The circuit breaker: publish is downstream of a recon gate that can halt the DAG.
from airflow.decorators import dag, task
from airflow.exceptions import AirflowFailException
import pendulum

@dag(schedule="0 5 * * *", start_date=pendulum.datetime(2026, 1, 1),
     catchup=False, tags=["finance", "wap"])
def daily_close():

    @task
    def write():                      # build marts into an isolated staging schema
        run_dbt("build --select marts.finance+ --target close_staging")

    @task
    def audit(_):                     # run recon + dbt tests against staging
        breaks = query_scalar(
            "select count(*) from close_staging.recon_report "
            "where recon_status = 'BREAK'"
        )
        if breaks > 0:
            # circuit breaker: do NOT publish, page the on-call, keep last-good live
            raise AirflowFailException(f"RECON BREAK: {breaks} day/currency rows fail to tie out")

    @task
    def publish(_):                   # atomic swap staging -> live; nothing partial goes live
        run_sql("alter table analytics.fct_payments swap with close_staging.fct_payments")

    publish(audit(write()))

daily_close()

Deep dive · Failure modes

The third lever. The interviewer points at a ⚡ on the board — "the payments API is down for two hours during your run. Then what?" This is Chapter 07. Handle it by failure domain, not as one blob.

Steps 5–6 · Trade-offs & evolution

Step back and make the trade-offs explicit — the interviewer wants to hear you choose, with reasons anchored to the SLAs you wrote in Step 2.

Then Step 6 — evolution and what breaks next, which is your on-ramp to L4/L5 (Ch. 08):

• At 10× volume, the daily 7-day lookback re-scan gets expensive. First lever: partition pruning on event_date so the merge only touches the lookback partitions. Next: dbt's microbatch strategy to parallelize by day.
• When a second consumer appears (data science wants raw events), the landing tables become a shared surface — that's when I'd formalize a data contract and move to an Iceberg/Delta lakehouse for schema evolution and time-travel, so a restatement is a snapshot, not a rebuild.
• When ops genuinely needs sub-minute, add a streaming path for the operational view only (kappa-style), while the reconciled daily batch stays the finance source of truth. Don't make finance pay the complexity tax for an ops requirement.
• What breaks next: the single Airflow instance and the manual break triage. The L5 move is a platform play — self-serve contracts, automated triage, org-wide freshness SLOs — but I'd name that as a direction, not build it for one pipeline.

A second prompt to rehearse

Run the same six steps on a different shape of problem so the framework is muscle memory, not a memorized script. This one inverts the priorities — latency now matters more than exact reconciliation.

How you'd attack it — the outline, not the full walk (that's your rehearsal):

• Clarify & SLAs. Freshness target (couple of minutes ⇒ streaming or micro-batch is justified here). Volume and peak/burst. Correctness bar — is "approximately right, fast" acceptable? For product analytics, usually yes, which flips the central trade-off versus the finance scenario.
• Architecture. Clients → Kafka (partition by user/session key for ordering) → stream processor (Flink or Spark Structured Streaming) → serving store for dashboards, with a parallel raw sink to the lake for replayable batch correction. Draw the streaming row on top, the batch-correction row beneath — a lambda/kappa decision to name explicitly (Ch. 08).
• Late data — the marquee deep dive here (Ch. 04). Offline mobile ⇒ event-time processing with watermarks and bounded allowed lateness; windowing (session windows for funnels) that emit on watermark and correct on late arrivals; the explicit completeness-vs-latency trade-off. This is where the level climb lives in this scenario.
• Reconciliation/validation (Ch. 06). Lighter than finance, but not zero: volume/freshness anomaly monitors (the "signups dropped" alert is literally a DQ control chart), schema-on-write contracts for the event payloads, and a nightly batch recount from the lake as the corrective source of truth for the fast-but-approximate streaming numbers.
• Failure modes (Ch. 07). Consumer lag & back-pressure, checkpoint/restart for exactly-once-ish state, poison events to a dead-letter, and the dedup key for at-least-once delivery from clients that retry uploads.
• Trade-offs & evolution. Streaming buys latency at the cost of operational complexity and harder correctness — the mirror image of scenario one. State that contrast out loud; it proves you're matching the design to requirements rather than pattern-matching to a favorite stack.

✓ Check yourself

Answer from memory before opening each one.

1. The prompt describes an app database, a payments provider, late events and a requirement that revenue tie out to the processor. Which of those drive the design, and what do they decide about batch versus streaming?

   Show answer

   Two sources with different semantics, explicit late data, and a hard reconciliation against an external source of truth. Together they make this a financial pipeline where correctness beats latency. Nothing needs sub-second freshness and streaming would make reconciliation harder, so choose batch or micro-batch, hourly for ops and a daily close for finance. See clarify and set SLAs.

2. A refund for a capture made three weeks ago arrives today. The incremental model re-reads a seven-day lookback. Does the filter go on event time or ingestion time, and which partition does the refund end up in?

   Show answer

   On ingestion time, because the refund arrives today with an old event time and an event-time filter would never re-read it. The merge on the payment event id then writes it into its own event-date partition, so the original day is corrected and today's revenue is untouched. See late-arriving data.

3. The recon query joins source and target with a full outer join and tiers its result into OK, WARN and BREAK. What does the join type catch that an inner join would hide, and who sets the tiers?

   Show answer

   It surfaces days or currencies present on one side only: a settlement with no matching events means a missed ingest, and events with no settlement may be unsettled captures. An inner join would drop both silently. Finance agrees the tolerance up front, with zero for exact ties and a small rounding band logged as a warning. See reconciliation.

4. The payments API is down for two hours during the run. What happens to orders marts and payments marts, and how do events missed in that window get back?

   Show answer

   Orders publish on time and payments hold with a stale flag, because failure domains are separate. A poll-based reconciler fetches events the webhooks missed, and the daily settlement reconciliation is the backstop: a permanently lost event shows up as a break and gets backfilled. Webhooks are at-least-once and deduplicated on event id. See failure modes.

5. Ops now wants dashboards under a minute stale, while finance still needs exact daily numbers. Do you move the whole pipeline to streaming? Argue it.

   Show answer

   No. Add a streaming path for the operational view only and keep the reconciled batch as the finance source of truth, so finance does not pay the complexity tax for an ops requirement. The deciding factors are the freshness SLA each consumer has and how much reconciliation difficulty each can accept. See trade-offs and evolution.

6. Take the clickstream prompt with offline mobile clients. Which decisions from the payments design flip, and which carry over unchanged?

   Show answer

   Latency now dominates and approximate numbers are acceptable, so streaming with event-time watermarks and bounded allowed lateness is justified. What carries over is the dedup key for at-least-once uploads, a dead-letter path, freshness and volume monitors, and a nightly batch recount from the lake as the corrective source. See a second prompt to rehearse.

Takeaway

• Run the same six steps on any prompt: clarify, set SLAs, sketch breadth first, deep-dive late data, reconciliation and failure, then state trade-offs and evolution.
• Name the load-bearing parts of the prompt back to the interviewer, and choose batch or streaming against the SLAs rather than by reflex.
• Late data is absorbed by a lookback on arrival time and an idempotent merge on a natural key, with each row landing in its event date.
• Reconcile against an independent source of truth by day and currency, with tiers agreed in advance, inside a write-audit-publish gate.
• Handle failure by domain, and let the reconciliation backstop double as the recovery mechanism for events lost in an outage.
• Add a streaming path only for the consumer that needs it, and keep the reconciled batch as the finance source of truth.
• The same process can reach the opposite design when requirements differ, as the clickstream prompt shows.

Next, test the individual mechanisms with questions at each level, with answers climbing from foundational to staff. → Practice Questions

Further reading

• Stripe: Receive Stripe events in your webhook endpoint — Stripe docs. At-least-once delivery, retries and duplicate events, which is why the pipeline dedups on event id.
• Stripe: Payout reconciliation report — Stripe docs. How a processor's own report ties payouts to the transactions behind them.
• Stripe: Idempotent requests — Stripe API reference. A production idempotency-key design.
• dbt: About incremental strategy — dbt Labs docs. Merge, insert_overwrite and microbatch, which the model in this chapter relies on.
• Iceberg: Branching and Tagging — Apache Iceberg docs. Branches as the staging area in write-audit-publish.