Data Engineering Practical Exercises

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

Lesson Overview

Reading about skew is not the same as watching one Spark task burn while fifty others sit idle. Reciting Kafka offset rules is not the same as replaying a day of progress and proving the mart did not double-count. This lesson is the gym for Module 6: graded labs that force batch ETL, Spark joins under skew, Kafka consumption, data-quality gates, lakehouse merges, and a cost dry-run—each with starter schemas, expected outputs, and rubrics you can apply without a live instructor.

TechLearningPro is the shared dataset story. You will land progress-like events, compact them, join to a lesson dimension that hides a hot course_id, consume a compact topic fragment, validate grain before publish, merge into a completion-v4 table, and price a bad board query against a pruned one. Data Engineering Interview Questions and Answers told you which skills the panel scores. Here you produce artifacts those scores require. Data Engineering Quiz and Assessment will test whether the skills stuck under a clock.

Work in a local engine where possible: DuckDB or Postgres for SQL labs, PySpark local mode for join and skew, Testcontainers or a local Kafka if you have it, or a clearly labeled mock consumer when you do not. Cloud dry-runs may use documented sample byte counts if you lack account access; label them as illustrative the same way Data Platform Cost and Performance Optimization did.

Learning Objectives

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

1. Complete a batch ETL lab that lands, validates, transforms, and publishes a daily partition idempotently.
2. Diagnose and mitigate a skewed Spark join using stage-oriented evidence and a measured fix.
3. Implement a Kafka consumer pattern that commits only after a successful idempotent sink write.
4. Author data-quality checks for uniqueness, freshness, volume, and definition_version presence.
5. Execute a lakehouse-style merge into a certified completion table and prove reruns are safe.
6. Run a cost dry-run comparison between raw-style scans and pruned mart scans with written assumptions.
7. Use rubrics to self-grade each lab at the same bar used in interview drills.
8. Package schemas, sample data, outputs, and notes as a portfolio-ready exercise pack.
9. Connect each lab failure mode to an operational alert you would actually page on.
10. Refuse "green job, wrong data" by failing publish when checks fail.
11. Measure file sizes and partition counts before calling a write production-like.
12. Document limitations when using mocks instead of real Kafka or cloud dry-runs.
13. Map weak lab scores back to interview bank domains from lesson 65.
14. Prepare evidence artifacts for the timed assessment in lesson 67.
15. Keep a single publisher mental model across every lab that touches the mart.

Prerequisites

• Lesson 65 interview banks so you know which evidence phrases graders seek.
• Spark join and performance lessons for skew vocabulary and file-size targets.
• Kafka consumer lessons for offset commit timing and poison handling.
• Lakehouse lessons (Delta or Iceberg) for merge and commit order.
• Lesson 58 cost meters and the SQL window lessons for dedupe patterns.
• A working Python 3.10+ environment; PySpark 3.4+ recommended for labs 2 and 5; optional confluent-kafka or kafka-python for lab 3.

Detailed Core Concepts

What "graded practical" means here

Each lab has a problem statement, starter schema, tasks, expected outputs, and a weighted rubric. Passing is not "I ran something." Passing is meeting the acceptance checks: idempotent rerun, evidence captured, wrong paths rejected. Partial credit exists. Skipping DQ to make a merge "work" is an automatic cap at 60% on that lab.

Shared business context

TechLearningPro emits learning progress events. Certified completion for the Monday board uses definition_version = 'completion-v4'. Resets create new attempts. The board counts distinct learners who completed a lesson under that definition for a date range. Labs use reduced volumes so they finish on a laptop: thousands of events, not eighty million. The shapes still teach production failure modes.

Shared starter schemas

sqlCopy
-- Lab pack schemas (SQL dialect: use DuckDB/Postgres-compatible types)

CREATE TABLE raw.progress_event (
  event_id      VARCHAR PRIMARY KEY,
  learner_id    VARCHAR NOT NULL,
  lesson_id     VARCHAR NOT NULL,
  course_id     VARCHAR NOT NULL,
  event_type    VARCHAR NOT NULL, -- progress | complete | reset
  event_time    TIMESTAMP NOT NULL,
  event_date    DATE NOT NULL,
  payload_json  VARCHAR
);

CREATE TABLE dim.lesson (
  lesson_id   VARCHAR PRIMARY KEY,
  course_id   VARCHAR NOT NULL,
  title       VARCHAR NOT NULL,
  is_active   BOOLEAN NOT NULL
);

CREATE TABLE staged.completion_attempt (
  event_id           VARCHAR PRIMARY KEY,
  learner_id         VARCHAR NOT NULL,
  lesson_id          VARCHAR NOT NULL,
  course_id          VARCHAR NOT NULL,
  is_complete        BOOLEAN NOT NULL,
  event_time         TIMESTAMP NOT NULL,
  event_date         DATE NOT NULL,
  definition_version VARCHAR NOT NULL
);

