Hands-on tutorials

Author your first data contract

Draft an enforceable contract for one real dataset by walking the six parts — owner, schema, semantics, SLA, breaking-change policy, enforcement — starting from the data-contract template, and finish with the CI test that makes it a contract instead of documentation.

Catalogs and ownership: who owns this table and is it safe to use?

Every incident resolves faster when you can ask a catalog what this data is, who owns it, and whether it is safe to use.

Compute unit economics for a pipeline

Turn cluster and warehouse billing into unit economics — cost per run, per 1M rows, per model build — so a regression shows up as a number, not a surprise invoice.

Cut Athena bytes scanned with partitions and projection

Walk one expensive Athena query through the Partition Advisor's levers in order: confirm the per-TB bill model, partition on the dominant filter column, replace MSCK REPAIR with partition projection, compact small files, and move to columnar compression.

Decide when a Delta table needs OPTIMIZE or Z-ORDER

Apply the Delta Optimization Advisor's decision rules to one table: read file stats for the OPTIMIZE trigger, pick Z-ORDER columns from actual query predicates, weigh liquid clustering for high-cardinality write-heavy tables, and prove the choice by comparing numFiles and bytes scanned before and after.

Deduplicate records without rewriting the table

Row counts are inflated and revenue double-counted after a retry or a fan-out join.

Enforce contracts in CI with the schema change check

Install the Schema Change Check as a pre-commit gate over staged schema files so a breaking change exits non-zero unless a migration plan or deprecation annotation rides along with it — the producer-CI placement that catches breakage before merge.

Express a freshness check as a metric and an alert

The first hands-on of the series: declare a freshness SLA, express staleness as a metric, and write an alert rule — including what happens when no data arrives at all.

First hands-on — a correct SELECT with a justified join

Stand up a local DuckDB through the DuckDB MCP server, inspect before you query with list_tables and describe_table, and write one SELECT with a join whose cardinality you can justify.

Fix SQL lint with the SQLFluff core rule pack

Install the SQLFluff core rule pack, point it at your warehouse dialect, and lint a fixture that fails on purpose: indentation, keyword capitalisation, SELECT star, and unused CTEs.

Hold the cloud data stack mental model — storage, catalog, compute, IAM

Build the map before opening a console: object storage holds the bytes, a managed catalog describes them, and serverless compute bills for what a query scans.

Hold the observability mental model: infrastructure metrics versus data signals

Two layers live on one data platform: infrastructure metrics (CPU, lag, job success) and data signals (freshness, volume, schema, distribution).

Hold the row-versus-column mental model

Before you pick a file format, understand why analytics engines read columnar files at all: row groups carrying min/max statistics let a reader prune most of the file before decoding it, and columnar layout means a scan of three columns never touches the other twenty.

Hold the streaming mental model: events, partitions, delivery semantics

Build the vocabulary before touching a cluster: events instead of rows, partitions instead of tables, at-least-once delivery instead of exactly-once, and the lambda/kappa decision for speed versus correctness.

Hold the system design mental model — data systems as tradeoffs

• Frame any data system as a tradeoff among reliability, scalability, and maintainability
• Name the six mechanisms DDIA teaches and map each one to a system you operate

Inspect Kafka topics and groups with the Kafka MCP server

Connect an agent to a Kafka cluster through the Kafka MCP server and work through its four tools: list_topics for inventory, describe_topic for a single topic, describe_consumer_group for lag, and consume_messages for sampling — stream debugging from an agent, with the sampling risks called out up front.

Install cost guardrails before the invoice

Instrument, attribute, then guard: the Cost Guardrails pattern in four moves — a unit metric per pipeline, a baseline of the last N runs, CI failing on regression, and hard caps.

Keep an agent read-only until a human approves

Coding agents can do real data-engineering work, but can also ship a schema change or drop a table.

Name the four dimensions of data quality

Build the mental model before writing a single test: freshness, completeness, validity, consistency.

Open the Parquet file — row groups, pages, encodings

A Parquet file is not a black box — it is row groups of column chunks made of pages, with statistics that readers use for predicate pushdown.

