Data engineering — routing hub

The shared entry point for building and operating data pipelines. Its job is two things the individual skill descriptions can't do: route you to the right specialist, and state the cross-cutting principles every specialist assumes. When you already know the specialist, load it directly — this hub is for the choices you make before that, and for the discipline that spans all of them.

Routing — which skill

If no specialist fits the storage layer yet, apply the principles below directly — they are format-agnostic.

Cross-cutting principles (shared by every data sub-skill)

Load-bearing in any pipeline, whatever the storage engine. Each is paired with a falsifiable test — if you can't pass the test, the principle is being violated, not "applied differently." Specialists restate the engine-specific form; the canonical version lives here.

1. Every write is idempotent

Re-running the same step — same input window, same batch — produces identical state, never duplicates. This is what makes crashes, retries, and backfills safe. Achieve it with deterministic keys (upsert/merge on a primary key, partition-overwrite of a window, or content-addressed filenames), never by hoping a step runs exactly once.

Test: run the step twice on the same window. Is the output byte-identical? If row counts grow, it isn't idempotent.

2. Move forward by a watermark, not a full recompute

Every step persists how far it has consumed — a max timestamp, a sequence number, a pagination cursor, a snapshot id — and resumes from there. "Rebuild from history" exists only behind an explicit --rebuild flag. A step with no watermark gets slower in proportion to data growth, silently.

Watch for O(history) leaks disguised as incremental — a watermark exists somewhere in the code, but a specific operation still scales with total history. Named shapes and measured cases: references/o-history-leaks.md. The ones that keep shipping:

• Entity-keyed lifetime reload — watermark picks which keys changed, then WHERE entity_id IN (...) with no time predicate. Timer path predicates keys and the partition time column. Lifetime is --rebuild / --full.
• Chunked unique on a frozen basis — each checkpoint unique()s corpus-so-far, or merges against a start-of-run snapshot that logs no existing. Unique this chunk; upsert into current gold.
• Write-window / read-window split — overwrite is partitioned; the read still walks the whole raw tree.
• Unwindowed sidecar / snapshot unique — a "daily" job scans a lifetime fact table into RAM. Daily path is a lookback merge; lifetime is --full.

Test (entity leak): a new fact for one entity today — does runtime grow with that entity's lifetime row count, or with the lookback window? If it grows with lifetime, the watermark is decorative.

Test (chunked unique leak): after chunk 2, does the merge log existing= / preserved= against current gold, or existing=0 / no existing again? If the latter, the basis is frozen or PK-invalid and each chunk is O(corpus-so-far). Peak RSS after chunk N must not track unique(rows 1..N).

Test (read vs write window): with no new files today, does the gold step skip, or does RSS grow with the full raw tree? If it grows, the read is lifetime.

A full-rebuild trigger (schema change, resolver bump, backfill) belongs on its own lane, separate from the regular incremental run, with checkpoints that survive a retry. A rebuild whose retry logic wipes its own staging checkpoints resets to record one on every crash and, on a large enough history, never finishes — measured: 9 attempts over 19 hours, 0 successes, each retry restarting a 602-day rebuild from day 1.

Test: if the upstream grew 10× tomorrow, would this step's runtime grow 10×? If yes, it lacks a watermark. Separately: does a crash mid-rebuild resume from a checkpoint, or restart from record one? If it restarts, the rebuild lane isn't resumable and won't survive its own retries on real data volume.

3. Fence schema at every boundary

Validate columns, engine types, and null fractions at each layer/stage edge, and fail loud at the boundary. Column names are not enough: Iceberg timestamptz vs DuckDB DATE, List(Utf8) vs Utf8, and field order are schema. A malformed upstream payload should error at ingestion with a clear message — not surface as a cryptic cast error three stages downstream. Keep a strict mode that turns warnings into hard failures in production. Iceberg/Arrow/DuckDB form: data-apache-lakehouse.

This is mechanical validation. Semantic correctness (right entity, right attributes for the consumer use — quality flags, trust ladders, cohort fences) is a separate concern: use data-semantic-quality. Schema fences alone do not make rows trustworthy for product aggregates.

Test: if an upstream adds a column or flips a type (including timezone-aware vs naive, list vs scalar), which stage fails, and is its error message actionable? If the failure is far from the cause, the fence is missing.

4. Publish atomically; readers never see a partial write

A reader sees either the old state or the new one, never half. Use tmp → rename for files and a transactional commit for catalogued tables. Never mutate a live output in place while readers are pointed at it.

Test: kill the writer mid-publish. Can a reader observe a torn/partial result? If yes, the publish isn't atomic.

5. Resilience is implemented, not hoped for

Retry transient failures with exponential backoff + jitter; trip a circuit breaker per external dependency so one outage degrades one lane instead of hanging every loop; route un-processable records to a dead-letter queue with enough context to replay. A swallowed exception in an unattended loop is data loss you discover a week later. Code: references/resilience-and-idempotency.md.