CREATE TABLE published.completion_attempt (
  learner_id         VARCHAR NOT NULL,
  lesson_id          VARCHAR NOT NULL,
  course_id          VARCHAR NOT NULL,
  attempt_id         VARCHAR NOT NULL,
  is_complete        BOOLEAN NOT NULL,
  event_date         DATE NOT NULL,
  definition_version VARCHAR NOT NULL,
  PRIMARY KEY (attempt_id, definition_version)
);

Sample seed sketch (conceptual counts graders expect you to materialize):

Lab map and suggested order

Do A→D→E as a vertical slice if time is short; still attempt B, C, and F before lesson 67.

Decision lens applied to labs

For every lab, write four lines before coding: correctness risk, freshness impact, scale assumption, refusal (what you will not fake). Graders read those lines. They separate engineers from tutorial followers.

Evidence you must capture

• Row counts before/after dedupe
• Spark stage screenshot or text metrics for skew lab
• Consumer commit ordering explanation with code pointer
• DQ fail that blocks publish (show the failure once on purpose)
• Merge rerun producing identical published counts
• Dry-run byte estimates table with labels

Architecture and Design Diagrams

The lab pack follows the same path as production, reduced.

mermaidCopy
flowchart TB
  subgraph labA [Lab A Batch ETL]
    RAW["raw.progress_event"] --> STG["staged.completion_attempt"]
  end
  subgraph labB [Lab B Spark]
    STG --> JOIN["Join dim.lesson + skew fix"]
  end
  subgraph labC [Lab C Kafka]
    TOPIC["progress topic fragment"] --> SINK["Idempotent sink + commit"]
  end
  subgraph labD [Lab D DQ]
    STG --> DQ["Uniqueness freshness volume version"]
  end
  subgraph labE [Lab E Merge]
    DQ --> PUB["published.completion_attempt"]
  end
  subgraph labF [Lab F Cost]
    PUB --> DRY["Dry-run mart vs raw scan"]
  end

Isolation matters even in exercises: do not let Lab B's experimental salted keys write into the published table used by Lab F until DQ passes.

Technical Explanation

Lab A — Batch ETL mechanics

Goal: transform raw events into staged completion attempts for one event_date, idempotently.

Transformation rules:

1. Keep events with event_type in ('complete', 'reset') as attempt-relevant; map complete to is_complete=true, reset to a new attempt marker with is_complete=false unless your written rules say resets close attempts differently—document the choice.
2. Set definition_version to completion-v4 for this pack.
3. Dedupe on event_id keeping latest event_time.
4. Write only the target event_date partition into staged, replacing that partition safely.

Idempotency pattern (SQL illustration):

sqlCopy
DELETE FROM staged.completion_attempt WHERE event_date = DATE '2026-11-10';

INSERT INTO staged.completion_attempt
SELECT event_id, learner_id, lesson_id, course_id,
       (event_type = 'complete') AS is_complete,
       event_time, event_date, 'completion-v4' AS definition_version
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY event_id ORDER BY event_time DESC
  ) AS rn
  FROM raw.progress_event
  WHERE event_date = DATE '2026-11-10'
    AND event_type IN ('complete', 'reset')
) u
WHERE rn = 1;

Expected output checks: rerunning the script leaves identical counts; duplicate event_ids collapse; definition_version is never null.

Lab B — Spark join and skew

Goal: join progress to dim.lesson and aggregate completes by course_id, then fix skew on course_mega.

Starter PySpark sketch:

pythonCopy
from pyspark.sql import functions as F

progress = spark.read.parquet("data/progress_day")
lessons = spark.read.parquet("data/dim_lesson")

# Intentionally naive join for the broken baseline
joined = progress.join(lessons, "lesson_id")
agg = joined.filter(F.col("event_type") == "complete") \
            .groupBy("course_id").count()
agg.write.mode("overwrite").parquet("out/agg_naive")

Broken baseline symptoms to capture: one task far longer; shuffle read skewed.

Mitigation options (pick one, measure):

• Salt course_id for hot keys during partial aggregate, then combine.
• Separate hot-key path versus cold-key path.
• Enable AQE skew join if your Spark version supports it and show the plan difference.

Acceptance: wall time improves materially on the hot key path, or task time histogram shows no single straggler beyond 3× median, with notes on residual risk.

Lab C — Kafka consumer

Goal: consume a fragment, write to a sink table keyed by event_id, commit offsets only after success, park poison messages.

Pseudocode contract:

pythonCopy
# Pseudocode — illustrate ordering; adapt to your client
while running:
    records = consumer.poll(timeout)
    for rec in records:
        try:
            row = parse_and_validate(rec)
            sink.upsert(row)  # idempotent by event_id
        except Poison as ex:
            dead_letter.publish(rec, error=str(ex))
            # still continue; do not block partition forever
    consumer.commit()  # after successful handling of the batch

Acceptance: crash injection before sink write does not advance offsets; reprocessing does not duplicate sink rows; poison count matches seeded 15; happy-path row count matches valid messages.

