Row Count Reconciliation AI Prompt

Build a row count reconciliation framework to verify data completeness across pipeline stages.

1. Count capture at each stage:
   - Extract: rows read from source
   - After filter: rows meeting extraction criteria
   - After transformation: rows output from each major transformation step
   - Load: rows written to target
   - Store all counts in a reconciliation metadata table: pipeline_run_id, stage, table_name, row_count, timestamp

2. Reconciliation checks:
   - Source to target: abs(source_count - target_count) / source_count < {{tolerance}} (e.g. 0.001 = 0.1% tolerance)
   - Explain expected differences: deduplication, filtering, and type-specific exclusions
   - For CDC pipelines: verify inserts + updates + deletes = total source changes

3. Historical comparison:
   - Compare today's count to the same day last week (day-of-week adjusted)
   - Alert if count differs by more than 2σ from the rolling 30-day average for that day
   - Hard alert if count is 0 (empty load — almost always a pipeline error)

4. Metadata table DDL:
   ```sql
   CREATE TABLE pipeline_reconciliation (
     run_id VARCHAR,
     pipeline_name VARCHAR,
     stage VARCHAR,
     table_name VARCHAR,
     expected_count BIGINT,
     actual_count BIGINT,
     variance_pct DECIMAL(10,4),
     status VARCHAR, -- PASS / WARN / FAIL
     run_timestamp TIMESTAMP
   )
   ```

5. Alerting:
   - FAIL: variance > {{fail_threshold}}% → block downstream, page on-call
   - WARN: variance > {{warn_threshold}}% → log warning, notify data team
   - PASS: variance within tolerance → log and continue

Return: reconciliation metadata table DDL, count capture code, comparison queries, and alerting logic.