Data Engineering Take-Home Projects: 5 Realistic Briefs With Rubric

data engineering take home is the mid-and-senior interview format that decides most DE offers in 2026 — a bounded coding project (nominally 4-8 hours, realistically 15-25) that surfaces the design decisions, code-quality signals, and testing habits a whiteboard round physically cannot. Every candidate at the mid-and-senior level eventually receives one, and the outcome hinges less on "am I a good engineer" than on "do I know the five canonical brief shapes and the rubric hiring managers score them against" — the candidates who ship a strong submission are the ones who spent 20 hours on the right thing, not 40 hours on the wrong one.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer handed you a repo link with the words "spend 4-8 hours on this." It walks the five canonical data engineering take home project briefs — Brief 1 (CSV → warehouse ELT with SCD Type 2 and idempotent re-runs), Brief 2 (Kafka streaming aggregate with tumbling windows and exactly-once semantics), Brief 3 (dashboard from raw events via dbt + Metabase with an SLA-bound refresh), Brief 4 (Great Expectations data-quality suite against a dirty source), and Brief 5 (Feast-style feature store with online + offline sync and point-in-time correctness) — plus the six-axis grading rubric (correctness 30%, code quality 20%, tests 15%, docs 15%, performance 10%, extras 10%) that decodes exactly which interview signals each brief is designed to probe. Each brief pairs a teaching block with two worked-example scenarios and a full Solution Tail — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the streaming axis with the streaming practice library →.

---

On this page

• Why take-homes decide the mid-to-senior DE hire
• Brief 1 — ELT from CSV to warehouse with SCD Type 2
• Brief 2 — Kafka streaming aggregate with exactly-once
• Brief 3 — Dashboard from raw events via dbt + Metabase
• Briefs 4 + 5 + the grading rubric
• Cheat sheet — DE take-home recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why take-homes decide the mid-to-senior DE hire

Five brief shapes, one six-axis rubric — the format most senior DE offers now hinge on

The one-sentence invariant: a data engineering take home is a bounded, self-contained project (typically shipped as a Git repo with a Docker Compose stack) that gives the hiring manager the one signal a whiteboard cannot — a real, running artefact with tests, docs, and design decisions they can grade against a written rubric — and 90% of DE take-homes in 2026 are minor variations on the same five brief archetypes, scored against the same six weighted axes. Understanding the archetypes and the rubric turns "spend 4-8 hours on this" from a lottery into a solvable planning problem.

The 2026 hiring reality.

• Whiteboard rounds shrinking. Most mid-and-senior DE loops now include one take-home in place of a second on-site coding round. The candidate cost is higher (15-25 real hours), but the signal is far richer — hiring managers get a full artefact to evaluate.
• Take-homes gate the on-site. A weak take-home usually ends the loop. A strong take-home makes the follow-up on-site a conversation about your submission rather than a fresh coding gauntlet.
• Repo-and-README format has stabilised. Almost every DE take-home in 2026 arrives as (a) a GitHub repo link, (b) a brief in README.md or a Notion doc, and (c) an expectation that you'll return a Git repo (or fork) with your solution, tests, and a design writeup.
• Docker Compose is the assumed baseline. Interviewers expect docker-compose up to boot your stack — source DB, warehouse, orchestrator, and dashboard where relevant. "Runs on my machine" is a fail signal in 2026.

The five canonical brief shapes — 90% of DE take-homes are one of these.

• Brief 1 — ELT from CSV to warehouse (SCD Type 2). Read messy CSVs, dedupe on a natural key, apply Slowly Changing Dimension Type 2 for the customer/product master, ship idempotent re-runs. Postgres or Snowflake target; dbt or plain Python loader.
• Brief 2 — Streaming aggregate (Kafka). Consume from Kafka, apply a tumbling or sliding window, aggregate by key, guarantee exactly-once semantics, emit to a sink topic or a low-latency store. Kafka Streams, Flink, or confluent-kafka Python.
• Brief 3 — Dashboard from raw events. Raw event stream → dbt staging + marts → Metabase or Superset dashboard with 3-5 tiles and a documented refresh SLA. Star vs one-big-table modelling choice explained.
• Brief 4 — Data-quality tests. Great Expectations (or dbt tests) against a deliberately dirty source. Cover null rate, uniqueness, referential integrity, freshness, and one custom domain expectation. Alerting on failure.
• Brief 5 — ML feature store. Feast or in-house — online (Redis) + offline (S3 Parquet) sync, point-in-time correctness for training-time joins, feature freshness SLA.

The six-axis rubric — what interviewers actually score.

• Correctness (30%). Does the code produce the right output for the brief's spec, including the edge cases the brief calls out? This is the largest weight; a beautiful codebase that fails the acceptance test loses on this axis alone.
• Code quality (20%). Readable, factored, idiomatic. Modules with clear boundaries; functions under 40 lines; no TODO graveyard. Language-idiomatic patterns (Python type hints; SQL CTEs over subqueries; Kotlin data classes over Java POJOs).
• Tests (15%). Unit tests on the core transform; at least one integration test that runs against a real (containerised) dependency; smoke test that docker-compose up boots green. Zero tests = auto-fail at senior level.
• Documentation (15%). README with what/how-to-run/design/trade-offs/what-I-skipped sections. Inline comments only for non-obvious choices. A short design doc or architecture diagram earns full marks.
• Performance (10%). Reasonable choices — indexed joins, bounded memory, appropriate batch sizes. Not over-engineered (no Kafka for a 10-MB CSV) and not under-engineered (no single-threaded for row in csv.reader: at 10M rows).
• Extras / trade-off analysis (10%). CI setup, retrospective section, "what would change at 10× scale" paragraph, thoughtful assumption list. This is where senior candidates separate themselves.

What interviewers listen for — and what they never read.

• Read first. README (top-to-bottom, always). Design doc if present. docker-compose.yml to check for reproducibility. Test files to check what is tested, not just how many.
• Scanned second. The core transform file (usually one module: elt/pipeline.py, streaming/aggregator.py, dq/expectations.py). CI config if present.
• Rarely opened. A 500-line main.py. Generated fixtures. Anything in /vendor or /node_modules.
• Auto-fail signals. No README. No tests. docker-compose up doesn't boot. Kafka spun up for a 10 MB CSV. A single monolithic script with no modules.

The 20-hour time-box rule — and why "polished 80% beats incomplete 100%".

• The brief says 4-8; expect 15-25. Every senior DE has done a take-home. The stated budget is almost always the minimum-viable budget; realistic mid-and-senior takes 15-25 hours. Time-box strictly at 20-25; do not slide past 30.
• 80% shipped beats 100% attempted. A polished, well-tested, well-documented implementation of the core brief scores far higher than a half-finished attempt at every stretch requirement. Cut scope early; document what you skipped.
• README-first workflow. Draft the README skeleton at hour 1 (before writing code). It forces you to articulate scope, non-goals, and assumptions. Fill it in as you build; polish at hour 22.
• Reserve the last 20% for docs and tests. If hour 16 arrives with no tests and no README, stop coding features. Ship what you have with tests and docs; that beats a full-feature-set with neither.

Worked example — decode which brief a real take-home actually is

Detailed explanation. The single highest-leverage skill in take-home prep is pattern-matching — reading a brief and immediately recognising which of the five archetypes it is, because that recognition unlocks the entire stack, scaffolding, testing strategy, and cheat sheet. Walk through three anonymised real briefs and classify each.

• Brief A. "Given a nightly dump of customers.csv, load it into a warehouse. Handle name and address changes over time so we can query 'what did this customer look like on 2025-01-15'. Ship as Docker Compose."
• Brief B. "Attached is a stream of clickstream events on a Kafka topic. Compute per-user session counts in 30-minute windows. Send the results to an output topic. Handle late events and duplicates gracefully."
• Brief C. "You have orders, users, and products tables in Postgres. Build a dashboard showing DAU, top-10 products by revenue, and a 7-day rolling active-user trend. We use dbt; feel free to use Metabase or Superset."

Question. Classify each brief against the five archetypes, name the primary stack the brief implies, and identify the axis the interviewer is most heavily probing.

Input.

Brief	Archetype	Primary stack	Heaviest rubric axis
A	Brief 1 — ELT + SCD Type 2	Python/dbt + Postgres + Docker Compose	Correctness (SCD-2 close-old-open-new)
B	Brief 2 — Kafka streaming aggregate	Kafka + Kafka Streams / confluent-kafka + Docker Compose	Correctness (exactly-once + late events)
C	Brief 3 — Dashboard from raw events	Postgres + dbt + Metabase + Docker Compose	Code quality (model layering)

Code.

# Pattern-matcher — turns brief text into an archetype
def classify_brief(brief_text: str) -> str:
    """Map raw brief text to one of the five archetypes."""
    t = brief_text.lower()

    # Brief 2 — streaming
    if any(k in t for k in ("kafka", "stream", "topic", "window", "exactly-once")):
        return "Brief 2 — Kafka streaming aggregate"

    # Brief 4 — data quality
    if any(k in t for k in ("data quality", "great expectations", "expectations", "null rate", "freshness")):
        return "Brief 4 — Data quality tests"

    # Brief 5 — feature store
    if any(k in t for k in ("feature store", "feast", "online", "offline", "point-in-time", "pit")):
        return "Brief 5 — ML feature store"

    # Brief 3 — dashboard
    if any(k in t for k in ("dashboard", "metabase", "superset", "tile", "chart", "kpi")):
        return "Brief 3 — Dashboard from raw events"

    # Brief 1 — ELT / SCD (default for CSV → warehouse)
    if any(k in t for k in ("csv", "warehouse", "scd", "dedupe", "load", "elt")):
        return "Brief 1 — ELT from CSV to warehouse"

    return "unknown — request clarification"


# Classify the three real briefs
for label, text in [
    ("A", "nightly dump of customers.csv ... name and address changes over time"),
    ("B", "stream of clickstream events on a Kafka topic ... 30-minute windows"),
    ("C", "orders, users, products in Postgres ... dashboard showing DAU"),
]:
    print(f"{label}: {classify_brief(text)}")
# A: Brief 1 — ELT from CSV to warehouse
# B: Brief 2 — Kafka streaming aggregate
# C: Brief 3 — Dashboard from raw events

Step-by-step explanation.

1. Brief A contains "customers.csv", "load", and "changes over time" — the SCD-2 signature. The archetype is Brief 1. The words "so we can query 'what did this customer look like on 2025-01-15'" is the tell — this is not a plain load; it's an SCD-2 load. Interviewers are probing whether you can implement close-old-open-new correctly.
2. Brief B contains "Kafka topic", "30-minute windows", and "duplicates gracefully" — the streaming-aggregate signature. Archetype is Brief 2. The "duplicates gracefully" phrasing is the exactly-once probe; the "handle late events" is the watermarking probe. Both are correctness weights.
3. Brief C contains "dashboard", "dbt", and "DAU / top-10 / 7-day trend" — the dashboard-from-raw-events signature. Archetype is Brief 3. The "we use dbt" is the tell — interviewers want to see dbt project layering (staging → marts), tests, and a rendered dashboard. Code quality (layering) is the biggest weight here.
4. Notice that each brief telegraphs its archetype in the first two sentences. Reading the brief three times before writing any code — as the take-home Meta section always instructs — is how you catch these signals reliably.
5. If the brief matches none of the five archetypes, that's your clarifying-question moment: reach out and ask "should this be a batch or streaming pipeline?" or "is the expected output a table, a dashboard, or a service?" before you burn hours going the wrong direction.

Output.

Signal in brief	Archetype	Confidence
"kafka topic", "window", "exactly-once"	Brief 2 — streaming	high
"great expectations", "null rate", "freshness"	Brief 4 — DQ tests	high
"feature store", "feast", "online + offline"	Brief 5 — feature store	high
"dashboard", "metabase", "kpi tiles"	Brief 3 — dashboard	high
"csv", "scd", "load into warehouse"	Brief 1 — ELT	high
ambiguous mix	request clarification	—

Rule of thumb. Read the brief three times before you write one line of code. In the first read, classify the archetype. In the second read, identify the rubric axis the interviewer is heaviest on (correctness, code quality, or performance). In the third read, list every acceptance criterion — those become your test cases.

Worked example — allocating a 20-hour time budget across the rubric

Detailed explanation. A senior DE candidate does not "just start coding" on hour 1 of a take-home. They allocate hours across the six rubric axes in advance, sanity-check the allocation against the brief, then execute against the plan. Walk through the canonical 20-hour allocation for Brief 1 (ELT + SCD-2) and show the check-in points.

• Hour 0-2. Read brief three times; classify archetype; list acceptance criteria; write clarifying questions; draft README skeleton.
• Hour 2-6. Scaffold Docker Compose + Postgres + loader service; write the simplest possible end-to-end flow (CSV → staging → dim) that runs; commit early.
• Hour 6-14. Implement SCD-2 close-old-open-new; add unique constraints; make it idempotent; add primary integration test.
• Hour 14-18. Write unit tests for core transform; add smoke test; add design doc / architecture diagram.
• Hour 18-20. Polish README (run instructions, design decisions, trade-offs, what-I-skipped); final docker-compose down && docker-compose up sanity check; submit.

Question. Draft the 20-hour plan for Brief 1 and identify the two check-in points where scope must be cut.

Input.

Rubric axis	Weight	Hours allocated	Check-in threshold
Correctness	30%	8 h	must pass at hour 14
Code quality	20%	3 h	ongoing (refactor as you go)
Tests	15%	3 h	at least one test by hour 12
Docs	15%	3 h	README skeleton at hour 2
Performance	10%	1 h	benchmark at hour 15
Extras	10%	2 h	only if hours 0-18 are green

Code.

20-hour DE take-home plan — Brief 1 (ELT + SCD Type 2)
======================================================

Hour  0-2 : Read brief 3x; write clarifying questions; draft README
             skeleton with 7 sections (what, quickstart, architecture,
             assumptions, trade-offs, testing, what-I-skipped).
             Commit: initial-scaffold.

Hour  2-6 : docker-compose.yml (Postgres + loader). Loader reads CSV,
             writes to raw.customers_staging with COPY. First green
             `docker-compose up`. Commit: staging-load-works.
             CHECK-IN 1 → if hour 6 arrives with no working end-to-end,
             cut scope: drop the SCD-2 stretch, ship a plain overwrite,
             recover the rubric points elsewhere.

Hour  6-14: Implement SCD-2. Add customers_dim(customer_key, customer_id,
             name, email, valid_from, valid_to, is_current). Write the
             MERGE that closes stale rows and opens new ones. Enforce
             idempotency: run twice → identical state. Commit at each
             milestone (staging, merge, idempotency).
             CHECK-IN 2 → hour 14 must have SCD-2 passing the acceptance
             test. If not, ship SCD-1 with a "future work: SCD-2" note.

Hour 14-18: Tests. pytest for the merge transform (unit); one integration
             test that runs the full pipeline against a fresh Postgres.
             Architecture diagram (draw.io PNG committed to /docs).
             Commit: tests-and-docs.

Hour 18-20: README polish. Write: overview, quickstart (docker-compose
             up), architecture (link diagram), design decisions
             (why-SCD-2 + why-Postgres), trade-offs (batch vs stream),
             what-I-skipped (CI, alerting), testing (how to run pytest).
             Final docker-compose down && up. Commit: final.

Hour 20+  : STOP. Submit. Do not add features. If under budget, add CI
             (~30 min GitHub Actions) as a rubric-extras win.

Step-by-step explanation.

1. Hours 0-2 look wasteful ("no code yet") but they are the highest-leverage hours in the entire take-home. Every hour of reading + planning saves 3 hours of wrong-direction coding later. Draft the README skeleton in this window — it becomes your outline and your final artefact.
2. Hours 2-6 target the simplest possible end-to-end flow. Not the right flow — the simplest one. A staging table populated by COPY, with no transforms, no idempotency, no SCD-2. Getting to green here is a psychological win and a technical safety net: even if hours 6-20 disintegrate, you have a shippable minimum.
3. CHECK-IN 1 at hour 6 is a mandatory scope-cut gate. If you don't have a working end-to-end by hour 6, the plan is broken — cut the SCD-2 stretch, ship a plain overwrite, and recover points on tests and docs. Do not blindly power through.
4. Hours 6-14 implement the actual brief — SCD-2 with idempotency. This is where the correctness points live. Commit at each milestone; each commit is a fallback state in case a later change breaks the pipeline.
5. Hours 14-18 are non-negotiable for tests and design docs. This is the block that separates "shipped it" from "shipped it well" — most candidates skip this block and lose the 30% of rubric weight on tests + docs. Reserve it religiously.
6. Hours 18-20 are the README polish window. Read the README as if you were the hiring manager. Does it explain what to run? Does it defend the design decisions? Does it name the trade-offs? A clean README on a solid submission is worth an entire rubric axis.

Output.

Hour	Milestone	Rubric axis served
2	README skeleton committed	Docs
6	End-to-end docker-compose up works	Correctness (baseline)
14	SCD-2 + idempotency test passes	Correctness (senior)
16	Unit + integration tests green	Tests
18	Architecture diagram + design doc	Docs + Extras
20	README polished; final submit	Docs

Rule of thumb. Allocate hours to rubric weights, not to features. Enforce a scope-cut gate at hour 6 and again at hour 14. If either gate is missed, cut the stretch and recover points on tests and README. A 20-hour submission that hits every rubric axis beats a 30-hour one that maxes one axis and skips the others.

Worked example — the clarifying-question template that saves 8 hours

Detailed explanation. Sending 5-8 crisp clarifying questions in the first hour of a take-home is a senior signal that also functions as a scope-cut lever — the answers narrow the acceptance criteria and prevent you from over-building. Interviewers almost always appreciate them; they mark you as engaged and thoughtful. Walk through the template that generalises across all five briefs.

• Scope questions. Which acceptance criteria are must-have vs stretch? Is testing required or bonus?
• Data questions. Should I assume the CSV / Kafka topic is well-formed, or should I handle malformed inputs? Are duplicates expected?
• Deployment questions. Is a fresh Docker Compose the target, or a cloud deployment? Should the reader be able to run this on macOS / Linux only?
• Stack questions. Any preferred language / framework? Any language / framework I should avoid (e.g. no Java if the team is Python-only)?
• Time questions. Is the 4-8 hour budget a hard cap or a suggested minimum? Should I flag if I go past it?

