# 01 — Architecture & Modeling

Decisions in this file are made once per platform, not once per pipeline.
When auditing, mismatches between the platform's stated architecture and what
the code actually does are findings in themselves.

## Size the problem honestly

- **Default to single-node engines below ~100 GB working set.** DuckDB (1.5.x
  as of mid-2026) or Polars on one machine outperforms a Spark cluster on
  cost, latency, and operational burden for the overwhelming majority of
  analytical workloads. "We might grow" is not a reason; migrating a
  well-modeled SQL project later is cheaper than running Spark for 5 GB now.
- **Reach for distributed engines (Spark, Trino, warehouse MPP) when:** the
  working set genuinely exceeds single-node memory+disk economics (TBs per
  query), you need concurrent heavy users on shared compute, or the
  organization already operates the platform and marginal cost is near zero.
- **AUDIT:** A Spark/EMR/Dataproc cluster processing < 50 GB/day with no
  growth trajectory is a MEDIUM cost finding. Quantify: rows/bytes per run vs
  cluster size.

```python
# BAD: 8-node Spark cluster for a 3 GB daily file
df = spark.read.parquet("s3://bucket/daily/")  # 3 GB
df.groupBy("region").agg(...).write.parquet(...)

# GOOD: same job, one process, no cluster
import duckdb
duckdb.sql("""
  COPY (SELECT region, sum(amount) FROM 's3://bucket/daily/*.parquet'
        GROUP BY region)
  TO 's3://bucket/marts/region_daily.parquet'
""")
```

## ELT over ETL — and the exceptions

- **Default: extract-load raw, transform in the warehouse/lakehouse (ELT).**
  Raw data lands unmodified; transformations are versioned SQL run where the
  data lives. This gives replayability (re-derive everything from raw),
  auditability, and lets analysts iterate without re-extraction.
- **Land raw data immutably.** The landing zone is append-only, partitioned
  by load time, retained per policy. Never "fix" raw data — fix the
  transform and rerun.
- **Transform before load (ETL) only when justified:** PII must be
  masked/tokenized before it touches the analytical store (see
  `sota-privacy-compliance`); the source format is pathological (mainframe
  copybooks, multi-GB XML) and pre-parsing is cheaper than in-warehouse
  parsing; volume reduction at the edge materially cuts transfer/storage cost.
- **Never transform-in-flight as an excuse to skip raw retention.** If the
  transform is wrong, untransformed data is gone.

## Warehouse vs lakehouse

Decide by ownership and access patterns, not fashion. Both converge: every
major warehouse reads/writes Iceberg (v3 is GA on Snowflake/Databricks and
Spark 4.0; verify your engine — Trino v3 support still lagged as of writing),
and lakehouse engines speak SQL.

- **Choose a managed warehouse (Snowflake/BigQuery/Redshift-class) when:**
  one team owns the data end-to-end, workloads are SQL-dominant, you want
  zero storage-layer ops, and per-query cost is acceptable.
- **Choose a lakehouse (Iceberg/Delta on object storage) when:** multiple
  engines must read the same tables (Spark + Trino + warehouse + Python),
  data volume makes warehouse storage pricing punitive, you need open-format
  exit options, or ML workloads need direct file access.
- **Hybrid is the 2026 norm:** lakehouse for bronze/silver bulk, warehouse
  (often via external Iceberg tables) for gold/serving. Avoid copying data
  between them when external-table access suffices.
- **Table format choice:** Iceberg if multi-engine neutrality matters (widest
  catalog/engine support, REST catalog standard); Delta if Databricks is the
  center of gravity. Do not run both as peers — pick one as the canonical
  format. DuckLake (1.0+ as of mid-2026) is viable for DuckDB-centric small
  platforms but verify engine/ecosystem fit before committing.
- **AUDIT:** Same dataset copied into both a lake and a warehouse with two
  transform stacks = HIGH (divergence is inevitable). Two table formats with
  no stated rationale = MEDIUM.

## Layered modeling (medallion / staging-core-mart)

Names differ (bronze/silver/gold ≈ raw/staging/core/mart); the contract is
what matters:

- **Raw/bronze:** source-faithful, append-only, never queried by consumers.
  Schema = whatever the source sent, plus load metadata (`_loaded_at`,
  `_source_file`).
- **Staging/silver (1:1 with source entities):** rename to house conventions,
  cast types, deduplicate, no joins, no business logic. One staging model per
  source table. This is where upstream drift breaks loudly.
- **Core/intermediate:** business entities and processes — joins, grain
  changes, business logic, dimensional models live here.
- **Mart/gold:** consumer-shaped, documented, contracted. Dashboards and
  reverse-ETL read only from here.
- **Rules:** consumers never read below mart/core; no layer reads from a
  layer above itself; no model reads raw except its own staging model.
- **AUDIT:** Dashboards querying raw/bronze = HIGH. Business logic in staging
  (joins, CASE-heavy derivations) = MEDIUM. Circular/layer-skipping refs =
  MEDIUM.

```sql
-- BAD staging model: SELECT *, business logic, a join
SELECT *, CASE WHEN o.status IN ('x','y') THEN 'churn' END AS churn_flag
FROM raw.orders o JOIN raw.customers c ON o.cust = c.id;

-- GOOD staging model: enumerate, rename, cast, dedupe. Nothing else.
SELECT
  order_id::bigint            AS order_id,
  cust                        AS customer_id,
  lower(status)               AS order_status,
  created::timestamptz        AS created_at
FROM raw.orders
QUALIFY row_number() OVER (PARTITION BY order_id ORDER BY _loaded_at DESC) = 1
```

## Dimensional modeling still matters

Star schemas remain the right core-layer shape for business processes:
cheap-to-scan fact tables plus reusable conformed dimensions. Columnar
engines did not obsolete them — they made the joins cheap.

- **Facts:** one row per business event at a declared grain. Write the grain
  in the model docs ("one row per order line per day"). Mixed-grain facts are
  a MEDIUM finding. Facts carry foreign keys + numeric measures; degenerate
  dimensions (order number) are fine inline.
- **Dimensions:** conformed and reused. One `dim_customer` shared by all
  facts, not per-mart copies that drift.
- **Surrogate keys:** deterministic hashes of the natural key
  (`md5(source || natural_key)`) beat sequence-generated keys in
  rebuild-from-raw ELT — they're stable across full rebuilds.

### Slowly changing dimensions — pick deliberately

- **Type 1 (overwrite):** default when history of the attribute doesn't
  drive analysis (fixing typos, current email). Cheapest; destroys history.
- **Type 2 (versioned rows with `valid_from`/`valid_to`/`is_current`):** use
  when facts must join to the attribute *as it was at event time* (customer
  tier at purchase, sales territory at booking). Facts join on key + date
  range, or capture the dimension surrogate key at load time.
- **Type 3 / hybrids:** narrow uses (single "previous value" column). Don't
  build elaborate Type 6 machinery on speculation.
- **In ELT, prefer snapshots over hand-rolled SCD merge logic** (dbt
  snapshots or equivalent): capture source state on schedule, derive Type 2
  ranges from snapshots. Hand-written SCD2 MERGEs are a classic source of
  silent corruption — audit their handling of deletes and reruns closely.
