Funnel

Current state assessment first. Your nightly full-refresh pipeline costs $12/run and takes 4 hours. Here's the incremental migration plan:

Step 1: Identify CDC capability per source

• PostgreSQL: Use logical replication slots (pgoutput) for real-time CDC
• REST APIs: Check for modified_after filter parameter — if available, use incremental pulls with watermark tracking
• S3 file drops: Already incremental by nature — process new files only using file modification timestamps

Step 2: Bronze layer changes

• Switch from mode='overwrite' to mode='append' with mergeSchema=true
• Add metadata columns: _ingested_at, _source_system, _source_file
• Partition by ingestion date for cost-effective historical replay

Step 3: Silver layer dedup

• Use Delta Lake MERGE (upsert) on primary key instead of full overwrite
• Window function dedup: ROW_NUMBER() OVER (PARTITION BY pk ORDER BY _ingested_at DESC)
• Implement SCD Type 2 for slowly changing dimensions

Expected results:

• Runtime: 4 hours → 15 minutes
• Cost: $12/run → $0.40/run (97% reduction)
• Freshness: 24-hour → 15-minute latency
• Zero data loss during migration — run both pipelines in parallel for 2 weeks, then cutover.

This is a data quality enforcement gap. Nulls are propagating because there's no explicit gate between Silver and Gold. Here's the fix:

Immediate fix (today):

1. Add a WHERE customer_id IS NOT NULL filter to your Gold aggregation query
2. Add a Great Expectations check in the Silver layer:

expect_column_values_to_not_be_null(column='customer_id', mostly=0.999)

The mostly=0.999 allows 0.1% nulls (data quality score) but will fail if the null rate exceeds that threshold.

Root cause investigation: The null customer_ids entered at Bronze ingest. Run this diagnostic:

SELECT _source_system, _ingested_at::date, COUNT(*) as null_count
FROM silver_orders WHERE customer_id IS NULL
GROUP BY 1, 2 ORDER BY 3 DESC;

This tells you which source system and when the nulls started appearing. Most likely: an upstream API change dropped the field from certain event types.

Long-term fix:

• Add schema contract validation in dbt with contract: enforced: true
• Set up Slack alerting when null rate on customer_id exceeds 0.1%
• Add row-level data quality score column to Silver: dq_score = CASE WHEN customer_id IS NULL THEN 0.5 ELSE 1.0 END
• Gold layer only reads rows where dq_score >= 0.9