Question. Draft five clarifying questions for a Brief 3 dashboard take-home before starting the build.

Input.

Question axis	Example question	Why it matters
Scope	"Are all 3 tiles required, or is DAU the priority?"	Cut scope early if priorities exist
Data	"Are events schema-stable, or should I handle version drift?"	Determines defensive parsing effort
Deployment	"Should this run on your team's Metabase, or ship its own?"	Own Metabase adds 2 hours; team Metabase adds 0
Stack	"dbt-core or dbt-cloud? PostgresSQL or Snowflake target?"	Affects config, adapters, and testing
Time	"Is the 4-8 hour brief a soft budget I should flag if exceeded?"	Sets your check-in habit expectation

Code.

Subject: Clarifying questions for the take-home brief

Hi [hiring manager],

Thanks for the brief — I'm planning to start on Saturday. Before I dive in,
five quick clarifying questions so I can budget time correctly:

1. SCOPE. The brief lists three dashboard tiles (DAU, top-10 products,
   7-day rolling actives). Are all three must-haves, or would you prefer
   two shipped well over three shipped shallow? I'd like to sequence
   the work.

2. DATA. Should I assume the events table is schema-stable, or should
   I defensively handle drift (extra columns, missing keys, string vs
   int for user_id)? Both are reasonable; the answer changes 2-3 hours
   of the plan.

3. DEPLOYMENT. Is the expectation that I ship a Docker Compose stack
   that boots Postgres + dbt + Metabase locally, or should I target
   your existing Metabase? Local stack adds ~2 hours but is more
   reproducible.

4. STACK. Any preference between dbt-core and dbt-cloud? Postgres and
   Snowflake as the warehouse? I'll default to dbt-core + Postgres
   unless you'd prefer otherwise.

5. TIME. The brief mentions 4-8 hours. I typically find these take
   15-20 real hours to ship well — is that acceptable, or should I
   strictly cap at 8?

Happy to make judgement calls on all five if you'd prefer, but the
answers will materially improve the submission. I'll target Monday
end-of-day for the return.

Best,
[Candidate]

Step-by-step explanation.

1. Question 1 (scope) is the highest-leverage question — the answer directly reshapes your 20-hour plan. If the hiring manager says "DAU is the priority", you skip the top-10 tile as a stretch, ship two tiles polished, and note "future work: top-10 tile" in the README.
2. Question 2 (data) surfaces the "defensive parsing" tradeoff. A brief that assumes clean data is a 4-hour brief; a brief that requires malformed-input handling is a 10-hour brief. The answer tells you how to allocate hours.
3. Question 3 (deployment) preempts the biggest hidden-scope trap: standing up a full local stack (Postgres + dbt + Metabase in Compose) adds real hours. If the hiring team runs their own Metabase, target theirs; you save 2 hours.
4. Question 4 (stack) protects against wasting time on the wrong tool. If the team is dbt-cloud, shipping dbt-core forces them to translate; if the team is Snowflake, shipping Postgres forces them to re-run everything.
5. Question 5 (time) sets an expectation that you'll take real hours, not stated hours. This is a senior signal — it demonstrates that you've done take-homes before and know the stated budget is a floor, not a ceiling.

Output.

Response received	Action to take
"DAU is priority; other tiles nice-to-have"	Ship 1 tile polished; skip 2 tiles
"Schema is stable; don't defensively parse"	Save 3 hours on error handling
"Target our Metabase; skip local one"	Save 2 hours on stack setup
"We use dbt-cloud + Snowflake"	Use dbt-cloud + Snowflake trial account
"15-20 hours is fine; flag if you go past 25"	Set explicit hour-25 hard-cap

Rule of thumb. Send 5-8 crisp clarifying questions in the first hour of every take-home. The answers narrow scope by 20-40%. Hiring managers see the questions as senior signal; they never see silence as senior signal.

Senior interview question on take-home strategy

A senior interviewer might ask: "You've just received a DE take-home brief for a CSV-to-warehouse loader with SCD Type 2 and idempotent re-runs, nominal 4-8 hour budget. Walk me through how you allocate the 20 real hours you'll spend on it — clarifying questions, scope cuts, testing strategy, README structure, and the check-in points where you'd cut features to preserve rubric coverage."

Solution Using rubric-first hour allocation + scope-cut checkpoints + README-driven scaffolding

DE take-home 20-hour execution plan — Brief 1 (ELT + SCD-2)
============================================================

HOUR 0-1 — clarifying questions
-------------------------------
Send 5 questions before touching code:
  1. Which acceptance criteria are must-have vs stretch?
  2. Is the CSV guaranteed clean, or should I handle malformed rows?
  3. Docker Compose expected, or cloud deployment acceptable?
  4. Any preferred stack (dbt vs plain Python; Postgres vs Snowflake)?
  5. Is the 4-8h budget a hard cap? I typically ship in 15-20h.

HOUR 1-2 — README skeleton
--------------------------
Commit an empty-but-structured README:
  # Project title
  ## Quickstart          (docker-compose up + expected output)
  ## Architecture        (diagram placeholder)
  ## Assumptions         (list unknowns)
  ## Design decisions    (why-SCD-2, why-Postgres, why-Docker)
  ## Trade-offs          (batch vs stream, dedupe strategy)
  ## Testing             (how to run pytest)
  ## What I skipped      (stretch items + reason)

HOUR 2-6 — end-to-end scaffold
------------------------------
docker-compose.yml with two services (postgres, loader).
Loader script:
  1. Read CSV into pandas DataFrame.
  2. Validate schema with pydantic.
  3. COPY into raw.customers_staging.
  4. INSERT INTO customers_dim SELECT DISTINCT ... FROM staging.
Green docker-compose up. Commit: staging-load-works.
CHECK-IN 1: end-to-end green? If NO -> cut SCD-2 scope now.

# HOUR 6-14 — SCD-2 implementation
# elt/scd2.py
"""SCD Type 2 merge for customers_dim."""
from datetime import datetime, timezone

import psycopg2

SCD2_MERGE_SQL = """
BEGIN;

-- 1. Close out rows whose natural key exists in staging AND whose
--    tracked attributes differ (name or email changed).
UPDATE customers_dim AS d
   SET valid_to    = %(now)s,
       is_current  = FALSE
  FROM raw.customers_staging AS s
 WHERE d.customer_id = s.customer_id
   AND d.is_current   = TRUE
   AND (d.name  IS DISTINCT FROM s.name
     OR d.email IS DISTINCT FROM s.email);

-- 2. Insert new rows for
--    (a) natural keys not in customers_dim, OR
--    (b) natural keys just closed in step 1 (attribute changed).
INSERT INTO customers_dim
       (customer_id, name, email, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.email, %(now)s, NULL, TRUE
  FROM raw.customers_staging AS s
 WHERE NOT EXISTS (
        SELECT 1
          FROM customers_dim d
         WHERE d.customer_id = s.customer_id
           AND d.is_current   = TRUE
   );

-- 3. Truncate staging so re-runs start clean.
TRUNCATE raw.customers_staging;

COMMIT;
"""


def apply_scd2(conn) -> None:
    """Apply SCD-2 merge; idempotent — re-running is a no-op if
    no rows changed since the previous run."""
    with conn:
        with conn.cursor() as cur:
            cur.execute(SCD2_MERGE_SQL, {"now": datetime.now(timezone.utc)})

# HOUR 14-18 — tests
# tests/test_scd2.py
import psycopg2
import pytest

from elt.scd2 import apply_scd2
from elt.loader import load_csv


@pytest.fixture
def fresh_db():
    conn = psycopg2.connect("host=localhost dbname=test user=test password=test")
    with conn, conn.cursor() as cur:
        cur.execute("TRUNCATE customers_dim, raw.customers_staging RESTART IDENTITY;")
    yield conn
    conn.close()


def test_first_load_populates_dim(fresh_db):
    load_csv(fresh_db, "tests/fixtures/customers_day1.csv")
    apply_scd2(fresh_db)
    with fresh_db.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM customers_dim WHERE is_current;")
        assert cur.fetchone()[0] == 3


def test_second_load_no_changes_is_noop(fresh_db):
    # First run
    load_csv(fresh_db, "tests/fixtures/customers_day1.csv")
    apply_scd2(fresh_db)
    # Second run — same CSV, no changes
    load_csv(fresh_db, "tests/fixtures/customers_day1.csv")
    apply_scd2(fresh_db)
    with fresh_db.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM customers_dim;")
        assert cur.fetchone()[0] == 3   # idempotent — no duplicate rows


def test_email_change_closes_old_opens_new(fresh_db):
    load_csv(fresh_db, "tests/fixtures/customers_day1.csv")
    apply_scd2(fresh_db)
    load_csv(fresh_db, "tests/fixtures/customers_day2_email_changed.csv")
    apply_scd2(fresh_db)
    with fresh_db.cursor() as cur:
        cur.execute("""
            SELECT COUNT(*)
              FROM customers_dim
             WHERE customer_id = 1;
        """)
        assert cur.fetchone()[0] == 2   # old (closed) + new (current)

Step-by-step trace.

Hour	Deliverable	Rubric axis served	Cumulative rubric coverage
1	5 clarifying questions sent	Extras (senior signal)	5%
2	README skeleton committed	Docs (structure)	15%
6	End-to-end green docker-compose up	Correctness (baseline)	40%
14	SCD-2 merge + idempotency test passing	Correctness (senior)	65%
16	3 pytest tests green (unit + integration)	Tests	80%
18	Architecture diagram + design doc	Docs + Extras	92%
20	README polished; final submit	Docs	100%

After the 20-hour execution, the submission covers every rubric axis: correctness via a working idempotent SCD-2 merge, code quality via factored modules with type hints and pydantic validation, tests via three pytest cases including idempotency, docs via a 7-section README plus architecture diagram, performance via indexed joins and bounded batch inserts, and extras via the clarifying-question paper trail and an optional GitHub Actions CI config committed if the plan runs under budget.

Output:

Rubric axis	Weight	Coverage achieved
Correctness	30%	Full — SCD-2 close-old-open-new + idempotency test
Code quality	20%	Full — factored modules, type hints, pydantic
Tests	15%	Full — 3 pytest cases + smoke test
Documentation	15%	Full — 7-section README + architecture diagram
Performance	10%	Partial — indexed joins; batch inserts; benchmark section noted as future work
Extras	10%	Full — clarifying questions + CI config + retrospective

Why this works — concept by concept:

• Rubric-first hour allocation — the plan assigns hours to rubric weights, not to features. Correctness gets 8 hours because it's 30% of the score; docs get 3 hours because they're 15%. This is the mental model that beats "just start coding" every time.
• Scope-cut checkpoints — CHECK-IN 1 at hour 6 and CHECK-IN 2 at hour 14 are mandatory gates. If either is missed, scope drops. This preserves rubric coverage under adverse conditions; without gates, one blocker devours the entire budget.
• README-driven scaffolding — starting with the README skeleton at hour 2 forces you to articulate scope, non-goals, and trade-offs before you code. The README becomes your outline; filling it in as you build guarantees you never ship a code-only submission with no docs.
• Idempotency test as an executable acceptance criterion — the test_second_load_no_changes_is_noop test is the single highest-signal test in the entire submission. It proves the pipeline is safe to re-run — the single most-probed correctness axis in Brief 1 hiring.
• Cost — 20 real hours (not the stated 4-8), one Git repo, one Docker Compose file, seven README sections, three pytest cases, one architecture diagram. Every artefact maps to a rubric weight; nothing is decorative. The eliminated cost is the 10-15 hours senior candidates typically lose to over-building the wrong axis.

SQL
Topic — sql
SQL take-home warm-up problems

Practice →

ETL Topic — etl ETL practice for DE take-home pipelines

Practice →

---

2. Brief 1 — ELT from CSV to warehouse with SCD Type 2

The most common DE take-home archetype — messy CSV in, dimensional warehouse out, idempotent every run

The one-sentence invariant: Brief 1 is the pattern where the candidate must load one or more messy CSVs into a warehouse dimensional model, dedupe on natural keys, preserve historical attribute changes via SCD Type 2 (valid_from / valid_to / is_current), and guarantee that running the pipeline twice against the same input yields byte-identical warehouse state — this brief tests three axes simultaneously (correctness on SCD-2 close-old-open-new, code quality on the loader factoring, and tests on the idempotency invariant) and is the single most common data engineering take home project archetype in 2026 hiring loops.

Where it shows up.

• Company signal. Brief 1 dominates mid-market SaaS, e-commerce, and fintech take-homes — anywhere the source system is transactional and the warehouse is Snowflake, BigQuery, Redshift, or Postgres.
• Data volume. Typically 10K-1M rows across 2-4 CSVs (customers, orders, products, addresses). Real production sizes; not toy 100-row files.
• Time budget claim. "4-6 hours" is the stated budget; expect 15-20 real hours to hit every rubric axis.
• Return format. Git repo with docker-compose.yml + README.md + elt/ module + tests/ folder + optional docs/ with architecture diagram.

What interviewers listen for.

• Do you name SCD Type 2 unprompted? — required senior signal.
• Do you write the idempotency test as an explicit pytest case? — senior signal.
• Do you enforce UNIQUE (customer_id, is_current) where is_current = TRUE at the schema level? — senior signal (catches double-open bugs).
• Do you separate staging from dim tables? — required (skipping staging is a code-quality red flag).
• Do you validate incoming CSV rows with pydantic / dataclass validators before loading? — senior signal.

The 2026 reality — dbt + Snowflake is the reference stack, but plain Python + Postgres wins on Docker-Compose reproducibility.

• Reference stack. dbt-core + Snowflake trial + dbt snapshots for SCD-2. Cleanest, most idiomatic, best for candidates targeting analytics-engineering-flavoured DE roles.
• Reproducible stack. Python (pandas or duckdb) + Postgres + docker-compose. No cloud accounts required; boots in 30 seconds; interviewers can run it on any laptop. Wins on the "can I run it?" axis.
• Anti-stack. A Kafka + Kubernetes + Airflow setup for a 100 K-row CSV. Over-engineering is an auto-fail signal — interviewers read it as "does not calibrate complexity to problem size."

The SCD Type 2 mental model — close-old-open-new every attribute change.

• Track columns. Decide which attributes are SCD-2-tracked (name, email, address) vs SCD-1-overwrite (last_login_ts). Only tracked-attribute changes trigger a new row.
• Close-old. On attribute change, update the existing current row: valid_to = now(), is_current = FALSE.
• Open-new. Insert a new row with valid_from = now(), valid_to = NULL, is_current = TRUE.
• Point-in-time query. SELECT * FROM customers_dim WHERE customer_id = 42 AND '2025-06-01' BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31') returns the customer as of 2025-06-01.

The failure modes senior interviewers pre-empt.

• Double-open. Bug where the merge inserts a new row without closing the old one; two is_current = TRUE rows exist for the same natural key. Prevent with a partial unique index: CREATE UNIQUE INDEX ON customers_dim (customer_id) WHERE is_current;.
• Non-idempotent MERGE. Bug where re-running the pipeline with unchanged input still writes new rows. Prevent with the IS DISTINCT FROM check on tracked attributes — if nothing changed, the UPDATE and INSERT both hit zero rows.
• Missing dedupe. Bug where the same customer appears twice in the CSV and both land in customers_dim. Prevent with SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, updated_at DESC in staging.
• Overwriting instead of appending. Bug where the loader truncates customers_dim before every load. Prevent by only ever appending to dim tables; truncation should apply to staging only.

Worked example — end-to-end pandas + Postgres SCD-2 loader

Detailed explanation. Walk through the canonical Brief 1 implementation: a Python loader reads customers.csv, validates rows with pydantic, COPYs into a staging table, then runs the SCD-2 merge. Every artefact — schema, loader, merge SQL, entry point — is production-shaped but small enough to grade in an hour.

• Files. elt/schema.sql (DDL), elt/loader.py (CSV read + validate + COPY), elt/scd2.py (merge), elt/main.py (entry point), docker-compose.yml (Postgres + loader).
• Runtime. docker-compose up boots Postgres, runs migrations, executes the loader against data/customers.csv, exits 0. Full pipeline in under 30 seconds on a laptop.

Question. Implement the schema, loader, and merge for a customers SCD-2 loader that ingests a CSV with columns customer_id, name, email, updated_at.

Input.

Component	Purpose
raw.customers_staging	Landing zone; truncated on each run
customers_dim	SCD-2 dimension; append-only
elt/loader.py	Read CSV; validate; COPY into staging
elt/scd2.py	Close-old-open-new merge
docker-compose.yml	Postgres 16 + loader service

Code.

-- elt/schema.sql
CREATE SCHEMA IF NOT EXISTS raw;

CREATE TABLE IF NOT EXISTS raw.customers_staging (
    customer_id  BIGINT      NOT NULL,
    name         TEXT        NOT NULL,
    email        TEXT        NOT NULL,
    updated_at   TIMESTAMPTZ NOT NULL
);

CREATE TABLE IF NOT EXISTS customers_dim (
    customer_key  BIGSERIAL   PRIMARY KEY,
    customer_id   BIGINT      NOT NULL,
    name          TEXT        NOT NULL,
    email         TEXT        NOT NULL,
    valid_from    TIMESTAMPTZ NOT NULL,
    valid_to      TIMESTAMPTZ,
    is_current    BOOLEAN     NOT NULL DEFAULT TRUE
);

-- Prevent double-open: only one current row per natural key
CREATE UNIQUE INDEX IF NOT EXISTS idx_customers_dim_current
    ON customers_dim (customer_id)
 WHERE is_current;

-- Speed up point-in-time queries
CREATE INDEX IF NOT EXISTS idx_customers_dim_pit
    ON customers_dim (customer_id, valid_from, valid_to);

# elt/loader.py
"""Load customers CSV into raw.customers_staging via COPY."""
from datetime import datetime
from pathlib import Path

import psycopg2
from pydantic import BaseModel, EmailStr, ValidationError


class CustomerRow(BaseModel):
    customer_id: int
    name: str
    email: EmailStr
    updated_at: datetime


