Enterprise Data Platform Capstone Project

Data Engineering & Modern Data Platforms · Lesson 68 · Production Data Engineering and Capstone

Lesson Overview

The Monday board is no longer a slide. It is a product you must build, operate for several weeks, and defend with evidence. TechLearningPro's learning organization needs a governed path from raw progress events to a certified completion mart that forty people can open before standup, with definition_version pinned, quality gates that can refuse a publish, observability that names the broken hop, an SLO with an error budget, a cost sheet that survives a price-card update, and a runbook a teammate can follow at 05:40 without calling you.

This is Capstone Project 6. Lessons 59 through 67 gave you DataOps habits, observability, governance, reliability language, infrastructure as code, system-design practice, interview drills, exercises, and assessment. Module 5 left you one serving engine, one cloud choice with reopen conditions, lakehouse table semantics, identity and audit scope, and a cost sheet that refuses to trade 06:00 for idle waste. Module 4 left an optional Kafka path and Schema Registry and Event Contracts for the stream form of a contract. Module 3 left a Spark publish with a gate and a pointer. This lesson does not invent a seventh platform. It asks you to assemble the ones you already studied into one operable progress→completion platform and to keep it honest for multiple weeks.

The volumes stay fixed with the course: about 40 GB of compacted Parquet progress a day at design scale, a raw JSON landing that can be orders of magnitude larger if you still keep one, a completion mart of tens of gigabytes once columnar and grained, about forty board readers on Monday morning. Your local fixture will be thousands of rows with the same schemas and the same failure shapes. Passing the fixture without a production configuration, SLO statement, cost sheet, and runbook is an incomplete capstone. Passing a glamorous architecture diagram that never published a day is also incomplete.

Learning Objectives

By the end of this capstone you will be able to:

1. State the business questions the completion board answers, and the questions it deliberately refuses.
2. Fix grain, keys, prohibited columns, and definition_version for the certified mart.
3. Choose one cloud and one serving engine from Module 5, with reopen conditions written beside the choice.
4. Design ingest → quality → transform → lakehouse table → mart publish → board, with Kafka as an optional path rather than a second truth.
5. Implement Spark and SQL transforms that are idempotent for a restated event_date list.
6. Wire quality gates so only a passing gate moves the publish pointer or swaps the certified view.
7. Publish observability signals for freshness, volume, schema, and job health that map to Data Observability and Operational Monitoring.
8. Write an SLO and error budget for 06:00 publish that matches Data Reliability and SLA/SLO Design.
9. Deliver a cost sheet that splits scan bytes, capacity hours, idle hours, and safety copies per Data Platform Cost and Performance Optimization.
10. Operate for multi-week phases with acceptance criteria, a demo script, a grading rubric, and a runbook that covers late data, gate failure, and rollback.

Prerequisites

• Lessons 59 through 67 in this module. The capstone is their integration. Reread the control sheet, the SLO template, the IaC boundary, and the system-design checklist before you open a cloud console.
• Module 5, especially lessons 48 through 58: lake, lakehouse, Delta or Iceberg, one serving engine, security scope, and the cost sheet.
• Spark Performance Optimization and Big Data Processing Capstone for file size, adaptive execution, and gate-before-pointer.
• Data Quality Engineering for fail, quarantine, and publish bits.
• Apache Airflow Architecture and DAGs or an equivalent orchestrator you already operate in this course's spirit.
• Lesson 43 if you take the optional Kafka path. Do not clone the Kafka course. Use registry compatibility as one contract surface among table and API contracts that lesson 69 will generalize.
• No internal TechLearningPro SDK. Public Spark, SQL, object storage, warehouse or lake SQL, and documented cloud services only.

Detailed Core Concepts

The product you are shipping

Content, learning science, and the Monday operations board ask, for a business date: how many learners reached a completion state under completion-v4, by lesson and by course, with duplicates removed, with prohibited columns absent, and with a publish that finished before 06:00 local board time. They ask it again after a restatement. They do not ask this platform to charge a card, to serve the five-minute provisional tile as the certified grain, or to maintain two engines that both claim to be certified.

The certified deliverable is a mart table or view, for example published.completion_attempt, at a grain you write down and keep: typically one row per (learner_key, lesson_id, attempt_number) or the course-agreed completion grain from earlier modules, with event_date, definition_version, and measures the board needs. A secondary aggregate such as published.completion_day may exist for cheap board scans. Both must share one publisher and one definition_version.

Raw progress remains immutable landing. Conformed or bronze/silver layers hold typed, deduplicated, quality-checked data. The lakehouse table (Delta or Iceberg) holds the analytical truth the mart reads. The board reads the mart only. Investigators get a cutoff workgroup or role that can touch raw under a date predicate, per the cost and security lessons.

What "multi-week" means here

