Advanced & launch, ship your own.

A pure design-before-code week: you stand up your capstone repository skeleton and fill it with decision artifacts — a project charter, a 4-part architecture document, and 3 ADRs — anchored to a real, profiled public source in your own target industry. No pipeline code ships this week; the deliverable is a defensible design a reviewer can trace from source to golden record. You are proving a human made judgment calls and wrote down why.

1. Pick your industry and shortlist 2 candidate real public sources (from data.gov, SEC EDGAR, CoinGecko, ReliefWeb, NOAA, CMS/HealthData.gov, or Faker for locked-down/fraud domains). For each, open the actual portal and note volume, refresh cadence, format, and licence/ToS.
2. Profile your top candidate for 30–60 min: pull a sample, count rows, inspect natural keys, spot duplicates/nulls/format drift, and confirm it is messy enough and rich enough to reach golden records. Write a 5-line ‘source feasibility’ note and commit the sample profile.
3. Scaffold the capstone repo from the starter template: create /docs (charter.md, architecture.md), /docs/adr (0001–0003), /src, /tests, /resources placeholders, and a README stub. Do NOT write pipeline code — leave TODO markers.
4. Write charter.md: 1–2 sentence business framing, the problem, the named decision-making persona, the data-product grain (one row = ?), and 3 measurable KPIs the product moves. Restate the problem in one sentence at the top.
5. Write architecture.md Part 1 (context/C4-lite diagram: source → Databricks → consumer) and Part 2 (medallion data flow) — declare the explicit grain at bronze, silver, and gold, and where CDC/idempotency and quarantine live.
6. Write architecture.md Part 3 (Unity Catalog governance: catalogs/schemas, ownership, access grants, PII handling) and Part 4 (deployment: DABs bundle layout, CI-CD stages, environments).
7. Author 3 ADRs using the template — one each for a batch-vs-streaming choice, a modeling choice (star vs Data Vault vs OBT), and a matching choice (exact vs fuzzy). In each, record options considered, the decision, what you gave up, and the revisit trigger.
8. Do a ‘30-second skim’ self-audit: open only the README + top of each doc and check a stranger could name the problem, the grain, and the 3 decisions. Fix anything that fails the skim.
9. Record a 3–5 minute Loom walking a reviewer through the charter and one ADR in trade-off shape (‘I chose X over Y because Z, which cost me A, I’d revisit via B’), tying the decision to your persona. Commit and push everything with a clean history.

• Michael Nygard — Documenting Architecture Decisions (the original ADR post)
• adr.github.io — Architecture Decision Records (templates & tooling)
• Databricks Well-Architected Lakehouse framework
• The C4 model for visualising software architecture (context/container diagrams)
• Databricks Asset Bundles (DABs) — project structure & deployment
• Data Vault vs. dimensional (star) modeling — trade-offs overview
• Unity Catalog — governance model for the lakehouse

# ADR 0001: <TODO short decision title, e.g. Batch vs. streaming ingestion>

- Status: Proposed            <!-- TODO: Proposed | Accepted | Superseded by ADR-#### -->
- Date: 2026-09-23
- Deciders: <TODO your name>
- Persona affected: <TODO the named decision-maker this serves, NOT a tool>

## Context
<!-- TODO: 3-5 sentences. What business/technical forces make this decision necessary?
     Tie it to the persona and a KPI from the charter. Recognition before evaluation. -->

## Options considered

### Option A — <TODO name>
- Pros: <TODO>
- Cons: <TODO>

### Option B — <TODO name>  (the one you will REJECT — write it just as carefully)
- Pros: <TODO>
- Cons: <TODO>

### Option C — <TODO optional third>
- <TODO or delete>

## Decision
<!-- TODO: One sentence in trade-off shape:
     "I chose X over Y because Z." -->

## Consequences
- What this costs me (A): <TODO the price you knowingly pay>
- What I gave up: <TODO the capability the rejected option would have given>
- Revisit trigger (B): <TODO the concrete signal/metric that would make me reopen this>

## Links
- Charter: ../charter.md
- Architecture: ../architecture.md#<TODO section>

