PR Data Diffs

name: data-diff-checker
description: Compare data output for changed models

triggers:
  - type: pull_request
    on_changed_files: "models/**/*.sql"

tools:
  preset: standard

prompt: |
  For each changed model:
  1. Run the model on PR branch
  2. Compare with production version
  3. Report row count changes and sample differences
  4. Comment on PR with findings

name: data-diff-checker
description: Comprehensive data comparison for changed dbt models

triggers:
  - type: pull_request
    events: [opened, synchronize]
    paths:
      include: ["models/**/*.sql"]
    conditions:
      - type: pr_labels
        none_of: ["skip-diff"]

tools:
  preset: standard

restrictions:
  sql:
    read_only: false  # Need to create temp tables for comparison
    allowed_schemas: ["raw", "staging", "marts", "dbt_pr_build"]
    max_query_time: 600  # 10 min for large comparisons
  git_operations:
    can_commit: false  # Read-only, just comment

notifications:
  slack:
    channel: "#data-quality"
    on_failure: true

prompt: |
  ## 1. Identify models to compare
  
  For each SQL file changed in this PR:
  
  a) Skip if:
     - Model is brand new (no prod version to compare)
     - Changes are only to comments or whitespace
     - PR has "skip-diff" label
     - Model is in seeds/ or snapshots/
  
  b) Prioritize by model type:
     - **High priority**: Mart models (fct_, dim_)
     - **Medium priority**: Intermediate models
     - **Low priority**: Staging models (usually 1-1 with source)
  
  c) Check if model is safe to run:
     - No dependencies on unreleased models
     - Not incremental (or can run full-refresh)
     - Not too expensive (check for table size hints)
  
  ## 2. Build PR version
  
  For each model to compare:
  
  a) Check if already built in PR schema:
     ```sql
     SELECT COUNT(*) 
     FROM information_schema.tables 
     WHERE table_schema = 'dbt_pr_build_{pr_number}'
     AND table_name = '{model_name}'

dbt build --select {model_name} --target pr_build --full-refresh

SELECT 
  'production' as version,
  COUNT(*) as row_count
FROM production.{schema}.{model_name}

UNION ALL

SELECT 
  'pr' as version,
  COUNT(*) as row_count
FROM dbt_pr_build_{pr_number}.{schema}.{model_name}

WITH prod_stats AS (
  SELECT 
    '{column}' as column_name,
    COUNT(*) as total_rows,
    COUNT({column}) as non_null_count,
    COUNT(DISTINCT {column}) as distinct_count,
    -- For numerics
    MIN({column}) as min_val,
    MAX({column}) as max_val,
    AVG({column}) as avg_val,
    STDDEV({column}) as stddev_val
  FROM production.{schema}.{model_name}
),
pr_stats AS (
  SELECT 
    '{column}' as column_name,
    COUNT(*) as total_rows,
    COUNT({column}) as non_null_count,
    COUNT(DISTINCT {column}) as distinct_count,
    MIN({column}) as min_val,
    MAX({column}) as max_val,
    AVG({column}) as avg_val,
    STDDEV({column}) as stddev_val
  FROM dbt_pr_build_{pr_number}.{schema}.{model_name}
)
SELECT 
  p.*,
  pr.*
FROM prod_stats p
CROSS JOIN pr_stats pr

-- Rows in PROD but not in PR (deleted)
SELECT 'deleted' as diff_type, p.*
FROM production.{schema}.{model_name} p
LEFT JOIN dbt_pr_build_{pr_number}.{schema}.{model_name} pr
  ON p.{primary_key} = pr.{primary_key}
WHERE pr.{primary_key} IS NULL
LIMIT 100

-- Rows in PR but not in PROD (added)
SELECT 'added' as diff_type, pr.*
FROM dbt_pr_build_{pr_number}.{schema}.{model_name} pr
LEFT JOIN production.{schema}.{model_name} p
  ON pr.{primary_key} = p.{primary_key}
WHERE p.{primary_key} IS NULL
LIMIT 100

-- Rows with changed values
SELECT 
  'modified' as diff_type,
  p.{primary_key},
  -- Show key columns that changed
  p.{column} as prod_value,
  pr.{column} as pr_value
FROM production.{schema}.{model_name} p
JOIN dbt_pr_build_{pr_number}.{schema}.{model_name} pr
  ON p.{primary_key} = pr.{primary_key}
WHERE p.{column} != pr.{column} 
   OR (p.{column} IS NULL AND pr.{column} IS NOT NULL)
   OR (p.{column} IS NOT NULL AND pr.{column} IS NULL)
LIMIT 100

-- Compare revenue totals, for example
SELECT 
  'production' as version,
  SUM(revenue) as total_revenue,
  AVG(revenue) as avg_revenue,
  COUNT(DISTINCT customer_id) as unique_customers
FROM production.{schema}.{model_name}

UNION ALL

SELECT 
  'pr' as version,
  SUM(revenue) as total_revenue,
  AVG(revenue) as avg_revenue,
  COUNT(DISTINCT customer_id) as unique_customers
FROM dbt_pr_build_{pr_number}.{schema}.{model_name}

## 📊 Data Diff Report

Compared **{model_count}** changed models between production and this PR.

---

### ✅ Models with Expected Changes

**`fct_orders`** - Row count increased
- **Production**: 1,245,890 rows
- **PR Branch**: 1,248,103 rows (+2,213 rows, +0.18%)
- **Analysis**: Increase expected from new orders in time window ✓

---

### ⚠️  Models with Unexpected Differences

**`fct_customer_lifetime_value`** - Row count decreased significantly
- **Production**: 45,678 rows
- **PR Branch**: 38,290 rows (-7,388 rows, **-16.2%**)
- **Severity**: HIGH

**Detailed Analysis:**

| Metric | Production | PR | Change |
|--------|------------|----|----|
| Total Customers | 45,678 | 38,290 | -16.2% |
| Avg LTV | $3,245 | $3,892 | +19.9% |
| Total LTV | $148.2M | $149.0M | +0.5% |
| Null LTV Count | 0 | 0 | - |

**Root Cause Investigation:**

Looking at the SQL diff, you added a filter:
```sql
WHERE total_orders >= 2  -- NEW FILTER

## 7. Handle edge cases

- **Large models**: Sample comparison instead of full table (>10M rows)
- **Slow models**: Set timeout, partial results if exceeded
- **Build failures**: Report failure, don't compare
- **No primary key**: Use row hashing for comparison
- **Too many differences**: Provide summary, link to full results
- **Incremental models**: Compare full-refresh version or skip

## 8. Clean up

After comparison:
- Drop temporary tables created for comparison
- Clear PR build schema if not needed
- Log execution time and costs for monitoring