Respond to a failing quality expectation

A quality expectation failed in production and bad data may be reaching consumers.

Review a DAG for idempotency and retry safety

Walk the Pipeline Reviewer's four-step pass over a DAG: apply the idempotency test to every task, check retry, timeout and concurrency settings, predict backfill behaviour, and propose missing integration tests — then close the loop with the Airflow DAG Reviewer's checklist so every task is safe to re-run before the DAG merges.

Review a Glue job for sizing, bookmarks, and catalogue drift

Take a Glue/Spark job through the AWS Glue Job Reviewer's three checks: size workers from the input files' partitions instead of habit, verify the job bookmark against the input path layout, and diff the Glue Data Catalog schema against the Parquet file footer.

Review SQL for fan-out joins

Follow the SQL Reviewer agent's four-step pass over a real query: read the diff and its context, establish the grain of every result set, apply the SQL Review skill's five rules while stopping at the first fan-out, and emit findings with file positions plus a rewritten clause you can verify against the twelve-case evaluation fixture.

Run the back-of-envelope drill — numbers before architecture

Estimation is the warm-up act of every design conversation: QPS, storage per day, retention, peak factor, all written down before a single box is drawn.

Run the dbt warehouse tests starter end to end

Clone the dbt-warehouse-tests starter, seed its deterministic customers and orders data, and build the staging → intermediate → marts pipeline with dbt build --target dev.

Run the full SQL review pass on a PR

Run the SQL Reviewer agent's full pass over a pull request: read the diff in context, state the grain of every result set, then check the correctness bugs the SQL Review skill targets — fan-out joins, NULL-unsafe NOT IN and = predicates — and emit findings with file:line and a rewritten clause you can verify against the evaluation fixture.

Scaffold an idempotent DAG from the template

Generate a new pipeline from the Airflow DAG template and keep its guarantees — an idempotent skeleton with task groups, retries and backoff, SLA, pools, and a reschedule-mode sensor — then make catchup a deliberate choice and every write partition-keyed or a MERGE.

Stand up the S3 → Glue → Athena starter

Deploy the serverless query-in-place starter end to end: Terraform provisions buckets, catalog, and a bounded Athena workgroup, Parquet lands in a Hive layout, the table is registered with partition projection, and pruning is proven with explain analyze.

Start a PySpark project from the template

Scaffold a testable PySpark project with one command and learn where each piece lives: transforms in src/, 3-10 row fixtures in tests/data/, and a Makefile that runs lint plus tests offline.

The batch processing mental model

• Name the four causes of a slow batch job: skew, partition count, spill, over-scan
• Explain what a shuffle does to a hot key

The data reliability mental model — freshness, completeness, correctness

Build the map before writing a single check: a consumer experiences a table as three properties — freshness, completeness, correctness — declared as an SLA in a data contract, enforced by a CI test, and spent like an error budget when failures land.

The lakehouse mental model — lake vs warehouse vs table formats vs zones

Build the map before touching a table: object storage is files with no structure for trust or ownership, a table format (Iceberg or Delta) adds snapshots, manifests, hidden partitioning, ACID, and evolution, and medallion zones make trust legible from raw to serving.

The orchestration mental model — DAGs, schedules, idempotency, backfills

Build the map before touching code: a DAG plus its schedule decides when runs happen, the idempotency test decides whether re-runs are safe, catchup decides what happens to missed intervals, and a backfill decides how history gets fixed — four decisions every later lesson sharpens.

The relational mental model — sets, grain, and NULL

Before the first SELECT, build the model every later lesson uses: a query returns a set with a stated grain, a join changes that grain by its cardinality, and NULL never compares as you expect.

The twelve-factor vocabulary for pipeline jobs

Data pipelines run as jobs on shared infrastructure, so the twelve-factor principles apply to them — config in the environment, backing services as attached resources, logs as event streams, disposability, and dev/prod parity.

The warehouse mental model — micro-partitions, clustering, scan vs compute