• Most Dangerous Data Architecture Decisions (Ranked)
• The $50K Pipeline Mistake Every Data Engineer Makes
• The Question Every Engineer Should Ask Before Coding

• Designing Data-Intensive Applications, 2nd Ed. O'Reilly · Book
• Fundamentals of Data Engineering O'Reilly · Book

A mini-project in your chosen track, applied to the capstone where possible. TRACK A: re-model one subject area of your capstone (or Project 1) as a Data Vault 2.0 raw vault — hubs, links, satellites with hash keys and lineage columns — load it idempotently in Databricks, then build a small star-schema mart on top and write the ADR on when this earns its keep. TRACK B: add one bounded LLM step to your capstone's bronze/silver flow (e.g., standardize messy free-text categories, extract fields from unstructured source, or classify records), wrap it in a semantic/context layer plus input/output guardrails and a quarantine-on-low-confidence path, then build and run an eval harness against a hand-labeled gold set and report the pass rate. Both tracks end with the same deliverables: working code, an ADR, and a documented human-in-the-loop override.

1. CHOOSE (both tracks): In your capstone repo, create docs/adr/ADR-00X-elective.md. Write your track choice and a reader-first rationale (2-4 sentences). State what you are deliberately NOT building this week and why (over-engineering guard). Commit before writing any code.
2. TRACK A — Step 1: Pick ONE subject area from your capstone/Project-1 silver model (e.g., provider–payer–price, or customer–order–product). Sketch the vault on paper: identify business keys (hubs), relationships/transactions (links), and descriptive/historical attributes (satellites). Save the sketch as an image in docs/.
3. TRACK A — Step 2: In a Databricks notebook, build the hub tables: compute a deterministic hash key over the business key, add load_date and record_source. Load insert-only and idempotent (re-running adds no duplicates). Repeat for link tables (hash of the parent business keys).
4. TRACK A — Step 3: Build satellite tables: hash-diff the descriptive columns so only CHANGED context inserts a new row (recoverable history). Verify by loading the same batch twice and a modified batch once; confirm row counts prove idempotency + change capture.
5. TRACK A — Step 4: Build a small star-schema mart (one fact + 2-3 dims) as a gold view/table ON TOP of the vault, to prove the vault still serves consumers. Then write the ADR body: when Data Vault beats a star here, the join/complexity cost, and your revisit trigger.
6. TRACK B — Step 1: Pick ONE narrow, bounded task on your real source (standardize free-text categories, extract fields from unstructured text, OR classify records into an allowed set). Define a DETERMINISTIC contract: exact output schema + the closed set of allowed values. Write it down first.
7. TRACK B — Step 2: Build the semantic/context layer: assemble the schema, business glossary, and allowed-value list you will inject into the prompt so the model reasons over YOUR data. Call the LLM via Databricks ai_query / an LLM API from a notebook on a small batch.
8. TRACK B — Step 3: Add guardrails: validate/normalize input, enforce the output schema, and route low-confidence or off-contract responses to a QUARANTINE table (reuse your Phase 1-3 quarantine pattern) instead of silently accepting them. Log every call (input, output, confidence) for audit.
9. TRACK B — Step 4: Hand-label a gold set (30-100 rows), run the eval harness (MLflow evaluate or a simple accuracy/precision script), record the pass rate, and pick ONE case where you override the model — document it. Write the ADR: why a bounded LLM step here, cost/latency/non-determinism, and your revisit trigger.

• Databricks — Data Vault best practices on the Lakehouse
• AutomateDV (dbtvault) — Data Vault 2.0 hub/link/satellite reference & hashing
• Scalefree — Data Vault 2.0 concepts (hubs, links, satellites, hash keys)
• Databricks — ai_query / AI Functions for batch LLM inference in SQL & Python
• MLflow — LLM evaluation (mlflow.evaluate) for building an eval harness
• Databricks — Unity Catalog governance for AI/model + data assets

# Week 45 elective scaffold. Choose ONE track. Delete the other block.
# Deliverables for BOTH tracks: working code + ADR + documented human-in-the-loop override.