def load_csv(conn, csv_path: Path) -> int:
    """Validate + COPY the CSV into staging. Returns row count."""
    valid_rows: list[str] = []
    errors: list[str] = []

    with open(csv_path) as fh:
        header = fh.readline()          # skip header
        for lineno, line in enumerate(fh, start=2):
            cid, name, email, updated_at = line.rstrip("\n").split(",")
            try:
                CustomerRow(customer_id=cid, name=name, email=email, updated_at=updated_at)
            except ValidationError as exc:
                errors.append(f"line {lineno}: {exc.errors()[0]['msg']}")
                continue
            valid_rows.append(line)

    if errors:
        print(f"warning: {len(errors)} malformed rows dropped; first: {errors[0]}")

    with conn, conn.cursor() as cur:
        cur.execute("TRUNCATE raw.customers_staging;")
        cur.copy_expert(
            "COPY raw.customers_staging(customer_id, name, email, updated_at) FROM STDIN CSV",
            fh_like_from(valid_rows),
        )
        cur.execute("SELECT COUNT(*) FROM raw.customers_staging;")
        return cur.fetchone()[0]


def fh_like_from(rows: list[str]):
    """Wrap a list of CSV lines as a file-like object for COPY."""
    import io
    return io.StringIO("".join(rows))

Step-by-step explanation.

1. The schema separates raw.customers_staging (truncated on each run; landing zone) from customers_dim (append-only; SCD-2). Never mix them — reviewers read a truncated customers_dim as an auto-fail.
2. The partial unique index WHERE is_current is the single most important schema-level guard against double-open bugs. If the SCD-2 merge logic ever tries to insert a second is_current = TRUE row for the same customer_id, Postgres rejects the transaction. This turns a subtle correctness bug into a loud failure.
3. CustomerRow(BaseModel) with pydantic validates each row before it reaches Postgres. Bad emails, non-integer IDs, and malformed timestamps are dropped with a warning rather than crashing the load. This is the "defensive parsing" senior signal.
4. copy_expert uses Postgres COPY protocol — the fastest bulk-load mechanism. INSERT ... VALUES (...) in a loop is 100× slower and shows up as a performance-axis red flag in the review.
5. The warning: ... malformed rows dropped log line is a senior touch — it tells the interviewer the loader is defensive, not silent, when the source data is dirty. Silence on malformed rows is a correctness-axis red flag.

Output.

Run	CSV rows	Valid rows	Staging count	Dim rows added
1 (bootstrap)	10,000	9,998 (2 bad emails)	9,998	9,998
2 (no changes)	10,000	9,998	9,998	0
3 (email change on 100 rows)	10,000	9,998	9,998	100 (new) + 100 (closed)

Rule of thumb. For every Brief 1 loader, (a) separate staging from dim, (b) use a partial unique index on (natural_key) WHERE is_current as your double-open defense, (c) validate rows with pydantic before COPY, and (d) COPY not INSERT. Skipping any of these four costs a rubric axis.

Worked example — the idempotency test that proves the SCD-2 merge is safe

Detailed explanation. The single highest-signal test in a Brief 1 submission is the idempotency test — running the pipeline twice against the same input must produce identical warehouse state. This test alone catches the majority of SCD-2 bugs (double-open, non-idempotent MERGE, missing dedupe) and is the test hiring managers open first. Walk through the full pytest case.

• Fixture. Fresh Postgres via pytest-docker; migrations applied; both tables truncated.
• Scenario 1. Load CSV twice; assert SELECT COUNT(*) FROM customers_dim is identical.
• Scenario 2. Load CSV; change one email; load again; assert one old row is closed and one new row is opened.
• Scenario 3. Load CSV; load a totally different CSV; assert historical rows are preserved (never deleted).

Question. Write the pytest module that covers the three idempotency scenarios and the double-open defense.

Input.

Test case	Setup	Expectation
test_repeat_load_is_noop	Load same CSV twice	Row count unchanged after 2nd run
test_email_change_closes_and_opens	Load; change email; load	1 row closed, 1 row opened
test_history_preserved	Load A; load B (disjoint)	A's rows still queryable
test_double_open_rejected	Manually insert bad row	Postgres raises UniqueViolation

Code.

# tests/test_scd2_idempotency.py
import pytest
import psycopg2
from psycopg2 import errors as pg_errors

from elt.loader import load_csv
from elt.scd2 import apply_scd2


@pytest.fixture
def conn():
    c = psycopg2.connect("host=localhost dbname=test user=test password=test")
    with c, c.cursor() as cur:
        cur.execute("TRUNCATE customers_dim, raw.customers_staging RESTART IDENTITY;")
    yield c
    c.close()


def test_repeat_load_is_noop(conn, tmp_path):
    csv = tmp_path / "day1.csv"
    csv.write_text(
        "customer_id,name,email,updated_at\n"
        "1,Alice,[e-mail],2025-01-01T00:00:00Z\n"
        "2,Bob,[e-mail],2025-01-01T00:00:00Z\n"
    )
    # Run 1
    load_csv(conn, csv); apply_scd2(conn)
    # Run 2 (same CSV)
    load_csv(conn, csv); apply_scd2(conn)

    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM customers_dim;")
        assert cur.fetchone()[0] == 2   # no duplicates


def test_email_change_closes_and_opens(conn, tmp_path):
    day1 = tmp_path / "day1.csv"
    day1.write_text(
        "customer_id,name,email,updated_at\n"
        "1,Alice,[e-mail],2025-01-01T00:00:00Z\n"
    )
    day2 = tmp_path / "day2.csv"
    day2.write_text(
        "customer_id,name,email,updated_at\n"
        "1,Alice,[e-mail],2025-02-01T00:00:00Z\n"
    )
    load_csv(conn, day1); apply_scd2(conn)
    load_csv(conn, day2); apply_scd2(conn)

    with conn.cursor() as cur:
        cur.execute("""
            SELECT email, is_current, valid_to IS NULL AS still_open
              FROM customers_dim
             WHERE customer_id = 1
             ORDER BY valid_from;
        """)
        rows = cur.fetchall()
    assert len(rows) == 2
    assert rows[0] == ("[e-mail]",   False, False)   # closed
    assert rows[1] == ("[e-mail]", True,  True)    # opened


def test_double_open_rejected(conn):
    # Insert one current row; attempt to insert a second current row.
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO customers_dim(customer_id, name, email, valid_from, is_current)
            VALUES (99, 'X', '[e-mail]', now(), TRUE);
        """)
    conn.commit()

    with pytest.raises(pg_errors.UniqueViolation):
        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO customers_dim(customer_id, name, email, valid_from, is_current)
                VALUES (99, 'Y', '[e-mail]', now(), TRUE);
            """)
        conn.commit()
    conn.rollback()

Step-by-step explanation.

1. test_repeat_load_is_noop is the idempotency proof. If the merge is broken (missing IS DISTINCT FROM check on tracked attributes), the second run inserts duplicate rows and the assert fails. This test alone catches the single most common Brief 1 bug.
2. test_email_change_closes_and_opens proves SCD-2 semantics. The first row must be closed (is_current = FALSE, valid_to IS NOT NULL); the second row must be opened (is_current = TRUE, valid_to IS NULL). Both must exist — deleting the old row is an SCD-1 pattern, not SCD-2.
3. test_double_open_rejected proves the schema-level guard. The partial unique index rejects any attempt to have two current rows for the same natural key. This test is defensive: if a future refactor breaks the merge, the schema catches it.
4. Missing from this fixture is a pytest-docker boot for a fresh Postgres. In a real submission, use pytest-docker or testcontainers-python so the tests boot their own Postgres. The interviewer's pytest -q should run green out of the box.
5. Committing these three tests in tests/test_scd2_idempotency.py sends the strongest possible signal: not just "I know SCD-2" but "I've built one before and I know the bugs to prevent."

Output.

Test	What it proves	Bugs caught
test_repeat_load_is_noop	Idempotency	Missing IS DISTINCT FROM; missing dedupe
test_email_change_closes_and_opens	SCD-2 semantics	Overwrite instead of close-old-open-new
test_history_preserved	Append-only invariant	Truncating dim in loader
test_double_open_rejected	Schema-level guard	Broken merge that opens two current rows

Rule of thumb. For every Brief 1 submission, ship at minimum an idempotency test, a change-detection test, and a double-open test. These three cover ~80% of the correctness axis. Missing them turns a submission from "senior" to "mid" in the reviewer's mental model.

Senior interview question on Brief 1 ELT + SCD Type 2

A senior interviewer might ask: "You've received a Brief 1 take-home: load customers.csv and orders.csv into a Postgres warehouse with SCD Type 2 on customers, dedupe on order_id for orders, guarantee idempotent re-runs. Walk me through the dbt project layout, the snapshot config, the tests you'd write, the Docker Compose stack, and the README structure."

Solution Using dbt snapshots + surrogate keys + docker-compose + dbt tests

# docker-compose.yml
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: dbt
      POSTGRES_PASSWORD: dbt
      POSTGRES_DB: warehouse
    ports: ["5432:5432"]
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "dbt"]
      interval: 3s

  loader:
    build: ./loader
    depends_on:
      postgres:
        condition: service_healthy
    volumes:
      - ./data:/data:ro
    command: python -m loader.main /data/customers.csv /data/orders.csv

  dbt:
    build: ./dbt
    depends_on:
      loader:
        condition: service_completed_successfully
    environment:
      DBT_PROFILES_DIR: /workspace
    volumes:
      - ./dbt:/workspace
    command: bash -c "dbt deps && dbt snapshot && dbt run && dbt test"

# dbt/snapshots/customers_snapshot.yml
snapshots:
  - name: customers_snapshot
    relation: source('raw', 'customers')
    config:
      strategy: check
      unique_key: customer_id
      check_cols: [name, email]
      updated_at: updated_at

-- dbt/models/marts/dim_customers.sql
-- Wrap the snapshot as the current dim view for downstream marts.
{{ config(materialized='view') }}

SELECT
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'dbt_valid_from']) }} AS customer_key,
    customer_id,
    name,
    email,
    dbt_valid_from AS valid_from,
    dbt_valid_to   AS valid_to,
    dbt_valid_to IS NULL AS is_current
FROM {{ ref('customers_snapshot') }};

# dbt/models/marts/schema.yml — the tests hiring managers grade
version: 2

models:
  - name: dim_customers
    columns:
      - name: customer_key
        tests: [unique, not_null]
      - name: customer_id
        tests: [not_null]
      - name: email
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "email LIKE '%@%'"

  - name: fct_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_key

Step-by-step trace.

Layer	Component	Purpose
Compose	postgres + loader + dbt	Boot-once local warehouse
Loader	pandas + pydantic + COPY	Raw CSV → raw.customers + raw.orders
dbt snapshot	strategy: check, check_cols	SCD-2 close-old-open-new automatic
dbt model	dim_customers view over snapshot	Downstream marts see a clean dim
dbt tests	unique + not_null + relationships	Ship 6-10 tests without writing SQL
Idempotency	dbt snapshot is idempotent by design	Re-run = no-op if no attribute change

After docker-compose up, Postgres boots, the loader ingests both CSVs into raw.customers and raw.orders, dbt snapshots customers (producing SCD-2 rows with dbt_valid_from / dbt_valid_to), builds dim_customers and fct_orders marts, and runs the tests — all green in ~30 seconds. Running docker-compose up a second time against unchanged CSVs is a no-op on customers_snapshot; the tests still pass.

Output:

Rubric axis	Coverage
Correctness	dbt snapshot handles SCD-2; unique + relationships tests
Code quality	dbt project layout (staging/marts/snapshots); typed SQL
Tests	6-10 dbt tests (unique, not_null, relationships, expression)
Docs	dbt docs generated (dbt docs generate); README + architecture diagram
Performance	Snapshot is incremental; indexed joins on customer_id
Extras	Docker Compose boots full stack; loader logs dropped-row count

Why this works — concept by concept:

• dbt snapshots with check strategy — SCD-2 without hand-writing MERGE SQL. check_cols: [name, email] tells dbt to open a new row whenever those attributes change; unchanged rows are silently skipped, giving idempotency for free.
• Surrogate key via dbt_utils.generate_surrogate_key — deterministic hash of (customer_id, dbt_valid_from) yields a stable primary key for downstream fact tables to join against. Never use BIGSERIAL for surrogate keys in dbt snapshots; the sequence is not reproducible across re-runs.
• relationships test on fct_orders.customer_key — proves referential integrity at every dbt run. If a fact row references a non-existent dim row, the test fails; the pipeline halts. This catches upstream loader bugs before they poison the warehouse.
• Docker Compose with service_completed_successfully — the loader must finish before dbt runs; the healthcheck on Postgres ensures the loader doesn't race the DB boot. Every stage of the DAG has an explicit dependency; nothing is time-based.
• Cost — one docker-compose.yml, one dbt project, ~150 lines of SQL, ~50 lines of Python, ~10 lines of tests. Boots in 30 seconds on a laptop. Compared to a hand-rolled SCD-2 loader, dbt snapshots deliver the same rubric coverage at 30% of the code — and the code is more idiomatic for interviewers targeting analytics-engineering DE roles. O(rows) per snapshot check; the CI-friendly rebuild cost is <1 min for 1M rows.

SQL
Topic — slowly-changing-data
SCD Type 2 practice problems

Practice →

ETL Topic — etl ETL problems for take-home CSV loaders

Practice →

---

3. Brief 2 — Kafka streaming aggregate with exactly-once

The correctness-heaviest take-home archetype — windows, state, exactly-once, and the operational story downstream consumers assume

The one-sentence invariant: Brief 2 is the pattern where the candidate must build a streaming service that consumes from a Kafka topic, applies a tumbling or sliding window (typically 15 minutes for financial or session data, 1 minute for real-time metrics), aggregates by a key (customer_id, user_id, product_id), guarantees exactly-once semantics under consumer restart and partition rebalance, persists window state to survive crashes, and emits the aggregate to a sink topic or low-latency store — the brief is designed to probe the single skill senior stream engineers get wrong most often, which is that "at-least-once with idempotent consumer" is not the same guarantee as "exactly-once with transactional producer + read_committed consumer".

Where it shows up.

• Company signal. Brief 2 dominates fintech, adtech, real-time analytics, gaming, and ride-share take-homes — anywhere the business signal is time-windowed and freshness matters. Historically Kafka-heavy shops; increasingly Kinesis and Pub/Sub too.
• Data volume. Simulated stream at 100-5000 events/second, usually via a bundled Python producer that replays a fixture file into Kafka. Real production shapes.
• Time budget claim. "4-6 hours" is the stated budget; expect 15-20 real hours because the Kafka + state + exactly-once plumbing is the load-bearing complexity, not the aggregation logic.
• Return format. Git repo with docker-compose.yml booting Kafka (usually Redpanda or Kafka + Zookeeper), a producer service, a consumer/processor service, tests, and a README.

What interviewers listen for.

• Do you name tumbling vs sliding vs session windows unprompted and pick the right one? — senior signal.
• Do you configure the producer with enable.idempotence=true and transactional.id=<unique>? — required senior signal.
• Do you set the consumer's isolation.level=read_committed so it never reads uncommitted aggregates? — required.
• Do you name watermarks or an equivalent late-event strategy (allowed lateness, dropped-late counter)? — senior signal.
• Do you persist window state to a state store (RocksDB in Kafka Streams; keyed state in Flink; a compacted Kafka topic in Faust) so consumer restart doesn't lose in-flight windows? — required senior signal.

The 2026 reality — Kafka Streams and Flink dominate; Faust is legacy; confluent-kafka Python is a valid take-home shortcut.

• Reference stack. Kafka Streams (Java or Kotlin) with RocksDB state stores and transactional producers. This is the reference implementation most senior interviewers grade against.
• Enterprise stack. Apache Flink with keyed state, event-time windows, and watermarks. More powerful; higher setup cost. Choose only if the brief explicitly names Flink.
• Shortcut stack. Python + confluent-kafka with a hand-rolled window (dict keyed by (customer_id, window_start)) and a checkpoint file. Cheap to boot in Docker Compose; adequate for a take-home if the brief allows Python.
• Anti-stack. A single-file Python script that consumes with for msg in consumer: and writes to Redis without transactions or state. Auto-fail on the exactly-once axis.

The five windowing choices — pick the right one or lose the correctness axis.

• Tumbling. Fixed-size, non-overlapping windows ([00:00, 00:15), [00:15, 00:30)). Default choice for "compute per-customer sum every 15 minutes." Cheap; deterministic.
• Sliding (hopping). Fixed-size, overlapping windows ([00:00, 00:15), [00:05, 00:20), ...). Use when the metric must update every 5 minutes but always cover the last 15. More state to maintain.
• Session. Dynamic-size windows separated by inactivity gaps. Use for "user session duration"; brief will explicitly name it.
• Global (unbounded). Never emit until an explicit trigger. Rare in take-homes; only if the brief says "compute lifetime total".
• Event-time vs processing-time. Event-time uses the timestamp field on the event; processing-time uses wall clock. Event-time is the correct default; interviewers listening for you to name it.

The exactly-once contract — the three settings that turn "at-least-once" into "exactly-once".

• Producer. enable.idempotence=true (default in Kafka 3+) plus transactional.id=<stable-unique-id> plus wrap each aggregate write in producer.begin_transaction() / producer.commit_transaction().
• Consumer. isolation.level=read_committed so the downstream never sees aggregates from aborted transactions.
• Processing. Either use Kafka Streams' built-in processing.guarantee=exactly_once_v2 (which threads all three settings automatically) or thread them manually in Python by including the source offset in the same transaction as the sink write.

The failure modes senior interviewers pre-empt.

• Duplicates on rebalance. Consumer group rebalance causes a partition to move to a new consumer; without exactly-once semantics, the new consumer re-processes messages already committed by the old one. Prevent with transactional producer + read_committed consumer.
• Silent late-event drop. An event arrives after its window has closed; naive implementations silently drop it. Prevent with an allowed_lateness window (Flink) or a late_events_counter metric.
• State-store loss on restart. Consumer crashes; in-memory window state disappears; on restart the consumer produces incomplete aggregates. Prevent with RocksDB persistence (Kafka Streams) or a compacted Kafka topic (custom implementations).
• Wall-clock vs event-time drift. Using time.time() as the window boundary produces wrong aggregates when the producer lags. Prevent by using the event's timestamp field as the window boundary.