This is not a weekend tutorial. Plan four phases with gates between them. A phase that fails its acceptance criteria does not unlock the next phase's grade weight. Instructors and peer reviewers should see evidence per phase: schemas, job ids, gate logs, dry-run bytes, SLO burn, cost lines, and a short recorded or live demo.

Adjust calendar to your cohort. Do not collapse Phase 4 into a slide. Operation is the product.

Paths you may choose

Batch-primary path (default). Object storage landing of progress Parquet or compacted JSON, orchestrated daily (and restatement) jobs, Spark or warehouse SQL transforms, lakehouse table, mart publish, board on the pruned mart. This matches most of Modules 2, 3, and 5.

Optional Kafka path. A topic such as learning.progress (JSON or Avro with registry) lands to the same raw zone via Connect or a consumer job. The certified mart still comes from the lakehouse/batch publish. The stream may feed a provisional tile with a different SLO. Cursors stay separate. Two writers into the certified grain fail the design review.

You must declare which path is in scope in Phase 0. Adding Kafka mid-capstone without updating the charter, contracts, and runbook is a grade risk, not a feature.

Volumes, fixtures, and honesty

Write design volumes in the project README. Reviewers must see which tests used the fixture and which settings change at design scale.

Ownership boundaries

You own: ingest configuration for the declared path, quality rules and gate, Spark/SQL transforms, lakehouse table commits, mart publish pointer or swap, board SQL, observability dashboards or queries, SLO and error-budget log, cost sheet, runbook, IaC for what you deploy.

You do not own: rewriting the product's OLTP enrollment system, building a second cloud "for comparison" that also serves the board, inventing a private SDK, or replacing Module 4 with a Kafka-only certified mart.

Metric owner (role you assign in the charter): definition_version, unknown-lesson or duplicate caps, restatement horizon. Platform owner: identities, audit gap, reservation isolation. On-call for the phase-4 weeks: a named person or rotation, even if it is you.

Decision lens for every design choice

1. Correctness: complete, valid, deduplicated, semantically consistent with completion-v4.
2. Freshness: publish before 06:00; provisional tile separate if present.
3. Scale: design volumes above; fixture proves shape, not volume.
4. Reliability: replay, late data, gate failure, rollback of pointer.
5. Security and governance: least privilege, prohibited columns, audit scope from lesson 57/61.
6. Cost and operability: measured bytes and H vs H_idle; runbook that a stranger can use.

Architecture and Design Diagrams

End-to-end progress→completion. Dashed edge is the optional Kafka path. The board never reads raw.

mermaidCopy
flowchart LR
  subgraph sources [Sources]
    APP["Learning app progress"]
    KFK["Optional: learning.progress topic"]
  end
  subgraph land [Landing]
    RAW["Raw zone immutable"]
    Q["Quality + quarantine"]
  end
  subgraph curate [Curate]
    SP["Spark / SQL transforms"]
    LH["Lakehouse table Delta or Iceberg"]
  end
  subgraph serve [Serve]
    MART["published.completion_*"]
    BOARD["Monday board"]
  end
  subgraph ops [Operate]
    OBS["Observability"]
    SLO["SLO and error budget"]
    COST["Cost sheet"]
    RB["Runbook"]
  end
  APP --> RAW
  KFK -.-> RAW
  RAW --> Q --> SP --> LH --> MART --> BOARD
  SP --> OBS
  LH --> OBS
  MART --> OBS
  OBS --> SLO
  MART --> COST
  BOARD --> COST
  SLO --> RB
  OBS --> RB

Publish clock versus restatement. Only a green gate moves the certified pointer.

mermaidCopy
sequenceDiagram
  participant Orch as Orchestrator
  participant Job as Transform job
  participant Gate as Quality gate
  participant Ptr as Publish pointer
  participant Board as Board SQL
  Orch->>Job: Run for event_date list
  Job->>Job: Stage under job_id
  Job->>Gate: Staged metrics + samples
  alt Gate pass
    Gate->>Ptr: Swap or update pointer
    Board->>Ptr: Read certified definition_version
  else Gate fail
    Gate->>Orch: Fail task; keep previous pointer
    Orch->>Orch: Page runbook; burn error budget if late
  end

Technical Explanation

Ingest that does not invent a second truth

Landing writes append-only objects keyed by ingest time or by a documented partition. Progress fields include at least event_id, learner_key, lesson_id, event_time, and enough payload to evaluate completion under completion-v4. Email, payment tokens, and raw secrets are prohibited at the gate even if a buggy producer emits them.

If you use Kafka, the consumer or Connect sink must land the same logical event once into raw with an idempotency key (event_id). Offset commits follow after durable land, not before. Schema evolution follows lesson 43 compatibility rules for the subject you registered. The batch job still owns the certified grain.