from pyspark.sql import functions as F

CATALOG = "<your_capstone_catalog>"   # TODO: set
SCHEMA  = "<your_schema>"            # TODO: set
RECORD_SOURCE = "<source_system_name>"  # TODO: e.g. 'cms_provider_data'

# ============================================================
# TRACK A — DATA VAULT 2.0 (hub / link / satellite)
# ============================================================

def hash_key(*cols):
    # TODO: normalize (trim/upper/null-handling) BEFORE hashing so keys are stable.
    # Standard is sha256 over business key(s) with a separator.
    concatenated = F.concat_ws("||", *[F.upper(F.trim(F.col(c).cast("string"))) for c in cols])
    return F.sha2(concatenated, 256)

def load_hub(src_df, business_keys, hub_table):
    # Insert-only + idempotent: only NEW business keys land.
    staged = (src_df
        .withColumn("hub_hk", hash_key(*business_keys))
        .withColumn("load_date", F.current_timestamp())
        .withColumn("record_source", F.lit(RECORD_SOURCE))
        .select("hub_hk", *business_keys, "load_date", "record_source")
        .dropDuplicates(["hub_hk"]))
    # TODO: MERGE into hub_table ON hub_hk WHEN NOT MATCHED INSERT.
    # TODO: assert re-running this batch adds 0 rows (idempotency proof).
    raise NotImplementedError("TODO: implement idempotent hub MERGE")

def load_satellite(src_df, hub_keys, context_cols, sat_table):
    # Only insert a new row when the descriptive context CHANGED (hash-diff).
    staged = (src_df
        .withColumn("hub_hk", hash_key(*hub_keys))
        .withColumn("hash_diff", hash_key(*context_cols))
        .withColumn("load_date", F.current_timestamp())
        .withColumn("record_source", F.lit(RECORD_SOURCE)))
    # TODO: compare hash_diff to the latest sat row per hub_hk; insert only on change.
    # TODO: prove recoverable history: load same batch twice (no new rows),
    #       then a modified batch once (exactly the changed rows appear).
    raise NotImplementedError("TODO: implement hash-diff satellite load")

# TODO: load_link() for the relationship/transaction (hash of parent business keys).
# TODO: build a small star-schema mart (1 fact + 2-3 dims) as a gold view ON TOP.
# TODO: ADR — when Data Vault beats a star HERE, the join cost, your revisit trigger.

# ============================================================
# TRACK B — AI-ASSISTED DE (one bounded LLM step)
# ============================================================

ALLOWED_VALUES = ["<value_1>", "<value_2>", "<UNKNOWN>"]  # TODO: closed set = your contract

def build_context(row):
    # Semantic/context layer: inject YOUR schema + glossary + allowed values.
    # TODO: return a compact prompt grounded in the row + the allowed set only.
    raise NotImplementedError("TODO: assemble context-grounded prompt")

def call_llm(prompt):
    # TODO: call via Databricks ai_query OR your LLM API (load Claude API skill for Anthropic).
    # Return dict: {"value": ..., "confidence": ...}
    raise NotImplementedError("TODO: single bounded call, typed output")

def guarded_infer(row):
    result = call_llm(build_context(row))
    # GUARDRAILS:
    # TODO: enforce result['value'] in ALLOWED_VALUES (else route to quarantine).
    # TODO: if confidence < THRESHOLD -> quarantine table, do NOT accept silently.
    # TODO: log (input, output, confidence) to an audit table for lineage.
    raise NotImplementedError("TODO: enforce output contract + quarantine path")

# TODO: hand-label a gold set (30-100 rows) -> gold_labels table.
# TODO: eval harness (mlflow.evaluate or accuracy/precision script); record PASS RATE.
# TODO: pick ONE case you OVERRIDE the model; document why (human-in-the-loop).
# TODO: ADR — why a bounded LLM step here; cost/latency/non-determinism; revisit trigger.

• What Senior Data Engineers Actually Use AI For
• The Four Things Your AI Agent Needs Before Production
• Senior-Engineer AI Architecture Checklist (free)