- **AUDIT:** Analyses that need point-in-time attributes joined against a
  Type 1 dimension = HIGH (numbers are silently wrong for any historical
  period).

## One Big Table pragmatism

Wide denormalized tables (OBT) are legitimate **mart-layer** artifacts:
build the star in core, then flatten into OBT where a BI tool or consumer
benefits. Columnar storage makes width nearly free to store and scan-prune.

- OBT as the *only* model (no dimensional core underneath) = MEDIUM: every
  new question forces re-deriving logic, and SCD handling becomes ad hoc.
- Never maintain the same metric logic in two OBTs. Derive both from one
  core model.

## Semantic layers

- Define each business metric **once** — in a semantic layer (dbt MetricFlow,
  Cube, LookML-class) if you have multiple BI/consumer surfaces, or simply in
  a single mart model if you have one. The anti-pattern is the same metric
  hand-written in five dashboards.
- A semantic layer is justified by *consumer multiplicity*, not team size.
  One BI tool + one team → mart models are your semantic layer; don't add a
  product.
- **AUDIT:** Grep dashboards/notebooks for re-implemented revenue/active-user
  definitions. Divergent definitions of one metric = HIGH (trust erosion is
  the most expensive data failure).

## Data mesh caution

Data mesh is an **org pattern** (domain ownership, data-as-a-product,
federated governance), not a technology purchase. Apply it only when multiple
domain teams *already* have engineers who can own pipelines end-to-end.

- A central 4-person data team "adopting mesh" is a red flag: it produces
  fragmentation without ownership.
- The durable, steal-able ideas regardless of mesh: producer-owned data
  contracts (rules/04), domain-aligned mart ownership, self-serve platform
  tooling.
- **AUDIT:** "Mesh" with no contracts, no per-domain owners on call for their
  data products, and one shared platform team doing all the work = the label
  is decorative; assess it as a centralized platform.

## Audit checklist

- [ ] Engine size vs data size: any distributed cluster processing < 50
      GB/day without rationale?
- [ ] Raw layer exists, is append-only/immutable, and is not queried by
      consumers or BI tools?
- [ ] Staging models: 1:1 with sources, columns enumerated (no `SELECT *`),
      typed, deduped, no joins/business logic?
- [ ] Layer discipline: no consumer reads below mart/core; no
      layer-skipping or circular references?
- [ ] Each fact table has a documented, single grain?
- [ ] Dimensions conformed (one copy) and SCD strategy explicit per
      dimension; point-in-time analyses backed by Type 2 or snapshots?
- [ ] Surrogate keys stable under full rebuild?
- [ ] Key metrics defined exactly once; no divergent copies in
      dashboards/marts?
- [ ] Single canonical table format; no unjustified duplicate storage of the
      same dataset across lake and warehouse?
- [ ] If "data mesh" is claimed: named domain owners, contracts, and
      operational responsibility actually exist?

# 02 — Pipelines & Orchestration

The prime directive: **every pipeline run is safe to repeat.** Retries,
backfills, and "just rerun it" are how data platforms are actually operated;
a pipeline that corrupts on rerun is broken even if its happy path is
perfect.

## Idempotency — the prime directive

A task given the same logical interval must produce the same stored result
no matter how many times it runs, and a partial failure mid-run must not
leave double-counted or half-written data.

- **Choose the write strategy before the transform:**
  - *Partition overwrite* (delete-insert or `INSERT OVERWRITE` /
    `replace_where`): default for interval-partitioned facts. The task owns
    exactly its interval's partition(s) and replaces them wholesale.
  - *MERGE on a unique key:* for upserted entities/dimensions and late-data
    cases where the interval doesn't bound the affected rows.
  - *Full rebuild:* fine for small dimensions/marts — simplest possible
    idempotency.
  - *Blind append:* only into the immutable raw landing zone keyed by a
    unique load ID, never into modeled layers. **A scheduled `INSERT INTO ...
    SELECT` against a modeled table is a CRITICAL finding** — every retry
    duplicates rows.
- **Writes must be atomic per run.** Table formats (Iceberg/Delta) give
  atomic commits; on plain Parquet, write to a temp prefix and swap. Never
  delete-then-insert as two separately failable steps without a transaction
  or atomic swap.
- **Side effects too:** notifications, reverse-ETL pushes, API calls inside
  pipelines need idempotency keys, or must move to a terminal step that runs
  only after the data write commits.

```sql
-- BAD: duplicates on every retry/rerun
INSERT INTO fct_orders
SELECT ... FROM stg_orders WHERE order_date = '{{ ds }}';

-- GOOD: rerun-safe partition replacement (engine-equivalents: Iceberg
-- INSERT OVERWRITE, Delta replaceWhere, BigQuery MERGE/partition decorator)
DELETE FROM fct_orders WHERE order_date = '{{ ds }}';
INSERT INTO fct_orders SELECT ... FROM stg_orders WHERE order_date = '{{ ds }}';
-- (as one transaction / atomic commit)

-- GOOD: keyed merge when rows aren't bounded by the interval
MERGE INTO dim_customer t USING batch s ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
```

## Logical time, not wall-clock time

- **The orchestrator supplies the interval; the task uses only that.**
  `WHERE created_at >= NOW() - INTERVAL '1 day'` is a HIGH finding: results
  depend on when the task ran, reruns produce different data, and backfills
  are impossible.
- Parameterize every extraction and transform by `[interval_start,
  interval_end)`. Half-open intervals; never overlap, never gap.
- Late-arriving data: don't widen the window with fudge factors ("reprocess
  last 3 days just in case") as the *only* mechanism — that's a cost/cover
  tradeoff that still misses data later than the fudge. Prefer watermark
  columns (below) or merge-based reprocessing of affected keys.

```python
# BAD: wall-clock-relative — rerun tomorrow, get different data
@task
def extract():
    df = read_sql(f"SELECT * FROM orders WHERE created_at > "
                  f"'{datetime.now() - timedelta(days=1)}'")

# GOOD: orchestrator-supplied logical interval — rerunnable forever
@task
def extract(data_interval_start, data_interval_end):
    df = read_sql(
        "SELECT ... FROM orders WHERE created_at >= %s AND created_at < %s",
        (data_interval_start, data_interval_end),
    )
```

## Incremental processing & watermarks

- **Incremental by default for large sources.** Track a high-water mark on a
  monotonic column (`updated_at`, log sequence number, ingestion time) and
  select `> last_watermark AND <= new_watermark`. Persist the watermark
  transactionally **with** the load — watermark stored in a separate system
  updated after the write is a classic double/miss bug.

```sql
-- BAD: watermark = "whatever was max at read time", stored elsewhere later.
-- Crash between load and watermark-update => reprocess or skip.

-- GOOD: bounded window, watermark committed with the data
BEGIN;
INSERT INTO staging_orders
  SELECT * FROM src.orders
  WHERE updated_at > (SELECT wm FROM etl.watermarks WHERE tbl='orders')
    AND updated_at <= :new_wm;          -- bounded above: deterministic
UPDATE etl.watermarks SET wm = :new_wm WHERE tbl='orders';
COMMIT;
```
- `updated_at` maintained by application code is unreliable (bulk fixes skip
  it, clock skew). Prefer DB-generated change tracking or CDC (rules/03)
  for correctness-critical syncs; periodically reconcile row counts against
  the source either way.