Build the map before tuning anything: a table is a set of micro-partitions, every query is described by bytes scanned, partitions scanned ratio, and queue time, a clustering key decides how much gets pruned, and a slow query is either a scan problem or a compute problem — never both at once.

Troubleshoot a lagging group with the Kafka Troubleshooter

Hand the Kafka Troubleshooter agent the consumer-group describe output, per-partition offsets, and consumer logs, then follow its four-step workflow from lag trend to the least destructive fix.

What makes an agent — model, tools, loop, guardrails

Build the map before touching anything: an agent is a model that reasons, tools that act, a loop that iterates, and guardrails that bound it.

Wire cloud tooling — registry skills, MCP servers, and one review workflow

Wire the registry's cloud tooling into one agent workflow: install the Glue and Athena review skills, bind the AWS MCP server to a least-privilege profile, and compose both skills plus the MCP server into the architecture reviewer.

Wire the core MCP servers for a data repo

Wire the three reference MCP servers a data repo needs: filesystem for the working directory, git for history and commits, and GitHub for issues and pull requests.

Write the minimum dbt test set for a model

Start from the model's grain, then rank dbt tests by the cost of the failure they prevent: uniqueness and not-null on the grain key, relationships on foreign keys, accepted values on enums, and freshness on sources.

Your first lakehouse table — create, query, measure, compact

Run the whole loop in one sitting on a scratch table: create and query a first Delta or Iceberg table from the official docs, read its health from the files metadata table with one SQL query, run a first OPTIMIZE or rewrite_data_files, and prove it worked by comparing numFiles and bytes scanned before and after.

ACID and concurrent writes — why compaction and ingestion collide

What ACID on object storage actually guards, and where it stops: Delta commits transactions on files, but compaction is also a writer, and the two meet in the ingestion window.

Aggregations and windows without the skew

• Read p50 vs p99 task durations to confirm an aggregation is skewed
• Salt a hot key and aggregate twice

Arrow — columnar memory and zero-copy interchange

• Explain Arrow's role as the columnar in-memory format and interchange substrate
• Name the pieces — language bindings, IPC and Flight protocols, zero-copy interchange

Attribute a 4x Spark cost regression

• Diff shuffle write and input bytes run-over-run
• Attribute a cost regression to one stage

Attribute spend with the Cost Lens plugin

Install the Cost Lens plugin and use its three commands to attribute spend, compute unit economics, and guard cost-per-run.

Audit an MCP server's tool contracts

• Read a tool schema and predict its side effects
• Classify tools by blast radius

Avro and schema evolution for event payloads

• Name Avro's four moving parts — schema declaration, evolution rules, encoding, container format
• Explain why schema evolution rules belong to the schema registry

Capstone — the batch pipeline checklist

Bring the API → S3 → Spark → Parquet starter up end to end, then audit it: offline transform tests, idempotent partition-scoped writes, complete partitions, replay-safe extraction, and baseline metrics with a CI guard.

Capstone — the checklist that decides whether a DAG ships

Close the series by running the full review pass over your own pipeline: the idempotency test per task, partitioned or MERGE writes, deliberate catchup, backoff retries, reschedule sensors, bounded concurrency — and a documented runbook for the day history needs fixing.

Capstone — the cloud guardrail checklist

Close the series by turning nine lessons into one standing checklist: workgroup bytes limits and compaction on storage, sized workers and stable bookmarks on compute, resource monitors and unit-metric alerts on spend, and least-privilege IAM everywhere — then produce the four review outputs as evidence.

Capstone — the foundations checklist

Ten lessons compress into one checklist — place every decision on the lifecycle, lay out analytics files with row groups and statistics, expect Arrow at the boundaries, evolve event payloads with Avro's rules, and run jobs twelve-factor style.

Capstone — the reliability checklist for a pipeline you can page on

Close the series by running the reliability checklist over your own pipeline: a declared and enforced SLA, idempotent tasks with backoff retries, a documented backfill runbook — then drill the incident path from alert to blameless postmortem with dated, owned action items.

Capstone — the SQL correctness checklist

