Data Engineering Foundations
Advanced 18 min read Data Engineering Foundations

Case Study: End-to-End Data Platform

Reconciling every dollar, end to end

A worked end-to-end design: a retail company ingests clickstream, orders and CRM into a lakehouse, models revenue in gold, governs access, and discovers the design weakness near 'production'.

case studydesignlakehousereconciliationend-to-enddelta
Prerequisites: Warehouse vs Lake Data Pipeline Fundamentals
Before we start

What you will learn

  • Design a defensible end-to-end data platform for a concrete business
  • Trace revenue from sources through layers to a reconciled dashboard
  • Identify where naive architectures break and how governance redeems them
Introduction

What is this?

A retail company ('Acme Outfitters') sells online and in 40 stores. Analysts want: daily revenue by channel, category and store; live fraud alerts; and marketing audiences. This case study designs the platform that delivers those, then shows the incident when a schema change nearly shipped wrong revenue.

Motivation

Why do you need it?

  • End-to-end thinking is how hiring happens and how real platforms survive.
  • Reconciliation between layers proves the pipeline trustworthy — a top interviewer topic.
  • The failure mode shown (silent schema drift) is the most common production data incident.
Think about it

Mental model

Imagine

A bank's double-entry ledger

Think of

An end-to-end data platform

A bank can tell you the money in vs money out at any minute, reconciled with the balance. Your platform should be able to do the same with rows: what entered, what transformed, what's served.

Mechanics

How it works

  • Ingest: order events stream to Kafka (near-real-time) and nightly DB snapshots land in S3 bronze.
  • Land bronze as immutable Delta tables; register in the catalog with owners and lineage.
  • Silver: dedupe orders by order_id, join to store/customer dimensions; keep natural keys.
  • Gold: daily revenue fact + channel/store/category aggregates, defined once in the semantic layer.
  • Serve: dashboards read gold; fraud reads real-time windows; marketing reads customer segments from silver.
  • Governance: row-level access for stores, column masking for card data, quality gates at each promotion, per-hour freshness SLA.

Acme's end-to-end revenue pipeline.

Orders (Kafka)
DB snapshots
Bronze (Delta)
Silver
Gold
Serve + govern
Real world

In production

  • Reconciliation: the gold revenue by channel is compared nightly to the sum of silver order totals and to the SQL from the accounting source — any mismatch fails the run.
  • Freshness: gold must refresh by 06:00 ET; the platform alerts at 06:10 via PagerDuty.
  • Access: store managers read only their store's rows (row-level security); marketing reads only non-PII columns.
Guidelines

Best practices

  • Make the gold layer the single source for every certified metric; dashboards must not re-derive definitions.
  • Gate each layer promotion with quality checks keyed to the natural key of the layer.
  • Automate RLS and masking centrally so a new report can't accidentally expose PII.
  • Reconcile gold to source monthly as a formal audit; document drift explanation.
Watch out

Common mistakes

  • Letting each dashboard define 'revenue' its own way — divergence is guaranteed.
  • No natural-key deduplication at silver, so retries double-count orders.
  • Publishing gold before its freshness gate passes — consumers train themselves to distrust the number.
  • Storing unmasked card PANs in bronze where governance assumed they'd always be masked upstream.
Root cause analysis

Common issues

Error
Revenue flashed a 0.7% drop nobody could explain
Root cause

A source renamed `order_amount` → `amount` and silver's SELECT * silently shrank many rows to 0/NULL

Fix

Schema contract enforcement at bronze + typed mapping, not SELECT *, and a gold-vs-silver reconciliation gate.

Error
Fraud alerts lag 9 minutes on a 2-minute SLA
Root cause

Fraud windows read gold (batch) instead of real-time silver windows

Fix

Serve the windowed fraud metric from the streaming path, keeping gold for the nightly reconciliation.

Error
Marketing audience export duplicated customers
Root cause

Customer join from CRM snapshots without a natural-key dedupe

Fix

Dedupe customer silver on crm_id with full-refresh semantics; add a uniqueness gate.

When things break

Debugging tips

  • When gold disagrees with the ledger: diff silver vs gold by natural key, then gate the joining step.
  • For freshness breaches, trace the alert's layer: extraction, silver or gold?
  • For access 'shadows' (someone sees others' stores), check RLS policy application, not the dashboard logic.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Why is reconciling gold back to silver a high-value gate?

2

SELECT * from a renamed source column is a low-risk pattern in analytics.

Go deeper

Official documentation

Delta Lake docs — schema evolution
docs