- **Late data policy is explicit per table:** how late is accepted (e.g.
  reprocess partitions up to 7 days back via merge), and what happens after
  (corrections batch, or documented "closed" partitions).
- In dbt: incremental models must define `unique_key` (or use
  insert-overwrite strategy) and handle the full-refresh path; an
  incremental model that appends without a key is the same CRITICAL as
  above.

## Backfill design

Backfills are a feature you design, not an emergency you improvise.

- Every pipeline is runnable for an **arbitrary historical interval** with
  the same code path as the scheduled run (this falls out of logical-time
  parameterization).
- **Bound parallelism.** Backfilling 3 years of daily partitions must not
  fire 1,000 concurrent tasks at the source DB or warehouse. Cap concurrency
  (e.g. Airflow `max_active_runs`/pools) and consider chunked
  ranges (monthly chunks of daily logic) for efficiency.
- Order matters when models depend on prior intervals (cumulative tables,
  SCDs): declare `depends_on_past`-style constraints; otherwise allow
  parallel intervals.
- Quality checks run on backfilled partitions too — a backfill that bypasses
  checks is how bad history gets written.
- **AUDIT:** Ask "how would you reload March?" If the answer involves editing
  code or manual SQL, that's a HIGH finding.

## Orchestration discipline

Applies to Airflow (3.x as of 2026), Dagster, Prefect, and kin.

- **The DAG declares ALL dependencies.** Hidden coupling — task B reads a
  table task A writes, but no edge exists and B just runs "later by cron" —
  is a HIGH finding (cron-spaghetti). Prefer dataset/asset-aware scheduling
  (Airflow assets, Dagster assets): downstream runs *because* upstream
  produced, not because it's 6am. Partitioned assets (Airflow 3.2+) extend
  this per logical interval — downstream triggers per partition, unifying
  asset scheduling with the logical-time rule above.

```python
# BAD: coupled by cron offsets and hope
ingest_dag  = DAG("ingest",  schedule="0 5 * * *")
mart_dag    = DAG("marts",   schedule="0 6 * * *")  # "ingest is done by 6, right?"

# GOOD: data-aware — marts run when the asset is actually updated
orders_stg = Asset("warehouse://staging/orders")
ingest_dag = DAG("ingest", schedule="0 5 * * *")    # producer task outlets=[orders_stg]
mart_dag   = DAG("marts",  schedule=[orders_stg])   # consumer triggered by production
```
- **Tasks are stateless and idempotent**; orchestrator metadata is not a data
  store. No XCom-ing dataframes; pass references (paths, table names,
  intervals).
- **Retries: limited and meaningful.** 2–3 retries with exponential backoff
  for transient failures. Retrying a deterministic transform error 10 times
  is noise; retrying forever masks outages. Alert on final failure, always.
- **Timeouts on every task** sized from observed duration; a hung extraction
  blocking the daily run for 12 silent hours is an availability incident.
- **SLAs/freshness alerts on outcomes, not just task failure:** "mart X not
  updated by 7am" pages someone even if no task technically failed (e.g.
  scheduler outage, upstream never triggered).
- **No logic in the orchestrator that belongs in the transform.** Operators
  orchestrate; SQL/code transforms. Business logic hidden in DAG Python is
  untestable and invisible to lineage.
- **Dependency-aware vs event-driven scheduling:** time-based schedules suit
  source-pull batch; asset/event-driven suits multi-team chains (no
  guessed cron offsets) and arrival-driven loads (file lands → run).
  Event-driven chains still need end-to-end freshness SLAs, since "nothing
  triggered" looks like success.

## dbt-style transformation discipline

