Chapter 2 — Key Takeaways (The Data Engineering Lifecycle)

The page to have open when something breaks and you do not yet know where.

The model

   ┌───────────┐    ┌────────┐    ┌───────────┐          ┌──────────┐
   │ GENERATE  │───▶│ INGEST │───▶│ TRANSFORM │─────────▶│  SERVE   │
   └───────────┘    └────────┘    └───────────┘          └──────────┘
        │                │              │                     │
   ╔════▼════════════════▼══════════════▼═════════════════════▼═══════╗
   ║                            S T O R E                             ║
   ╚══════════════════════════════════════════════════════════════════╝
   ┌──────────────────────────────────────────────────────────────────┐
   │ security │ data mgmt │ DataOps │ architecture │ orchestration │ SWE │
   └──────────────────────────────────────────────────────────────────┘

Store is a substrate, not a step. Every stage reads and writes it.

The diagnostic — use this first

Locate the stage before choosing a tool. Four hypotheses, four queries, ~12 minutes:

The count-at-every-layer query is the highest-value five minutes in any investigation. Write it before you need it; put it in the runbook.

SELECT 'bronze' AS layer, COUNT(*) FROM bronze.x  WHERE d = :dt
UNION ALL SELECT 'silver', COUNT(*) FROM silver.x WHERE d = :dt
UNION ALL SELECT 'gold',   COUNT(*) FROM gold.x   WHERE d = :dt;

Also: notice what is not broken. It eliminates half the tree for free.

The six questions about every source system

1. What is the system of record for this fact when two systems disagree?
2. What is the update pattern — append-only, mutable, or hard deletes? (hard deletes break naive ingestion completely)
3. Is there a reliable change timestamp? Four ways updated_at lies: app-maintained (misses direct SQL), assigned at transaction start, second granularity, not set on every code path.
4. Who can change the schema, and will you be told?
5. What load can it take, and when?
6. What does the data actually mean? ← nobody asks this one.

The watermark bug

 t=100.0  txn A begins, updated_at = 100.0
 t=100.5  txn B commits, updated_at = 100.5
 t=100.6  extract reads B, sets watermark = 100.5
 t=101.0  txn A commits with updated_at = 100.0  →  permanently invisible

Fixes, in ascending correctness: overlap the window (turns loss into duplicates, which idempotent writes already handle) · use a commit-ordered monotonic column · use CDC (Ch. 14).

Ingestion decisions

Why at-least-once + idempotent writes: it moves the hard problem from the transport layer, where it is a distributed systems problem, to the write layer, where a DELETE solves it.

The five transformation layers

The grain trap

-- one row per order line, joined to promotions... 2 promotions = 2 rows = doubled revenue
SELECT oi.*, p.promotion_code
  FROM fct_order_item oi LEFT JOIN promotions p ON p.order_id = oi.order_id;

Valid SQL. Correct values. Wrong grain. Three defenses, use all three:

1. Declare the grain in the docs, in words, at the top.
2. Test it — unique on the declared key, in CI and production.
3. Assert row counts across the join.

SELECT DISTINCT is not a fix. It masks the symptom and silently collapses legitimately distinct rows.

Store — four decisions you own

System (Part II) · Format (Ch. 11) · Layout ← made by default, paid on every read · Lifecycle policy (Ch. 33, Ch. 31)

Small-files arithmetic, Kestrel clickstream at 30-second commits:

$$\frac{86{,}400}{30} \times 12 = 34{,}560\ \text{files/day} = 12.6\text{M/year at 27 KB each}$$

Full-year scan ≈ $5.05 in GET requests alone — and the latency hurts long before the bill does. Compact to 256 MB files: ~1,332 files, same bytes.

Serve — five patterns

BI/analytics · machine learning (point-in-time correctness) · reverse ETL (inverts your risk — bugs face customers) · data as a product · self-serve.

The boundary fails in both directions, with the same symptom (dashboards disagreeing):

Workable: modeled star schema at the boundary + a few measured aggregates + exactly one place each metric is defined.

The six undercurrents and how each fails

⚠️ Data management fails against a statutory clock. GDPR erasure: one month to respond, extendable by two for complex requests. Finding every copy is a design decision made in Part II.

Fail loudly, not plausibly

Choose loud. The exception is a genuinely availability-critical surface — and in analytics, you almost never are.