Enterprise ETL Platform and Batch Pipeline Architect

Wrote docs/architecture/etl-architect/00-overview/etl-architect-overview.md and docs/architecture/etl-architect/verification/fitness-self-check.md. Complete enterprise ETL architecture blueprint establishing Airflow DAG orchestration, Spark batch processing, data contract schemas, and automated reconciliation.

---

skill: etl-architect

Enterprise ETL Platform Architecture: Global Retail Batch [ETLARCH-RET-001]

Summary

This specification establishes the enterprise ETL (Extract, Transform, Load) Platform Architecture blueprint, batch processing DAG topologies, data contract schemas, and reconciliation frameworks for global-retail-batch-etl v3.0 under run ID retail-etlarch-001. It governs batch processing pipelines across 480 retail hypermarkets ingesting 18 million daily point-of-sale transactions and generating statutory financial ledger feeds. It decisively investigates and resolves the batch failure and financial restatements demonstrated in incident ETL-4919 (where an un-monitored, cron-scheduled Python ETL script crashed mid-execution during monthly reconciliation, causing partial table updates that went unnoticed for 5 days, resulting in double-counted revenue and an emergency $28M accounting correction). The architecture enforces declarative DAG workflow orchestration via Apache Airflow on Kubernetes, implements idempotent write-audit-publish (WAP) staging patterns on Apache Spark, guarantees

100% data contract validation at extraction seams, and institutes

automated ledger reconciliation gates.

Detailed Description

Operating enterprise ETL systems with fragmented cron jobs, shell scripts, and unversioned SQL queries guarantees operational disaster. When pipelines fail mid-execution without transactional checkpointing, databases are left in half-updated, inconsistent states. Downstream business users make critical financial decisions on corrupted datasets without knowing a failure occurred. Modern ETL Platform Architecture establishes a

governed batch engineering framework: pipelines execute as versioned Directed Acyclic Graphs (DAGs) with explicit dependencies, tasks adhere to Write-Audit-Publish (WAP) patterns (staging data in temporary tables, auditing quality rules, and publishing atomically), extractors enforce strict schema contracts, and financial totals reconcile automatically before feeds release to enterprise ledgers.

Daily Point-of-Sale Transaction Dumps (18 Million Daily Rows)
                               │
                               ▼
[ Extraction Seam: Airflow Managed Ingestion Task ]
  ├── Enforces Source Schema Contract & Checksum Verification
  └── Ingests Raw Data into Isolated Staging Lake Partition
                               │
                               ▼ (Distributed Spark Transformation)
┌─────────────────────────────────────────────────────────────────────────────┐
│ Transformation Engine: Apache Spark on AWS EKS                              │
│   ├── Step 1: Cleanses and Normalizes SKU Codes & Currency Conversions      │
│   ├── Step 2: Writes Transformed Data to Ephemeral Audit Staging Table      │
│   └── Step 3: Executes Great Expectations Automated Data Quality Suite      │
└──────────────────────────────────────┬──────────────────────────────────────┘
                                       │
         ┌─────────────────────────────┴─────────────────────────────┐
         ▼ (Quality Audit: PASSED)                                   ▼ (Audit Fails: ETL-4919 Fix)
[ Atomic Publish & Ledger Release ]                         [ Pipeline Aborted & Quarantined ]
  ├── Swaps Audit Staging into Production Tables (< 1s)       ├── Zero Partial Data Written
  └── Emits Certified Financial Reconciliation Token          └── Diagnostic: `ERR_AUDIT_CHECK_FAILED`

Criteria and weights

Alternatives rejected

Contracts and Invariants

Write-Audit-Publish Atomicity Invariant [INV-ETL-01]
  Batch ETL jobs must write transformed data to an isolated staging area and pass all data quality assertions
  before publishing data to production tables. Direct partial writes to production target tables are strictly barred.

Deterministic Batch Idempotency Invariant [INV-ETL-02]
  Every ETL pipeline execution must be strictly idempotent. Executing a batch job multiple times with the
  identical input partition date must yield the identical state without duplicating records or ledger sums.

Zero Silent Batch Failure Mandate [INV-ETL-03]
  Any unhandled task error, timeout, or quality contract violation must immediately trigger pipeline termination,
  dispatch a Sev-1 alert to Financial Data Operations, and prevent publication of unverified data.

Ownership and Handoffs

Traceability

Verification

No validator was supplied, so no command was run.

Reviewer self-check against enterprise ETL architecture standards:

• Atomicity Rigor: PASS. Write-Audit-Publish staging prevents partial table updates (ETL-4919 closed).
• Idempotency: PASS. Partition overwrite semantics guarantee identical outputs on pipeline re-runs.
• Contract Enforcement: PASS. Ingestion extraction seams validate Avro schemas before processing.
• Markdown Hygiene: PASS. Native Markdown syntax strictly adheres to rule_markdown.md.

Open Decisions

• DEC-ETL-01: Elena Rostova to determine whether automated currency exchange rate adjustments should use European Central Bank or Bloomberg closing fix rates in Q1 (Owner: Elena Rostova).

Next steps

1. Data Platform squad provisions the Apache Airflow cluster on AWS EKS using the official Helm chart.
2. Financial Analytics engineering implements the Spark WAP staging framework in PySpark.
3. Conduct staging resilience drill simulating task failure during Step 2 to confirm zero partial data written to production.

---

skill: etl-architect

Global Retail Batch ETL — Fitness Self-Check [ETLARCH-RET-FIT-001]

Summary

This fitness self-check evaluates the enterprise ETL platform architecture against three critical red-capable domain failure probes: dual writer, undefined grain, and silent schema drift. All targeted probes pass by design construction. A self-check is supporting evidence, never the authoritative gate. Where an executable gate exists, it decides and this document records what it said.

Detailed Description

Residual Risk

• Snowflake warehouse compute queue queuing latency during massive end-of-quarter financial recalculations. Accepted by Elena Rostova with multi-cluster warehouse auto-scaling.

Traceability

Verification

No validator was supplied, so no command was run.

Open Decisions

None.

Next steps

1. Architecture Guild incorporates ETL fitness probes into automated Airflow DAG CI testing.
2. Platform team configures Prometheus alerts monitoring nightly batch completion times against the 4-hour SLA.
3. Conduct quarterly disaster recovery drill validating automated pipeline backfill from raw archive storage.