(Applies to dbt and equivalents — SQLMesh, etc. dbt note: the Fusion engine is
in preview (preparing for GA) and dbt Core 2.0, built on the Fusion foundation,
is in alpha as of mid-2026; don't hard-require Fusion-only features yet.)

- **Tests on every model that matters:** at minimum `unique` + `not_null` on
  the primary key of every core/mart model, relationship tests on critical
  FKs, accepted-values on enums. Severity-tier them (rules/04).
- **No `SELECT *` crossing model boundaries** (staging enumerates; marts
  list final columns). `*` within a CTE chain of one model is fine.
- **Documentation is part of the model:** description + column docs on all
  mart models; exposures declared for dashboards/consumers so impact
  analysis works.
- One model = one purpose; no 1,500-line mega-models. Use intermediate
  models; materialize hot paths as tables, cheap glue as views/ephemeral.
- **CI runs build + tests on changed models and their downstream**
  (state/defer-based selection) against a non-prod target before merge.
  rules/06 covers deployment.
- Pin package and adapter versions; reproducible builds.

## Audit checklist

- [ ] Every scheduled write is partition-overwrite, MERGE, or full
      rebuild — zero blind appends outside raw landing?
- [ ] Writes atomic per run (no separately-failable delete-then-insert)?
- [ ] All tasks parameterized by logical interval; no `NOW()`-relative
      filters in scheduled code?
- [ ] Watermarks persisted atomically with the data they describe?
- [ ] Late-data policy documented per incremental table?
- [ ] Backfill: arbitrary interval, same code path, bounded parallelism,
      checks still run?
- [ ] DAG edges match actual data dependencies (no cron-offset coupling)?
- [ ] Retries bounded with backoff; timeouts set; final-failure alerts
      routed to an owner?
- [ ] Freshness/SLA alerting on outputs, independent of task success?
- [ ] dbt (or equivalent): key tests on every core/mart model, no
      cross-model `SELECT *`, exposures declared, CI builds changed models
      before merge?

# 03 — Streaming & CDC

Streaming is the most expensive way to move data. It buys latency and pays
in operational complexity, harder testing, and harder reprocessing. Make it
earn its place.

## When streaming is justified

- **Require a named consumer with a sub-minute (or low-minutes) latency
  need:** fraud/abuse decisions, operational dashboards driving immediate
  action, ML features that decay in minutes, user-facing freshness.
- "The business wants real-time" usually means "the daily batch is too
  slow." Try 15-minute or hourly micro-batch first — same orchestration,
  same idempotent batch semantics, ~10x less operational surface.
- **Streaming is also legitimate as transport regardless of latency:** CDC
  replication (below) and high-volume event ingestion where a durable log
  beats files. Transport-streaming + batch-transform is a sound and common
  shape.
- **AUDIT:** A Flink/Kafka Streams job whose only consumer is an hourly
  dashboard = MEDIUM (cost/complexity without benefit). No documented
  latency requirement for a streaming component = ask; absence of any
  consumer needing it = finding.

## Kafka-style log fundamentals

(Kafka 4.x is current — ZooKeeper is gone, KRaft-only; the KIP-848 consumer
rebalance protocol is GA in 4.0+ and consumers opt in via
`group.protocol=consumer`. Same fundamentals apply to Pulsar/Kinesis/Redpanda.)

- **Ordering exists only within a partition.** Choose the partition key as
  the entity whose event order matters (`order_id`, `user_id`,
  `aggregate_id`). Random/round-robin keys on a topic where consumers assume
  per-entity order is a CRITICAL correctness finding.
- **Key skew:** one hot key = one hot partition = one maxed consumer no
  matter how many instances you add. Check partition-level lag/throughput
  for skew before scaling out.
- **Partition count is concurrency ceiling** for a classic consumer group
  (one consumer per partition max). Size with headroom (you can add
  partitions but that **reshuffles key→partition mapping**, breaking
  per-key ordering across the boundary — plan it, don't improvise it).
  KIP-932 "share groups" (queue semantics, more consumers than partitions,
  per-record acks) are production-ready since Kafka 4.2 but sacrifice
  per-key ordering — only for true queue workloads.
- **Consumer groups & offsets:** commit offsets only after the message's
  effects are durably stored. Auto-commit-on-poll means a crash between
  commit and processing **loses data**; commit-after-process means
  duplicates on crash — which is why sinks must be idempotent (below).
- **Rebalancing pitfalls:** long processing between polls (`max.poll.interval.ms`
  exceeded) gets consumers evicted, causing rebalance storms; slow
  startup + eager rebalance causes stop-the-world pauses. Use cooperative/
  KIP-848 protocols, keep per-poll work bounded, and process slow items
  async with pause/resume rather than blocking poll.
- **Retention is a contract:** consumers must be able to be down for less
  than retention and recover. Lag monitoring with alerts on every production
  group is mandatory; lag approaching retention = imminent data loss.

## Exactly-once reality

"Exactly-once" end-to-end is achieved as **effectively-once: at-least-once
delivery + idempotent application of effects.** Audit the sink, not the
producer flag.

- **Idempotent producer** (`enable.idempotence=true`, default in modern
  clients): prevents broker-side duplicates from producer retries. Necessary,
  nowhere near sufficient.
- **Kafka transactions / EOS:** give atomic consume-transform-produce
  *within Kafka* (Kafka→Kafka pipelines, Streams `processing.guarantee=
  exactly_once_v2`). The moment data leaves Kafka for a warehouse, lake, or
  API, transactions stop covering it.
- **At the sink, make redelivery harmless:**
  - MERGE/upsert on a natural or deterministic key (event ID,
    `topic-partition-offset`).
  - Or atomic write+offset: store consumed offsets in the same transaction
    as the data (e.g. in the target DB), resume from stored offsets.
  - Or rely on the connector's documented mechanism (e.g. table-format sinks
    committing offsets inside the table commit) — verify, don't assume.

```python
# BAD: duplicates on every crash between write and commit
for msg in consumer:
    warehouse.insert("events", msg.value)      # append
    consumer.commit()                          # separate failure domain

# GOOD: redelivery-proof — offset stored atomically with the data
for batch in consumer.batches():
    with target_db.transaction() as tx:
        tx.merge("events", batch.rows, key="event_id")     # idempotent
        tx.upsert("kafka_offsets", batch.tp, batch.last_offset)
# on startup: seek(stored_offset + 1); Kafka's committed offset is advisory
```
- **AUDIT:** Consumer writes appends to a warehouse table and commits Kafka
  offsets separately = duplicates on every crash/rebalance = CRITICAL if the
  table feeds metrics. "We have exactly-once because idempotent producer is
  on" = misunderstanding; check the sink.

## Schema registry & evolution

- **Every production topic has a registered schema** (Avro/Protobuf/JSON
  Schema). Schemaless JSON topics shared across teams = HIGH; every consumer
  is one producer refactor away from breaking.
- **Pick and enforce a compatibility mode.** `BACKWARD` (new schema reads
  old data: add optional fields, delete fields) lets consumers upgrade
  after producers — the common default. `FORWARD` = consumers first.
  `FULL` for long-lived shared topics. Never `NONE` in prod.
- Breaking changes (rename, retype, semantic change) = **new topic** (or
  contract-versioned subject) + migration window, not an in-place break.
- Defaults on new fields; never reuse field IDs/positions (Protobuf/Avro).

## CDC patterns

- **Log-based CDC (Debezium-class, 3.x current) is the default** for
  replicating OLTP into the analytical platform: reads the WAL/binlog,
  emits ordered change events, near-zero source impact. Query-based
  ("`SELECT WHERE updated_at >`") misses deletes and intermediate states —
  acceptable only for append-only sources.
- **Snapshot + stream:** initial consistent snapshot, then stream from the
  log position captured at snapshot start. Re-snapshot procedure must exist
  (incremental snapshots) for adding tables or recovering from gaps.
- **Deletes & tombstones:** CDC delete events must be **applied** downstream
  (merge-delete or soft-delete flag), and Kafka tombstones (null payloads on
  compacted topics) must be handled by every consumer. A lake table fed by
  CDC where deletes are dropped silently diverges from source = CRITICAL.
- **Apply layer:** CDC streams are change *logs*; downstream either stores
  the log (append, then dedupe/window to current state in SQL) or maintains
  a mirror via MERGE keyed on PK ordered by LSN/commit timestamp. Out-of-order
  application (e.g. merging on wall-clock `updated_at` with ties) silently
  resurrects deleted/stale rows.

```sql
-- GOOD: latest-state mirror from a CDC change log, deletes applied,
-- ordered by log position (LSN), not wall-clock
MERGE INTO mirror.customers t
USING (
  SELECT * FROM cdc.customers_changes
  QUALIFY row_number() OVER (PARTITION BY id ORDER BY source_lsn DESC) = 1
) s ON t.id = s.id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED AND s.op != 'd' THEN INSERT ...;
```
- **Outbox pattern** for application-emitted events (avoiding dual-write
  inconsistency) is owned by `sota-architecture` — use it; don't tail
  business tables to fake events.
- Schema changes on source tables flow through CDC: test ADD COLUMN and
  type-widening paths; alert on incompatible DDL rather than silently
  dropping fields.

## Watermarks, windowing, late data

- **Event time, not processing time,** for any business aggregation.
  Processing-time windows shift numbers whenever the pipeline lags.
- A **watermark** declares "events older than T are no longer expected" and
  triggers window emission. Set allowed lateness from measured event-delay
  distributions (p99/p999), not guesses.
- Decide explicitly what happens to later-than-watermark events: drop +
  count (alert on the count), side-output to a corrections path, or
  re-emit updated window results (consumers must then handle retractions/
  upserts). Silent drop with no metric = HIGH.
- Window types: tumbling for periodic aggregates, sliding for rolling
  metrics, session for activity grouping. State for long/huge windows needs
  TTLs — unbounded keyed state is a slow OOM.

## DLQ & poison messages

- **Every streaming consumer has a poison-message policy.** Default: retry
  N times (with backoff), then route the message + error metadata +
  original topic/partition/offset to a DLQ topic, and continue. A consumer
  that crash-loops on one bad message takes the whole partition hostage =
  HIGH.
- DLQs are monitored (alert on arrival rate) and have a replay path back to
  the source topic after fix. An unmonitored DLQ is a data black hole —
  MEDIUM.
- Never DLQ-and-forget messages whose absence corrupts aggregates; for
  those, halt and page instead (block-tier, by analogy with rules/04).

## Backpressure

Consumers must degrade by slowing intake (pause/resume, bounded buffers),
not by buffering unboundedly in memory. Mechanics and patterns are owned by
`sota-async-concurrency`; in Kafka terms: bound in-flight work, watch lag as
the system-level backpressure signal, scale consumers before lag approaches
retention.

## Audit checklist

- [ ] Each streaming component has a named consumer/latency requirement, or
      is justified as transport?
- [ ] Partition keys match the entity whose ordering matters; skew checked?
- [ ] Offsets committed only after effects are durable; auto-commit
      semantics understood?
- [ ] Sinks idempotent (merge key / transactional offsets) — exactly-once
      claims verified at the sink?
- [ ] Consumer lag monitored with alerts on all prod groups; retention >
      max tolerable downtime?
- [ ] Schemas registered with enforced compatibility mode; breaking changes
      via new topic/version?
- [ ] CDC: log-based for sources with deletes; deletes/tombstones applied
      downstream; ordering by LSN not wall-clock; re-snapshot path exists?
- [ ] Aggregations on event time with explicit watermark/lateness policy;
      late-drop counted and alerted?
- [ ] DLQ per consumer, monitored, with replay procedure; no crash-looping
      on poison messages?
- [ ] Keyed state has TTLs; consumer memory bounded under lag?

# 04 — Data Quality & Contracts

Bad data that flows is worse than no data: dashboards stay green, decisions
get made, and trust — the only real product of a data platform — erodes.
Quality is enforced at boundaries and tiered by consequence.

## Data contracts at the producer boundary

A contract is **schema + semantics + SLOs**, owned by the producer, enforced
mechanically. Schema alone is half a contract.

- **Schema:** field names, types, nullability, enums — registered and
  compatibility-checked (registry for streams, declared source schemas or
  dbt model contracts for batch).
- **Semantics:** grain ("one row per order line"), key uniqueness, units and
  timezone (`amount` in cents? `created_at` in UTC?), enum meanings,
  delete behavior (hard/soft/tombstone).
- **SLOs:** freshness ("available by 06:00 UTC", "p99 event lag < 5 min"),
  completeness, and a deprecation policy (notice period for breaking
  changes).
- **Enforcement beats documentation:** CI on the producer side fails builds
  that break the contract (schema registry compat check, dbt `contract:
  enforced`, contract-test suites). A contract in a wiki nobody's CI reads =
  LOW-value; consumers breaking on producer deploys despite a "contract" =
  HIGH.
- Contracts go where ownership changes hands: source→platform and
  mart→consumer. Don't contract every internal model — that's change
  friction without protection.

```yaml
# GOOD: dbt model contract on a mart boundary
models:
  - name: fct_orders
    config:
      contract: {enforced: true}
    columns:
      - name: order_line_id
        data_type: bigint
        constraints: [{type: not_null}]
        tests: [unique]
      - name: amount_usd_cents   # unit in the name beats unit in the wiki
        data_type: bigint
```

## Expectation testing — the core battery

Every core/mart model carries checks from this battery; critical ones carry
all that apply.

- **Freshness:** `max(loaded_at)` within SLO. The single highest-value
  check — most data incidents are "it silently stopped."
- **Volume:** row count of the latest interval within expected band
  (absolute bounds or relative to trailing window, e.g. ±50% of 7-day
  median; use week-over-week comparisons for weekly-seasonal data).
  Zero-row intervals almost always block.
- **Nulls:** `not_null` on keys and critical measures. Watch *null-rate
  jumps* on optional columns too — a field going 2%→40% null is an upstream
  break that `not_null` won't catch.
- **Uniqueness:** primary/grain key unique. Duplicate grain = every
  downstream aggregate inflated = block-tier, always.
- **Referential integrity:** facts' FKs resolve to dimensions (or to an
  explicit `unknown` member). Orphaned facts silently vanish from
  inner-join dashboards.