Compaction is part of landing operations when tools emit tiny files. Target 128–512 MB for analytical reads before repeated transforms, matching lessons 31 and 58.

Quality as a publish bit

Checks that belong in the gate for this capstone:

• Null or empty event_id, learner_key, lesson_id rate above a signed threshold fails publish.
• Duplicate event_id within the staged day fails or is resolved by a documented deterministic rule before publish.
• Prohibited columns present in staged output fail publish.
• Unknown lesson_id rate above the metric owner's cap fails publish (rows may remain visible in a quarantine or with null manifest attributes, but the certified mart does not silently absorb an unbounded unknown rate).
• Row-count reconciliation: staged facts versus source counts within a documented tolerance after dedupe.
• definition_version must equal the pinned version for this release.

A green transform with a red gate leaves the previous pointer. That is success of the gate, not failure of the platform.

Transforms: Spark and SQL

Spark 3.5 DataFrame or Spark SQL path: filter the restatement horizon by event_date (or ingest horizon with event-time derivation), project allowed columns, join broadcast lesson manifest, apply completion rules for completion-v4, aggregate to grain, write Parquet/Delta/Iceberg to a staging location keyed by job_id.

Warehouse SQL path (if your Module 5 engine is the transform home): MERGE or partition overwrite into the lakehouse-synced or native table, same grain and version columns, same gate before the certified view swaps.

Idempotency: a second job id for the same event_date list replaces staged bytes and, after a new green gate, replaces the pointer. Neighbor dates stay. uuid() and nondeterministic salts in the transform are refused; salts use deterministic hashes when needed for skew.

Lakehouse table

Use Delta or Iceberg as taught in Module 5. Partition by event_date. Keep definition_version as a column, not only as a folder rumor. Commits are the unit of atomicity. Do not publish by renaming a directory and hoping list-after-write is enough unless your charter explicitly stays on the plain-lake marker pattern from lesson 48 and you document the limits.

Time travel and snapshot expiration belong in the runbook: how to read yesterday's certified snapshot, how long you keep it, who may expire.

Mart publish and board SQL

The board statement names columns and filters event_date and definition_version directly so the planner prunes. Example shape:

sqlCopy
SELECT lesson_id, COUNT(*) AS completions
FROM published.completion_attempt
WHERE event_date BETWEEN DATE '2026-11-03' AND DATE '2026-11-09'
  AND definition_version = 'completion-v4'
GROUP BY lesson_id;

Dry-run or history bytes before and after pointing the board at the mart are Phase 3 evidence. A BI extract must invalidate on definition_version change.

Observability

Minimum signals:

• Job: start, end, success/fail, duration, rows in/out, file count, average file size.
• Data: freshness of max event_date published, volume vs baseline, null/duplicate/unknown rates, schema fingerprint or contract version.
• Serve: board query latency and bytes scanned for the certified statement.
• Budget: SLO burn for missed 06:00; cost lines for scans and capacity.

Map alerts to runbook sections. An alert without an owner and a next step is decoration.

SLO and cost

Primary SLO candidate: certified mart for yesterday's event_date queryable with definition_version = completion-v4 by 06:00 on board days, 99% over a rolling window you define in Phase 0. Error budget is the allowed misses. A cheaper plan that systematically misses 06:00 is not a saving.

Cost sheet lines: storage class for raw and curated; bytes scanned for board SQL; capacity hours H inside the window and H_idle outside; ELT isolation; optional second region only with restore drill; no second certified mart.

Security

Reuse lesson 57/61: separate roles for land, transform, gate, board read, and investigate-raw. KMS and audit scope stay as decided. Prohibited columns are a gate, not a wiki hope. Row-level filters on learner_key or org unit, if your cohort requires them, belong on the mart and must be tested with a negative case: a principal that must not see another tenant's completions.

Orchestration and the morning critical path

The orchestrator is not the product, but it owns the clock. A typical board-day DAG:

1. Wait for landing completeness signal (file marker, Kafka lag under threshold for the optional path, or both as documented).
2. Compact if the metadata check says the day is too fragmented.
3. Run transform for event_date = yesterday (and any restatement list).
4. Run gate against stage.
5. On success, move pointer / refresh certified view; on failure, alert and stop.
6. Run board canary query: the certified SQL with a bytes and latency capture.
7. Emit observability events and append the error-budget log if step 5 slipped past 06:00.

Retries must be idempotent. Retrying a gate failure by appending a second certified copy is how Module 3's doubled day returns. Retry staging under a new job_id, re-gate, then move the pointer once.

SLAs in Airflow (or equivalent) are orchestration alerts. Product SLOs are promises to the board. Do not confuse a task SLA of "finish in 40 minutes" with "mart ready by 06:00." Both can exist; only the product SLO burns the error budget in this capstone.

Late data and the restatement horizon

