/sota-data-engineering

name: sota-data-engineering
description: >-
  State-of-the-art data engineering rules (2026) for building and auditing
  data pipelines and analytics infrastructure. Covers architecture and
  modeling (ELT, lakehouse vs warehouse, dimensional models, medallion
  layering), pipeline and orchestration discipline (idempotency, incremental
  loads, backfills, dbt-style transformations), streaming and CDC (Kafka,
  exactly-once reality, schema evolution, Debezium-style capture), data
  quality and contracts, columnar storage and table-format performance
  (Parquet, Iceberg, Delta), and pipeline operations/governance. Use when
  designing, implementing, reviewing, or auditing batch/streaming pipelines,
  warehouses, or lakehouses. Trigger keywords: data pipeline, ETL, ELT,
  Kafka, streaming, data warehouse, dbt, Airflow, orchestration, data
  quality, lakehouse, Iceberg, Delta, CDC, Parquet, backfill, watermark,
  Spark, DuckDB, data contract, medallion, dimensional model.

SOTA Data Engineering

Purpose

Expert rules for analytical data systems: pipelines, streaming, warehousing, lakehouse storage, data quality, and operations. OLTP schema/query/index craft is owned by [`sota-databases`](../sota-databases/SKILL.md) — reference it, do not duplicate it. Outbox and event-driven service patterns live in [`sota-architecture`](../sota-architecture/SKILL.md); backpressure mechanics in [`sota-async-concurrency`](../sota-async-concurrency/SKILL.md); PII handling in [`sota-privacy-compliance`](../sota-privacy-compliance/SKILL.md) (or [`sota-code-security`](../sota-code-security/SKILL.md) if absent).

Two modes. Pick by intent, then load only the `rules/` files the task needs.

BUILD mode

Use when designing or implementing pipelines, models, streaming jobs, or storage layouts.

1. **Size the problem first.** Read [`rules/01-architecture-and-modeling.md`](rules/01-architecture-and-modeling.md) before choosing tools. Most "big data" is small data; DuckDB/Polars on one node before a distributed engine. ELT into a warehouse/lakehouse is the default shape, not a decision to revisit per pipeline. 2. **Idempotency is the prime directive.** Every pipeline you write must be safe to rerun for any interval at any time. No blind appends. Design the write strategy (partition overwrite / merge key / insert-overwrite) before the transform logic ([`rules/02-pipelines-and-orchestration.md`](rules/02-pipelines-and-orchestration.md)). 3. **Batch unless a consumer needs sub-minute data.** Justify streaming in writing before building it ([`rules/03-streaming-and-cdc.md`](rules/03-streaming-and-cdc.md)). 4. **Quality checks ship with the pipeline, not after.** Every new model gets freshness, volume, uniqueness, and not-null checks tiered block/warn ([`rules/04-data-quality-and-contracts.md`](rules/04-data-quality-and-contracts.md)). 5. **Decide the physical layout when you create the table.** Partitioning, clustering/sort, file sizing, and the maintenance job are part of the table's definition ([`rules/05-storage-and-performance.md`](rules/05-storage-and-performance.md)). 6. **Ship with operability.** Dev/prod isolation, write-audit-publish for risky changes, freshness alerting, a runbook entry ([`rules/06-operations-and-governance.md`](rules/06-operations-and-governance.md)).

AUDIT mode

**Coverage:** every `rules/` file's Audit checklist applies in AUDIT mode. Mark one *not applicable* only with its reason in the report — never skip a file because the thing it checks for is absent, since that absence may be the finding.

Use when reviewing an existing pipeline repo, dbt project, streaming topology, or warehouse.

Procedure: 1. Inventory: orchestrator + scheduler config, transformation tool (dbt or other), storage/table formats, streaming components, quality tooling, environments. Read the actual DAGs/models — never audit from README claims. 2. Load the rules files matching what exists (no Kafka → skip 03). 3. Verify every finding against real code/config/SQL. Confirm a non-idempotent write by reading the write statement, not by inferring from naming. 4. Report findings in the format below, ordered by severity.

Severity conventions:

• **CRITICAL** — data corruption or silent wrongness: non-idempotent writes

that double-count on retry, reruns that duplicate or lose data, CDC deletes not applied, PII landing in unprotected zones, prod credentials in dev.

• **HIGH** — likely incident or unbounded cost: no failure alerting on

business-critical pipelines, unbounded retries, full-table rescans of large sources each run, no backfill path, schema changes that break consumers.

• **MEDIUM** — erodes trust/efficiency: missing quality checks, `SELECT *`

staging, small-files accumulation, no documentation/lineage, warn-tier checks failing for weeks.

• **LOW** — hygiene: naming inconsistency, missing column descriptions,

suboptimal compression.

Finding format:

[SEVERITY] <one-line title>
Where: <file:line / model / DAG / topic>
Evidence: <the actual code/config/SQL that proves it>
Impact: <what goes wrong, when>
Fix: <concrete change, smallest safe diff>

Rules index

| File | Read this when... | |---|---| | [`rules/01-architecture-and-modeling.md`](rules/01-architecture-and-modeling.md) | Choosing engines/architecture (warehouse vs lakehouse vs DuckDB), designing layers (staging/core/mart), dimensional modeling, SCDs, One Big Table, semantic layers, evaluating a data-mesh pitch. | | [`rules/02-pipelines-and-orchestration.md`](rules/02-pipelines-and-orchestration.md) | Writing or reviewing any batch pipeline: idempotency, incremental loads, watermarks, late data, backfills, Airflow/orchestrator DAG design, dbt project discipline, scheduling strategy. | | [`rules/03-streaming-and-cdc.md`](rules/03-streaming-and-cdc.md) | Anything Kafka/Flink/CDC: deciding streaming vs micro-batch, partition keys, consumer groups, offsets, exactly-once claims,