- **Accepted values / ranges:** enums in the known set; amounts within sane
  bounds; dates not in the future (a `2106-02-07` timestamp is an epoch bug,
  not a fact).
- **Distribution drift:** track means/quantiles/category mix on
  business-critical columns; alert on significant shift. Warn-tier — drift
  is a question, not a verdict.

```yaml
# GOOD: minimum battery on a critical mart, tiered (dbt syntax; Soda/GX
# equivalents exist for all of these)
models:
  - name: fct_orders
    tests:
      - dbt_utils.recency:            # freshness — blocks
          datepart: hour, field: loaded_at, interval: 26
      - dbt_utils.expression_is_true: # volume — warns, human triages
          expression: >
            (SELECT count(*) FROM {{ this }} WHERE order_date = current_date - 1)
            BETWEEN 0.5 * {{ var("orders_daily_median") }}
                AND 2.0 * {{ var("orders_daily_median") }}
          config: {severity: warn}
    columns:
      - name: order_line_id
        tests: [unique, not_null]     # grain — blocks, always
      - name: customer_id
        tests:
          - relationships: {to: ref('dim_customer'), field: customer_id}
```

## Severity tiers: block vs warn

Two failure modes to avoid: everything blocks (one flaky check halts the
platform nightly, team starts ignoring red) and nothing blocks (corrupt data
flows downstream while a Slack channel nobody reads fills up).

- **Block (fail task, stop downstream):** contract violations, duplicate
  keys, null keys, zero-row loads, broken referential integrity on critical
  marts. Criterion: *would you rather show stale data than this data?* If
  yes → block. Stale + alert beats wrong + silent.
- **Warn (alert, let flow):** drift, moderate volume anomalies, null-rate
  shifts on non-key columns, slow-burn issues.
- Every warn goes to an **owned** channel with a triage expectation. A
  warn-tier check failing for 3+ weeks untriaged = the check is dead;
  fix it or delete it (MEDIUM finding).
- Implement tiers natively: dbt test `severity: error|warn`, Great
  Expectations / Soda / pandera equivalents all support it.

## Anomaly detection caution

ML-based anomaly detection on data metrics (auto-thresholds on volume,
freshness, distributions) is a **supplement, not a substitute** for explicit
checks.

- It cannot block (too many false positives) and it can't encode semantics
  ("orders must never be negative").
- Untuned anomaly monitors produce alert fatigue that kills the *real*
  alerts. Budget alerts-per-week per owner; tune or cut anything over.
- Right order: explicit battery on what you know must hold → anomaly
  detection for the unknown-unknowns on a *small* set of business-critical
  tables.

## Lineage

- Table-level lineage is table stakes — it falls out of dbt/orchestrator
  graphs. Use it for impact analysis ("what breaks if I change this?") and
  incident blast-radius.
- **Column-level lineage pays where:** PII propagation must be traced
  (which marts contain `email`? — see `sota-privacy-compliance`),
  regulated-metric provenance, and deprecating wide legacy tables. Don't
  buy/maintain column-level everywhere for its own sake.
- Lineage that requires manual upkeep will rot; derive it from code
  (parsed SQL, orchestrator metadata) or don't claim it.

## Data incident response

When bad data ships, the order is: **stop the spread → communicate →
quarantine → fix → reprocess → verify → postmortem.**