Progress events carry event_time and land with an ingest timestamp. An event for Tuesday that arrives Wednesday must not be ignored if it falls inside the metric owner's restatement horizon (commonly a few days). The operate phase must include at least one such event in the fixture and in a live or lab restatement.

Rules that keep you honest:

• Certified aggregates for a date are not final until the horizon closes.
• The board may show "preliminary" versus "closed" if you add that attribute; do not silently change Tuesday's number on Friday without a restatement job id in the log.
• Streaming tiles do not rewrite certified dates to absorb late data; the batch restatement does.

What "done" is not

Done is not a green CI on lint. Done is not a warehouse full of tables named after feelings. Done is a reviewer reproducing: land → refuse bad data → publish good data → read board → break gate → see old pointer → restore → restate late event → show cost and SLO artifacts. If any hop is "we'll add later," the phase is not exited.

Practical Implementation or Design Walkthrough

Phase 0 — Charter (acceptance)

Deliver a decision record that states:

1. Cloud and region; serving engine; lakehouse format (Delta or Iceberg).
2. Batch-primary vs Kafka-optional; if Kafka, topic name, registry subject, and landing job.
3. Grain of published.completion_attempt and any aggregate.
4. definition_version string for the cohort (completion-v4 unless instructor overrides).
5. SLO draft and error budget.
6. Owners: metric, platform, on-call.
7. Out of scope list (OLTP rewrite, second engine serving, Flink substitute for certified publish, etc.).
8. Fixture location and how production config differs.
9. Identity sketch: roles for land, transform, gate, board, investigate-raw.
10. Restatement horizon in days and how late events enter the date list.

Exit criteria: reviewer signs the charter or returns written gaps. No cloud spend required yet beyond what your school lab already allows.

Charter anti-patterns to reject in review: "we will support all three clouds"; "Spark and the warehouse both certified"; "SLO: best effort"; "Kafka TBD"; "security later"; "cost sheet after go-live."

Phase 1 — Ingest and quality

1. Create raw bucket/prefix or equivalent with lifecycle notes.
2. Land fixture progress; optionally land one synthetic Kafka batch if in scope.
3. Implement contract validation (JSON Schema or Avro checks; SQL expectations).
4. Quarantine bad rows; metrics for quarantine rate.
5. Document producer ownership: which app field maps to which column.

Exit criteria: deliberate bad fixture fails the gate; good fixture lands; prohibited column sample is rejected; README shows how to replay a day.

Phase 2 — Transform and lakehouse

1. Implement Spark or SQL job reading raw/conformed for an explicit date list.
2. Write staged output under job_id.
3. Create/merge lakehouse table partitioned by event_date with definition_version.
4. Prove idempotent rerun for the same dates.
5. Attach explain or warehouse plan for the main join/aggregate.
6. Compact if file sizes are pathological.

Exit criteria: two job ids, one visible sum after second green path; crash before pointer leaves previous sum (simulate by failing gate); neighbor date untouched.

Phase 3 — Mart, board, observability, cost

1. Publish mechanism: pointer file, Iceberg branch, Delta symlink pattern, or view swap—documented and atomic enough for your engine.
2. Board SQL with prune-friendly filters; before/after bytes.
3. Dashboards or saved queries for the minimum signals.
4. Cost sheet filled with measured or labeled-illustrative rates.
5. IaC or documented click-ops boundary from lesson 63: what is code, what is console, why.

Exit criteria: board reads only mart; observability shows a forced failure; cost sheet names refusals (no raw board scans, no dropped grain check).

Phase 4 — Operate, restate, demo

1. Restate a bounded date list including a late event whose ingest date is after event_date.
2. Run an incident drill: gate fail, late publish, and rollback of pointer.
3. Keep an error-budget log for the operate window.
4. Deliver demo script evidence and rubric packet.

Exit criteria: restatement correct; drill notes with timestamps; demo under time limit; rubric self-score with links to artifacts.

Minimal project layout (suggested)

textCopy
capstone-completion/
  README.md
  charter/decision-record.md
  contracts/progress.schema.json
  jobs/transform_completion.py
  sql/board_completion.sql
  gates/quality_checks.sql
  ops/slo.md
  ops/cost-sheet.md
  ops/runbook.md
  ops/observability.md
  fixtures/
  tests/
  iac/   # optional but scored

Gate sketch (SQL expectations style)

sqlCopy
-- Pseudocode / warehouse assertions; adapt to your runner.
ASSERT (SELECT COUNT(*) FROM staged.completion_attempt
        WHERE learner_key IS NULL OR lesson_id IS NULL) = 0;

ASSERT (SELECT COUNT(*) FROM staged.completion_attempt c
        WHERE EXISTS (
          SELECT 1 FROM information_schema.columns x
          WHERE x.table_name = 'completion_attempt'
            AND x.column_name IN ('email', 'payment_token')
        )) = 0;