Close the series by turning nine lessons into one standing checklist: a grain statement for every SELECT, a cardinality justification for every join, NULL-safe anti-joins, explicit casts, lint in CI, and one decision rule for slow queries.

Capstone — the standing lakehouse checklist

Close the series by running the full checklist on one table you own: zones with gates, a deliberately chosen format, the three standing monitors, the four-part maintenance plan as a scheduled job, guardrail-enforced windows, and the escalation line that decides when it pages.

Capstone — the warehousing checklist, end to end

Close the series by auditing one warehouse project against everything it taught: models with tests and docs, layered staging to marts with a declared grain, incremental loads with a lookback, CI on a staging schema, monitored query history, and a repo structure a new joiner can read.

Capstone — wire a dataset's quality gates end to end

Close the series by walking one SLA-bearing dataset through every gate the series taught — six-part contract, CI compatibility check, staged quality suite, quarantine split, quarantine-rate alerts — and finish with the monitoring signals and incident paths that keep the gates honest.

Capstone: the data-platform observability checklist

• Assemble the full stack — metrics, alerts, traces, lineage, catalog
• Gate schedule and column changes pre-merge with SLA and impact checks

Check lakehouse table health with Lakehouse Guard

Use the Lakehouse Guard plugin's three commands on a Delta/Iceberg table: /table-health for a status read, /plan-compaction for a maintenance plan, and /expire-snapshots for a safe expiry plan.

Choose storage engines on tradeoffs — reads, writes, and consistency

Storage choice is where a design's promises become real: the engine you pick decides what replication buys, what partitioning costs, and which consistency semantics your merges get.

Choosing file formats — Parquet, Avro, and Arrow's role

The three formats in this series are not competitors — they answer different questions.

Column-level lineage: the concepts and the event standard

Table-level lineage says a job touched a table; column-level lineage says which columns feed which.

Compression and encodings — the tradeoffs behind the file

Encodings and compression codecs are where file size and scan speed are actually decided — Parquet applies encodings and codecs per column chunk with statistics on top, while Avro carries schema plus encoding for event payloads.

Debug a Kafka stream with the Kafka Console plugin

Drive the Kafka Console plugin's three commands across a lag incident: /topic-info for inventory, /lag-triage for per-partition lag and rebalance detection, and /sample-topic for a read-only message sample with a PII redaction pass.

Define reliability in numbers — SLIs, SLOs, and error budgets

• Define an SLI and an SLO for a data property like freshness or completeness
• Compute an error budget and state what burning it permits and forbids

Design resilient pipelines — retry, circuit breaker, bulkhead

• Match each pipeline failure shape to its named pattern and its considerations
• Cap retries with backoff and know when a circuit breaker replaces them

Diagnose a skewed Spark stage

• Read task-level duration histograms to confirm skew
• Choose between AQE skew join, salting and broadcast

Diagnose a small-files explosion

Follow the small-files runbook from symptom to prevention: confirm the tens-of-thousands-of-tiny-files signature, query the metadata tables for the average file size, compact to a 128-256 MB target, coarsen an over-partitioned layout, and schedule compaction outside the ingestion window.

DR and replication — design the recovery path before you need it

Disaster recovery for data systems starts before the outage: DDIA's replication and consistency chapters name the mechanisms, and the registry's runbooks name the recovery paths — code rollback for deterministic failures, quarantine-then-retry for data errors, shadow-then-swap for recomputes.

Fan out over date batches with dynamic task mapping

Run one task definition over many date batches with dynamic task mapping — the docs' mapping feature applied the way the backfill material demands: batched ranges, smallest first, a validated sample after each batch.

Format-driven performance incidents — small scans and wrong layouts

When a scan reads the whole lake for one column, the cause is usually the file layout, not the engine — missing statistics, row groups sized without intent, encodings chosen by default.

Gate backfills with the safety check hook

Wire the Backfill Safety Check as a pre-run gate that refuses any backfill lacking an explicit date range, a shadow or snapshotted target, and a window clear of the scheduled production run — and watch it block the bare backfill command that would otherwise corrupt the target.