If you lack Kafka, implement the same contract over a recorded JSONL file with a manual offset integer in a side file. Label it as a mock and still satisfy commit-after-sink ordering.

Lab D — DQ checks

Minimum checks:

Implement as SQL assertions or a small Python runner that exits non-zero. Expected output includes one intentional failing run log and one passing run log.

Lab E — Lakehouse merge

Goal: merge staged completes into published.completion_attempt for the day.

sqlCopy
-- Illustrative MERGE (Spark SQL / DuckDB-style; adapt to Delta/Iceberg)
MERGE INTO published.completion_attempt t
USING staged.completion_attempt s
ON t.attempt_id = s.event_id AND t.definition_version = s.definition_version
WHEN MATCHED THEN UPDATE SET
  is_complete = s.is_complete,
  event_date = s.event_date,
  course_id = s.course_id,
  learner_id = s.learner_id,
  lesson_id = s.lesson_id
WHEN NOT MATCHED THEN INSERT (
  learner_id, lesson_id, course_id, attempt_id,
  is_complete, event_date, definition_version
) VALUES (
  s.learner_id, s.lesson_id, s.course_id, s.event_id,
  s.is_complete, s.event_date, s.definition_version
);

Rerun merge twice; published counts must hold. Delete a staged day and rebuild; published day matches the rebuild. Do not merge before DQ in the happy path script.

Lab F — Cost dry-run

Compare two statements with measured or illustrative bytes:

sqlCopy
-- Bad: raw-style wide read (simulate with SELECT * from raw for 7 days)
SELECT lesson_id, COUNT(DISTINCT learner_id)
FROM raw.progress_event
WHERE event_date BETWEEN DATE '2026-11-03' AND DATE '2026-11-09'
GROUP BY lesson_id;

-- Good: published mart
SELECT lesson_id, COUNT(DISTINCT learner_id)
FROM published.completion_attempt
WHERE event_date BETWEEN DATE '2026-11-03' AND DATE '2026-11-09'
  AND definition_version = 'completion-v4'
  AND is_complete
GROUP BY lesson_id;

Produce a table: statement, bytes or proxy rows scanned, readers=40, refreshes=1, illustrative $/TB, Monday cost, cache assumption=miss. Refuse recommendations that add a second mart.

How graders read your notes

Short beats long. "Skew: task 12 at 8.2 min vs median 0.4 min; salted 16 ways; post median 0.5 max 1.1" beats a page of Spark autobiography. Put refusals in bold-free plain sentences at the end of each lab note.

Lab A deep dive — expected numeric example

Suppose raw for 2026-11-10 contains 1,200 rows, 20 of which are duplicate event_ids (40 rows collapsing to 20), and 180 resets. After filter to complete and reset, you might have 800 rows before dedupe and 780 after. Staged should show 780. Board-facing complete count is not 780; it is the distinct learners with is_complete=true after your reset rule. Write both numbers in NOTES.md. Graders look for people who confuse event counts with learner counts—the same trap as interview Q1 in lesson 65.

Reset rule options you may choose (pick one, test it):

1. Append-only attempts. A reset inserts is_complete=false attempt; prior complete attempts remain for audit; "current complete" is a window over attempts.
2. Close-and-open. A reset marks prior open attempt closed incomplete and opens a new attempt id derived from the reset event.

Either is acceptable in this pack if tests lock the behavior. Switching mid-lab without a version bump is not.

Lab B deep dive — building a reproducible hot key

Do not rely on random chance for skew. Construct it:

pythonCopy
# Force skew: 70% of completes map to lessons in course_mega
from pyspark.sql import functions as F

lessons = spark.read.parquet("data/dim_lesson")
mega_lessons = [r.lesson_id for r in lessons.filter("course_id = 'course_mega'").collect()]

progress = (
    spark.read.parquet("data/progress_day")
    .withColumn(
        "lesson_id",
        F.when(F.rand() < 0.70, F.array([F.lit(x) for x in mega_lessons])[ (F.rand() * len(mega_lessons)).cast("int") ])
         .otherwise(F.col("lesson_id")),
    )
)

If array construction is awkward in your Spark version, prewrite a CSV that already concentrates keys. The point is intentional skew you can re-run. Capture spark.sparkContext.statusTracker() metrics or write task metrics from the Spark UI as a CSV export. Without artifacts, claims of "it felt faster" score zero on the evidence column.

Salt implementation detail graders like:

pythonCopy
HOT = {"course_mega"}
SALT_N = 16

def with_salt(df):
    return df.withColumn(
        "salt",
        F.when(F.col("course_id").isin(list(HOT)), (F.rand() * SALT_N).cast("int")).otherwise(F.lit(0)),
    )

partial = with_salt(joined).groupBy("course_id", "salt").count()
final = partial.groupBy("course_id").agg(F.sum("count").alias("count"))

