ETL Design

ETL is correctness under change: schema drift, partial loads, retries, and reconciliation with upstream systems.

When to Offer This Workflow

Trigger conditions:

• Batch loads into warehouse or data lake
• Choosing between CDC, snapshots, and incremental watermarks
• Missing rows, duplicates, or inconsistent aggregates downstream

Initial offer:

Use six stages: (1) source contract, (2) extract strategy, (3) transform rules, (4) load & dedupe, (5) validation, (6) operations & backfill). Confirm batch window and SLA.

Stage 1: Source Contract

Goal: Document schema, primary keys, change indicators (updated_at, CDC log position), and access constraints (rate limits, read replicas).

Stage 2: Extract Strategy

Goal: Full dump vs incremental watermark vs CDC—trade freshness, source load, and complexity.

Practices

• CDC for large sources; snapshots for small or infrequent tables

Stage 3: Transform Rules

Goal: Deterministic transforms; surrogate keys; business rules versioned; handling of deletes (tombstones vs hard deletes).

Stage 4: Load & Dedupe

Goal: Upsert keys; partitions; rerunnable jobs with same batch id producing the same outcome (idempotent load).

Stage 5: Validation

Goal: Row counts, checksums, key uniqueness, referential checks; alert on threshold breaches.

Stage 6: Operations & Backfill

Goal: Replay by date range; monitor lag; dead-letter or quarantine bad rows with reason codes.

Final Review Checklist

• Source contract and keys documented
• Extract mode matches SLA and source constraints
• Transforms deterministic and versioned
• Idempotent load strategy
• Validation and reconciliation defined

Tips for Effective Guidance

• Plan for late-arriving facts and slowly changing dimensions in analytics paths.
• Pair with data-pipelines for orchestration and monitoring.

Handling Deviations

• Near-real-time: document micro-batch or streaming semantics separately.