ASSERT (
  SELECT COUNT(*) FROM staged.completion_attempt
  WHERE definition_version <> 'completion-v4'
) = 0;

Transform sketch (PySpark shape)

pythonCopy
# PySpark 3.5.x sketch — fixture-friendly; production config beside it.
from pyspark.sql import functions as F

PROHIBITED = {"email", "payment_token", "card_number"}

def assert_no_prohibited(df):
    bad = PROHIBITED.intersection(set(df.columns))
    if bad:
        raise ValueError(f"prohibited columns: {sorted(bad)}")

def build_completion(progress, manifest, definition_version="completion-v4"):
    assert_no_prohibited(progress)
    p = progress.select(
        "event_id", "learner_key", "lesson_id", "event_time", "event_date"
    ).dropDuplicates(["event_id"])
    m = F.broadcast(manifest.select("lesson_id", "course_id", "manifest_id"))
    joined = p.join(m, "lesson_id", "left")
    # apply_completion_v4 is your documented rule set, not a hidden SDK
    return apply_completion_v4(joined).withColumn(
        "definition_version", F.lit(definition_version)
    )

Label any omitted helpers as required student work. Do not submit a notebook that only prints schemas.

Enterprise Real-World Scenarios

TechLearningPro: the board that lied about being done

A cohort declared Phase 3 complete because a dashboard showed numbers. The dashboard read a personal schema that applied completion-v3 SQL. The lakehouse table held completion-v4. Monday's meeting used the personal schema. The gate had never run against the board's statement. Observability showed green jobs writing v4. The demo failed when the reviewer pointed the certified view at the board SQL and the counts moved.

Repair: board SQL in the repo is the only allowed statement; BI tools import it; definition_version is a filter, not a comment; Phase 3 exit includes a diff of board bytes and a screenshot of the version pin. Personal schemas are deleted or renamed sandbox_.

Northline: Kafka as a second mart

Northline's optional path became a Streams job writing directly to published.completion_attempt "for freshness," while the nightly Spark job also merged. Counts flickered. The on-call runbook said "rerun Spark," which doubled some days and not others.

Repair: Kafka lands raw only; provisional tile uses a different table and SLO; certified grain has one writer; charter amendment required before any second writer; incident review cites cursor separation from earlier streaming lessons.

Contoso learning portal: cost sheet after the invoice

A provisioned warehouse sized for Monday ran all month. The board still scanned seven days of raw JSON through a legacy workbook. The capstone cost sheet said "TBD." The invoice arrived during Phase 4.

Repair: before/after dry runs; point workbook at mart; split H and H_idle; pause or lower baseline when resume fits; refuse to call the late board a cost win. Lesson 58's arithmetic becomes Phase 3 evidence, not optional reading.

Healthcare training subsidiary: prohibited columns under audit

A subsidiary delivering clinical continuing education inherited a progress producer that still attached email "for support." The transform projected it into the lakehouse "temporarily." The board never showed email. An auditor asked who could SELECT * from the lakehouse role. The answer was "the transform role and three analysts who share it."

Repair: prohibited list in the gate fails publish if the column exists anywhere in staged output; transform role cannot be the board role; support tools use a separate path with its own legal basis; capstone security evidence includes a negative test that a board principal cannot read email even when a bad stage tried to ship it. Lesson 61's privacy posture is not optional color for this cohort.

Fintech academy: SLO burned by shared pools

The merge and the board shared one warehouse queue. A backfill kicked off at 05:10 after a bugfix. The board queued behind it. Jobs were "successful" at 06:25. The error budget had no entry because the team tracked only hard failures, not lateness.

Repair: isolate ELT and board; cap backfills outside the morning window or on a separate pool; define lateness as an SLO miss; append the budget log whenever the canary query is late, even when every task is green. Reliability is a clock, not only a stack trace.

What a strong Phase 4 week looks like

Monday: normal publish, canary green, budget untouched. Tuesday: inject late events for Monday's event_date, restate, confirm board movement with a new job id in the log. Wednesday: fail the gate with a prohibited column in a staging branch, confirm pointer unchanged, fix, republish. Thursday: pause and resume capacity if your engine supports it; record resume minutes against the 06:00 margin. Friday: peer runs the runbook rollback section while you stay silent except for safety. Artifacts from that week are the operate grade.

Sample week-by-week evidence pack

Reviewers grade faster when evidence is boring and complete. A sample pack:

Week 1 folder. Charter PDF or markdown with signatures; raw prefix screenshot or aws s3 ls equivalent; contract file; failing fixture log; passing fixture log; quarantine metric query.

Week 2 folder. Job config for production vs fixture; explain output; two job_id directories listed; neighbor-date count query before/after; lakehouse DESCRIBE HISTORY or snapshots list.