Gate promotion with a pre-deploy DQ check

Wire the pre-deployment DQ check so a pipeline is only promoted when its quality suite passes against staged output.

Govern cloud spend — right-sizing, resource monitors, and unit economics

Govern cloud spend the way you govern data quality: right-size each warehouse per environment, put a resource monitor between the workload and the invoice, and alert on cost-per-run instead of total spend.

Hooks — deterministic gates around an agent

• Explain where hooks run — on tool-call and pre-run events, outside the model
• Match each safety hook to the failure it prevents

Idempotency and reruns — make every task safe to run twice

Retries are inevitable, so a task that appends duplicates rows every time it is cleared: hold each task to the idempotency test, replace its partition atomically instead of appending, and assert the invariant in CI with a test that runs the same date twice.

Incident — the dashboard query that went slow

Run the slow-query runbook as an incident: confirm the regression, split scan cost from compute contention, pull partitions_scanned and bytes from query history as evidence, apply the matched fix, and escalate on the shared-warehouse SLA before the queue takes other teams down with it.

Incident — the job that quadrupled in cost overnight

An invoice alert or a cost-per-run dashboard spike lands: one job's compute cost quadrupled with no functional change.

Incident — the numbers doubled and no alarm fired

Row counts are inflated and metrics double-counted after a retry, a fan-out join or a backfill — the incident signature of a silent fan-out.

Incident drill: a broken upstream caught by freshness and lineage

The source job quietly changed from hourly to daily; the mart's 6h freshness SLA now fails by construction.

Incremental processing with a watermark

• Bound incremental work with a watermark and a lookback window
• Write incremental output idempotently so a re-run is safe

Inside a skill — SKILL.md, references, examples, the loading protocol

A skill is the unit of reusable agent behavior: one SKILL.md with front-matter, plus directories the agent loads on demand.

Instrument pipelines with OpenTelemetry: traces for data jobs

Traces give every pipeline run a story — which task ran, how long, with what attributes.

Lineage tooling in practice: DataHub MCP and the lineage-graph plugin

Put lineage to work with two tools — a DataHub MCP server that lets an agent search entities and read lineage and schema, and a lineage-graph plugin that turns column-level impact analysis into agent commands.

Make a DAG data-aware with reschedule-mode sensors

Stop guessing when upstream data arrives: add a sensor in reschedule mode so the DAG waits for its input without holding a worker slot, read the scheduling-semantics pages first, and bound the sensor with timeout and concurrency settings like any other task.

Make queries prune — clustering keys and scan diagnosis

Choose a clustering key from the predicates of your most expensive queries, prove the prune with partitions_scanned versus partitions_total, and fix the disease you actually have — a lost predicate or a low-selectivity key on the scan side, or queue time on the compute side.

Make the sink idempotent so replays never duplicate rows

The core producer-side reliability skill for streams: you cannot stop replays, so the sink must converge.

Model a star schema — facts, conformed dimensions, declared grain

Turn the analytical layer into facts surrounded by conformed dimensions: declare the fact grain in schema.yml, stage and intermediate models below it, SCD2 where history must survive, and scaffold the whole thing from the dbt project template so staging, marts, tests, and CI exist from commit one.

Onboard an agent with the DE Data Toolkit

Onboard an agent onto a data repo with the DE Data Toolkit plugin: the SQL/Spark review skills, the destructive-SQL guard, a secret scanner, and the file/git MCP servers already wired.

Own the consumer side — offsets, rebalances, and the KIP-848 protocol

Consumer reliability is where streams quietly break: a rebalance loop looks like slowness, consumers past the partition count buy nothing, and stop-the-world rebalances pause consumption.

Plan and execute a backfill that doesn't break production

Historical output is wrong or missing, and the fix touches a production table: define correct first, map the blast radius from lineage, then shadow-write in date batches smallest-first, validate a sample per batch, and swap atomically — behind a gate that demands a rollback path.

Plan and execute a safe backfill