• Building a Scalable Data Warehouse with Data Vault 2.0 O'Reilly · Book
• Data Vault: An Introduction (Michael Olschimke) Udemy · Course
• Generative AI for Data Engineers Specialization (IBM) Coursera · Specialization

A full technical-interview simulation run entirely on your existing two-project portfolio (Project 1 healthcare + your capstone) — no new data. You build and run timed SQL drills against your own silver/gold tables, construct and cold-run a system-design whiteboard, grade a take-home against a rubric, then harden the portfolio's README, architecture doc, and repo hygiene so it survives the 30-second skim. Output is a reusable interview-prep dossier plus a measurably cleaner repo.

1. Assemble your SQL drill deck: from the 6-pattern catalog, write 10 problem PROMPTS (not solutions) that target your own silver/gold tables — e.g., 'return one row per provider per effective period' (SCD-2), 'find the longest run of days a price stayed unchanged' (gap-and-island), 'rows in bronze with no matching gold key' (anti-join). Store each with the expected grain and a one-line expected-shape assertion.
2. Run three timed 25-minute SQL sets using the starter harness: solve against your real tables, speak the grain + pattern aloud before typing (record yourself), and log per-problem: solved/not, time, and where you stalled. Target >=8/10 by set three.
3. Build your capstone's 6-box whiteboard one-pager: fill every box (grain, source & CDC/incremental strategy, bronze/silver/gold, idempotency & reprocessing, tests/quarantine, serve & Unity Catalog governance) with a specific fact from YOUR build, plus the golden-record output at the end.
4. Cold whiteboard a NOVEL prompt in 20 minutes on the clock (use an unfamiliar public schema, e.g. NYC TLC trips): drive the same 6 boxes, then explicitly map each box back to how you solved the equivalent in your capstone. Record the audio; note where you froze.
5. Grade a take-home against the rubric: take your own capstone (or a peer's) submission and score it on grain correctness, idempotency, tests/quarantine, docs/ADRs, and repo hygiene. Write the 3 highest-leverage gaps and the concrete fix for each.
6. Refactor the portfolio README + architecture doc: lead with a one-line 'what data product / what grain / two golden-record outputs', embed the architecture diagram, link the 4-part architecture doc and ADRs, and add a 'skim in 30 seconds' section. Test it by having someone read for 30s and repeat it back.
7. Repo hygiene pass: verify structure, .gitignore, zero secrets/credentials in history, a real commit story, pinned deps, and that the DABs/CI-CD config and tests are visible from the root. Fix anything a reviewer would flag.
8. Record a 2-minute spoken tradeoff narration on one real capstone decision using the 'X over Y because Z, cost A, revisit via B' shape, tied to a named stakeholder — not too clean, not rehearsed.
9. Assemble the Interview Prep I dossier: SQL scorecard, whiteboard one-pager, cold-prompt notes, graded take-home rubric, and links to the refactored README/architecture doc and the recorded tradeoff.

• DataLemur — SQL interview questions (windowing, ranking, joins) with difficulty tiers
• StrataScratch — SQL & data-project interview practice
• Designing Data-Intensive Applications (Kleppmann) — the system-design vocabulary interviewers expect
• Databricks — Lakehouse / Well-Architected best practices (grounding for the whiteboard's serve & govern boxes)
• Architecture Decision Records (ADR) — templates and rationale
• Make a README — structure and checklist for a README that reads senior

# Databricks notebook / PySpark. Timed SQL drill harness for Interview Prep I.
# Run one cell per problem. Speak grain + pattern ALOUD before you type the SQL.
import time
from dataclasses import dataclass, field

CATALOG = "TODO_your_catalog"      # TODO: your Unity Catalog
SCHEMA  = "TODO_your_silver_schema"  # TODO: drill against SILVER, not GOLD
TIME_BUDGET_MIN = 25                  # per 10-problem set

@dataclass
class Drill:
    id: str
    prompt: str          # the ask, as an interviewer would state it
    grain: str           # ONE row per ____  (state before solving)
    pattern: str         # windowing | dedup-to-grain | gap-and-island | as-of/SCD2 | anti-join
    solution_sql: str = ""  # TODO: you fill this on the clock

# TODO: author 10 drills against YOUR silver tables. Keep prompts realistic;
#       do NOT pre-write solution_sql — leave it empty and solve live.
DRILLS: list[Drill] = [
    # Drill("D1", "Return the current effective row per provider", "one row per provider", "dedup-to-grain"),
    # Drill("D2", "Longest run of days price stayed unchanged", "one row per price-run", "gap-and-island"),
    # ... TODO: 8 more, covering every pattern in the catalog at least once.
]

def narrate_gate(d: Drill) -> None:
    # Force the interview habit: say it before you type it.
    print(f"[{d.id}] PATTERN={d.pattern} | TARGET GRAIN='{d.grain}'")
    print("Say aloud now: (1) restate the ask (2) name the grain (3) name the pattern. Then type.")
    # TODO: optionally record audio here for later self-review.

def assert_grain(df, key_cols: list[str]) -> None:
    total = df.count()
    distinct = df.select(*key_cols).distinct().count()
    ok = total == distinct
    print(f"GRAIN CHECK on {key_cols}: rows={total} distinct={distinct} -> {'PASS' if ok else 'FAIL'}")
    # TODO: if FAIL, your query broke the grain — the #1 thing interviewers watch. Fix before moving on.

def run_drill(d: Drill, key_cols: list[str]):
    narrate_gate(d)
    t0 = time.time()
    # TODO: write d.solution_sql live, then execute it:
    # df = spark.sql(d.solution_sql.format(cat=CATALOG, sch=SCHEMA))
    df = None  # TODO replace
    elapsed = round(time.time() - t0, 1)
    print(f"[{d.id}] elapsed={elapsed}s")
    # assert_grain(df, key_cols)  # TODO enable once df is real
    # TODO: log solved(Y/N), elapsed, and where you stalled into your scorecard.
    return df

# scorecard: append one row per attempt across your 3 timed sets.
SCORECARD: list[dict] = []  # TODO: {id, set, solved, seconds, stall_note}
# TODO: after 3 sets, compute solved-rate and median time; target >=8/10 by set 3.

• The Senior Engineer Interview Question Exposing Impostors
• Your Portfolio Project Is Getting You Rejected!

• Cracking the Data Engineering Interview O'Reilly · Book
• Practicing Coding Interview Questions in Python DataCamp · Course

A no-new-data communication lab: you script, record, and self-critique a 5–8 minute portfolio walkthrough, build a personal 'story bank' of trade-off and failure answers mined from your own ADRs, and run two timed mock rounds (a human behavioral round and an AI-screen round) against a scoring rubric. The deliverable is evidence that a human — you — did the work and can explain the judgment behind it.

1. Story-mine your own repos (60 min): open your Project 1 and capstone ADRs, quarantine logs, and architecture docs. Extract at least 8 raw decision points into the story-bank template — one row each for the grain choice, an SCD/CDC choice, the MDM match-threshold choice, a quarantine/quality gate, an idempotency guarantee, a governance (Unity Catalog) choice, a cost/measure-first optimization, and one genuine failure you hit and fixed.
2. Convert 5 rows to the TRADE-OFF shape (45 min): for each, write one sentence in 'I chose X over Y because Z, which cost me A, and I would revisit via B.' Reject any row where A ('what it cost') or B ('how I'd revisit') is blank — a missing cost is the tell of a rehearsed non-answer. Every Z must name a person or role.
3. Convert 3 rows to the FAILURE-STORY shape (45 min): scene → what you SAW (the actual symptom in data/logs, e.g. row counts doubling, a null spike, a duplicate golden record) → diagnosis in order → the fix → one lesson. Keep each to ~90 seconds spoken.
4. Script the walkthrough (45 min): fill the walkthrough beat-sheet scaffold — 30s framing / 60s architecture+one ADR / 3–4 min bronze→silver→gold→golden-record demo narrating one live quality decision / 60s second project as repeatable method / 30s ownership close. Write speaker notes, not a read-aloud paragraph; mark the ONE decision you will narrate live.
5. Prep the 30-second skim (20 min): before recording, verify the two repos pass the skim test — README leads with business framing + architecture diagram, ADR folder visible, tests/quarantine visible, a badge or CI status. Fix anything that would make a skimmer think 'tutorial.'
6. Record take 1 and self-score (40 min): screen-record the walkthrough (Loom/OBS/QuickTime). Watch it once against the rubric: Did you open with a person, not a tool? Did you stay in 5–8 min? Did you narrate a live decision, not just click? Did you end on golden records + ownership? Note the three weakest moments.
7. Record take 2 (30 min): re-record fixing only those three moments. Do NOT polish to 'too clean' — leave the natural seams. Keep both takes; you will compare in the live session.
8. Run the AI-screen simulation (30 min): use the provided prompt runner (or a phone timer) for 5 prompts, HireVue rules — prompt shown, 30s think, one 2–3 min take, no re-record, camera on. Save all five clips unedited.
9. Draft your answer-key notes (15 min): for each AI-screen clip, jot the one improvement you'd make — this becomes your pre-interview warmup sheet, not a re-record.

• Amazon Behavioral Interview / STAR method and Leadership Principles (official prep guidance)
• HireVue — how asynchronous / AI video interviews work (candidate guidance)
• Architecture Decision Records (ADR) — Michael Nygard's original template and pattern
• Loom — record and share a screen + camera walkthrough (free tier)
• OBS Studio — free open-source screen recorder (Loom alternative, fully local)

# Portfolio Walkthrough Beat-Sheet + Story Bank (STARTER — fill every TODO)

## PART A — 5–8 min Walkthrough beat-sheet
# Rule: every beat names a PERSON/ROLE before it names a tool. Mark the ONE decision you narrate live with (LIVE).

### [0:00–0:30] Business framing (RECOGNITION before evaluation)
- Who is hurt when this data is wrong? TODO: <named role, e.g. "the finance analyst reconciling contracted prices">
- The one-line stakes: TODO: <what bad decision the wrong data causes>
- DO NOT open with "So I used Databricks..."  <- delete this line only when you've replaced the opener above

### [0:30–1:30] Architecture in 60s (slow-front-end, fast-back-end)
- Grain of the gold product: TODO: <one row = ____>
- The 4-part arch doc lives at: TODO: <repo link>
- The ONE ADR I'll name out loud: TODO: <decision + the person it served>

### [1:30–5:00] Demo path bronze -> silver -> gold -> golden record
- Bronze (raw, recoverable history): TODO: <what I show>
- Silver (conformed + quality gate): TODO: <the quarantine/test I point at>
- (LIVE) Narrate ONE decision as it happens: TODO: <e.g. match threshold, idempotent MERGE key>
- Gold / MDM golden record: TODO: <the survivorship rule + why>

### [5:00–6:00] Second project = proof of a repeatable METHOD
- Same spine, different industry: TODO: <one contrast that proves it wasn't luck>

### [6:00–6:30] Ownership close
- What I'd watch in prod / what I'd revisit: TODO: <one honest item>

---
## PART B — Story Bank (one row per decision; convert to a shape)
# Reject any TRADE-OFF row with an empty COST or REVISIT column.

| # | Decision point | Shape | X (chose) | Y (over) | Z (because — NAME THE PERSON) | A (cost) | B (revisit via) |
|---|----------------|-------|-----------|----------|-------------------------------|----------|-----------------|
| 1 | Grain          | trade-off | TODO | TODO | TODO | TODO | TODO |
| 2 | CDC / SCD      | trade-off | TODO | TODO | TODO | TODO | TODO |
| 3 | MDM threshold  | trade-off | TODO | TODO | TODO | TODO | TODO |
| 4 | Quality gate   | trade-off | TODO | TODO | TODO | TODO | TODO |
| 5 | Governance     | trade-off | TODO | TODO | TODO | TODO | TODO |

### Failure stories (3 rows) — scene -> what I SAW -> diagnosis in order -> fix -> one lesson
- F1: TODO scene | SAW: TODO <the symptom in data/logs> | diagnosis: TODO -> TODO -> TODO | fix: TODO | lesson: TODO
- F2: TODO ...
- F3: TODO ...

---
## PART C — AI-screen runner (HireVue rules: 30s think, one 2-3 min take, no re-record)
# TODO: run these 5 prompts on a timer; save clips; do NOT re-record.
prompts = [
  "Tell me about a time you caught a data-quality problem others missed.",   # TODO answer live
  "Describe a technical decision you would make differently now.",           # TODO
  "Walk me through a disagreement about a design choice.",                   # TODO
  "Tell me about the hardest ambiguity in your capstone.",                  # TODO
  "Why this architecture and not something simpler?",                        # TODO (defend-your-design)
]
# TODO: after each, write ONE improvement note in your warmup sheet — not a re-record.

• The Senior Engineer Interview Question Exposing Impostors
• Your Portfolio Project Is Getting You Rejected!

• Successful Interviewing Coursera · Course
• Cracking the Data Engineering Interview O'Reilly · Book

A three-week solo build sprint that turns your Week 44 charter into a shipped, defensible data product on your own industry. Week one is design and the bronze/silver foundation; week two is the gold layer, CDC/incremental with idempotency, and MDM golden records; week three is governance, tests/quarantine, DABs/CI-CD hardening, and a recorded walkthrough. You work mostly solo with minimal scaffolding - the point is to prove you can do it without hand-holding. Learners without a viable second source instead extend Project 1 with a new business question and a new source, applying the identical checklist.

1. WEEK 1 - DESIGN & CHARTER LOCK (design before code). Finalize the project charter: name the stakeholder, the one business question, the grain of your gold product, the definition of 'done', and explicit non-goals. Write the 4-part architecture doc (context/sources -> logical medallion design -> physical Databricks/Unity Catalog layout -> operations & recovery). Open an ADR log and write your first 3 ADRs (e.g., source choice, grain choice, incremental strategy). Commit charter + architecture + ADRs BEFORE any pipeline code - slow on the front end, fast on the back end.
2. WEEK 1 - SOURCE & BRONZE. Ingest your real messy source (data.gov / CMS / SEC EDGAR / CoinGecko / ReliefWeb / NOAA / Faker for locked-down domains) into a bronze table exactly as received: full fidelity, source metadata columns (ingest timestamp, source file/endpoint, batch id), no cleaning. Profile the raw data and write down, in the README, the three messiest things you found (nulls, dupes, drifting schema, encoding, late records) - these become your silver requirements.
3. WEEK 1 - SILVER, TESTED. Build silver: typed, deduplicated, conformed, with a clear primary key at the declared grain. Encode your data-quality expectations as explicit assertions and route failures to a quarantine table with a written reason code - do not silently drop rows. Add the first round of tests (schema, not-null on keys, uniqueness at grain, referential checks). Prove a deliberately corrupted batch lands in quarantine and does NOT reach silver.
4. WEEK 2 - GOLD PRODUCT. Model the gold layer to answer the charter's business question at the declared grain (dimensional or wide aggregate as fits). Write the grain statement at the top of the gold table doc. Validate gold against a hand-computed spot check so you can defend the numbers, not just produce them.
5. WEEK 2 - CDC / INCREMENTAL + IDEMPOTENCY PROOF. Convert the load from full-refresh to incremental (MERGE / change-feed / watermark as appropriate). Then PROVE idempotency: run the identical incremental load twice and capture evidence that gold row counts and keys are unchanged. Replay one prior day/batch and show the pipeline reconstructs the same state (recoverable history). Log both proofs as assertions or screenshots in the repo.
6. WEEK 2 - MDM GOLDEN RECORDS. Resolve entities across your source(s) into golden records: define match rules, survivorship rules (which field wins and why), and a stable golden key. Document why you chose deterministic vs probabilistic matching and what a wrong merge would cost the stakeholder. Show a before/after on a real duplicate cluster from your data.
7. WEEK 3 - GOVERNANCE & SECURITY. Put the whole product under Unity Catalog: catalog/schema layout, table + column comments, ownership/grants that reflect least privilege, and lineage you can screenshot. Tag or mask at least one sensitive column and write one sentence on the governed-access decision and who it protects.
8. WEEK 3 - DABs / CI-CD HARDENING. Package everything as a Databricks Asset Bundle and deploy through a CI/CD pipeline (lint + tests run on push, bundle deploys the job). Prove repeatable deployment: tear the job down and redeploy it from a clean clone with one command, and confirm it runs green. This is the 'repeatable deployment' pillar made real.
9. WEEK 3 - RECORD, POLISH & DEFEND. Finalize the README for the 30-second senior skim (grain, diagram, run instructions, test/CI badges, ADR links). Record a 5-8 minute walkthrough that opens on the business question and ends on golden records. Pre-write trade-off-shaped answers to the three hardest questions about your build. Do a final teardown-redeploy-rerun to confirm end-to-end reproducibility before you call it shipped.

• Databricks Asset Bundles (DABs) - overview & CI/CD
• Unity Catalog - governance, lineage, and grants
• Delta Lake MERGE INTO (idempotent upserts / CDC)
• Delta change data feed (CDC / incremental)
• The medallion (bronze/silver/gold) architecture
• Architecture Decision Records (ADR) - Michael Nygard's original template

# capstone/  (repo skeleton - the 30-second senior skim starts here)
#   README.md                <- grain statement, diagram, run-from-clean-clone steps, CI badge
#   docs/architecture.md      <- 4 parts: context/sources | logical | physical | ops & recovery
#   docs/adr/0001-*.md ...    <- >= 3 ADRs, Nygard template
#   databricks.yml            <- DAB bundle (TODO: define job, targets, CI deploy)
#   src/bronze.py  src/silver.py  src/gold.py  src/mdm.py
#   tests/  .github/workflows/ci.yml

from pyspark.sql import DataFrame, SparkSession
from delta.tables import DeltaTable

# --- INCREMENTAL, IDEMPOTENT UPSERT STUB -------------------------------------
def merge_incremental(spark: SparkSession, source_df: DataFrame,
                      target_table: str, business_keys: list[str]) -> None:
    """Upsert a batch into a Delta target so that re-running the SAME batch
    produces the SAME target (idempotency). Fill the TODOs; do not ship stubs."""
    # TODO(grain): assert source_df is unique on `business_keys` BEFORE merge,
    #   or your MERGE will double-count. Route violations to quarantine.
    # TODO(cdc): if using change data feed / watermark, filter source_df to the
    #   new increment here and record the watermark you consumed.
    merge_condition = None  # TODO: build "tgt.k = src.k AND ..." from business_keys

    tgt = DeltaTable.forName(spark, target_table)
    (tgt.alias("tgt")
        .merge(source_df.alias("src"), merge_condition)  # TODO: real condition
        # TODO: whenMatchedUpdate only when a change is detected (hash compare)
        # TODO: whenNotMatchedInsertAll()
        .execute())
    # TODO(proof): after merge, assert row_count(target) and key-set are stable
    #   on a second identical run; log the assertion into docs/ as your evidence.

# --- QUALITY GATE + QUARANTINE STUB -----------------------------------------
def split_valid_quarantine(df: DataFrame) -> tuple[DataFrame, DataFrame]:
    # TODO: encode your declared expectations (not-null keys, unique at grain,
    #   domain/range checks). Return (valid, quarantined_with_reason_code).
    # Never silently drop - every quarantined row carries a written reason.
    raise NotImplementedError

# --- MDM GOLDEN RECORD STUB --------------------------------------------------
def resolve_golden_records(silver_df: DataFrame) -> DataFrame:
    # TODO: match rules (deterministic vs probabilistic - justify in an ADR)
    # TODO: survivorship rules (which field wins, why) + stable golden key
    raise NotImplementedError

• 30-Day Databricks Portfolio Projects That Get You Hired
• The Data Project That Actually Gets You Hired!
• Example repo: ca-public-health-facility
• DE Job-Search Blueprint (free)

• Data Engineering Professional Certificate (DeepLearning.AI) Coursera · Certificate
• Data Engineer in Python (career track) DataCamp · Track