Verify final totals equal naive agg totals. Performance without correctness is a failed lab.

Lab C deep dive — poison contract and headers

Poison messages should retain headers when parked:

• original topic, partition, offset
• consume timestamp
• exception class and truncated message
• correlation event_id if parse partially succeeded

DLQ payloads that lose offset metadata cannot be replayed surgically. Your dead-letter publisher can be a folder of JSONL files in the mock implementation. Name files with partition and offset. Show ls dlq | wc -l equals 15.

Crash test procedure:

1. Process batch size 50 with a flag CRASH_AFTER_UPSERT=1 for the first batch only.
2. On restart, assert sink count did not duplicate those 50.
3. Turn flag off; finish topic; assert sink count equals valid message count.

Write the assert as code, not as a paragraph.

Lab D deep dive — check runner shape

pythonCopy
# checks/run_dq.py — exits 2 on blocker
import sys

def main(con):
    blockers = []
    dup = con.execute(
        """
        SELECT COUNT(*) FROM (
          SELECT attempt_id, definition_version, COUNT(*) c
          FROM publish_candidate
          GROUP BY 1,2 HAVING COUNT(*) > 1
        )
        """
    ).fetchone()[0]
    if dup:
        blockers.append(f"uniqueness:{dup}")
    bad_ver = con.execute(
        "SELECT COUNT(*) FROM publish_candidate WHERE definition_version <> 'completion-v4'"
    ).fetchone()[0]
    if bad_ver:
        blockers.append(f"version:{bad_ver}")
    # freshness, nulls, volume similarly...
    if blockers:
        print("FAIL", ",".join(blockers))
        return 2
    print("PASS")
    return 0

if __name__ == "__main__":
    sys.exit(main(get_con()))

Wire your merge script to abort when this returns non-zero. The intentional fail run inserts one duplicate into publish_candidate and saves console output to labs/d_dq/fail.log.

Lab E deep dive — atomicity notes

If your engine lacks MERGE, emulate with:

1. Build a replacement delta table for the day in a temp location.
2. Swap/publish by atomic rename or by inserting into a new table version and updating a pointer table published.current_version.

Document which emulation you used. The learning objective is rerun safety and readers never seeing half-written days. Deleting published rows mid-window without a swap is a lab failure even if final counts look fine on your laptop.

Lab F deep dive — worked illustrative arithmetic

Assume measured proxies: raw scan processes 2.0 TB equivalent; mart scan processes 0.005 TB; 40 readers; 1 refresh; $5/TB illustrative.

• Raw Monday: 2.0 * 40 * 1 * 5 = 400 USD
• Mart Monday: 0.005 * 40 * 1 * 5 = 1 USD

Write the cache assumption. List idle capacity as a separate qualitative line if you are not given a warehouse hour price: "measure resume before recommending pause." Refusal: "Do not add warehouse #2 for isolation; use workload groups on one mart."

Environment and dependency pins

Document in NOTES.md:

textCopy
python==3.11.x
pyspark==3.5.x
duckdb==1.1.x   # or postgres 16
# optional
confluent-kafka==2.3.x

Labs should run with these pins on a clean virtualenv. If you deviate, say why. Ungraded mystery environments waste review time.

Accessibility of evidence for oral review

In lesson 67 and in job take-homes, someone will ask you to open artifacts. Keep paths stable. Prefer text metrics over screenshots when possible; screenshots are fine as supplements. If using screenshots, name them lab-b-skew-before.png and lab-b-skew-after.png.

Practical Implementation or Design Walkthrough

Problem

Build the TechLearningPro exercise pack end to end for day 2026-11-10, then prove Lab F's cost story using the published output. Treat this walkthrough as the instructor reference solution shape, not a copy-paste contest. Your numbers may differ; your invariants may not.

Step 1 — Seed data

Generate raw events with a scripted faker or CSV. Ensure:

• 20 duplicated event_ids
• At least 50 resets
• Heavy concentration of completes on lessons under course_mega
• 15 malformed Kafka JSON lines for Lab C

Step 2 — Run Lab A and record counts

Document raw_count, deduped_count, staged_count. Rerun once. Diff must be empty.

Step 3 — Run Lab B baseline then fix

Save naive metrics JSON. Apply salt fix. Save fixed metrics JSON. Write a five-line comparison.

Step 4 — Run Lab C with crash test

Kill process after sink write for one batch but before commit (or simulate by reordering in mock). Confirm redelivery does not duplicate. Inject poison; confirm DLQ depth 15.

Step 5 — Run Lab D fail-then-pass

Break uniqueness on purpose; show blocked publish. Fix; show pass artifact.

Step 6 — Run Lab E merge twice

Snapshot counts hash: md5(order(learner_id, lesson_id, attempt_id, is_complete)) or equivalent. Second merge keeps hash.

Step 7 — Run Lab F sheet

Fill:

Step 8 — Package

Folder layout expectation:

textCopy
exercise-pack/
  schemas.sql
  data/
  labs/a_batch_etl/
  labs/b_spark_skew/
  labs/c_kafka_consumer/
  labs/d_dq/
  labs/e_merge/
  labs/f_cost/
  NOTES.md
  SCORES.md

Step 9 — Reference NOTES.md skeleton (fill with your numbers)

markdownCopy
# TechLearningPro exercise pack notes

### Invariants
- Grain: attempt_id + definition_version
- Board counts distinct learners with is_complete under completion-v4
- Reset rule: <append-only | close-and-open>
- Single publisher into published.completion_attempt

### Lab A
- raw_count / deduped / staged: 
- rerun diff: empty | NOT empty
- refusal: no silent drop of resets

### Lab B
- before max/median task times:
- after max/median:
- totals match naive: yes/no
- method: salt N= / split / AQE

### Lab C
- mode: real kafka | mock jsonl
- crash test: pass/fail
- dlq count:
- commit order proof path:

### Lab D
- fail.log reason:
- pass.log timestamp:
- blockers enabled: uniqueness, version, nulls, freshness, volume?

### Lab E
- merge engine:
- count hash run1 / run2:
- DQ gated: yes/no

### Lab F
- raw proxy / mart proxy:
- monday raw $ / mart $:
- price label:
- cache assumption:
- refusals:

### Alerts worksheet
(see table in lesson)

### Weak spots for lesson 67
- 

Step 10 — Sample expected board query output shape

After Labs A–E, this query should run on published data for the seeded week and return one row per lesson with completes:

sqlCopy
SELECT lesson_id, COUNT(DISTINCT learner_id) AS learners
FROM published.completion_attempt
WHERE event_date BETWEEN DATE '2026-11-08' AND DATE '2026-11-10'
  AND definition_version = 'completion-v4'
  AND is_complete
GROUP BY lesson_id
ORDER BY learners DESC, lesson_id;

Save the top twenty rows to labs/e_merge/board_preview.csv. Graders use it to spot absurd counts (for example, learners exceeding seeded population). If your generator created 300 distinct learners, no lesson should show 500.

Step 11 — Local Spark configuration hints

For laptop labs:

textCopy
spark.sql.shuffle.partitions  8
spark.driver.memory           2g
spark.executor.memory         2g

Low partition counts make skew visible without needing huge data. Hundreds of partitions on tiny data hide stragglers among scheduling noise. Document the configs you used in Lab B notes.

Step 12 — Acceptance checklist (print and tick)

• schemas.sql applies clean on empty database
• Seed manifest checksums recorded
• Lab A rerun idempotent
• Lab B before/after metrics saved
• Lab B totals equal
• Lab C crash test coded
• Lab C DLQ = 15
• Lab D fail and pass logs
• Lab E dual merge hashes match
• Lab F arithmetic table complete
• Refusals listed
• SCORES.md filled honestly
• Peer grade scheduled or self-oral rehearsal recorded

Do not start lesson 67 until the checklist is mostly green. Timed assessment with missing lab evidence feels like an ambush you scheduled yourself.

Step 13 — Common repair playbook when the vertical slice breaks

1. If merge fails uniqueness, stop and fix Lab D candidate building—do not loosen the primary key.
2. If Spark totals diverge after salting, you salted after a filter inconsistently; recompute naive baseline on the exact same filtered set.
3. If Kafka mock passes but counts drift, your upsert is append-only; switch to true upsert by event_id.
4. If cost sheet favors raw, your mart query is wrong or mart empty; fix publish before storytelling.
5. If freshness check always fails, your seed event_time may be historical relative to "now"; parameterize expected watermark to the seed's max time for labs.

Enterprise Real-World Scenarios

TechLearningPro academy week

New platform engineers spend five half-days on this pack before receiving production DAG keys. Someone skips DQ because merge demos better in standup. On their first on-call, a duplicate event_id generator lands and the board double-counts. The postmortem makes Lab D mandatory with a failing demo checked into notes. Practice is cheaper than that Monday.

Northline contractor onboarding

Northline uses the same lab shapes on order lines: batch load, skewed join on a retailer's mega seller id, Kafka refunds topic, DQ on refund status codes, merge to finance grain, cost dry-run for portal versus finance isolation. Contractors who cannot pass Lab C commit ordering do not touch refunds consumers. The rubric is the hiring bar applied to vendors.

Capstone rehearsal under fatigue

Run the vertical slice A→D→E starting at 21:00 after a workday. Fatigue reveals brittle scripts and missing assertions. Note where you skipped evidence. Those skips are the incidents you will cause later. Re-run only the skipped proofs the next morning. The pack is as much about operational discipline as about APIs.

Pair grading protocol

Swap packs with a peer. You may not award points for claims without files. You may ask one clarifying question per lab. Fill their SCORES.md independently, then compare. Disagreements usually cluster on Lab C mock honesty and Lab F proxy labeling. Align on standards before lesson 67's timed assessment so self-scores mean something.