Week 3 folder. Board SQL; dry-run bytes before/after; observability screenshot or saved query IDs; cost sheet spreadsheet; IAM role list; IaC plan or click-ops exception list.

Week 4 folder. Error-budget log CSV; restatement job id; late-event fixture note; gate-fail drill notes with timestamps; rollback command transcript; demo recording link; self-scored rubric.

Zip or link the pack in the README. Do not make the reviewer mine Slack.

Grading philosophy for instructors

Weight operate and honesty above novelty. A student who chooses batch-only, hits every acceptance gate, and writes a clear "Kafka out of scope" charter should outscore a student who wires Kafka, Streams, Flink, and two warehouses while missing the gate-before-pointer rule. Novelty without operability is a Module 4 remix, not a Module 6 capstone.

Partial credit guidance: if Phase 2 is excellent but Phase 4 is empty, cap the overall score below passing even if the weighted sum looks kind. The rubric rows for ops and demo exist to prevent that loophole—enforce a minimum in those rows.

Dependency versions (pin what you use)

Document in README, adjusting to your lab:

• Python 3.11.x
• PySpark 3.5.x (if Spark path)
• Delta Lake or Iceberg runtime matching the Spark/cloud docs you follow
• Orchestrator version (Airflow 2.x or cloud managed equivalent)
• pytest for fixture tests

Do not claim "latest" without a pin. Reviewers must be able to recreate the fixture environment.

Explicit non-goals reminder for students

You will not: rebuild TechLearningPro's OLTP; implement a new completion definition without metric-owner signoff; submit a Kafka-only certified mart; submit three clouds; disable quality to hit a demo clock; delete raw data to "clean" a restatement; or treat illustrative dollar rates as a real invoice.

Capstone FAQ

Can I use dbt instead of Spark? Yes if your Module 5 engine path supports it and you still provide idempotent date-list restatement, a gate before exposing certified relations, and evidence equivalent to the Spark explain (compiled SQL and run artifacts). The course does not require dbt; it requires operable publish semantics.

Can I skip the lakehouse table and publish only a warehouse table? Yes if your charter picks a warehouse-native mart as the single lakehouse-equivalent system of record and you document time travel/backup limits. Do not run two certified systems of record.

How long must Phase 4 be? At least five board-days of evidence or an instructor-approved compressed simulate-week with the same drill set. A single successful demo day is not operate evidence.

What if my lab has no Kafka? Choose batch-primary. That is a first-class path.

What if dry-run bytes are unavailable? Capture engine query history scanned bytes/rows or an EXPLAIN with estimated rows, and say what you could not measure. Honesty scores higher than invented precision.

Mapping course lessons into deliverables

Use the table in your README to show you are integrating, not reinventing.

Best Practices

• Charter first. Cloud, engine, grain, SLO, Kafka yes/no, and out of scope before spending.
• One certified publisher. Optional stream is a different product with a different SLO.
• Gate before pointer. Green compute with red data must not update the board.
• Idempotent restatement for an explicit date list; neighbor dates untouched.
• Board SQL in version control with prune-friendly filters and definition_version.
• Observability that names the hop: land, gate, transform, publish, serve.
• Error-budget log during operate weeks; burn is a first-class artifact.
• Cost sheet with measured bytes and honest H_idle; illustrative rates labeled.
• Runbook with copy-pasteable diagnosis for gate fail, late publish, rollback, late data.
• Prohibited columns enforced in code and gate, not only in a design doc.
• IaC or an explicit click-ops exception list; no mystery console resources in the demo.
• Demo timed against the script; rubric packet linked to artifacts, not memory.

Common Mistakes and Anti-Patterns

Architecture theater without a published day. Beautiful Mermaid, empty mart. Grade follows evidence of publish and operate.

Two certified writers. Spark and a streaming job both "help." Counts flicker. One writer only.

Board on raw JSON. Repeats the lesson 58 incident. Bytes explode; cost sheet becomes fiction.

Gate that only warns. Warnings are not a publish bit. Fail closed for certified grain.

Pointer update before gate. Crash windows serve partial days. Stage, gate, then pointer.

Hourly partitions for a daily board. Tiny files, slow list, no benefit. Partition by event_date.

Dropping checks to hit 06:00. Steals correctness to buy freshness; burns trust harder than the error budget.

Kafka without charter. Mid-project topic becomes a second truth. Amend Phase 0 or remove it.

Cost sheet with only storage. Scans and idle capacity are the loud meters. Fill them.

Runbook that says "debug." Name queries, log locations, rollback command, and escalation.

Personal BI definitions of completion. Drift from completion-v4. Repo SQL is law.

Fixture settings shipped as production. shuffle.partitions = 8 in prod is an incomplete capstone.

Troubleshooting and Design Review