1. **Stop the spread:** pause downstream pipelines/reverse-ETL consuming the
   bad data. Blocking checks should have done this; do it manually if not.
2. **Communicate first, fix second:** notify downstream owners and stamp
   affected dashboards ("data since DATE under investigation") *before*
   debugging. Consumers acting on known-bad data is the real damage.
3. **Quarantine:** snapshot/copy bad partitions for forensics; with
   Iceberg/Delta, note the pre-incident snapshot ID — time travel is your
   forensic record (until retention expires it; act fast, see rules/05).
4. **Fix and reprocess** through the normal backfill path (rules/02) —
   never hand-`UPDATE` a mart; if raw is intact, re-derive. This is where
   idempotency pays its rent.
5. **Verify** with the same checks that should have caught it — and add the
   check that was missing.
6. **Postmortem** for trust-damaging incidents: which boundary lacked which
   check; fix the class, not the instance.
- **AUDIT:** No documented incident path / no way to mark data as
  known-bad to consumers = MEDIUM; becomes HIGH for platforms feeding
  financial or operational decisions.

## Testing pipelines (CI)

Quality checks validate *data in production*; tests validate *logic before
merge*. You need both — a check can't catch a bug that produces plausible
numbers.

- **Unit-test transforms with fixture data:** tiny handcrafted inputs +
  expected outputs, covering edge cases (nulls, duplicates, late rows,
  timezone boundaries, empty input). dbt unit tests (1.8+), pytest +
  DuckDB/Polars for Python transforms, or framework-native harnesses for
  streaming (e.g. topology test drivers).

```yaml
# GOOD: dbt unit test — logic verified pre-merge, no warehouse data needed
unit_tests:
  - name: dedupe_keeps_latest_version
    model: stg_orders
    given:
      - input: source('shop', 'orders')
        rows:
          - {order_id: 1, status: "open",   _loaded_at: "2026-01-01"}
          - {order_id: 1, status: "closed", _loaded_at: "2026-01-02"}
    expect:
      rows:
        - {order_id: 1, order_status: "closed"}
```
- **DuckDB as the CI workhorse:** runs most ANSI-ish SQL in-process — fast
  warehouse-free tests for portable SQL. Dialect-specific SQL still needs a
  dev schema in the real engine (cheap, ephemeral, per-PR).
- **CI on sample data:** build changed models + downstream against
  representative samples; full-volume runs are for staging, not per-PR.
- Pure functions are testable functions: keep transforms free of hidden
  `NOW()`/env reads (rules/02's logical-time rule makes this automatic).
- **AUDIT:** A transform repo with zero tests where a one-character SQL
  change can silently flip a company metric = HIGH.

## Audit checklist

- [ ] Producer-boundary contracts exist (schema + semantics + SLOs) and are
      CI-enforced, not wiki-only?
- [ ] Every core/mart model: freshness, volume, unique-key, not-null-key
      checks at minimum?
- [ ] Checks explicitly tiered; block-tier actually stops downstream
      (not just alerts)?
- [ ] Zero-row and duplicate-key conditions block on critical marts?
- [ ] Warn alerts owned and triaged — no weeks-old ignored failures?
- [ ] Referential integrity checked between facts and dimensions?
- [ ] Anomaly detection (if any) supplements explicit checks and isn't an
      alert-fatigue source?
- [ ] Lineage derivable from code; column-level where PII/regulatory needs
      it?
- [ ] Incident path documented: pause downstream, notify, quarantine,
      reprocess via backfill, verify?
- [ ] Transform logic unit-tested with fixtures; CI builds changed models
      pre-merge?

# 05 — Storage & Performance

Analytical performance is mostly **scan avoidance**: read fewer files, fewer
row groups, fewer columns, fewer bytes. Every rule here is a variation on
that theme, and every one is also a cost lever.

## Parquet internals that matter

- **Row groups are the pruning unit.** Each carries min/max stats per
  column; engines skip row groups whose stats exclude the predicate
  (predicate pushdown). This only works if data is **sorted/clustered** on
  the filtered column — random layout makes every min/max range span
  everything, and pruning dies. Target row groups ~128 MB-ish (engine
  defaults are sane; pathological cases are tiny row groups from trickle
  writes).
- **Columnar projection:** only referenced columns are read. `SELECT *` in
  pipelines defeats the format's core advantage (and rules/01 bans it across
  layers anyway).
- **Dictionary encoding + page stats** make low-cardinality string filters
  cheap; sorted data compresses dramatically better (run-length/delta).
  Sorting is simultaneously a performance and a storage-cost move.
- **File sizing: target ~128 MB–1 GB per file.** Two failure modes:
  - *Small files* (the classic killer): thousands of KB-sized files from
    streaming/micro-batch/per-key writes turn metadata listing and open
    overhead into the dominant cost. Symptom: query time flat as data
    shrinks. Fix: compaction (below) and buffered/batched writes.
  - Giant single files limit read parallelism.
- **AUDIT:** `ls` a few partitions. Median file size under ~10 MB on a
  frequently-queried table = MEDIUM, HIGH at scale. CSV/JSON as the *query*
  layer (not just landing) = MEDIUM — convert to Parquet at staging.

## Partitioning vs clustering/sorting

- **Partitioning** (directory/metadata-level split, one value-set per
  partition): for the **coarse, always-filtered** dimension — almost always
  event date. Rules:
  - Partition count sanity: aim for partitions ≥ ~1 GB. Don't partition
    small tables at all.
  - **Never partition on high-cardinality columns** (`user_id`,
    `order_id`) — that's the small-files problem by construction =
    HIGH finding.
  - Iceberg uses *hidden* partitioning via transforms (`days(ts)`,
    `bucket(N, id)`) — queries on the raw column prune automatically, and
    partition schemes can evolve without rewriting old data.
- **Clustering/sorting within partitions:** for the second-tier filter
  columns (`customer_id`, `country`). Sort on write (`ORDER BY` in CTAS,
  Iceberg sort orders) or use the platform's clustering (Delta liquid
  clustering, warehouse cluster keys) so min/max pruning works inside
  partitions.
- Rule of thumb: **partition on one time column; sort/cluster on 1–3 query
  columns; stop.** Multi-level partitioning (`date/country/category`) is
  usually a small-files generator.

```sql
-- BAD: high-cardinality partitioning → millions of tiny files
CREATE TABLE events PARTITIONED BY (user_id) ...;

-- GOOD (Iceberg): coarse hidden time partition + bucket + sort
CREATE TABLE events (event_ts timestamp, user_id bigint, ...)
PARTITIONED BY (days(event_ts), bucket(32, user_id));
ALTER TABLE events WRITE ORDERED BY (user_id);
```

## Table formats: Iceberg / Delta

Open table formats add ACID commits, snapshots, and schema evolution on
object storage. Status (verified mid-2026): **Iceberg format v3** is ratified
and GA across major engines (deletion vectors, row lineage; engine support
for v3 features still varies — confirm your engines before enabling v3
features). **Delta Lake 4.x** is current (variant type, collations; deletion
vectors and liquid clustering mature from 3.x).