Best Practices

• Write invariants before code; code is the easy part.
• Capture before/after evidence for every performance claim.
• Fail publish on blocking DQ; never "warn only" for uniqueness on certified grains.
• Keep lab published tables separate from experimental skew outputs until checks pass.
• Label mocks and illustrative prices honestly.
• Prefer idempotent partition replaces and merges over fragile incremental hacks in exercises.
• Timebox labs; unfinished evidence is a score signal, not a moral failure.
• Map each lab to interview domains: A modeling/SQL, B Spark, C Kafka, D reliability, E lakehouse, F cloud/cost.
• Store scores in SCORES.md with rubric weights.
• Refactor only after the first passing evidence pack exists.
• Use realistic nasty data: duplicates, resets, poison, hot keys.
• Document the completion rule you chose for resets so graders are not guessing.

Common Mistakes and Anti-Patterns

Tutorial-only happy data. No duplicates, no skew, no poison. You learn nothing. Seed the nastiness.

Committing Kafka offsets first. Lab C fail. Sink-then-commit, plus idempotent upsert.

Broadcasting a large side "because join." Driver pain. Measure sizes.

Merging without DQ. Certified garbage. Gate it.

Calling repartition(1000) a skew fix. Often slower. Salt or split hot keys with evidence.

Cost sheet without cache assumption. Fiction. Write "miss on first dated refresh."

One giant notebook, no package layout. Ungradable. Use the folder contract.

Changing grain mid-lab silently. Board logic drifts. Version it.

Deleting raw to "clean" duplicates. Destroys replay story. Dedupe into stage instead.

Copying instructor hashes without running. Fails oral follow-up in assessment week.

Using wall clock as the only Spark metric. Clocks lie on busy laptops. Prefer stage task distributions.

Hard-coding completion-v4 in six places without a constant. Drift when lesson 67 asks for a v5 shadow. Use one config object.

Treating DLQ as /dev/null. Poison without replay metadata trains the wrong ops muscle.

Skipping Lab F because "cost was lesson 58." Assessment will ask for a dry-run table. Forty-five minutes now beats a zero later.

Peer grading on vibes. If the file is missing, the score is zero. Friendship is not an artifact.

Recovery stories you should be able to tell

After you finish the pack, rehearse three short stories for oral review:

1. The duplicate that almost shipped. How Lab D fail.log looked, what caused it, how merge stayed blocked.
2. The straggler. How you saw course_mega, what fix you chose, how you proved totals.
3. The crash before commit. What would have happened with auto-commit mentality, and how your test caught it.

These stories are also interview gold. Labs that cannot be narrated were probably not understood.

Troubleshooting and Design Review

Instructor-style walkthrough of a failing submission

A common failing pack looks polished in README prose and empty in evidence folders. Lab A has a screenshot of a notebook cell, not a rerun diff. Lab B claims "AQE fixed it" without before/after task metrics and without a totals equality check. Lab C uses a mock that appends forever and calls it upsert. Lab D has only a passing log. Lab E merges once. Lab F quotes lesson 58's $400 figure without measuring local proxies.

How a grader responds: return the pack with a single page of gaps, no line-by-line nagging. The learner reopens only the failing proofs. Capstone access stays closed until Lab C commit ordering and Lab D fail-then-pass exist. Harsh? Production is harsher. The pack's job is to fail safely.

Stretch criteria for honors-level packs

Not required to pass, but recognized:

• CI job that runs Lab A SQL and Lab D checks on every commit
• Property test that merge reruns preserve counts across random duplicate injections
• Flame-friendly Spark event log snippet committed as text
• Cost sheet that also estimates H versus H_idle qualitatively for a fictional warehouse
• Northline variant README showing how schemas map from completion to order_line

Honors work still must pass the base rubrics. Fancy CI with broken idempotency does not score.

Data generator reference algorithm

1. Draw learner ids from a pool of 300.
2. Draw lessons from 200, with 80 tagged course_mega.
3. For each day, emit ~1,500 events with timestamps spread across the day.
4. Force 20 event_ids to repeat with later timestamps (duplicates).
5. Emit ≥50 resets referencing prior learner-lesson pairs.
6. On 2026-11-10, rewrite 70% of complete events' lesson_id into the mega set.
7. Write Kafka fragment by sampling 1,000 events to JSONL and corrupting 15 lines (truncate JSON, wrong types, missing keys).

Validate with SQL: duplicate count, reset count, mega share, poison count. If validation fails, fix the generator before any lab. Garbage seeds produce ungradable labs.

What "expected outputs" means when numbers differ

Graders do not require your staged count to be exactly 780. They require:

• Internal consistency between documented seed stats and outputs
• Idempotent reruns
• Invariants on keys and versions
• Evidence files that match the claims in NOTES.md

If you change seed volumes, update data/MANIFEST.md and the notes. Surprises without manifests look like bugs.

