Fact Table Loading Pattern AI Prompt

Implement a robust fact table loading pattern for {{fact_table}} using a {{loading_strategy}} approach.

1. Pre-load validation:
   - Check source row count vs expected range (alert if < 80% or > 120% of yesterday's count)
   - Verify all required foreign keys exist in their dimension tables (referential integrity check)
   - Check for duplicate natural keys in the incoming batch
   - Validate numeric measure ranges (no negative revenue, no impossible quantities)

2. Surrogate key lookup:
   - Never store natural keys in the fact table — always look up the surrogate key from the dimension
   - For each foreign key: JOIN to dimension on natural key WHERE is_current = TRUE
   - For late-arriving dimensions: look up the surrogate key valid at the event time (point-in-time lookup)
   - Late-arriving facts: if the dimension record did not exist at event time, use a 'unknown' placeholder record (surrogate_key = -1)

3. Incremental load to fact table:
   - Partition by date and load one partition at a time
   - Use INSERT OVERWRITE for the current partition (idempotent, safe to re-run)
   - Never UPDATE rows in a fact table — append or overwrite partitions only

4. Post-load validation:
   - Row count reconciliation: source count = fact table insert count
   - Measure totals reconciliation: SUM(revenue) in source = SUM(revenue) in fact for the loaded date
   - No NULL surrogate keys in the output (all dimension lookups resolved)

5. Audit columns:
   - Add to every fact table: load_timestamp, pipeline_run_id, source_system

Return: pre-load validation queries, surrogate key lookup logic, incremental load SQL, and post-load reconciliation queries.