Use the Backfill Planner's four-step plan — correctness criteria, downstream impact from lineage, cost and contention, staged batches — then execute it the way the backfill runbook demands: shadow table, sample validation per batch, and never beside the daily production run.

Production gotchas — NULL traps, implicit casts, silent drift

The bugs that survive testing compare wrong instead of failing loud: NOT IN over a nullable subquery, a comparison across types left to an implicit cast, and a schema change that silently turns a column to NULLs.

Production gotchas — silent failures and partial writes

The failures that become pages are the ones nobody was watching: a regression hiding in growth, a cron change that silently widens the freshness SLA, and appends that duplicate on retry — cap each with a guard, a computed worst-case lag, and an atomic partition replace.

Production gotchas — surprise bills, region defaults, and IAM overreach

The surprises that turn into invoices: workers sized by habit, a renamed prefix that silently resets a bookmark, thousands of small files, an agent holding an admin role, and a query that scans everything because the predicate missed the partition column.

Production gotchas — the configs and capacities that break streams

The five causes of consumer lag on one card, the two destructive shortcuts that must never happen, and the capacity trap of scaling past the partition count.

Production gotchas — topology, timeouts, concurrency

The DAG failures that become pages: unbounded concurrency, missing catchup policy, and a backfill that starves the daily run — cap them with max_active_runs and pools, then wire the monitoring and escalation path the backfill runbook prescribes.

Production gotchas: alert fatigue, missing contracts, no-data silence

Three failure modes of a monitoring stack that looks healthy: alerts that page until nobody reads them, metrics with no declared SLA behind them, and no-data windows that pass as health.

Read a Spark event log with the Spark Lens plugin

Install the Spark Lens plugin and work its three commands over a Spark event log: /spark-analyze to build a task histogram and flag skew, /spark-explain-plan to check the join strategy, and /spark-baseline to store the cost baseline the guard enforces.

Read an EXPLAIN plan and pick the one fix

Take the EXPLAIN output of a slow query and read it bottom-up, naming the expensive node before you touch anything.

Read reference architectures like an engineer — AWS as precedent

• Decompose a reference architecture into ingest, store, process, and serve
• Connect each architectural choice to a Well-Architected pillar

Reading data files — from bytes to columnar memory

A read is a pipeline in miniature — the reader consults statistics, prunes row groups, decodes only the columns you asked for, and hands you an in-memory columnar structure.

Respond when a data contract breaks

Work the broken-contract runbook end to end: identify which clause broke, bound the consumer impact set from lineage before anything else, decide whether the producer reverts or the consumer adapts, and close with consumer-driven contract tests in the producer's pipeline.

Review a dbt model PR before merge

Take a pull request that adds or changes a dbt model and walk the reviewer's pass: read the diff in context, state the model's grain and check it against the unique key test, choose table vs incremental vs view deliberately, and handle late-arriving data in the incremental filter.

Review a PySpark transform for silent quality failures

Run the five-check pass over a PySpark transform before it merges: null propagation, join fan-out, silent coercion, non-determinism, and missing post-write assertions.

Review a schema change against its contract

Run the Schema Reviewer's four-step pass over a producer's schema diff — classify each field change, resolve consumers from lineage, emit a dated migration plan — and use the schema-drift runbook to separate hard failures from silent nulls before anything merges.

Run an AWS data architecture review pass

Point the AWS Architecture Reviewer at an architecture description or IaC and follow its four-step workflow: map the services to a data flow, check IAM for least privilege, estimate dominant cost drivers, and identify single points of failure.

Run dbt model review and tests from the workbench plugin

Install the dbt Workbench plugin and drive one model change through its three commands: /dbt-review-model for the model review, /dbt-write-tests for a proposed test set, and /dbt-impact before you merge.

Run the Iceberg maintenance routine

Work an Iceberg table through the maintenance skill's plan: read the files metadata table to size the problem, rewrite data files to a 128-256 MB target, rewrite manifests when the manifest list is huge, expire snapshots without breaking time travel, and remove orphan files on a schedule with a safety margin.

Schema evolution and time travel — change the table, keep the past