Staged counts change on rerun. Delete/replace scope is wrong, or nondeterministic tie-break. Add event_id to order.

Skew fix makes cold keys slower. Over-salting. Salt only hot set.

Mock Kafka passes but real client fails. Commit API differences; re-test with real client before production.

DQ volume warning always on. Median baseline missing; seed history days or adjust rule and document.

Merge duplicates primary key. attempt_id mapping wrong; ensure event_id maps 1:1 to attempt.

Dry-run shows mart worse than raw. Mart not partitioned or query missing version/date filters; fix SQL before storytelling.

Design review before you mark yourself done:

• Which labs have fail-then-pass evidence?
• Where is commit-after-sink proven?
• What is the hot-key mitigation and metric?
• Does merge rerun keep counts?
• Are prices labeled illustrative if not live?
• What do you refuse?

Operational runbook excerpt you should author

Add labs/RUNBOOK.md with copy-pasteable commands:

bashCopy
# recreate schemas
duckdb pack.db < schemas.sql

# seed
python generators/seed.py --out data/

# lab A
python labs/a_batch_etl/run.py --date 2026-11-10
python labs/a_batch_etl/run.py --date 2026-11-10   # idempotent rerun

# lab D fail demo
python labs/d_dq/run.py --inject-duplicate
python labs/d_dq/run.py --clean

# lab E
python labs/e_merge/run.py --date 2026-11-10
python labs/e_merge/run.py --date 2026-11-10

If your runbook cannot be followed by a peer in thirty minutes, packaging score suffers. Clarity is part of production engineering.

Timeboxing when you only have one weekend

Protect Lab C and Lab D proofs; they fail interviews when missing. If Sunday slips, schedule a weekday evening for C rather than skipping.

Mapping pack scores to interview domains

Do not only rerun code. Speak the answer aloud after the lab fix so the interview muscle updates too.

Interview Questions and Answers

These questions probe lab judgment. They are distinct from lesson 65's bank.

Q1. Why require an intentional DQ failure in Lab D?

Because operators must see the block path. A check that never failed in practice is untrusted. The artifact proves the gate works.

Q2. How do you justify a mock Kafka in a graded pack?

If commit ordering, idempotent sink, and DLQ behavior are demonstrated, the learning objective holds. I label it mock and schedule a real-client verification before production on-call.

Q3. What evidence convinces you skew is fixed?

Task time histogram or stage metrics showing straggler within a small multiple of median, plus correct aggregate totals versus naive baseline.

Q4. Why map event_id to attempt_id in merge?

Stable idempotency key across replays. Surrogate ids generated at write time would duplicate on rerun.

Q5. Can Lab F use row counts instead of bytes?

As a proxy on local engines without bytes meters, yes if labeled. On cloud, prefer dry-run bytes. Never mix units silently.

Q6. What is the minimum refusal list for the pack?

No publish without uniqueness; no second mart in cost recommendations; no offset commit before sink success; no board SQL on raw for the "good" path.

**Q7. How does Lab A idempotency fail with INSERT only?

Duplicates accumulate. Need partition replace, merge, or truncate-in-transaction patterns.

Q8. When would you block on volume anomalies instead of warn?

When false Mondays are costlier than delayed publish, and baseline is trustworthy. For brand-new products, warn until median exists.

Q9. How do resets show up in expected outputs?

As additional attempt rows or documented state transitions, not as silent deletes of history.

Q10. What oral follow-up should you expect after submitting the pack?

"Show me the crash-before-commit test" and "what did you refuse in Lab F?" Prepare those demos first.

Q11. How do lab scores feed lesson 67?

Weak labs become quiz focus areas and architecture prompt topics. Bring SCORES.md.

Q12. Why keep experimental skew output out of published?

To protect the certified grain from salted keys or partial aggregates leaking into board inputs.

Hands-On Exercises

Exercise pack: Labs A–F graded submission

Problem statement. Complete all six labs for TechLearningPro day 2026-11-10 (and supporting seed days), package artifacts per the walkthrough layout, and self-score with the rubrics below.

Tasks.

1. Implement Lab A batch ETL with idempotent rerun proof.
2. Implement Lab B naive join metrics and one measured skew mitigation.
3. Implement Lab C consumer contract with crash and poison tests (real or labeled mock).
4. Implement Lab D checks with intentional fail and pass logs.
5. Implement Lab E merge with dual-run identical counts.
6. Implement Lab F cost comparison sheet with refusals.
7. Write NOTES.md mapping each lab to failure alerts you would page on.
8. Write SCORES.md using the weights below; call out any lab below 70% as a lesson 67 focus.

Expected deliverables. Full exercise-pack/ tree, evidence artifacts, notes, scores, refusal list.

Evaluation rubric (whole pack).

Per-lab micro-rubric (apply inside each folder).

Exercise: Seed-data generator mini-lab (ungraded warm-up)

Write a generator that emits the nastiness table below, then spot-check with SQL. This warm-up saves time in Labs A–C.