Publish green, board wrong. Check which object the board reads; compare definition_version; invalidate extracts; confirm pointer moved.

Gate always fails after restatement. Late data vs duplicate rule vs unknown-lesson cap; inspect quarantine rates; confirm date list includes ingest horizon for late events.

Job misses 06:00. File count (small files), shared pool with ELT, skew, or resume time; compact; isolate reservations; measure critical path.

Kafka lag blamed on Spark. Separate cursors; certified path does not wait on tile lag unless charter says so (it should not).

Costs spike on Monday only. Board concurrency on wide scans; dry-run the certified statement; look for SELECT *.

Rollback unclear. If you cannot name the previous pointer or snapshot id, the publish design is incomplete—fix before Phase 4 demo.

Canary green, humans unhappy. The canary may use a narrow date range while the board workbook scans a year, or the canary may hit a cached extract. Align canary SQL with the certified board statement byte-for-byte, including definition_version.

Restatement changes the wrong day. Confirm the date list, partition overwrite mode, and that Iceberg/Delta commits are per-partition or per-predicate as documented. A full-table rewrite "to be safe" is how neighbor dates move.

Observability noise. Alerting on every quarantine row trains people to ignore pages. Alert on gate fail, publish miss, canary miss, and sustained volume shocks; keep quarantine rate on a dashboard with a threshold the metric owner signed.

Design review checklist:

• What is the grain and who signed the unknown-lesson cap?
• How many writers touch the certified grain?
• What fails the gate, and what happens to the previous pointer?
• What are before/after board bytes?
• What is H vs H_idle, and which SLO sentence justifies H?
• Where is the error-budget log for the operate window?
• Can a teammate execute the runbook without you on the call?
• Is the canary SQL identical to the board's certified statement?
• Does the identity sheet separate investigate-raw from board read?
• Is Kafka (if present) proven to land only, with a test that a Streams-style writer to the mart is impossible under IAM?

Interview Questions and Answers

Q1. What does a progress→completion enterprise capstone prove that a single Spark job does not?

It proves you can operate a governed path: ingest, quality as a publish bit, idempotent transforms, lakehouse commits, a single certified mart, observability, SLO with budget, cost honesty, and a runbook under multi-week change. A job can be correct once. A platform is correct on Monday after a restatement and a gate failure.

Q2. Why must the optional Kafka path not write the certified mart?

Because two writers create flicker and unclear rollback. Kafka is excellent for landing and for a provisional tile with a separate SLO. The certified grain needs one publisher so definition_version and the pointer mean one thing during an incident.

Q3. How do you keep restatement idempotent?

Process an explicit event_date list into a stage keyed by job_id, gate the stage, then move a pointer or overwrite only those partitions/snapshots. A second successful run replaces the visible sum for those dates. Neighbor dates remain. Avoid append-only certified sinks without dedupe keys.

Q4. What belongs in the quality gate versus the transform?

Transforms shape and apply business rules. Gates enforce invariants the metric owner and security owner refuse to negotiate at 05:50: null keys, duplicates beyond rule, prohibited columns, unknown-lesson cap, version pin, count reconciliation. Gates fail closed for publish.

Q5. How do you connect this capstone to the Module 5 cost lesson?

Measure board bytes on raw versus mart, split capacity into H and H_idle, isolate ELT, compact files, and refuse savings that drop checks or miss 06:00. The cost sheet is a Phase 3 deliverable, not a retrospective apology after the invoice.

Q6. What SLO would you propose for the Monday board?

Certified mart for the prior event_date available with pinned definition_version by 06:00 on board days, with a stated percentile over a rolling window, and an error budget for misses. Provisional tiles get a different, looser or tighter, objective and must not write the certified table to "save" the merge.

Q7. How do you demo without lying?

Script shows charter choices, a deliberate gate failure leaving the previous pointer, a successful publish, board SQL with version filter, observability firing, cost before/after bytes, and a restatement. You do not demo a personal schema or a second engine.

Q8. What is the difference between the Module 3 Spark capstone and this one?

Module 3 proved batch publish mechanics for engagement tables. This capstone adds production operation across ingest options, lakehouse, mart serving, observability, SLO, cost, security scope, IaC boundary, multi-week phases, and a runbook—integration of Modules 2 through 6 around completion.

Q9. When would you fail a student design that "works"?

When it serves the board from raw, has two certified writers, updates the pointer before the gate, omits definition_version, has no error-budget log in Phase 4, or uses illustrative prices as if they were invoices without labels. Working and operable are different bars.

Q10. How does lesson 69 change what you submit here?

You already need contracts for progress and for the mart. Lesson 69 deepens producer-consumer ownership, CI compatibility tests, and warehouse/API contracts. In this capstone, ship at least one explicit contract artifact and a compatibility stance; expect to refine governance language after 69.