Change a table without breaking its readers or its history: the Iceberg spec's schema and partition evolution, Delta time travel, the history metadata table you query to see what happened, and the snapshot-expiry rule that keeps time travel working while storage stays bounded.

Speak the scaling vocabulary — load balancing, caching, sharding

Scale arguments go wrong when the words do: horizontal versus vertical, cache-aside versus write-through, shard by range versus hash.

Split a batch into quarantine and publish

Build the pipeline-dq-quarantine starter the way the Quarantine then Publish pattern prescribes: a quality contract splits each batch into a published set and a quarantine with reasons.

Stream changes, not tables — CDC from Postgres into the lakehouse

The intermediate pattern that replaces nightly extraction: read the write-ahead log through Debezium, stream through Kafka, dedupe by LSN, and MERGE into Delta/Iceberg.

Table format mechanics — snapshots, manifests, and the metadata tables

Open the hood on an Iceberg table: the spec's layers (snapshots, manifests, hidden partitioning), the files and history metadata tables you query with plain SQL, and the manifest list as a maintenance target in its own right.

Triage Kafka consumer lag from per-partition offsets

Combine the lag-triage skill's three questions with the lagging-consumer runbook: read per-partition lag to separate capacity from key skew, check the group state history for a rebalance loop, find the one stuck offset that marks a poison message, then apply the matching fix — and never reset offsets.

Walkthrough: the lineage-explainer skill, from question to impact set

Install the lineage-explainer skill and walk it end to end: ask what breaks if a column changes, read technical and business lineage from the store, and produce an impact report with owner, criticality and the earliest SLA that breaks.

Window a stream correctly — watermarks, dedupe, and late data

Stream processing in practice: start from the kafka-streaming template, pick event-time over processing-time windows, set a watermark you can defend, and prove what happens to late data.

Window functions — keep the grain, rank the rows

A window function computes over a set of rows and hands the answer back to every row, so the result keeps its grain — the property that makes a keep-latest dedupe safe and a bare GROUP BY grain-changing.

Wire task dependencies and retries that survive failure

Model the pipeline as task groups and give every task deliberate retries with backoff, a timeout and a pool — the skeleton's guarantees, verified the way the DAG reviewer verifies them before the DAG ships.

Capstone — the guarded agent checklist

Run the whole series against one agent setup: skills that load on demand, tools scoped at the connection, least-privilege credentials, audited tool contracts, hooks that fail closed, and a human gate on anything irreversible.

Capstone checklist — a design is done when these all hold

Nine lessons compress into one checklist: tradeoffs named, numbers written, storage chosen by mechanism, scaling lever identified, budget defined, failure patterns assigned, precedent cited.

Interview walkthrough — design a data platform end-to-end

The full arc in one 45-minute shape: requirements, back-of-envelope numbers, the storage and scaling argument, reliability as SLIs and budgets, and a deployed precedent to close.

Production gotchas — premature scaling and the missing capacity plan

The two self-inflicted design failures: complexity bought before load requires it, and capacity treated as an afterthought until the pager goes off.

Production gotchas — untrusted content, destructive actions, permission scope

An agent executes with your credentials, so its context and config are an attack surface.

The 3am batch incident path

• Collect the four pieces of evidence a failing Spark run needs
• Decide between revert, a config change, and a code fix

Tune a slow job — memory, partitions, baselines

Run the tuning loop on a slow batch job: parse task-duration and shuffle metrics, classify the stage as skew, partition count, spill or scan, apply the single highest-impact change, and verify it against a stored baseline the CI guard enforces.

Warehouse production gotchas — cost, full refreshes, late data

The three ways a healthy warehouse model hurts production: a full-refresh that rebuilds a 4B-row table unannounced, a lookback window too short to catch late-arriving orders, and cost drift nobody reviews.

When an agent does the wrong thing — contain, audit, postmortem

• Contain the damage before diagnosing — rollback first
• Reconstruct what the agent did from the audit trail

SQLFluff Core Rule Pack