Test: when an upstream dependency is down for an hour, does exactly one lane degrade and recover automatically, or does the whole host wedge? Where do failed records end up?

6. Peak memory is bounded by the batch, not the dataset

Tie peak RAM to batch/page/window size, not total volume — stream record batches, lazy-scan, paginate. A step whose memory scales with the table will eventually OOM on a busy day.

Test: does this step's peak RSS depend on total row count, or on batch size? If it scales with the data, it isn't streaming.

7. Freshness is observable

Every run records what it consumed and produced — a watermark, a row count, a run-log line. An unattended pipeline you can't interrogate ("when did this last update, and with how many rows?") is one you can't trust.

Job-success is not freshness. Distinguish absent (source never landed), empty (source ran, zero rows in window), stale (watermark older than the SLA), and timeout (unit SIGTERM — not a successful empty). Empty-chunk skip is not progress; empty-all and timeout are different exits (data-pipeline-operations). Serving SLIs consume these signals; they do not invent a second clock (data-api).

Test: without reading code, can you answer "when did table X last update and how many rows landed?" from a log or a property? If a unit is green, can you still tell empty from stale from timeout? If not, add the signal.

8. Enforcement is layered: runtime guard > CI gate > checklist > review convention

A principle enforced by only a checklist or a comment is a suggestion, not enforcement. Real enforcement stacks from strongest to weakest, and the top layer is the one that actually blocks a violation: a runtime guard that refuses to proceed (a constructor that raises without a required argument, an admission gate that refuses units without a memory cap); a CI gate that structurally parses the change (a unit-file parser checking caps are present and heavy units are routed through admission, a timer-overlap lint, a live-vs-repo drift audit — not a keyword grep); a checklist artifact reviewed per pipeline (a row per pipeline × falsifiable test × last-verified date); and a review convention (a PR template row) as the last, weakest backstop. Use as many layers as the risk warrants — structure beats memory, and memory is the only thing a checklist or convention relies on.

A checker that greps source for a comment or a string literal false-passes: it confirms the words exist, not that the code does what they claim. Enforcement has to parse structure — AST, unit-file fields, config schema — not text.

Test: delete the checklist — does anything still block a violation? If no, you have documentation, not enforcement, and the runtime guard or CI gate layer is missing.

Coverage audit playbook

Use when the ask is inventory, not a code change: how much data, how fresh, how usable. Query the live warehouse / serving path. Frozen onboarding docs and agent memory are not evidence.

Coverage audit:
- [ ] 1. Name the consumer question (source × grain × window)
- [ ] 2. Read live tables (or the serving sidecar + its publish token) — not README/CLAUDE.md
- [ ] 3. Per source: landed rows, last watermark / as-of, absent vs empty vs stale
- [ ] 4. Identity rate: assigned / landed (unresolved and quarantine are not “coverage”) — **data-identity-resolution**
- [ ] 5. Stratify (status, cohort, age). A global % hides empty slices
- [ ] 6. Name the consumer of each table (**data-table-lifecycle**)
- [ ] 7. If the question is estimate honesty, stop and load **data-product-eval**

Test: can you answer “how many resolved rows landed in the last 7 days, per source, and when did each watermark move?” from published signals? If the answer came from a markdown file, the audit failed.

Principle 7 (freshness is observable) is the signal. This playbook is how you read it.

References

• O(history) leak catalog (entity reload, frozen-basis unique, read/write window split, mtime cursor, sidecar lifetime scan): references/o-history-leaks.md
• Shared resilience & idempotency code (retry + jitter, circuit breaker, dead-letter queue, idempotency keys): references/resilience-and-idempotency.md
• Specialist: data-apache-lakehouse — the Iceberg-specific expression of these principles (OCC retry, snapshot watermarks, WAP branches, compaction).
• Specialist: data-api — the API-specific expression (rate-limit buckets, pagination-cursor watermarks, response schema fencing; serving with pushdown + keyset pagination + cache invalidation; sidecar rebuild as a bounded pipeline; serving vs lake memory; freshness SLIs).
• Specialist: data-duckdb — the embedded-engine expression (memory/thread budgeting, larger-than-memory spilling and its limits, Parquet read/write layout, connection-as-cache).
• Specialist: data-pipeline-operations — the multi-pipeline coexistence expression (claims-based admission control, capacity pools, the capacity ratchet loop, subprocess-scope accounting, job-fits-one-TimeoutStartSec).
• Specialist: data-table-lifecycle — the artifact-retirement expression (consumers-or-deprecate, drop durability, catalog-generated maintenance coverage).
• Specialist: data-semantic-quality — row-truth methodology (write-time quality attributes, entity-scoped rules, trust ladders, golden packs); domain thresholds stay in the product repo.
• Specialist: data-identity-resolution — attach/remap procedure (fail-closed, quarantine, collision, unresolved-rate, restamp order).
• Specialist: data-product-eval — estimate vs later realized truth (freeze, walk-forward, coverage vs error, release vs observe).