Q11. What runbook section do interviewers expect to hear first?

How to tell whether the board is late because land, gate, transform, or serve failed; how to roll back the pointer; and how to restate a date list with late events—without deleting raw.

Q12. How do Airflow SLAs differ from the product SLO in this capstone?

An Airflow SLA (or cloud scheduler alarm) says a task exceeded a duration or missed a schedule edge. The product SLO says the certified mart was queryable for the board by 06:00 with the pinned definition. A task can finish "successfully" after 06:00 and still burn the product error budget. I keep both: orchestration alarms for operators, product SLO for the business promise.

Q13. What evidence shows idempotent publish to a skeptical reviewer?

Two job_id values for the same event_date list, gate logs for both, a single visible aggregate equal to one copy of the data, and a neighbor date whose counts are unchanged. Bonus: kill the job after stage write and before pointer move, and show the board still reads the previous pointer.

Q14. Why is a canary board query part of the DAG, not a manual Monday habit?

Because manual checks fail when people are late or when the failure is bytes, not emptiness. The canary runs the certified SQL, records latency and bytes, and fails the morning if the serve path regressed—even when the transform row counts look fine.

Hands-On Exercises

Exercise: Multi-week TechLearningPro completion platform

Problem statement. Build and operate a progress→completion platform for TechLearningPro that lands progress, applies quality gates, transforms with Spark and/or SQL into a Delta or Iceberg table, publishes a certified mart for completion-v4, serves a Monday board query, and runs with observability, SLO, cost sheet, and runbook. Kafka is optional and must not own the certified grain. Work in phases with the acceptance criteria above. Use design volumes in the README and a local fixture for automated tests.

Tasks.

1. Write and get feedback on the Phase 0 charter (cloud, engine, grain, SLO, Kafka yes/no, owners, out of scope).
2. Implement landing + contract validation + quarantine metrics; prove a bad fixture fails the gate.
3. Implement idempotent transforms into a lakehouse table; prove two job ids and neighbor-date safety.
4. Publish mart; point board SQL at it; capture before/after bytes; add minimum observability.
5. Fill SLO + error-budget template and cost sheet (H, H_idle, scans, refusals).
6. Operate: restate dates with a late event; run gate-fail and rollback drills; keep the budget log.
7. Deliver a timed demo per the script and a rubric packet with links to artifacts.

Demo script (≤15 minutes).

1. Charter slide: one cloud, one engine, grain, Kafka decision (1 min).
2. Show raw land + deliberate bad record quarantined (2 min).
3. Fail gate on purpose; show previous pointer still serving (2 min).
4. Green publish; board SQL with definition_version; bytes note (3 min).
5. Observability panel for the failure you caused (2 min).
6. Restatement of one date with late event (2 min).
7. Cost sheet + SLO burn log excerpt (2 min).
8. Runbook open to rollback section (1 min).

Expected deliverables. Repo layout above; decision record; contracts; jobs; board SQL; gate evidence; lakehouse table definition; observability queries or screenshots; ops/slo.md; ops/cost-sheet.md; ops/runbook.md; Phase 4 drill notes; demo recording or live checklist; self-scored rubric with links.

Evaluation rubric.

Passing requires nonzero evidence in every row. A perfect transform with empty ops artifacts does not pass.

Lesson Summary

The enterprise capstone is the progress→completion platform operated as a product: one charter, one certified publisher, gates before pointers, lakehouse truth, mart-only board, observability that names hops, an SLO with a budget, a cost sheet that refuses raw Monday scans and silent idle capacity, and a runbook proven in drills. Kafka may land events; it may not become a second mart. Multi-week phases and a grading rubric make "it worked on my laptop" insufficient.

You arrived from Module 5 with engine and cost discipline, and from lessons 59–67 with operating language. Ship the platform, then keep it honest through restatement and failure. Data Contracts and Schema Governance will harden the producer-consumer agreements your gates already depend on. Data Mesh and Domain-Oriented Data Products will ask whether Learning, Billing, and Identity should own products differently than this centralized completion path—and when they should not.

Further Reading

• Apache Spark documentation for DataFrame writes, adaptive execution, and Structured Streaming boundaries you are not reimplementing as the certified path.
• Delta Lake documentation or Apache Iceberg documentation for commits, time travel, and partition evolution matching your charter.
• Apache Airflow documentation for DAG scheduling, retries, and SLAs as orchestration signals (distinct from product SLOs).
• Great Expectations or your warehouse's assertion framework for gate-as-code patterns.
• Module 5 lessons 48–58 for lakehouse, cloud choice, security, and cost arithmetic reused in Phase 3.
• Schema Registry and Event Contracts if the optional Kafka path is in your charter.
• Your cloud's current price card—illustrative rates in this course are ratios, not quotes.