Worked example — Python confluent-kafka tumbling-window sum with state persistence

Detailed explanation. The canonical Python Brief 2 implementation: a confluent-kafka consumer reads from orders, maintains a (customer_id, window_start) -> total_cents dictionary in memory, checkpoints that dictionary to disk every N seconds, emits per-window aggregates to orders_by_customer_windowed when a window closes, and uses transactional producer semantics so the offset commit and the aggregate emit happen atomically. Walk through the full loop.

• Producer. Transactional; transactional.id=orders-aggregator-v1; commits with each window emission.
• Consumer. isolation.level=read_committed; enable.auto.commit=false (offsets committed as part of the transaction).
• State. In-memory dict keyed by (customer_id, window_start); checkpointed to a JSON file every 10 seconds.
• Windowing. 15-minute tumbling on event time; window_start = event_ts.replace(minute=(event_ts.minute // 15) * 15, second=0, microsecond=0).

Question. Implement the aggregator loop with tumbling windows, transactional producer, and state checkpoint.

Input.

Setting	Value
Input topic	orders (schema: {customer_id, total_cents, event_ts})
Output topic	orders_by_customer_windowed
Window	15-minute tumbling, event-time
Guarantee	Exactly-once (transactional producer + read_committed consumer)
State	JSON checkpoint every 10 s
Watermark	Event-time; close windows 2 minutes after their end

Code.

# streaming/aggregator.py — Kafka tumbling-window aggregator with exactly-once
import json
import signal
import time
from collections import defaultdict
from datetime import datetime, timedelta, timezone
from pathlib import Path

from confluent_kafka import Consumer, Producer, TopicPartition, KafkaException

CHECKPOINT_FILE = Path("/state/aggregator_checkpoint.json")
WINDOW_MINUTES = 15
WATERMARK_LAG_MINUTES = 2   # close windows this long after their end

producer = Producer({
    "bootstrap.servers": "kafka:9092",
    "enable.idempotence": True,
    "transactional.id": "orders-aggregator-v1",
    "acks": "all",
    "retries": 2_147_483_647,
})
producer.init_transactions()

consumer = Consumer({
    "bootstrap.servers": "kafka:9092",
    "group.id": "orders-aggregator",
    "enable.auto.commit": False,
    "auto.offset.reset": "earliest",
    "isolation.level": "read_committed",
})
consumer.subscribe(["orders"])

# State — {(customer_id, window_start_iso): running_sum_cents}
state: dict[tuple[int, str], int] = defaultdict(int)


def window_start_of(event_ts: datetime) -> datetime:
    """Snap the event ts to its 15-minute tumbling window start."""
    minute = (event_ts.minute // WINDOW_MINUTES) * WINDOW_MINUTES
    return event_ts.replace(minute=minute, second=0, microsecond=0)


def load_checkpoint() -> None:
    if CHECKPOINT_FILE.exists():
        raw = json.loads(CHECKPOINT_FILE.read_text())
        for k, v in raw.items():
            cid, wstart = k.split("|")
            state[(int(cid), wstart)] = v


def save_checkpoint() -> None:
    dumped = {f"{cid}|{wstart}": total for (cid, wstart), total in state.items()}
    CHECKPOINT_FILE.write_text(json.dumps(dumped))


def emit_closed_windows(now: datetime, offsets: list[TopicPartition]) -> None:
    """Emit windows whose end is > WATERMARK_LAG_MINUTES in the past."""
    cutoff = now - timedelta(minutes=WATERMARK_LAG_MINUTES)
    to_emit: list[tuple[int, str, int]] = []
    for (cid, wstart_iso), total in state.items():
        wstart = datetime.fromisoformat(wstart_iso)
        wend   = wstart + timedelta(minutes=WINDOW_MINUTES)
        if wend <= cutoff:
            to_emit.append((cid, wstart_iso, total))

    if not to_emit:
        return

    producer.begin_transaction()
    for cid, wstart_iso, total in to_emit:
        payload = json.dumps({
            "customer_id":  cid,
            "window_start": wstart_iso,
            "total_cents":  total,
        })
        producer.produce(
            "orders_by_customer_windowed",
            key=str(cid).encode(),
            value=payload.encode(),
        )
        del state[(cid, wstart_iso)]

    # Commit offsets INSIDE the transaction — this is the exactly-once seam.
    producer.send_offsets_to_transaction(offsets, consumer.consumer_group_metadata())
    producer.commit_transaction()
    save_checkpoint()


def run() -> None:
    load_checkpoint()
    last_checkpoint = time.time()

    running = True

    def stop(_sig, _frm):
        nonlocal running
        running = False

    signal.signal(signal.SIGTERM, stop)
    signal.signal(signal.SIGINT, stop)

    while running:
        msg = consumer.poll(1.0)
        if msg is None:
            emit_closed_windows(datetime.now(timezone.utc), consumer.position(consumer.assignment()))
            continue
        if msg.error():
            raise KafkaException(msg.error())

        event = json.loads(msg.value())
        event_ts = datetime.fromisoformat(event["event_ts"])
        wstart = window_start_of(event_ts)
        state[(event["customer_id"], wstart.isoformat())] += event["total_cents"]

        # Periodic emit + checkpoint
        if time.time() - last_checkpoint > 10:
            emit_closed_windows(datetime.now(timezone.utc), consumer.position(consumer.assignment()))
            last_checkpoint = time.time()

    save_checkpoint()
    consumer.close()


if __name__ == "__main__":
    run()

Step-by-step explanation.

1. The producer is initialised with enable.idempotence=True and transactional.id="orders-aggregator-v1". init_transactions() fences any previous producer instance with the same transactional id — critical for exactly-once across restarts. Without it, a zombie old instance could commit after the new one and corrupt the aggregate.
2. The consumer is configured with enable.auto.commit=false — offsets are committed inside the producer transaction via send_offsets_to_transaction, not by the consumer's own commit path. This is the seam that makes offset advance and aggregate emit atomic.
3. isolation.level=read_committed on the consumer means downstream consumers of orders_by_customer_windowed never see aggregates from aborted transactions. If the aggregator crashes mid-transaction, the aggregate is invisible to readers.
4. The in-memory state dict is checkpointed to /state/aggregator_checkpoint.json after each transactional commit. On restart, load_checkpoint() restores the in-flight state. This handles the "consumer crash before window close" case — the state survives.
5. emit_closed_windows implements the watermark: windows whose end is more than 2 minutes in the past are considered closed and emitted. This gives late-arriving events a 2-minute grace period before being dropped. The late_events metric (not shown; wire to Prometheus) counts events arriving after their window has closed.

Output.

Setting	Behaviour
Transactional producer	Aggregate + offset commit atomic
read_committed consumer	Downstream never sees uncommitted rows
Watermark = 2 min	Windows closed 2 min after their end
Checkpoint every 10 s	State survives crash with <10 s loss window
Window boundary	Event-time, not wall-clock

Rule of thumb. For every Python Brief 2 aggregator, wire exactly-once via three settings simultaneously: producer with transactional.id + send_offsets_to_transaction, consumer with isolation.level=read_committed, and offsets committed inside the transaction. Miss any one and your exactly-once claim is a bug waiting to be reproduced.

Worked example — the duplicates-on-rebalance failure mode

Detailed explanation. A candidate ships a Brief 2 aggregator without transactional producers; the code uses producer.produce() + consumer.commit() as two separate operations. A partition rebalance mid-flight (triggered by scaling the consumer group) causes the following race: consumer A processes offsets 100-200 and emits aggregates to the sink; consumer A commits offsets after the aggregate emit; the rebalance revokes the partition before the commit lands; consumer B starts from offset 100 and re-emits the aggregates for 100-200. Downstream sees each aggregate twice. Walk through the diagnosis and the fix.

• Symptom. Downstream SELECT customer_id, window_start, SUM(total_cents) FROM orders_by_customer_windowed shows sums that are 1.5-2× the true value during rebalance windows.
• Root cause. Aggregate emit and offset commit are separate operations; rebalance between them causes duplicate processing.
• Fix. Wrap aggregate emit and offset advance in one Kafka transaction via producer.send_offsets_to_transaction.

Question. Diagnose the rebalance duplicate bug and rewrite the emit loop with transactional semantics.

Input.

Component	Before (buggy)	After (fixed)
Producer	plain	transactional (transactional.id set)
Emit + commit	2 ops (emit; then commit offsets)	1 op (begin_transaction; emit; send_offsets_to_transaction; commit)
Consumer	default isolation	isolation.level=read_committed
Duplicates on rebalance	yes (up to 1× extra)	none

Code.

# ---------- BUGGY: at-least-once with dupes on rebalance ----------
for msg in consumer:
    event = json.loads(msg.value())
    aggregate = compute_aggregate(event)
    producer.produce("orders_by_customer_windowed", value=json.dumps(aggregate))
    producer.flush()
    consumer.commit(msg)   # <-- if the process dies here, rebalance
                           #     replays the same message and duplicates
                           #     the aggregate

# ---------- FIXED: exactly-once via transactional producer ----------
producer.init_transactions()
consumer.subscribe(["orders"])

for msg_batch in poll_batched(consumer, batch_size=100):
    aggregates = [compute_aggregate(json.loads(m.value())) for m in msg_batch]

    producer.begin_transaction()
    for agg in aggregates:
        producer.produce("orders_by_customer_windowed", value=json.dumps(agg))
    # Advance offsets INSIDE the same transaction.
    # Kafka atomically commits both the produce and the offset advance.
    offsets = [TopicPartition(m.topic(), m.partition(), m.offset() + 1) for m in msg_batch]
    producer.send_offsets_to_transaction(offsets, consumer.consumer_group_metadata())
    producer.commit_transaction()

Step-by-step explanation.

1. In the buggy version, producer.produce() and consumer.commit() are two separate operations. If the process dies between them (or a rebalance revokes the partition), the offset never advances, and on restart the same message is re-processed. The downstream sink now has the aggregate twice.
2. In the fixed version, producer.init_transactions() fences any zombie producer with the same transactional.id. Only one producer per transactional id can be active at a time; the fenced one's future writes are rejected.
3. producer.begin_transaction() starts a transaction. Every producer.produce() inside the transaction is buffered but not visible to read_committed consumers until commit.
4. producer.send_offsets_to_transaction bundles the consumer's offset advance into the producer transaction. When commit_transaction() succeeds, the aggregates are visible to downstream and the source offsets are advanced — atomically.
5. On a rebalance, the transaction either committed (offsets advanced; aggregates visible) or aborted (offsets not advanced; aggregates invisible). Either way, no downstream consumer sees a partially-processed batch. This is exactly-once semantics.

Output.

Scenario	Buggy (at-least-once)	Fixed (exactly-once)
Normal flow	1× aggregate	1× aggregate
Rebalance mid-batch	up to 2× aggregate	1× aggregate
Producer crash pre-commit	offsets not advanced; replay dupes	transaction aborted; clean replay
Consumer restart	commit lag causes dupes	offsets in-transaction; no dupes
Downstream visibility	uncommitted messages visible	only committed messages visible

Rule of thumb. Never ship a Brief 2 aggregator with separate produce + commit calls. Either use Kafka Streams (which threads exactly-once by default with processing.guarantee=exactly_once_v2) or use producer.send_offsets_to_transaction in Python. Anything else is at-least-once masquerading as exactly-once.

Worked example — Docker Compose that boots Kafka + producer + aggregator + Kafka UI

Detailed explanation. Interviewers grade docker-compose up as a first-class rubric artefact. A one-command stack that boots Redpanda (or Kafka), a fixture producer, the aggregator, and a Kafka UI for debugging is worth an entire rubric axis on its own. Walk through the canonical Compose file for a Brief 2 submission.

• Redpanda over Kafka+Zookeeper. Redpanda is Kafka-API-compatible, single binary, boots in 2 seconds. Zookeeper-less Kafka (KRaft mode) is also fine; both beat the old Kafka + Zookeeper setup on boot time.
• Producer service. Replays a fixture JSONL file into the orders topic at 100 events/sec.
• Aggregator service. Runs the exactly-once tumbling-window aggregator from the previous example.
• Kafka UI. Web UI at localhost:8080 for inspecting topics; huge quality-of-life win for the reviewer.

Question. Write the docker-compose.yml that boots the four services with proper health checks and dependencies.

Input.

Service	Image	Port	Depends on
redpanda	redpandadata/redpanda:latest	9092	—
producer	(local build)	—	redpanda (healthy)
aggregator	(local build)	—	redpanda (healthy)
kafka-ui	provectuslabs/kafka-ui:latest	8080	redpanda (healthy)

Code.

# docker-compose.yml
services:
  redpanda:
    image: redpandadata/redpanda:v24.2.7
    command:
      - redpanda
      - start
      - --smp=1
      - --memory=1G
      - --overprovisioned
      - --node-id=0
      - --check=false
      - --kafka-addr=PLAINTEXT://0.0.0.0:9092
      - --advertise-kafka-addr=PLAINTEXT://redpanda:9092
    ports:
      - "9092:9092"
      - "9644:9644"
    healthcheck:
      test: ["CMD", "rpk", "cluster", "health"]
      interval: 3s
      timeout: 5s
      retries: 20

  bootstrap-topics:
    image: redpandadata/redpanda:v24.2.7
    depends_on:
      redpanda:
        condition: service_healthy
    entrypoint: /bin/bash
    command: |
      -c "rpk topic create orders --brokers redpanda:9092 -p 3 && \
          rpk topic create orders_by_customer_windowed --brokers redpanda:9092 -p 3 -c cleanup.policy=compact"

  producer:
    build: ./producer
    depends_on:
      bootstrap-topics:
        condition: service_completed_successfully
    environment:
      KAFKA_BOOTSTRAP: redpanda:9092
      EVENTS_PER_SEC: "100"
    volumes:
      - ./fixtures:/fixtures:ro
    command: python -m producer.replay /fixtures/orders.jsonl

  aggregator:
    build: ./aggregator
    depends_on:
      bootstrap-topics:
        condition: service_completed_successfully
    environment:
      KAFKA_BOOTSTRAP: redpanda:9092
    volumes:
      - agg-state:/state
    command: python -m streaming.aggregator

  kafka-ui:
    image: provectuslabs/kafka-ui:latest
    depends_on:
      redpanda:
        condition: service_healthy
    ports: ["8080:8080"]
    environment:
      KAFKA_CLUSTERS_0_NAME: local
      KAFKA_CLUSTERS_0_BOOTSTRAPSERVERS: redpanda:9092

volumes:
  agg-state:

Step-by-step explanation.

1. Redpanda is chosen over Kafka + Zookeeper because it boots in ~2 seconds vs ~30 seconds. For a reviewer running docker-compose up, that time difference matters. Redpanda is Kafka-API-compatible, so the client code is unchanged.
2. bootstrap-topics is a one-shot service (via service_completed_successfully) that creates the topics before the producer and aggregator start. Without it, the producer race-conditions the topic-auto-create default and sometimes fails silently.
3. cleanup.policy=compact on orders_by_customer_windowed enables Kafka log compaction — the latest aggregate per (customer_id, window_start) key is retained; older aggregates are garbage-collected. This is the correct topic policy for an aggregate output.
4. The agg-state named volume persists the aggregator's JSON checkpoint file across docker-compose down/up cycles. Without it, restart loses in-flight window state.
5. Kafka UI at localhost:8080 gives the reviewer a browser-based way to inspect topics, messages, and consumer group lag. Including it is a two-line addition that dramatically improves the reviewer's experience — pure rubric-extras signal.

Output.

Endpoint	Purpose
localhost:9092	Kafka broker (via rpk or code)
localhost:8080	Kafka UI (browse topics + messages)
docker-compose logs -f aggregator	Aggregator emit/commit logs
docker-compose logs -f producer	Producer replay logs
docker volume inspect brief2_agg-state	Aggregator state checkpoint

Rule of thumb. For every Brief 2 submission, ship a Docker Compose file that (a) boots Redpanda or KRaft-mode Kafka, (b) creates topics via a one-shot bootstrap service, (c) includes Kafka UI, and (d) persists aggregator state on a named volume. The reviewer opens the Compose file before the README — make it look shippable.

Senior interview question on Brief 2 streaming

A senior interviewer might ask: "You've received a Brief 2 take-home: consume events from Kafka topic orders, compute per-customer sum of order totals in 15-minute tumbling windows, emit to orders_by_customer_windowed, guarantee exactly-once. Walk me through the stack choice, the exactly-once wiring, the state persistence, the late-event handling, the tests, and the docker-compose that boots the whole thing."

Solution Using Kafka Streams (Kotlin) with tumbling windows + RocksDB state + exactly_once_v2

// streaming/src/main/kotlin/OrdersAggregator.kt
import org.apache.kafka.common.serialization.Serdes
import org.apache.kafka.streams.KafkaStreams
import org.apache.kafka.streams.StreamsConfig
import org.apache.kafka.streams.kstream.Materialized
import org.apache.kafka.streams.kstream.Suppressed
import org.apache.kafka.streams.kstream.TimeWindows
import org.apache.kafka.streams.StreamsBuilder
import java.time.Duration
import java.util.Properties

fun main() {
    val props = Properties().apply {
        put(StreamsConfig.APPLICATION_ID_CONFIG,           "orders-aggregator-v1")
        put(StreamsConfig.BOOTSTRAP_SERVERS_CONFIG,        "redpanda:9092")
        put(StreamsConfig.PROCESSING_GUARANTEE_CONFIG,     StreamsConfig.EXACTLY_ONCE_V2)
        put(StreamsConfig.DEFAULT_KEY_SERDE_CLASS_CONFIG,   Serdes.String()::class.java)
        put(StreamsConfig.DEFAULT_VALUE_SERDE_CLASS_CONFIG, Serdes.String()::class.java)
        put(StreamsConfig.REPLICATION_FACTOR_CONFIG,       1)
        put(StreamsConfig.COMMIT_INTERVAL_MS_CONFIG,       10_000)
    }

    val builder = StreamsBuilder()

    val orders = builder.stream<String, String>("orders")

    orders
        .mapValues { raw -> parseOrder(raw) }               // { customer_id, total_cents, event_ts }
        .selectKey { _, order -> order.customerId.toString() }
        .groupByKey()
        .windowedBy(
            TimeWindows.ofSizeAndGrace(
                Duration.ofMinutes(15),
                Duration.ofMinutes(2)                       // allowed lateness — 2-min watermark
            )
        )
        .aggregate(
            /* initializer */ { 0L },
            /* aggregator  */ { _, order, agg -> agg + order.totalCents },
            Materialized.`as`<String, Long>("orders-per-customer-window-store")
        )
        .suppress(Suppressed.untilWindowCloses(Suppressed.BufferConfig.unbounded()))
        .toStream()
        .map { windowed, total ->
            org.apache.kafka.streams.KeyValue(
                windowed.key(),
                jsonEncode(mapOf(
                    "customer_id"  to windowed.key().toInt(),
                    "window_start" to windowed.window().startTime().toString(),
                    "total_cents"  to total
                ))
            )
        }
        .to("orders_by_customer_windowed")

    val streams = KafkaStreams(builder.build(), props)
    Runtime.getRuntime().addShutdownHook(Thread(streams::close))
    streams.start()
}

# docker-compose.yml (streaming brief — Kotlin edition)
services:
  redpanda:      # (as before)
    image: redpandadata/redpanda:v24.2.7
    # ...

  aggregator:
    build:
      context: ./streaming
      dockerfile: Dockerfile
    depends_on:
      bootstrap-topics:
        condition: service_completed_successfully
    volumes:
      - agg-state:/tmp/kafka-streams   # RocksDB state directory
    environment:
      JAVA_OPTS: "-Xmx512m"

volumes:
  agg-state:

// streaming/src/test/kotlin/OrdersAggregatorTest.kt
import org.apache.kafka.streams.TestInputTopic
import org.apache.kafka.streams.TestOutputTopic
import org.apache.kafka.streams.TopologyTestDriver
import org.junit.jupiter.api.Test
import java.time.Duration
import kotlin.test.assertEquals

class OrdersAggregatorTest {

    @Test
    fun `tumbling window sums per customer`() {
        val driver = TopologyTestDriver(buildTopology(), streamsProps())
        val input:  TestInputTopic<String, String>  = driver.createInputTopic("orders",  ...)
        val output: TestOutputTopic<String, String> = driver.createOutputTopic("orders_by_customer_windowed", ...)

        input.pipeInput("1", """{"customer_id":1,"total_cents":100,"event_ts":"2026-07-04T09:00:00Z"}""")
        input.pipeInput("1", """{"customer_id":1,"total_cents":250,"event_ts":"2026-07-04T09:10:00Z"}""")
        input.pipeInput("1", """{"customer_id":1,"total_cents":300,"event_ts":"2026-07-04T09:20:00Z"}""")

        // Advance wall-clock past window close (15 min window + 2 min grace)
        driver.advanceWallClockTime(Duration.ofMinutes(18))

        val aggregates = output.readValuesToList()
        // Window [09:00, 09:15) = 100 + 250 = 350
        // Window [09:15, 09:30) = 300         (still open at test end)
        assertEquals(1, aggregates.size)
        assert("\"total_cents\":350" in aggregates[0])
    }

    @Test
    fun `late events beyond grace window are dropped`() { /* ... */ }
    @Test
    fun `exactly-once — restart replays without duplicate emit`() { /* ... */ }
}

Step-by-step trace.

Layer	Component	Purpose
Topology	TimeWindows.ofSizeAndGrace(15m, 2m)	15-min tumbling; 2-min allowed lateness
State	RocksDB orders-per-customer-window-store	Survives restart; local to task
Guarantee	processing.guarantee=EXACTLY_ONCE_V2	Transactional producer + offset commit atomic
Suppress	untilWindowCloses	Emit once per window (not per update)
Sink	orders_by_customer_windowed (compacted)	Latest aggregate per key retained
Test	TopologyTestDriver + advanceWallClockTime	Unit-testable without Kafka

After docker-compose up, Redpanda boots, topics are created, the aggregator starts and initialises RocksDB in /tmp/kafka-streams, the producer replays fixtures, and windows emit at their close time (15 min after window start + 2 min grace). Kafka UI at localhost:8080 shows both topics and per-consumer lag. Kill and restart the aggregator container: RocksDB state persists on the named volume; the aggregator resumes without re-emitting.

Output:

Rubric axis	Coverage
Correctness	Exactly-once via EXACTLY_ONCE_V2; late-event grace via ofSizeAndGrace
Code quality	Idiomatic Kafka Streams DSL; single file; no side-effectful transforms
Tests	TopologyTestDriver for unit tests; integration test via Compose
Docs	README with topology diagram + trade-off section
Performance	RocksDB state; suppressed emit (1× per window, not N updates)
Extras	Kafka UI; Docker Compose one-command boot; retrospective section

Why this works — concept by concept:

• processing.guarantee=EXACTLY_ONCE_V2 — Kafka Streams' opinionated exactly-once mode. Automatically threads transactional producer, offset commit in transaction, and read_committed consumer. Sets commit.interval.ms sensibly. Zero manual wiring; interviewers immediately recognise the config.
• TimeWindows.ofSizeAndGrace(15m, 2m) — tumbling window with an explicit 2-minute grace for late events. Events arriving after grace are dropped (metric-visible via record-late-total). This is the sanctioned late-event handling; not naming it costs the correctness axis.
• Suppressed.untilWindowCloses — without suppress, the aggregate emits after every update (thousands of times per window). With suppress, one final aggregate emits per window at close time. Downstream consumers see one authoritative value per (customer, window).
• RocksDB state store — Kafka Streams persists window aggregates to RocksDB in the container's /tmp/kafka-streams; mounted on a named volume in Compose, the state survives container restarts. Kafka Streams also backs the state with a compacted changelog topic for cross-instance recovery.
• Cost — one Kotlin project, ~80 lines of aggregator code, ~30 lines of tests, one Docker Compose file. RocksDB adds ~200 MB of local disk. Compared to the Python confluent-kafka version, Kotlin + Kafka Streams delivers stronger correctness guarantees at ~30% less code — trade-off is that Kotlin requires JVM in the container. O(events) per window; state size O(unique keys × active windows).

Streaming
Topic — streaming
Streaming aggregation practice problems

Practice →

SQL Topic — aggregation Aggregation problems for windowed sums

Practice →

---

4. Brief 3 — Dashboard from raw events via dbt + Metabase

The layering-heaviest DE take-home archetype — raw stream, dbt marts, dashboard, SLA-bound refresh

The one-sentence invariant: Brief 3 is the pattern where the candidate must transform a table of raw events (typically 100 K-10 M rows) into a small dbt project with staging → intermediate → marts layers, add dbt tests on every model, and render a 3-tile dashboard (DAU, top-N by revenue, rolling trend) in Metabase or Superset with a documented refresh SLA — the brief is designed to probe the single skill analytics engineering-flavoured DE roles care about most, which is model layering discipline (never flat SQL against raw), plus the ability to ship a rendered dashboard, not just SQL queries.

Where it shows up.

• Company signal. Brief 3 dominates B2B SaaS analytics teams, growth-analytics-heavy startups, and any team where the DE role sits close to product analytics. dbt Labs, Fivetran, Preset, and their customer bases lean heavily on this archetype.
• Data volume. 100 K-10 M raw events (clickstream, orders, sign-ups). Big enough to reward incremental models; small enough to run on a laptop.
• Time budget claim. "4-8 hours" is the stated budget; expect 15-25 real hours because the dbt project scaffolding, the dashboard rendering, and the SLA plumbing all take time.
• Return format. Git repo with docker-compose.yml booting Postgres + dbt + Metabase, a dbt_project.yml, staging/intermediate/marts models, a dbt sources.yml, tests, and a Metabase dashboard exported as a JSON or documented via screenshots.

What interviewers listen for.

• Do you separate staging → intermediate → marts layers explicitly? — required senior signal.
• Do you use sources.yml to declare raw tables, not raw table references in models? — required senior signal.
• Do you ship dbt tests (unique, not_null, relationships, accepted_values) on every mart? — required.
• Do you name the grain of each mart in its documentation block? — senior signal.
• Do you ship a rendered dashboard with 3-5 tiles, not just the SQL queries? — required.
• Do you document the refresh SLA (hourly, daily, event-driven) with an explanation of the cost/freshness trade-off? — senior signal.

The 2026 reality — dbt is the reference transform layer; Metabase and Superset compete on the visualisation layer.

• Reference transform stack. dbt-core (open-source) + Postgres. Free, boots in Docker, no cloud accounts. dbt-cloud is fine if the brief calls for it.
• Reference visualisation. Metabase (easier setup) or Superset (more powerful). Both boot in Docker; both accept dbt-modeled tables directly. Pick Metabase for a take-home unless the brief specifies Superset.
• Anti-stack. A one-off Python script that generates a static PNG dashboard. Interviewers want a live dashboard they can click through and change filters on.

The dbt layer discipline — staging, intermediate, marts, and why they matter.

• staging/stg_<source>_<table>.sql. One 1:1 view per raw source table. Renames columns to snake_case, casts types, filters obvious junk (WHERE deleted_at IS NULL). No joins.
• intermediate/int_<domain>_<action>.sql. Composable middle layer. Joins two staging models; adds derived columns. Named after the domain action they perform (int_orders_enriched_with_customers).
• marts/<domain>/<mart>.sql. Business-facing tables. dim_customers, fct_orders, mart_daily_metrics. One row per grain named in the doc block. This is what the dashboard reads.
• Never join raw tables directly in marts. Every mart's FROM clause references ref('stg_...') or ref('int_...') — never source(...) directly. This is the layering rule reviewers grade.

The three canonical dashboard tiles — pick these unless the brief says otherwise.

• Tile 1 — Daily active users (DAU). SELECT date_trunc('day', event_ts) AS day, COUNT(DISTINCT user_id) AS dau FROM fct_events GROUP BY 1. Line chart. This is the "am I alive?" metric — always tile 1.
• Tile 2 — Top 10 products by revenue. SELECT product_id, product_name, SUM(revenue) AS total FROM fct_orders GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 10. Bar chart. Answers "what makes us money."
• Tile 3 — 7-day rolling active users. SELECT day, AVG(dau) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_dau FROM dau_daily. Line chart. Answers "is trend up or down."

The failure modes senior interviewers pre-empt.

• Flat model structure. All logic in one 300-line SQL model that reads directly from raw.events. Auto-fail on code quality; skips the entire "why layering exists" senior signal.
• No tests. dbt without tests is not a dbt project — it's a folder full of SQL. Ship at least unique and not_null on every mart's primary key.
• No rendered dashboard. Shipping only SQL queries with a screenshot from Postico is not a dashboard. Boot Metabase and point it at the marts.
• Slow marts. A mart that takes 30 seconds to query is a mart that will fail in a live dashboard demo. Add indexes; materialise as table (not view) for anything the dashboard queries.

Worked example — dbt project layout for a Brief 3 dashboard

Detailed explanation. The canonical Brief 3 dbt project has a specific folder structure that interviewers immediately recognise. Getting the layout right — staging, intermediate, marts, sources, tests, docs — signals "I have shipped dbt in production." Getting it wrong — flat models, no sources.yml, no tests — signals the opposite. Walk through the full project skeleton.

• Sources. models/staging/_sources.yml declares raw tables that live in raw.*; dbt-docs picks them up.
• Staging. One view per source table, renaming and casting.
• Intermediate. Composable middle layer (e.g. sessionisation).
• Marts. dim_users, fct_events, mart_daily_metrics.
• Tests. schema.yml next to each mart with unique, not_null, relationships.

Question. Draft the dbt project layout for a Brief 3 that ingests raw.events, raw.users, raw.products and needs to serve a 3-tile dashboard.

Input.

Folder	File	Purpose
models/staging/	_sources.yml + stg_events.sql + stg_users.sql + stg_products.sql	1:1 clean views
models/intermediate/	int_events_sessionised.sql + int_orders_enriched.sql	Composable middle
models/marts/core/	dim_users.sql + fct_events.sql + schema.yml	Fact + dim
models/marts/metrics/	mart_daily_metrics.sql + mart_top_products.sql + mart_rolling_dau.sql + schema.yml	Dashboard-facing

Code.

# models/staging/_sources.yml
version: 2

sources:
  - name: raw
    schema: raw
    tables:
      - name: events
        description: "Raw clickstream events from application"
        columns:
          - name: event_id
            tests: [unique, not_null]
          - name: user_id
            tests: [not_null]
      - name: users
        description: "Raw users dump"
      - name: products
        description: "Raw products dump"

-- models/staging/stg_events.sql
{{ config(materialized='view') }}

SELECT
    event_id                     AS event_id,
    user_id                      AS user_id,
    product_id                   AS product_id,
    event_type                   AS event_type,
    (event_ts::timestamptz)      AS event_ts,
    (revenue_cents::bigint)      AS revenue_cents
  FROM {{ source('raw', 'events') }}
 WHERE event_ts IS NOT NULL
   AND user_id IS NOT NULL;

-- models/marts/metrics/mart_daily_metrics.sql
{{ config(materialized='table') }}

-- Grain: one row per (day). Powered by fct_events.
WITH events AS (
    SELECT * FROM {{ ref('fct_events') }}
)
SELECT
    date_trunc('day', event_ts)::date            AS day,
    COUNT(DISTINCT user_id)                       AS dau,
    COUNT(*) FILTER (WHERE event_type = 'order')  AS order_count,
    SUM(revenue_cents) FILTER (WHERE event_type = 'order') / 100.0 AS revenue_dollars
  FROM events
 GROUP BY 1
 ORDER BY 1;

-- models/marts/metrics/mart_rolling_dau.sql
{{ config(materialized='table') }}

-- Grain: one row per (day). Powered by mart_daily_metrics.
SELECT
    day,
    dau,
    AVG(dau) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )::int AS rolling_7d_dau
  FROM {{ ref('mart_daily_metrics') }};

# models/marts/metrics/schema.yml
version: 2

models:
  - name: mart_daily_metrics
    description: "One row per day; DAU + order count + revenue. SLA: refresh every 1h."
    columns:
      - name: day
        tests: [unique, not_null]
      - name: dau
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

  - name: mart_rolling_dau
    description: "One row per day; 7-day rolling avg of DAU. SLA: refresh every 1h."
    columns:
      - name: day
        tests: [unique, not_null]

Step-by-step explanation.

1. _sources.yml declares the three raw tables. Every downstream model that reads raw data reads via source('raw', 'events') — dbt-docs then renders the lineage from source to mart. Skipping sources and referencing raw.events directly in models is the single most common Brief 3 mistake.
2. Each stg_*.sql model is materialised as a view — cheap, always fresh, no storage cost. Views are correct for staging because staging is a rename+cast operation; there's no expensive computation to cache.
3. The intermediate layer (not shown in full here) is where joins happen. int_orders_enriched.sql joins stg_events with stg_users and stg_products — this becomes the input to multiple marts, so the join runs once, not per mart.
4. Marts are materialised as table — the dashboard queries them repeatedly, so caching the result is worth the storage cost. mart_daily_metrics is the single source of truth for the DAU tile; mart_top_products for the top-N tile; mart_rolling_dau for the trend tile.
5. Every mart's schema.yml documents (a) the grain in the description, (b) the SLA in the description, and (c) at least unique + not_null tests on the primary key. This is the layering discipline reviewers grade.

Output.

Layer	Materialization	Purpose	Rubric axis served
Staging views	view	1:1 clean	Code quality (layering)
Intermediate	view / ephemeral	Composable middle	Code quality (DRY)
Marts	table	Dashboard-facing	Performance (indexed reads)
Sources.yml	(declaration)	Lineage roots	Docs (dbt-docs generation)
schema.yml tests	(declaration)	Grain contract	Tests

Rule of thumb. For every Brief 3 dbt project, ship (a) a sources.yml for every raw table, (b) staging views renaming/casting, (c) intermediate views for joins, (d) marts materialised as tables, and (e) schema.yml tests on every mart's primary key. Miss any layer and the code-quality axis drops from senior to mid.

Worked example — Metabase dashboard boot + 3 tiles + SLA doc

Detailed explanation. Shipping a rendered Metabase dashboard — not just the underlying SQL — is a hard requirement. Reviewers open the dashboard URL first; the SQL queries second. Walk through the Compose service that boots Metabase, connects it to Postgres, and pre-provisions the 3 tiles via Metabase's JSON serialisation API.

• Metabase boot. Boots against a Postgres backend (Metabase's own settings) + a Postgres source (the dbt-modeled warehouse).
• Pre-provision. Metabase supports import/export via docker cp of an SQLite metadata file, or via the newer serialisation API (metabase serialize).
• SLA doc. README explains: "Dashboard refreshes hourly via dbt Cloud (or dbt run in cron); freshness SLA = 1 h; DAU is authoritative at hour boundaries."

Question. Write the Compose service for Metabase, the tile SQL, and the SLA section for the README.

Input.

Component	Value
Metabase image	metabase/metabase:v0.51.7
Backend	H2 (embedded) or Postgres
Source	Postgres warehouse (dbt-modeled)
Tiles	DAU line, top-10 products bar, 7-day rolling DAU line
Refresh SLA	1 h (documented in README)

Code.

# docker-compose.yml (Brief 3 excerpt)
services:
  postgres:
    image: postgres:16-alpine
    # ... (see Brief 1 example)

  dbt:
    build: ./dbt
    depends_on:
      postgres:
        condition: service_healthy
    volumes:
      - ./dbt:/workspace
    command: bash -c "dbt seed && dbt run && dbt test"

  metabase:
    image: metabase/metabase:v0.51.7
    depends_on:
      dbt:
        condition: service_completed_successfully
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: h2
      MB_DB_FILE: /metabase-data/metabase.db
    volumes:
      - metabase-data:/metabase-data
      - ./metabase-init:/init:ro
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:3000/api/health"]
      interval: 5s

volumes:
  metabase-data:

-- Tile 1 — DAU line chart
-- Data source: mart_daily_metrics
SELECT
    day,
    dau
  FROM public_marts.mart_daily_metrics
 WHERE day >= current_date - interval '30 days'
 ORDER BY day;

-- Tile 2 — Top 10 products by revenue (last 30 days)
SELECT
    product_name,
    SUM(revenue_cents) / 100.0 AS revenue_dollars
  FROM public_marts.fct_events e
  JOIN public_marts.dim_products p ON p.product_id = e.product_id
 WHERE e.event_ts >= current_date - interval '30 days'
   AND e.event_type = 'order'
 GROUP BY product_name
 ORDER BY revenue_dollars DESC
 LIMIT 10;

-- Tile 3 — 7-day rolling DAU
SELECT
    day,
    rolling_7d_dau
  FROM public_marts.mart_rolling_dau
 WHERE day >= current_date - interval '60 days'
 ORDER BY day;

## Refresh SLA

- **dbt refresh cadence.** Every 1 hour, triggered by a cron in the
  `dbt` service (or by dbt Cloud in production).
- **Freshness contract.** Dashboard tiles are authoritative as-of the
  most recent `mart_*` refresh; hourly freshness is the SLA.
- **Late-event handling.** Events with `event_ts` older than 24 h are
  filtered out of `fct_events` (see `stg_events.sql`); they are
  logged and preserved in `raw.events` for backfill.
- **Backfill.** `dbt run --select mart_daily_metrics --vars '{"start_date": "2026-01-01"}'`
  re-materialises historical days.
- **Trade-offs.** Hourly cadence gives users near-real-time visibility
  at ~$0.10/hour of warehouse compute. Sub-hourly would 4-6× cost
  without meaningful business signal for these metrics.

Step-by-step explanation.

1. Metabase depends on dbt completing successfully — this means the marts exist before Metabase boots, so first-time load renders green. Without the dependency, Metabase boots but the tiles fail with "table does not exist."
2. MB_DB_TYPE=h2 uses Metabase's embedded H2 database for its own settings — one less service in Compose. For production you'd use Postgres, but for a take-home, H2 keeps the stack simple.
3. Metabase's serialisation API (docker exec metabase java -jar /app/metabase.jar serialize in newer versions) lets you export the dashboard as JSON and check it into Git. Reviewers can import it deterministically instead of clicking through the UI.
4. Each tile SQL reads from the marts (never from raw). This preserves the layering discipline: dashboards see the business-facing schema; raw remains internal. Filtering WHERE day >= current_date - interval '30 days' scopes tiles to a sensible time window.
5. The SLA section in the README explicitly states the refresh cadence, the freshness contract, the backfill procedure, and the cost/freshness trade-off. This is senior signal — mid candidates say "refreshes hourly"; senior candidates explain why hourly is right for this data.

Output.

Reviewer action	Expected result
docker-compose up	Postgres + dbt + Metabase all green in <60 s
Open localhost:3000	Metabase dashboard with 3 tiles
Click "DAU" tile	Line chart, 30 days of data
Read README "Refresh SLA"	Explicit hourly cadence + trade-off
dbt test	All model tests pass

Rule of thumb. For every Brief 3 submission, ship (a) a rendered Metabase (or Superset) dashboard bootable via docker-compose up, (b) 3 tiles that read from marts only, and (c) an SLA section in the README that names cadence + freshness + backfill + cost trade-off. Reviewers open the dashboard first; make the first impression polished.

Worked example — dbt tests that catch the common Brief 3 bugs

Detailed explanation. dbt tests are half the code-quality axis and all of the tests axis on Brief 3. A submission with well-modeled SQL and zero tests scores below a submission with slightly-messier SQL and 20 well-chosen tests. Walk through the canonical test set that catches the four common Brief 3 bugs.

• Bug 1. Duplicate primary keys in marts (join blew up). Catch with unique on mart.id.
• Bug 2. Nulls in required foreign keys (upstream filter regressed). Catch with not_null on fk_id.
• Bug 3. Fact rows referencing non-existent dims (dim wasn't refreshed). Catch with relationships.
• Bug 4. Metric values out of expected range (bad casting; sign error). Catch with dbt_utils.expression_is_true.

Question. Write the schema.yml that catches all four bug classes for a Brief 3 project.

Input.

Test	Where	Bug it catches
unique	mart primary keys	Duplicate rows from a bad join
not_null	required columns	Upstream filter regression
relationships	fact.dim_id → dim.id	Missing dim rows
accepted_values	enum columns	Bad casts; typos
dbt_utils.expression_is_true	metric columns	Value out of expected range

Code.

# models/marts/core/schema.yml
version: 2

models:
  - name: fct_events
    description: "Fact table — one row per raw event. Grain: event_id."
    columns:
      - name: event_id
        tests: [unique, not_null]
      - name: user_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_users')
              field: user_id
      - name: event_type
        tests:
          - not_null
          - accepted_values:
              values: ['page_view', 'click', 'signup', 'order', 'refund']
      - name: revenue_cents
        tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"
              config:
                where: "event_type = 'order'"

  - name: dim_users
    description: "Dimension — one row per user_id. Slowly changing (Type 1)."
    columns:
      - name: user_id
        tests: [unique, not_null]
      - name: email
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "LIKE '%@%'"

  - name: mart_daily_metrics
    description: "One row per day; DAU + revenue. SLA: 1h refresh."
    columns:
      - name: day
        tests: [unique, not_null]
      - name: dau
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: revenue_dollars
        tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"

  - name: mart_top_products
    description: "Top-10 products by revenue. Grain: product_id."
    columns:
      - name: product_id
        tests: [unique, not_null]
      - name: revenue_dollars
        tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"

Step-by-step explanation.

1. unique on fct_events.event_id catches the "duplicate row from a bad join" bug — the single most common analytics-engineering bug. A one-to-many join upstream would double every fact row; the test fails loudly.
2. relationships: to: ref('dim_users'), field: user_id catches the "orphaned fact rows" bug — events referencing users that don't exist in the dim table. This bug ships silently in most naive implementations; dbt catches it every run.
3. accepted_values on event_type catches typos and enum drift. If the upstream service adds a new 'checkout_started' event type, the test fails and the analytics team notices before the dashboard tile silently omits the new events.
4. dbt_utils.expression_is_true: expression: ">= 0" on revenue_cents catches sign errors from bad casts (e.g. int32 overflow producing negative revenue). The where clause scopes the assertion to order events, which is the only event type where revenue should be positive.
5. Every mart's primary key gets unique + not_null. Every foreign key gets not_null + relationships. Every metric gets a range check. This is ~15 tests for a 3-mart project — enough to occupy 5 minutes of dbt test runtime and catch the top 4 bug classes.

Output.

Test count	Coverage	Bugs caught
4 × unique	Every mart PK	Duplicate rows from bad joins
8 × not_null	Every required column	Upstream filter regressions
2 × relationships	Every FK	Orphaned fact rows
1 × accepted_values	Enum column	Typos + drift
4 × expression_is_true	Every metric	Sign errors + bad casts
~19 total	Full mart coverage	Top-4 Brief 3 bug classes

Rule of thumb. For every Brief 3 submission, ship at minimum 15-20 dbt tests split across unique + not_null + relationships + accepted_values + expression_is_true. This suite runs in <1 minute, catches the top 4 bug classes, and takes the tests-axis rubric score from mid to senior.

Senior interview question on Brief 3 dashboard

A senior interviewer might ask: "You've received a Brief 3 take-home: a raw.events table (10 M rows, includes page views, clicks, sign-ups, orders, refunds), plus raw.users and raw.products. Build a dashboard showing DAU, top-10 products by revenue, and 7-day rolling active users. Use dbt for transforms; ship a Docker Compose that boots the whole stack. Walk me through your dbt project layout, the marts, the tests, the Metabase config, and the refresh-SLA doc."

Solution Using star-schema marts + dbt incremental models + Metabase pre-provisioned dashboard

-- models/marts/core/fct_events.sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    on_schema_change='fail'
) }}

-- Grain: one row per event_id. Incremental on event_ts.
SELECT
    e.event_id,
    e.user_id,
    e.product_id,
    e.event_type,
    e.event_ts,
    e.revenue_cents,
    u.signup_date,
    p.product_name,
    p.category
  FROM {{ ref('stg_events') }}   e
  LEFT JOIN {{ ref('dim_users') }}    u ON u.user_id    = e.user_id
  LEFT JOIN {{ ref('dim_products') }} p ON p.product_id = e.product_id

{% if is_incremental() %}
 WHERE e.event_ts > (SELECT COALESCE(MAX(event_ts), '1970-01-01') FROM {{ this }})
{% endif %}

-- models/marts/metrics/mart_daily_metrics.sql
{{ config(
    materialized='incremental',
    unique_key='day',
    on_schema_change='fail'
) }}

-- Grain: one row per day. Incremental on day.
WITH events AS (
    SELECT * FROM {{ ref('fct_events') }}
    {% if is_incremental() %}
     WHERE event_ts::date >= (SELECT COALESCE(MAX(day), '1970-01-01') FROM {{ this }}) - interval '1 day'
    {% endif %}
)
SELECT
    event_ts::date                                                     AS day,
    COUNT(DISTINCT user_id)                                             AS dau,
    COUNT(*) FILTER (WHERE event_type = 'order')                        AS order_count,
    SUM(revenue_cents) FILTER (WHERE event_type = 'order') / 100.0      AS revenue_dollars,
    SUM(revenue_cents) FILTER (WHERE event_type = 'refund') / 100.0     AS refund_dollars
  FROM events
 GROUP BY 1;

# dbt_project.yml (excerpt — sensible defaults for take-home)
name: brief3_dashboard
version: 1.0.0
config-version: 2

profile: brief3

models:
  brief3_dashboard:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table
      core:
        +schema: marts_core
      metrics:
        +materialized: incremental
        +schema: marts_metrics

vars:
  event_lookback_days: 30

# Makefile — one-command lifecycle for reviewers
.PHONY: up down refresh test

up:
    docker-compose up -d
    docker-compose exec dbt bash -c "cd /workspace && dbt deps && dbt seed && dbt run && dbt test"
    @echo "Dashboard ready at http://localhost:3000 (login: [e-mail] / metabase123)"

down:
    docker-compose down -v

refresh:
    docker-compose exec dbt dbt run

test:
    docker-compose exec dbt dbt test

Step-by-step trace.

Layer	Materialization	Purpose
stg_*	view	1:1 clean over raw
int_*	view	Composable joins
dim_users, dim_products	table	Slowly changing dimensions
fct_events	incremental	Append-only fact table; 10 M rows
mart_daily_metrics	incremental	One row/day; DAU + revenue
mart_top_products	table	Top-10 by revenue
mart_rolling_dau	table	7-day rolling avg
Metabase	(dashboard)	3 tiles reading from marts

After make up, Postgres boots, dbt seeds fixture data + runs 15 models + runs 19 tests (all green in ~90 s), Metabase boots on localhost:3000 with the pre-provisioned dashboard, and the reviewer can immediately click through the 3 tiles. make refresh re-runs dbt incrementally (~5 s for delta). make test re-runs the test suite.

Output:

Rubric axis	Coverage
Correctness	Star schema; incremental marts; correct DAU/top-N/rolling logic
Code quality	staging → intermediate → marts layering; sources.yml; typed SQL
Tests	19 dbt tests: unique + not_null + relationships + accepted_values + expression_is_true
Docs	README with SLA + trade-off; dbt-docs generated; architecture diagram
Performance	Incremental models; table-materialized marts; indexed FKs
Extras	Metabase pre-provisioned; Makefile; retrospective section

Why this works — concept by concept:

• staging → intermediate → marts layering — dbt's opinionated project layout. Every model has a clear layer identity; joins happen once in intermediate and are reused across marts. This is the code-quality signal reviewers value most for Brief 3.
• Incremental materialization on fct_events and mart_daily_metrics — first dbt run is full-refresh (~10 M rows in ~30 s); subsequent runs are incremental (~5 s for the delta). Without incremental, hourly refresh would be prohibitively expensive at 10 M rows.
• Star-schema marts (fct_events + dim_users + dim_products) — the canonical analytics-engineering model. Fact rows carry FKs to dims; dims carry attributes; joins happen at query time in the dashboard. Enables tile SQL to be short and readable.
• sources.yml + dbt docs generate — sources declare the raw tables as first-class dbt nodes; dbt docs generate produces a lineage graph visible in dbt docs serve. This graph is worth an entire rubric-extras axis on its own.
• Cost — one dbt project, ~15 models, ~19 tests, one dbt_project.yml, one Makefile, one Compose file, ~200 lines of SQL. Full stack boots in <60 s; refresh in <10 s. Compared to a raw Python + SQL script, dbt delivers stronger correctness guarantees (tests as first-class), better performance (incremental), and richer docs (dbt docs) at ~40% less code. O(delta) per incremental refresh; O(N) per full refresh.

SQL
Topic — window-functions
Window function problems for rolling metrics

Practice →

SQL Topic — joins Join problems for fact-dimension marts

Practice →

---

5. Briefs 4 + 5 + the grading rubric

The DQ suite, the feature store, and the six-axis rubric that grades every take-home

The one-sentence invariant: Brief 4 and Brief 5 sit at the specialised end of the archetype spectrum — Brief 4 tests whether the candidate can wire a Great Expectations (or dbt tests) suite against a deliberately dirty source with alerting on failure, and Brief 5 tests whether the candidate can design a Feast-style feature store with online (Redis) + offline (S3 Parquet) synchronisation and point-in-time-correct training-time joins — and both are graded against the same six-axis rubric (correctness 30%, code quality 20%, tests 15%, docs 15%, performance 10%, extras 10%) that decides every DE take-home offer, regardless of brief archetype.

Where they show up.

• Brief 4 (DQ) — company signal. Data-platform-heavy shops that already run dbt or Airflow and need a candidate who understands data quality as a first-class concern. Airbyte, Fivetran, dbt Labs, plus most B2B SaaS analytics teams.
• Brief 5 (feature store) — company signal. ML-heavy shops with a real MLE-alongside-DE division. Uber, DoorDash, Instacart, plus most consumer marketplaces. Feature stores are the DE side of ML platform work.
• Time budget. "4-8 hours" stated for both; expect 15-25 real hours for Brief 4 (GE setup takes time), 20-30 real hours for Brief 5 (feature store has more moving parts than any other brief).
• Return format. Same as the others — Git repo, Docker Compose, README, tests. Brief 5 additionally requires a design doc explaining the PIT-correctness invariant.

What interviewers listen for on Brief 4.

• Do you distinguish strict tests (fail the pipeline) from soft tests (warn only)? — senior signal.
• Do you cover the five canonical expectation kinds — row count, uniqueness, not-null, in-set / range, referential integrity — as separate expectations? — required.
• Do you wire alerting on failure (Slack webhook, PagerDuty, email)? — senior signal.
• Do you name the freshness expectation (expect_column_max_to_be_between('event_ts', now() - 24h, now())) as a first-class check? — senior signal.
• Do you generate the HTML expectation-suite report and check it into the repo (or serve it)? — senior signal.

What interviewers listen for on Brief 5.

• Do you name point-in-time correctness unprompted? — required senior signal (missing it is auto-fail).
• Do you separate the online store (Redis, DynamoDB) from the offline store (S3 Parquet, BigQuery)? — required.
• Do you register feature views (Feast) or feature groups (SageMaker/Vertex) as first-class artefacts? — required.
• Do you name the feature freshness SLA and the online-offline sync cadence? — senior signal.
• Do you handle the training-serving skew problem — same transformation logic in offline batch and online real-time paths? — senior signal.

The six-axis rubric — one weight decides which axis to invest in.

• Correctness (30%). Largest weight. If your submission gets the SCD-2 wrong, or produces duplicate aggregates under rebalance, or ships DQ tests that don't fail on obviously bad data, you lose 30% of your score in one axis. Correctness is where every take-home is won or lost.
• Code quality (20%). Second-largest weight. Modules with clear boundaries; typed function signatures; no 500-line main.py; language-idiomatic patterns. Reviewers can read code quality in 5 minutes; make the first 5 minutes look intentional.
• Tests (15%). Third weight. Reviewers open the tests/ folder before opening the source. A submission with zero tests loses 15% instantly and signals junior-level habits regardless of how good the code is.
• Documentation (15%). Tied third. A clean README with quickstart + architecture + design decisions + trade-offs + what-I-skipped is worth more than a comment on every function. Docs are the axis reviewers read first.
• Performance (10%). Smaller weight but easy to lose. Choosing INSERT ... VALUES in a loop over COPY, or for row in query.fetchall() over server-side cursors, or unindexed joins — each drops the axis. Getting it right is table-stakes; getting it wrong is a red flag.
• Extras / trade-off analysis (10%). Smallest weight but the differentiator between mid and senior. A retrospective section, an architecture diagram, a "what would change at 10× scale" paragraph, CI setup — any of these earns full extras.

The failure modes that lose axes across all five briefs.

• Over-engineering. Kafka for a CSV loader. Kubernetes for a 100 K-row job. Airflow for a single-task pipeline. Every one signals "cannot calibrate complexity to problem size" — auto-fail on multiple axes.
• Under-engineering. Single 500-line main.py with everything inline. No modules; no functions; no tests. Auto-fail on code quality + tests.
• Skipping the README. No docker-compose up instructions; no architecture; no design section. Reviewers open README first — an empty one loses 15% before they read a line of code.
• Ignoring the rubric. Spending 15 hours on a beautiful CI setup and 5 hours on the core transform. Balance hours to weights; correctness always comes first.

Worked example — Great Expectations suite for Brief 4

Detailed explanation. The canonical Brief 4 implementation: an expectation suite covering the five canonical expectation kinds against a raw.orders table, wired to run as a dbt post-hook or an Airflow task, with failures posted to a Slack webhook and the HTML docs served on localhost:8081. Walk through the suite and the wiring.

• Suite. orders_raw.warning.json — 12 expectations across the five kinds.
• Wiring. GE runs after every dbt refresh; failure of any strict expectation fails the pipeline; failure of a soft expectation posts a warning.
• Docs. great_expectations docs build generates static HTML; served by a small nginx container.

Question. Write the expectation suite (Python) and the Docker Compose service that runs GE + serves docs.

Input.

Expectation kind	Example	Strictness
Row count	expect_table_row_count_to_be_between(10000, 1000000)	strict
Uniqueness	expect_column_values_to_be_unique('order_id')	strict
Not-null	expect_column_values_to_not_be_null('user_id')	strict
In-set	expect_column_values_to_be_in_set('status', ['pending', 'paid', 'refunded'])	strict
Freshness	expect_column_max_to_be_between('event_ts', ...)	strict
Custom	expect_column_values_to_match_regex('email', r'^\S+@\S+$')	soft

Code.

# dq/build_suite.py — generate the orders_raw expectation suite
import great_expectations as gx
from great_expectations.checkpoint import Checkpoint

context = gx.get_context()

# 1. Connect to source
ds = context.sources.add_postgres(
    name="warehouse",
    connection_string="postgresql://dbt:dbt@postgres:5432/warehouse",
)
ta = ds.add_table_asset(name="orders_raw", table_name="orders", schema_name="raw")

# 2. Build suite with 6 expectations
suite = context.add_expectation_suite("orders_raw.warning")

batch_request = ta.build_batch_request()
validator = context.get_validator(batch_request=batch_request, expectation_suite=suite)

# Row count — strict
validator.expect_table_row_count_to_be_between(min_value=10_000, max_value=1_000_000)

# Uniqueness — strict
validator.expect_column_values_to_be_unique("order_id")

# Not-null — strict
validator.expect_column_values_to_not_be_null("user_id")
validator.expect_column_values_to_not_be_null("event_ts")

# In-set — strict
validator.expect_column_values_to_be_in_set("status", ["pending", "paid", "refunded"])

# Freshness — strict
from datetime import datetime, timedelta, timezone
now = datetime.now(timezone.utc)
validator.expect_column_max_to_be_between(
    column="event_ts",
    min_value=(now - timedelta(hours=24)).isoformat(),
    max_value=(now + timedelta(minutes=5)).isoformat(),
)

# Email format — soft (kept as warning; doesn't fail pipeline)
validator.expect_column_values_to_match_regex("email", r"^\S+@\S+\.\S+$")

validator.save_expectation_suite(discard_failed_expectations=False)

# 3. Wire checkpoint with Slack action on failure
context.add_or_update_checkpoint(
    name="orders_raw_checkpoint",
    validations=[{"batch_request": batch_request, "expectation_suite_name": "orders_raw.warning"}],
    action_list=[
        {"name": "store_validation_result", "action": {"class_name": "StoreValidationResultAction"}},
        {"name": "update_data_docs",       "action": {"class_name": "UpdateDataDocsAction"}},
        {"name": "slack_on_failure",       "action": {
            "class_name": "SlackNotificationAction",
            "slack_webhook": "${SLACK_WEBHOOK_URL}",
            "notify_on": "failure",
            "renderer": {"module_name": "great_expectations.render.renderer.slack_renderer", "class_name": "SlackRenderer"},
        }},
    ],
)

# 4. Run + build docs
result = context.run_checkpoint(checkpoint_name="orders_raw_checkpoint")
context.build_data_docs()

if not result["success"]:
    raise SystemExit("Data quality check failed — see docs on http://localhost:8081")

# docker-compose.yml (Brief 4 excerpt)
services:
  gx:
    build: ./dq
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      SLACK_WEBHOOK_URL: ${SLACK_WEBHOOK_URL:-}
    volumes:
      - ./dq:/workspace
      - ge-docs:/workspace/gx/uncommitted/data_docs
    command: python -m dq.build_suite

  ge-docs:
    image: nginx:alpine
    depends_on:
      gx:
        condition: service_completed_successfully
    ports:
      - "8081:80"
    volumes:
      - ge-docs:/usr/share/nginx/html:ro

volumes:
  ge-docs:

Step-by-step explanation.

1. The suite starts by connecting Great Expectations to Postgres via the add_postgres datasource and declaring orders_raw as a table asset. This is GE's declarative way of scoping which data to validate.
2. Each expect_* call is a first-class expectation with its own strictness. Six strict expectations cover the five canonical kinds (row count, uniqueness, not-null, in-set, freshness) — these fail the pipeline if any is violated.
3. expect_column_values_to_match_regex('email', ...) is a soft expectation — kept in the suite for visibility but doesn't fail the pipeline. This is the strict/soft distinction reviewers grade: senior candidates separate the two; junior candidates make everything strict (or nothing).
4. The Checkpoint binds the suite to (a) storing validation results, (b) updating the HTML docs, and (c) posting to Slack on failure. Slack alerting is the senior signal — reviewers explicitly look for it.
5. build_data_docs() generates static HTML in /workspace/gx/uncommitted/data_docs; the ge-docs nginx service serves it on localhost:8081. Reviewers open this page and immediately see suite results, coverage, and history.

Output.

Expectation	Kind	Strictness	Pass condition
Row count 10K-1M	Row count	strict	SELECT COUNT(*) in range
order_id unique	Uniqueness	strict	No duplicates
user_id not null	Not-null	strict	Zero nulls
status in ('pending','paid','refunded')	In-set	strict	No unknown values
event_ts max within 24h	Freshness	strict	Data is fresh
email matches regex	Custom	soft	Warning only

Rule of thumb. For every Brief 4 submission, ship (a) at least 8-10 expectations spread across the 5 canonical kinds, (b) a strict/soft distinction with reasoned justification, (c) alerting on failure (Slack, PagerDuty, or email), and (d) HTML data docs served by nginx or committed as static files. Reviewers open the docs page before they open the code.

Worked example — Feast feature store for Brief 5 with online + offline sync

Detailed explanation. The canonical Brief 5 implementation: a Feast feature repo declaring feature views for a churn model, an offline store on S3 Parquet (or local filesystem in Compose), an online store on Redis, and a materialisation job that syncs offline to online. The Docker Compose stack boots Redis, Postgres (offline mock), and a Feast service. The submission includes a training script that uses get_historical_features (PIT-correct) and a serving snippet that uses get_online_features (low-latency).

• Feature views. last_purchase_at, total_spent, session_count, days_since_signup per user_id.
• Offline store. File-based Parquet in data/offline/ (S3 in production).
• Online store. Redis on port 6379.
• Sync. feast materialize from offline to online, run on a cron.

Question. Write the Feast repo declaration, the training-time PIT-correct query, and the serving-time online lookup.

Input.

Component	Value
Feature view	user_features
Entity	user_id (int64)
Offline store	File (Parquet)
Online store	Redis
Freshness SLA	15 min (materialize every 15 min)
PIT correctness	Enforced via event_timestamp join

Code.

# feature_repo/feature_store.yaml
project: churn_features
registry: data/registry.db
provider: local
online_store:
    type: redis
    connection_string: redis:6379
offline_store:
    type: file
entity_key_serialization_version: 2

# feature_repo/entities.py
from feast import Entity, ValueType

user = Entity(
    name="user_id",
    value_type=ValueType.INT64,
    description="Application user",
)

# feature_repo/user_features.py
from datetime import timedelta

from feast import FeatureView, Field, FileSource
from feast.types import Float32, Int64, UnixTimestamp

from entities import user

user_source = FileSource(
    path="data/offline/user_features.parquet",
    event_timestamp_column="event_ts",
    created_timestamp_column="created_ts",
)

user_features = FeatureView(
    name="user_features",
    entities=[user],
    ttl=timedelta(days=90),
    schema=[
        Field(name="last_purchase_at", dtype=UnixTimestamp),
        Field(name="total_spent",      dtype=Float32),
        Field(name="session_count",    dtype=Int64),
        Field(name="days_since_signup",dtype=Int64),
    ],
    source=user_source,
    online=True,
)

# training/build_training_set.py — PIT-correct historical feature join
import pandas as pd
from feast import FeatureStore

fs = FeatureStore(repo_path="feature_repo")

# Entity dataframe — one row per training example, with event_ts = label time
label_events = pd.read_parquet("data/labels/churn_labels.parquet")
# columns: user_id, event_ts, churned (0 or 1)

training_df = fs.get_historical_features(
    entity_df=label_events,
    features=[
        "user_features:last_purchase_at",
        "user_features:total_spent",
        "user_features:session_count",
        "user_features:days_since_signup",
    ],
).to_df()

# PIT invariant: each row's features are as-of that row's event_ts
# — never features from after label time (no data leakage).
training_df.to_parquet("data/training/churn_train.parquet")

# serving/predict.py — online low-latency lookup
from feast import FeatureStore

fs = FeatureStore(repo_path="feature_repo")

def predict_churn(user_id: int) -> float:
    features = fs.get_online_features(
        features=[
            "user_features:last_purchase_at",
            "user_features:total_spent",
            "user_features:session_count",
            "user_features:days_since_signup",
        ],
        entity_rows=[{"user_id": user_id}],
    ).to_dict()

    # Feed features to loaded model (elided)
    return model.predict_proba([[
        features["last_purchase_at"][0],
        features["total_spent"][0],
        features["session_count"][0],
        features["days_since_signup"][0],
    ]])[0][1]

# docker-compose.yml (Brief 5 excerpt)
services:
  redis:
    image: redis:7-alpine
    ports: ["6379:6379"]
    healthcheck:
      test: ["CMD", "redis-cli", "ping"]
      interval: 3s

  feast:
    build: ./feature_repo
    depends_on:
      redis:
        condition: service_healthy
    volumes:
      - ./feature_repo:/workspace
      - ./data:/workspace/data
    command: bash -c "feast apply && feast materialize-incremental $$(date -u +%Y-%m-%dT%H:%M:%S)"

Step-by-step explanation.

1. feature_store.yaml declares Redis as the online store and local Parquet files as the offline store. provider: local keeps everything file-based for Docker Compose; provider: aws would wire S3 offline + DynamoDB online for production.
2. The FeatureView declares four features with typed schemas, a ttl=90 days (features older than this are considered stale), and a source with an explicit event_timestamp_column. The event timestamp is the anchor for PIT correctness.
3. get_historical_features(entity_df=..., features=...) executes a PIT-correct join: for each row in entity_df, Feast joins the latest feature value with event_ts <= entity_row.event_ts. This prevents the label-leakage bug where training features are later than the label time.
4. get_online_features(entity_rows=...) reads from Redis with sub-10-ms latency. The same feature names are used online and offline — this is the training-serving skew guarantee: whatever features the model was trained on, it sees at inference time.
5. feast materialize-incremental syncs the offline store's latest rows into Redis. Run on a 15-minute cron to meet the freshness SLA. Missing this cron means the online store serves stale features; the trade-off between cron cadence and Redis write cost is a doc-worthy design decision.

Output.

Path	Store	Latency	Freshness
get_historical_features	Parquet (offline)	seconds	as-of event_ts (PIT-correct)
get_online_features	Redis (online)	< 10 ms	last materialize (≤15 min old)
feast materialize	offline → online sync	minutes	cron every 15 min
Registry (registry.db)	file	—	version-controlled
TTL 90 days	offline + online	—	stale features flagged

Rule of thumb. For every Brief 5 submission, (a) name PIT correctness in the README's first paragraph, (b) separate online (Redis) from offline (Parquet/S3) explicitly, (c) use get_historical_features for training and get_online_features for serving with identical feature lists, and (d) document the materialize cadence and its trade-off. Missing the PIT-correctness call-out is a senior-signal auto-fail.

Senior meta-question on rubric-first take-home strategy

A senior interviewer might close a take-home debrief with: "Zoom out from the specific brief you just shipped. Walk me through how you'd approach any DE take-home — the intake, the scope-cut checkpoints, the rubric-first hour allocation, the README-driven workflow, the submission checklist. What would you never skip regardless of brief archetype?"

Solution Using rubric-first prioritisation + fixed submission checklist + retrospective section

The universal DE take-home playbook (any of 5 briefs)
=====================================================

INTAKE (hours 0-2)
------------------
1. Read brief 3x. Classify archetype (Brief 1-5). Identify heaviest
   rubric axis (usually correctness).
2. Send 5-8 clarifying questions covering scope, data, deployment,
   stack, and time.
3. Draft README skeleton with 7 sections: what, quickstart,
   architecture, assumptions, design decisions, trade-offs,
   what-I-skipped, testing.

BUILD (hours 2-14)
------------------
4. Simplest end-to-end first (hour 6 gate — if no green, cut scope).
5. Iterate to correctness (hour 14 gate — if not passing, ship simpler).
6. Commit early; commit often; each commit = fallback state.

FINISH (hours 14-20)
--------------------
7. Tests (unit + integration + smoke) at hour 16.
8. Design doc + architecture diagram at hour 18.
9. README polish + submission checklist at hour 20.

SUBMISSION CHECKLIST (must-hit before submit)
---------------------------------------------
[ ] docker-compose up boots green from empty state
[ ] README opens with quickstart in first 20 lines
[ ] Every mart / core module has at least 1 test
[ ] Design decisions section names >= 3 trade-offs explicitly
[ ] What-I-skipped section names stretch items + reason
[ ] Architecture diagram committed (draw.io PNG or ASCII)
[ ] Retrospective section names 3 things you'd do differently

RETROSPECTIVE TEMPLATE (extras axis; ~50 lines in README)
--------------------------------------------------------
"With 20 hours, I shipped X, Y, Z. If I had another 5 hours,
 I'd add A (CI), B (integration test against real Kafka), C
 (Prometheus metrics). At 10x scale I'd migrate D from batch
 to streaming and add E for observability. The single trade-off
 I'd revisit is F — I chose G for simplicity but H is the
 senior choice for production."

Step-by-step trace.

Phase	Hours	Deliverable	Rubric axis
Intake	0-2	5 clarifying questions + README skeleton	Extras + Docs
Simplest end-to-end	2-6	docker-compose up green baseline	Correctness (floor)
Core correctness	6-14	Full brief spec passing	Correctness (senior)
Tests	14-16	Unit + integration + smoke	Tests
Design doc	16-18	Architecture + trade-offs	Docs + Extras
Polish	18-20	README polish + submission checklist	Docs

After the 20-hour cycle, the submission covers every rubric axis regardless of brief archetype. The intake shapes scope; the scope-cut gates prevent runaway; the tests + design doc window guarantees the tests + docs axes are non-zero; the retrospective wins the extras axis. This is the universal playbook that generalises across all five briefs.

Output:

Rubric axis	Weight	Universal strategy
Correctness	30%	Simplest end-to-end at hour 6; full spec at hour 14
Code quality	20%	Modules with clear boundaries; language-idiomatic patterns
Tests	15%	Unit + integration + smoke by hour 16 (non-negotiable)
Docs	15%	README skeleton at hour 2; polish at hour 20
Performance	10%	Bounded memory; COPY not INSERT; indexed joins
Extras	10%	Retrospective + architecture diagram + CI if under budget

Why this works — concept by concept:

• Rubric-first prioritisation — allocates hours to rubric weights, not to features. Correctness gets 8 hours because it's 30%; docs get 3 hours because they're 15%. Every hour has a rubric target.
• Scope-cut gates at hour 6 and hour 14 — mandatory checkpoints where scope drops rather than the schedule slipping. Hour 6 gates the "end-to-end works" check; hour 14 gates the "core spec passes" check. Skipping gates is how take-homes silently blow past 40 hours.
• README-driven workflow — starting with an empty-but-structured README at hour 2 forces you to articulate scope, non-goals, and trade-offs before you write code. The README becomes the outline and the final artefact simultaneously.
• Fixed submission checklist — before submit, tick every item: docker-compose up green, README quickstart, tests present, design decisions named, what-I-skipped explicit, architecture diagram, retrospective. Missing any item is a rubric-axis loss.
• Cost — 20 real hours (matching the informal industry norm for a "4-8 hour" brief), one Git repo, one Compose file, ~15-30 tests depending on brief, one 7-section README, one architecture diagram. No wasted hours; no over-engineering. The eliminated cost is the 10-20 hours senior candidates typically lose to unfocused execution. Rubric coverage is monotone in the playbook — every hour raises the score on at least one axis.

Design
Topic — design
System design problems for DE take-home briefs

Practice →

SQL Topic — data-validation Data validation problems for DQ briefs

Practice →

---

Cheat sheet — DE take-home recipes

• Brief 1 ELT recipe (CSV → warehouse with SCD Type 2). Docker Compose with postgres:16-alpine + a loader service. Loader script reads CSV with pandas or duckdb, validates each row with pydantic (EmailStr, int, datetime), COPYs into a raw.<name>_staging table (truncated per run), then runs the SCD-2 merge against a <name>_dim table using IS DISTINCT FROM on tracked attributes so unchanged rows are a no-op. Enforce the double-open defense with CREATE UNIQUE INDEX idx_dim_current ON <name>_dim (natural_key) WHERE is_current. Ship at least three pytest cases: test_repeat_load_is_noop (idempotency), test_attribute_change_closes_and_opens (SCD-2 semantics), and test_double_open_rejected (schema-level guard). Prefer dbt snapshots with strategy: check + check_cols if the brief allows dbt — same rubric coverage at ~30% less code. Total: ~150 lines of SQL/Python + 3 tests + Docker Compose that boots in <30 s.

• Brief 2 streaming recipe (Kafka tumbling window + exactly-once). Redpanda (redpandadata/redpanda:v24.2.7) or KRaft-mode Kafka in Docker Compose; boot topics via a one-shot bootstrap-topics service (rpk topic create). Aggregator uses either Kafka Streams (Kotlin/Java) with processing.guarantee=EXACTLY_ONCE_V2 + TimeWindows.ofSizeAndGrace(15m, 2m) + Suppressed.untilWindowCloses + RocksDB state, or Python confluent-kafka with enable.idempotence=true + transactional.id=<unique> + producer.send_offsets_to_transaction + isolation.level=read_committed on the consumer + JSON-file state checkpoint every 10 s. Never ship separate producer.produce() + consumer.commit() calls — that's at-least-once masquerading as exactly-once. Include Kafka UI on localhost:8080 for reviewer inspection. Persist state on a named Docker volume so restart doesn't lose in-flight windows.

• Brief 3 dashboard recipe (dbt + Metabase from raw events). dbt-core project in Docker Compose alongside postgres:16-alpine + Metabase (metabase/metabase:v0.51.7). Project layout: models/staging/ (view; 1:1 clean over raw + _sources.yml), models/intermediate/ (view; composable joins), models/marts/core/ (table; dim_users + dim_products + fct_events), models/marts/metrics/ (incremental; mart_daily_metrics + mart_top_products + mart_rolling_dau). Ship 15-20 dbt tests across unique + not_null + relationships + accepted_values + expression_is_true. Metabase depends on dbt: service_completed_successfully so tiles work on first boot. Include a Makefile with up / down / refresh / test. Document the refresh SLA in the README with cadence + freshness + backfill + cost trade-off.

• Brief 4 DQ recipe (Great Expectations against a dirty source). Great Expectations 0.18+ (or 1.x) suite with 8-10 expectations across the five canonical kinds: expect_table_row_count_to_be_between (row count), expect_column_values_to_be_unique (uniqueness), expect_column_values_to_not_be_null (not-null), expect_column_values_to_be_in_set (in-set), expect_column_max_to_be_between (freshness). Distinguish strict from soft expectations explicitly. Wire a Checkpoint with SlackNotificationAction on failure (or PagerDuty). Run context.build_data_docs() and serve the HTML on localhost:8081 via an nginx sidecar. Failure of any strict expectation exits non-zero and fails the pipeline; soft expectations post a warning. Alternative: pure dbt tests with tests/ folder + custom SQL — lighter setup, same rubric coverage.

• Brief 5 feature store recipe (Feast with online + offline sync). Feast repo with feature_store.yaml declaring online_store: redis + offline_store: file (Parquet). Declare an Entity (e.g. user_id: int64), one or more FeatureViews with typed schemas (Float32, Int64, UnixTimestamp), and a FileSource with an explicit event_timestamp_column — the anchor for PIT correctness. Training-time uses fs.get_historical_features(entity_df=...) with an entity dataframe that has event_ts per row; Feast joins the latest feature value with event_ts <= entity_row.event_ts. Serving-time uses fs.get_online_features(entity_rows=...) for sub-10-ms Redis lookup. Materialise offline → online with feast materialize-incremental on a 15-minute cron. Name PIT correctness in the README's first paragraph.

• Rubric weights sticky-note. Correctness 30% (largest — where the brief's spec must pass), code quality 20% (factored modules, type hints, idiomatic patterns), tests 15% (unit + integration + smoke — zero tests = auto-fail at senior), docs 15% (README opens with quickstart; design decisions + trade-offs sections mandatory), performance 10% (bounded memory, COPY not INSERT, indexed joins), extras 10% (retrospective + architecture diagram + CI + "at 10× scale" section). Print this on a sticky note; use it in every take-home to allocate hours to weights, not features.

• README template (7 sections; ~200 lines). (1) # Project title + one-line summary of what the pipeline does. (2) ## Quickstart — 3 commands: git clone, docker-compose up, expected output. (3) ## Architecture — ASCII or PNG diagram + 2-3 paragraph explanation. (4) ## Assumptions — bullet list of unknowns you interpreted (e.g. "assumed clean CSV; unknown rows dropped with warning"). (5) ## Design decisions — 3-5 named decisions with alternatives considered (e.g. "chose Postgres over Snowflake because Docker Compose reproducibility outweighed cloud-native features for this brief"). (6) ## Trade-offs — 2-3 explicit trade-offs you made (e.g. "chose polling watermark over log-based CDC to fit the 20-hour budget; at 100× scale I'd migrate to Debezium"). (7) ## What I skipped — stretch items you didn't implement + reason. (8) ## Testing — how to run pytest/dbt test + what each test proves.

• Testing checklist (unit + integration + smoke — the 3 tests every submission needs). Unit test on the core transform — pytest against the merge/aggregate/expectation logic; runs in <1 second; no external dependencies. Integration test against a real containerised dependency — pytest-docker or testcontainers boots a fresh Postgres/Redis/Kafka; runs the pipeline; asserts state; runs in <30 seconds. Smoke test — docker-compose up boots green and every service reaches healthy state within 60 s; encoded as a shell script or a GitHub Actions workflow. All three must pass on a clean clone; run them in your CI (or manually) before submit.

• Dockerisation pattern (Docker Compose that "just works"). Use postgres:16-alpine (not postgres:latest — pin versions), redis:7-alpine, redpandadata/redpanda:v24.2.7, metabase/metabase:v0.51.7. Every service has a healthcheck; every dependent service uses depends_on: <svc>: condition: service_healthy or service_completed_successfully. Use named volumes for state that must survive down/up (dbt seed data, aggregator checkpoints, Redis persistence). Bind-mount project directories with :ro when read-only. Never use network_mode: host — breaks on macOS and reviewer sandboxes. Never rely on sleep 5 in an entrypoint; use healthchecks.

• Time-boxing rule (20-hour default budget). Design 20% (hours 0-4: read brief, clarifying questions, scope decisions, README skeleton, first commit). Build 50% (hours 4-14: simplest end-to-end at hour 6, full spec passing at hour 14). Docs + tests 25% (hours 14-19: pytest + dbt test suite, architecture diagram, design doc, README polish). Polish 5% (hour 19-20: final docker-compose down && up, sanity check, submit). Enforce scope-cut gates at hour 6 and hour 14 — if either gate is missed, cut a stretch item rather than sliding the schedule. Never go past hour 30; ship less complete rather than more incomplete.

• Common pitfalls (top 8 that lose rubric axes). (1) Over-engineering — Kafka for a CSV loader, Kubernetes for a single-task pipeline; auto-fail on judgement. (2) No README — every reviewer opens it first; empty = 15% lost. (3) Zero tests — auto-fail at senior level; 15% lost. (4) docker-compose up doesn't boot on a fresh clone — reviewers ding correctness + docs. (5) Single 500-line main.py — code-quality auto-fail. (6) Silent failure on bad input — reviewers expect defensive parsing + warning logs. (7) Missing the SCD-2 double-open defense — correctness axis lost on Brief 1. (8) Claiming exactly-once but shipping at-least-once — correctness axis lost on Brief 2.

• Clarifying-question template (5 questions in hour 1). (1) Scope — "Are all acceptance criteria must-haves, or would you prefer two ship well over three ship shallow?" (2) Data — "Should I assume the source is clean, or defensively parse malformed rows?" (3) Deployment — "Docker Compose local stack, or target your existing infrastructure?" (4) Stack — "Any preferred stack (dbt-core vs dbt-cloud; Postgres vs Snowflake; Python vs Java)?" (5) Time — "Is the 4-8 hour budget a hard cap? I typically ship in 15-20 real hours." Send them in the first hour before writing code; hiring managers universally respect the questions and answers narrow scope 20-40%.

• Submission checklist (must-hit before submit). [ ] docker-compose up boots green from docker-compose down -v state within 60 s. [ ] README opens with quickstart in first 20 lines. [ ] Every core module has at least one pytest test. [ ] pytest -q runs green on a clean clone. [ ] Design decisions section names ≥3 trade-offs. [ ] What-I-skipped section names stretch items + reason. [ ] Architecture diagram committed (PNG or ASCII). [ ] Retrospective section names 3 things you'd do differently at 10× scale. [ ] .env.example committed (never .env with real secrets). [ ] GitHub Actions or equivalent CI config committed if extras budget allows. Tick every item before the submit email.

• Over/under-engineering rule (calibrate complexity to problem size). Match tooling to problem size, not to your resume. A 100 K-row CSV loader with Kafka + Kubernetes + Airflow reads as "cannot calibrate" — auto-fail on multiple axes. A 10 M-event streaming aggregate with a single-file Python script and no state persistence reads as "does not understand the problem" — auto-fail on correctness. The right complexity for a 20-hour take-home is: one Docker Compose file, one language, 2-4 services, tests, README. If you need more, the brief is asking for more; if you need less, the brief is not.

• Retrospective template (extras axis; ~50 lines in README). Format: "With 20 hours, I shipped X, Y, Z. If I had another 5 hours, I'd add A (usually CI), B (usually a real integration test), C (usually Prometheus or logging). At 10× scale I'd migrate D from batch to streaming, add E for observability, and revisit F (naming a design decision I'd reconsider). The single trade-off I'd revisit is G — I chose H for simplicity/time-budget but I know J is the senior choice for production." This section alone earns most of the extras 10% and signals engineering maturity.

Frequently asked questions

How much time should I actually spend on a DE take-home?

The stated budget on almost every data engineering take home project in 2026 is "4-8 hours" — but every senior data engineer will tell you the real spend is 15-25 hours if you want to hit every rubric axis (correctness, code quality, tests, docs, performance, extras). The 4-8 hour framing is either the minimum-viable budget or the interviewer's polite fiction; nobody ships a testable, containerised, well-documented submission in 4 hours. Time-box strictly at 20-25 hours; do not slide past 30 hours regardless of how tempting one more feature is.

The mental model that keeps you under budget is rubric-first hour allocation: correctness gets 30% of the budget (~6-8 hours), code quality gets 20% (~4 hours, mostly refactor as you go), tests + docs each get 15% (~3 hours each), and performance + extras split 20% (~4 hours combined). Enforce two mandatory scope-cut gates: hour 6 (must have docker-compose up green end-to-end) and hour 14 (must have the core spec passing). If either gate is missed, cut a stretch feature immediately rather than pushing the schedule. Ship a polished 80% — a submission that hits every axis at 80% coverage beats a submission that maxes correctness and has zero tests + no README.

For the truly time-crunched, the minimum-viable 12-hour version is: hours 0-2 clarifying questions + README skeleton, hours 2-8 simplest end-to-end with correctness on the core requirement only (skip stretches), hours 8-10 unit tests + integration test, hours 10-12 README polish + submission. This is a passing submission, not a strong one. Reserve 20-25 hours if the offer matters.

Should I over-engineer to show off Kafka / Kubernetes / Airflow?

No — over-engineering is one of the most reliable auto-fail signals at senior level. Every senior interviewer has read a 100 K-row CSV loader with a Kafka topic + Zookeeper + Redis + Airflow + Kubernetes manifest and immediately downgraded the candidate. The rubric axis that governs this is judgement — implicit under code quality (20%) but load-bearing across correctness (30%) and performance (10%). Adding infrastructure that the brief doesn't need is not senior signal; it is the opposite of senior signal.

The correct complexity for a data engineering take home is: match the tool to the problem size named in the brief. A CSV loader with 100 K rows needs a Python script + Postgres + Docker Compose — nothing more. A streaming aggregate on a Kafka topic needs Kafka + a consumer + a state store — but not Kubernetes and not a service mesh. A dashboard from raw events needs dbt + Metabase + a warehouse — but not Airflow for a single-DAG refresh. If you find yourself reaching for Kubernetes on a take-home, stop; you are optimising for the wrong signal.

The safe rule of thumb: one Docker Compose file, one primary language, 2-4 services, tests, README. If the brief explicitly names Kafka, use Kafka. If it explicitly names Airflow, use Airflow. If it does not name a tool, do not add the tool. Senior interviewers weight "picks the right tool for the problem" more heavily than "knows exotic tools" — and shipping a clean minimal stack beats a Rube Goldberg pipeline every time.

What if I run out of time and can't finish?

Ship what you have, honestly documented. The single most-cited senior-signal move on incomplete submissions is a ## What I skipped section in the README that names each unimplemented item, explains why it was skipped (time budget, dependency issue, out of scope), and describes what you would do next if you had another 5 hours. Reviewers vastly prefer "shipped 80% honestly with a plan for the remaining 20%" over "shipped 100% with hidden gaps or broken tests."

The specific pattern that works: (1) Cut features at the hour-14 scope-cut gate rather than sliding the schedule. (2) In the README, list every acceptance criterion from the brief with a status: [x] implemented, [~] partial, [ ] skipped — see notes. (3) For each skipped item, write 2-3 sentences on your approach if you had done it (e.g. "SCD-2 implemented as SCD-1 due to time; the migration to SCD-2 would add a valid_from/valid_to/is_current triple and a MERGE with IS DISTINCT FROM on tracked attributes"). (4) Keep the tests you did write passing — never submit with a broken pytest -q.

Hiring managers reading this pattern see (a) you understood the full brief, (b) you time-boxed responsibly, (c) you know how to finish the missing work. They do not see incompetence; they see engineering maturity. Compare with the alternative — shipping 100% with hidden gaps, or worse, with failing tests — which reads as "either does not test or does not know it is broken." The honest incomplete submission wins.

Should I add CI / GitHub Actions?

Yes if it fits under the extras budget — typically the last 30-60 minutes of a 20-hour cycle. A minimal .github/workflows/ci.yml that runs docker-compose up -d, waits for services healthy, executes pytest -q (or dbt test), and posts the result badge to the README is worth roughly 3-5% of the total rubric score — the exact size of the extras axis. It signals "I ship production-flavoured artefacts even under time pressure" — a senior signal that separates candidates.

The minimal-viable CI config:

# .github/workflows/ci.yml
name: CI
on: [push, pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Boot stack
        run: docker-compose up -d --wait
      - name: Run tests
        run: docker-compose exec -T <test-service> pytest -q
      - name: Tear down
        if: always()
        run: docker-compose down -v

However — do not add CI at the expense of tests or README polish. The rubric weights say tests (15%) and docs (15%) are three times more valuable than the extras axis (10%). If hour 19 arrives and the README is thin or a mart test is missing, fix that instead of adding CI. The rule of thumb: CI is bonus; tests + docs are non-negotiable. Add CI only if the plan runs under budget and the mandatory axes are already at full coverage.

How much documentation is enough?

The right amount is enough that a reviewer who never opens the code understands what you built, why you built it that way, and what you consciously left out. In practice this is a ~200-line README with seven named sections: (1) one-line summary of what the pipeline does; (2) quickstart (3 shell commands + expected output); (3) architecture (ASCII diagram or PNG + 2-3 paragraphs); (4) assumptions (bullet list of unknowns you interpreted); (5) design decisions (3-5 named decisions with alternatives considered); (6) trade-offs (2-3 explicit trade-offs); (7) what-I-skipped (stretch items + reason); plus a separate ## Testing section explaining how to run and what each test proves.

Do not confuse documentation quantity with documentation quality. A 1000-line README full of low-signal Javadoc-style comments scores lower than a 200-line README with 5 well-articulated design decisions. Reviewers scan for structure — clear headers, code blocks with commands, an architecture diagram, and a trade-off section. Inline code comments are for non-obvious choices only — do not comment every line, and do not leave a TODO graveyard. If a function needs a docstring to explain what it does, the function name is probably wrong.

The senior signal that pays: an architecture diagram committed as a PNG (draw.io, Excalidraw, or Mermaid) plus a trade-offs section with 2-3 named alternatives you considered and rejected. Reviewers open these sections first; they are the highest-leverage 500 words in the entire submission. Everything else is table-stakes.

Should I ask clarifying questions before I start coding?

Yes — universally, without exception. Sending 5-8 crisp clarifying questions in the first hour of a data engineering interview take home is a senior signal that also functions as a scope-cut lever. Hiring managers appreciate the questions; the answers narrow scope by 20-40%, which frees hours you would have spent building the wrong thing. Silence in hour 1 signals junior habits; well-crafted questions signal engineering maturity.

The template that generalises across all five briefs: (1) Scope — "Are all acceptance criteria must-haves, or would you prefer two ship well over three ship shallow?" (2) Data — "Should I assume the source is clean, or defensively handle malformed rows and schema drift?" (3) Deployment — "Docker Compose local stack, or target your existing infrastructure?" (4) Stack — "Any preferred stack (dbt-core vs dbt-cloud; Postgres vs Snowflake; Python vs Java/Kotlin)?" (5) Time — "Is the 4-8 hour budget a hard cap or a soft target? I typically ship in 15-20 real hours." Add 1-2 brief-specific questions if the brief has obvious ambiguities.

Send the questions in one crisp email — not a Slack conversation, not a pull request thread. Frame them as "before I start, five quick clarifying questions" so the hiring manager knows the ask is bounded. Offer to make judgement calls on all five if the manager prefers, but note that the answers will materially improve the submission. Most managers reply within a day with clear answers; even a "no strong preference" reply is useful because it converts an unknown into a documented assumption. Never start coding without the answers if the brief is genuinely ambiguous; you will pay 5-10 hours of wasted effort per unresolved ambiguity.

Practice on PipeCode

• Drill the SQL practice library → for the SCD Type 2, deduplication, and incremental-load patterns that dominate Brief 1 ELT take-homes — the fastest way to build the muscle memory reviewers grade against.
• Rehearse on the ETL practice library → for the batch pipeline patterns, idempotency invariants, and staging → dim/fact layering discipline that Brief 1 and Brief 3 hiring managers explicitly probe.
• Sharpen the streaming axis with the streaming practice library → for the tumbling-window, exactly-once, and state-persistence problems that make or break Brief 2 submissions.
• Practice the aggregation problem set → for the windowed sums, per-key groupings, and rolling-metric patterns that appear in Briefs 2 and 3 side-by-side.
• Warm up the join practice library → for the fact-dimension joins that power every Brief 3 dashboard and every Brief 1 SCD-2 point-in-time query.
• Rehearse the window functions library → for the rolling-DAU and top-N-per-group patterns central to Brief 3 dashboard tiles.
• Drill the SCD (slowly changing data) library → for the close-old-open-new invariant that decides Brief 1 correctness.
• Stack against the data-validation library → for the Great Expectations-style row-count, uniqueness, not-null, in-set, and freshness patterns that carry Brief 4.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the five-brief archetype map against real graded inputs — the same rubric axes hiring managers use.

Lock in DE take-home muscle memory

Docs explain briefs. PipeCode drills explain the decisions — when SCD-2 close-old-open-new needs the schema-level double-open guard, when Brief 2 exactly-once requires `send_offsets_to_transaction`, when a Brief 3 mart must ship dbt tests to pass the tests-axis rubric, when Brief 4 needs strict-vs-soft expectation distinction, when Brief 5's point-in-time correctness call-out earns full extras. Pipecode.ai is Leetcode for Data Engineering — rubric-first practice tuned for the exact take-home signals mid-and-senior interviewers grade against.

Practice SQL problems →
Practice streaming problems →