• Configure SQLFluff for your warehouse dialect
• Customize rules for team standards

dbt Test Writer

• Rank tests by the cost of the failure they prevent
• Write a custom singular test

Incident Postmortem Writer

• Write a blameless timeline
• Separate trigger from contributing factors

SQL Review

A review pass that catches correctness bugs before the query reaches production.

dbt → warehouse → tests → docs

Transform raw landed data into a tested, documented star schema with dbt, and gate every change on data-aware CI.

Data Classification & Tagging Governance Pack

Standardized tag taxonomy, glossary, and classification rules for data governance

OPA Data Access Control Policy

Rego policies for least-privilege data access across warehouses, lakes, and catalogs

Snowflake Warehouse Configuration Template

Production-ready Snowflake warehouse configs with auto-scaling, resource monitors, and cost controls

Airflow DAG Reviewer

• Make every task safe to re-run
• Choose retries and timeouts deliberately

Athena Partition Advisor

• Read the scanned-bytes metric per query
• Apply partition projection for high-cardinality columns

AWS Glue Job Reviewer

• Size Glue workers from data volume, not habit
• Use job bookmarks correctly

Cost Unit-Economics Reporter

• Attribute compute cost to a pipeline run
• Define a unit metric that resists regressions

Data Contract Author

• Specify a freshness and completeness SLA
• Encode a contract as executable tests

dbt Model Review

• State a model's grain and match it to its unique key test
• Choose table vs incremental vs view deliberately

Delta Optimization Advisor

• Decide if a table needs OPTIMIZE from file stats
• Choose Z-ORDER columns from query predicates

Lineage Explainer

• Read the lineages of an asset from a lineage store
• Compute a downstream impact set

PII Classifier

• Classify columns into PII categories
• Choose masking vs hashing vs tokenisation

Pipeline Test Author

• Test a transform without a Spark cluster
• Assert invariants instead of golden files

Schema Change Contract Review

• Apply backward/forward compatibility rules to a diff
• Detect a breaking change hidden behind a "safe" rename

Spark Data Quality Review

• Identify the five classes of silent failure in a Spark transform
• Add pre/post row-count and null assertions to a job

SQL Query Plan Explainer

• Read a plan bottom-up and name the expensive node
• Detect cardinality misestimation from stale statistics

API → S3 → Spark → Parquet

Land a paginated REST API into S3 raw, transform with PySpark into partitioned Parquet, and make the whole thing idempotent and testable offline.

Pipeline → DQ → quarantine → publish

Put a contract in front of a table: validate each batch, publish the clean rows, quarantine the rest with reasons, and alert on the quarantine rate.

S3 → Glue → Athena

A serverless, query-in-place pattern: land Parquet in S3, catalogue it with Glue, and query with Athena while keeping scanned bytes (and cost) low.

Backfill Planner

• Scope a backfill by date and by dependency
• Detect which downstream tables must also be rebuilt

CDC Merge Designer

• Order change events deterministically
• Handle deletes (tombstones) without losing history

Iceberg Table Maintenance

• Read an Iceberg metadata table to size the problem
• Pick a compaction strategy and rewrite interval

Kafka Consumer Lag Triage

• Read per-partition lag to separate skew from throughput
• Detect a rebalance loop from consumer-group state

MCP Tool Contract Auditor

• Read a tool schema and predict its side effects
• Classify tools by blast radius

Spark Cost Regression Guard

• Capture cost-relevant metrics from a Spark run
• Store and compare a baseline

Spark Skew Diagnosis

• Read task-level duration histograms to confirm skew
• Choose between AQE skew join, salting and broadcast

Warehouse Clustering Advisor

• Separate scan inefficiency from compute inefficiency
• Choose a clustering key from predicate selectivity

Kafka → Spark Streaming → Delta

A low-latency stream into Delta Lake with watermarking, windowed aggregation and idempotent upserts that survive replays.

Postgres → CDC → Kafka → Lakehouse

Capture changes from Postgres with Debezium, stream through Kafka, and merge into a lakehouse table with correct ordering, deletes and replay safety.