Expected output: data/raw_progress_*.csv, data/dim_lesson.csv, data/kafka_fragment.jsonl, and a data/MANIFEST.md listing checksums.

Exercise: Alert mapping worksheet

For each lab, name one metric and one threshold that would page you in production.

Submit the worksheet inside NOTES.md. Missing alert mapping caps packaging score.

Lesson Summary

Practical competence is evidenced, not claimed. This lesson gave six graded labs—batch ETL, Spark skew, Kafka consumer semantics, DQ gates, lakehouse merge, and cost dry-run—on a shared TechLearningPro schema with rubrics and expected outputs. Idempotency, commit ordering, blocking checks, and honest meters recur across every path to the Monday board.

Carry SCORES.md and weak-lab notes into Data Engineering Quiz and Assessment. The assessment will timebox the same skills. The Enterprise Data Platform Capstone Project will demand them without starter training wheels. Keep a single publisher, and keep the artifacts that prove you earned it.

Appendix: Lab acceptance cards (tear-out style)

Card A — Batch ETL. Given raw duplicates and resets, staged day partition is deterministic, versioned completion-v4, rerun-safe. Deliver counts table + empty diff on rerun.

Card B — Skew. Given forced hot course_mega, produce naive metrics, mitigated metrics, equal totals, and a written method. Deliver metric files + equality proof.

Card C — Kafka. Given fragment with poisons, sink upserts by event_id, commit after success, DLQ=15, crash test passes. Deliver logs + DLQ listing + commit-order note.

Card D — DQ. Given candidate rows, blockers for uniqueness/version/nulls/(freshness/volume policy), intentional fail log and pass log, merge gated. Deliver exit codes + logs.

Card E — Merge. Given staged+DQ pass, published merge reruns converge, board preview CSV sane versus seed population. Deliver hash pair + preview.

Card F — Cost. Given mart vs raw statements, sheet with proxies, 40 readers, cache miss assumption, illustrative price label, refusals. Deliver markdown table.

Keep these cards next to your keyboard while coding. When the card's deliverable exists, move on. Endless polishing of Lab A while Lab C is empty is how packs fail.

Appendix: Example SCORES.md entries

markdownCopy
| Lab | Score | Evidence pointer | Weak layer |
|---|---:|---|---|
| A | 90 | labs/a_batch_etl/counts.md | — |
| B | 75 | labs/b_spark_skew/metrics_{before,after}.json | evidence clarity |
| C | 60 | mock only; crash test flaky | reliability |
| D | 85 | fail.log + pass.log | — |
| E | 80 | hash.txt | — |
| F | 70 | cost.md illustrative | honesty labeling |
Overall pack (weighted): 76%
Focus for lesson 67: Lab C commit proof rewrite; Lab F label pass

Honest mid scores beat fictional 100s. Assessment day will find the fiction.

Final integration test before you call the pack done

Run this sequence without editing code mid-flight:

1. Tear down the database or warehouse schema used for the pack.
2. Recreate from schemas.sql only.
3. Reseed from generator (or restore seed artifacts from checksummed files).
4. Execute A→D→E→F via runbook.
5. Execute B and C in either order.
6. Confirm SCORES.md still matches freshly produced evidence timestamps.

If the pack only works in a dirty notebook state, it is not done. Capstone and on-call both assume clean recreates. The integration test is the last lab, even without a letter.

Portfolio blurb (optional README section)

Employers skimming GitHub will not run your Spark UI exports. Give them a short blurb:

markdownCopy
### What this pack proves
- Idempotent daily staging of completion attempts with pinned definition_version
- Measured Spark skew mitigation on a forced hot course key
- Kafka-style consume loop with sink-before-commit and DLQ (real or labeled mock)
- Blocking DQ with fail-then-pass evidence
- Rerun-safe publish merge
- Cost dry-run sheet comparing raw-style versus mart reads for 40 readers

Link each bullet to a folder. Keep marketing adjectives out. Evidence paths sell harder than "passionate about data."

Handoff to lesson 67

Create HANDOFF.md with:

• Overall pack percent
• Labs below 70%
• Three oral demo scripts (duplicate gate, skew metrics, crash-before-commit)
• Cost sheet one-liner with cache assumption
• Open questions you still have (engine-specific merge syntax, real Kafka client, live dry-run access)

Lesson 67's remediation plan should consume this handoff verbatim. Do not invent a second weak-topic list from memory after a week.

Further Reading

• PySpark guide for local mode joins and metrics.
• Kafka Consumer documentation for commit semantics.
• Delta Lake MERGE or Iceberg merges for Lab E syntax on your engine.
• Great Expectations or engine-native assertions for DQ patterns aligned to Lab D.
• Course links: Data Quality Engineering, Spark Joins and Data Shuffling, Kafka Producers and Consumers, Data Platform Cost and Performance Optimization.
• Your exercise-pack/NOTES.md after a ruthless self-grade.