- **Snapshots & time travel:** every commit is a snapshot; you can query
  `AS OF` for debugging, incident forensics (rules/04), and WAP (rules/06).
  Time travel is bounded by snapshot retention — it is **not a backup
  strategy**.
- **Schema evolution:** safe in-place: add columns, widen types, rename
  (Iceberg tracks by field ID; renames are metadata-only). Still forbidden
  by *your* contracts without coordination (rules/04): downstream code keys
  on names.
- **Row-level changes:** merge-on-read (delete files/deletion vectors) makes
  MERGE/DELETE cheap at write time but accumulates read-time debt;
  copy-on-write is the reverse. CDC-heavy tables on merge-on-read **require**
  regular compaction of delete files or reads degrade steadily.
- **Maintenance is mandatory, scheduled, from day one:**
  - *Data compaction* (Iceberg `rewrite_data_files`, Delta `OPTIMIZE`):
    fixes small files and applies sort orders.
  - *Snapshot expiry* (`expire_snapshots` / `VACUUM`): unexpired snapshots
    = unbounded storage growth; retention window = your time-travel and
    concurrent-reader safety window (don't vacuum to zero).
  - *Manifest/metadata cleanup + orphan file removal* on a slower cadence.
  - **AUDIT:** An Iceberg/Delta table with no scheduled maintenance job =
    MEDIUM, HIGH for streaming/CDC-fed tables (they degrade fastest).

```sql
-- GOOD: scheduled weekly maintenance (Iceberg/Spark procedures)
CALL catalog.system.rewrite_data_files(
  table => 'db.events', strategy => 'sort',
  options => map('target-file-size-bytes', '536870912'));
CALL catalog.system.expire_snapshots(
  table => 'db.events', older_than => now() - INTERVAL 7 DAYS,
  retain_last => 20);
CALL catalog.system.remove_orphan_files(table => 'db.events');

-- Delta equivalents: OPTIMIZE events; VACUUM events RETAIN 168 HOURS;
```
- Concurrent writers: optimistic concurrency means conflicting commits
  retry; partition-overlapping concurrent writes (e.g. backfill + scheduled
  run on the same partitions) need coordination, not hope.

## Compression

- **zstd is the modern default** for Parquet (better ratio than snappy at
  similar read speed; level ~3 is the sweet spot — high levels buy little
  for analytics and cost write CPU). snappy remains fine; gzip is
  legacy-compat only; uncompressed is never right.
- Biggest compression lever is **sorting**, not codec choice (see above).
- Don't double-compress (gzip-ing Parquet files) and don't ship
  `.csv.gz` as a query layer — gzip CSV isn't splittable.

## Cost levers (warehouse credit burn & lake scan cost)

Cost in modern platforms ≈ bytes scanned × frequency + compute time ×
concurrency. Attack in this order:

1. **Prune more:** verify top queries actually hit partition/cluster
   pruning (`EXPLAIN` / query profile: partitions scanned vs total). A
   dashboard filter that wraps the partition column in a function can
   disable pruning — keep predicates sargable (see `sota-databases`).

   ```sql
   -- BAD: function over the partition column — full scan on many engines
   WHERE date(event_ts) = '2026-06-01'
   -- GOOD: range predicate on the raw column — prunes
   WHERE event_ts >= '2026-06-01' AND event_ts < '2026-06-02'
   ```
2. **Scan less per query:** incremental models instead of full rebuilds
   (rules/02); pre-aggregate hot dashboard queries into small marts;
   column pruning.
3. **Run less often:** match schedule to consumer need. An hourly rebuild
   feeding a daily-reviewed dashboard burns 24x the spend for zero value =
   classic MEDIUM finding.
4. **Materialization tradeoffs:** table for hot/expensive paths, view for
   cheap glue, incremental for large facts. A view chain 6 deep recomputed
   by every dashboard query is a hidden multiplier; conversely,
   materializing everything pays storage + build time for unread tables.
5. **Right-size and auto-suspend compute:** warehouses sized for the p99
   job running 24/7 for p50 work; aggressive auto-suspend; separate
   ETL/BI/ad-hoc compute pools so one team's scan storm doesn't queue
   everyone (and so cost is attributable per team).
6. **Watch the spend:** per-pipeline/per-team cost attribution and alerts
   on week-over-week jumps. Unattributed warehouse spend grows until
   someone panics.
- **AUDIT (quick wins, in order):** full-refresh models on large sources;
  queries scanning all partitions; small-files tables; oversized always-on
  compute; orphaned pipelines feeding dashboards nobody opens (check BI
  view counts — deleting a pipeline is the best optimization).

## Audit checklist

- [ ] Query layer is columnar (Parquet/native), not CSV/JSON?
- [ ] File sizes healthy (~128 MB–1 GB median); no small-files
      accumulation on hot tables?
- [ ] Partitioned on a coarse time column only; no high-cardinality or
      deep multi-level partitioning?
- [ ] Sort/cluster order defined matching top query predicates; pruning
      verified via query profiles?
- [ ] Iceberg/Delta: compaction + snapshot expiry + orphan cleanup
      scheduled on every table; CDC-fed merge-on-read tables compacted
      aggressively?
- [ ] Snapshot retention consciously chosen (forensics window vs storage)?
- [ ] Concurrent-writer conflicts (backfill vs scheduled) coordinated?
- [ ] zstd (or justified alternative) everywhere; no double compression?
- [ ] Hot dashboards served from pre-aggregated marts, not view chains over
      raw facts?
- [ ] Schedules match consumer freshness needs; no hourly builds of
      daily-read data?
- [ ] Compute auto-suspends, pools separated by workload, cost attributed
      per pipeline/team with trend alerts?

# 06 — Operations & Governance

A pipeline that works is a prototype; a pipeline that is deployable,
observable, recoverable, and access-controlled is a product. This file
covers the gap.

## Environments for data

Code environments are easy; **data** environments are where platforms cheat.

- **Three-way isolation as the baseline:** dev (engineers iterate), staging/
  CI (automated validation), prod (consumers). Separate
  databases/schemas/buckets *and* separate credentials — dev code holding
  prod write credentials is a CRITICAL finding waiting for a `--target`
  typo.
- **Dev reads (a copy or view of) prod-shaped data, never writes prod.**
  Patterns, best first:
  - *Zero-copy clones / shallow copies* (warehouse clones, table-format
    snapshots): instant, cheap, real data shape.
  - *Sampled subsets* refreshed on schedule: fast dev iteration; document
    the sampling so devs don't "discover" sampling artifacts.
  - *Masked copies* where PII rules require (PII handling itself:
    `sota-privacy-compliance`). Production PII in dev environments = HIGH.
- Synthetic-only dev data is a trap: pipelines pass on clean synthetic rows
  and die on real-world nulls/dupes/encodings. Use sampled-real (masked)
  where at all possible.
- **Schema parity:** dev/staging schemas built by the same code path
  (migrations / dbt) as prod — never hand-maintained copies that drift.
- Per-PR ephemeral schemas (e.g. `pr_1234` in a CI database) beat one
  shared, perpetually-dirty "staging" everyone fights over.

## Deploying pipeline changes

Pipeline code is software: version control, review, CI (rules/02, 04). The
extra dimension is that deploys change *tables others read*.

### Write-Audit-Publish (WAP) — default for risky changes

Never let unvalidated data become visible to consumers.

1. **Write** the new run's output to an unpublished location: a staging
   table, an Iceberg branch (engine support varies — verify yours), or an
   unswapped table version.
2. **Audit:** run the block-tier quality battery (rules/04) against the
   unpublished output — plus diff-vs-current checks for logic changes (row
   counts, key metric deltas within tolerance).
3. **Publish** atomically only on pass: branch fast-forward, partition
   swap, view-pointer flip, or atomic table swap.

Failed audit = nothing published, consumers see last good data + a freshness
note. This converts "we shipped wrong numbers" into "we shipped late."

```sql
-- BAD: write directly to the consumer-facing table, validate afterwards
INSERT OVERWRITE fct_orders PARTITION (ds='2026-06-11') SELECT ...;
-- (checks run later; consumers already saw whatever landed)

-- GOOD: WAP — write unpublished, audit, publish atomically
INSERT OVERWRITE fct_orders__wap PARTITION (ds='2026-06-11') SELECT ...;
-- run block-tier checks against fct_orders__wap; only then:
ALTER TABLE fct_orders REPLACE PARTITION (ds='2026-06-11')
  FROM fct_orders__wap;   -- engine-specific: Iceberg branch fast-forward,
                          -- partition exchange, or atomic view re-point
```

### Blue-green tables for breaking rebuilds

For logic changes that rewrite a whole mart: build `fct_orders__v2`
alongside, validate (full quality battery + reconciliation against v1 with
expected-diff documentation), then repoint the consumer-facing **view** to
v2. Keep v1 queryable through the agreed deprecation window for rollback and
consumer comparison.

- Corollary: **consumers read views, not physical tables.** The
  view layer is your indirection for blue-green, refactors, and renames.
- Rollback plan is part of the deploy: previous table/snapshot retained
  until the change has survived N cycles.
- **AUDIT:** Logic changes that rewrite consumer-facing tables in place
  with no validation gate = HIGH; with no rollback artifact = HIGH.

## Access control

(Mechanics of RBAC/row policies live with the engine — `sota-databases`;
PII classification and lawful basis — `sota-privacy-compliance`. This is the
data-platform layer.)

- **Grant to roles per layer, least privilege:** consumers get read on
  marts only (raw/staging contain unmodeled PII and un-cleaned data);
  pipelines get write only to their layer; humans don't share the service
  account. BI service accounts with `SELECT ANY` on raw = HIGH.
- **Column-level controls** (masking policies, secure views) for PII columns
  that must coexist with analytics — masked by default, unmasked for an
  audited role. **Row-level** policies for multi-tenant or
  regional-restriction marts.
- Tag/classify PII columns in the catalog at ingestion and let policies key
  off tags — per-column manual grants don't survive schema growth.
- Audit logging on access to sensitive marts; review grants on a schedule
  (orphaned humans, over-privileged services).

## Retention & GDPR deletion in immutable-ish stores

"Immutable raw forever" collides with deletion duties. Design for erasure
up front; retrofitting is brutal.

- **Know where every subject's data lives:** PII column tags + lineage
  (rules/04) make "find all copies" answerable. Copies include: raw,
  staging, marts, snapshots/time-travel, DLQs, dev clones, BI extracts.
- **Lakehouse deletion that actually deletes:** `DELETE`/`MERGE` on
  Iceberg/Delta marks rows deleted in the *current* snapshot — the bytes
  live on in old snapshots/files until **snapshot expiry + compaction/
  vacuum** run. The erasure SLA must account for the full chain: delete →
  expire snapshots → rewrite/vacuum files. Deletion-vector/merge-on-read
  tables additionally need compaction to physically drop the rows.
- **Kafka:** compacted topics honor tombstones (eventually — compaction
  lag); time-retention topics age data out. Long/infinite-retention topics
  containing PII need a deletion story or pseudonymization at produce time.
- **Crypto-shredding** (per-subject encryption keys; erase the key to erase
  the data) is the pragmatic answer for deep-archive/backup layers where
  rewriting is infeasible — key management discipline required
  (`sota-secrets-management`).
- Retention policies are **enforced by scheduled jobs, not by policy
  documents**: raw N months, snapshots N days, DLQs N days, dev clones
  auto-expire. Unbounded-retention PII stores = HIGH.
- Deletion requests are pipelines too: idempotent, monitored, with
  completion SLO and verification query.

## Observability for pipelines

(Generic telemetry stack: `sota-observability`. Data-specific layer here.)

- **The four golden signals of data:** freshness (did it land on time?),
  volume (the right amount?), quality (checks green?), lineage-aware status
  (what's blocked downstream?). One dashboard, all critical marts,
  red/green per signal.
- **Alert on consumer-facing symptoms, route to the owning team:** "mart X
  stale > SLO" beats 14 task-level alerts that all mean the same outage.
  Task-level detail belongs in the runbook drill-down, not the page.
- Every alert has an **owner and a runbook link**. Unrouted alerts and
  alert channels with >daily noise = the platform is unmonitored in
  practice (MEDIUM, HIGH for critical marts).
- Track **cost** as a first-class signal (rules/05): per-pipeline spend
  trend on the same dashboard — cost regressions are incidents too.
- SLOs documented per critical mart (freshness target + measurement) and
  reviewed; an SLO nobody measures is decoration.

## Runbook discipline

- Every production pipeline has a runbook answering: what it produces and
  for whom (links to contracts/SLOs); how to rerun/backfill an interval
  (exact command); known failure modes and fixes; upstream/downstream
  contacts; escalation path.
- Runbooks live next to the code (repo `runbooks/` or per-DAG docs), are
  linked from every alert, and are updated as part of incident postmortems
  (rules/04). A runbook last touched two years before the last incident is
  fiction.
- Test the runbook: a new on-call engineer should be able to execute a
  rerun from it without tribal knowledge. If only one person can operate a
  pipeline, that's a HIGH operational finding regardless of code quality.

## Audit checklist

- [ ] Dev/staging/prod separated in storage *and* credentials; dev cannot
      write prod?
- [ ] Dev data is cloned/sampled (masked where PII), refreshed by
      automation; schemas built from the same code as prod?
- [ ] Risky changes gated by WAP or blue-green + reconciliation; consumers
      read through views?
- [ ] Rollback artifact (previous table/snapshot) retained for every
      consumer-facing change?
- [ ] Layer-based grants: no consumer/BI access to raw; no shared human
      use of service accounts?
- [ ] PII columns tagged; masking/row policies keyed off tags; access to
      sensitive marts audit-logged?
- [ ] GDPR erasure path covers snapshots, compaction, DLQs, dev clones,
      and Kafka retention — with a measured SLA?
- [ ] Retention enforced by scheduled jobs for every layer (raw, snapshots,
      DLQ, dev clones)?
- [ ] Freshness/volume/quality dashboard for critical marts; alerts
      symptom-based, owned, runbook-linked, low-noise?
- [ ] Per-pipeline cost visible with trend alerts?
- [ ] Runbooks current, co-located with code, executable by a newcomer?