Data Quality and Anomaly Detection Questions

SELECT MAX(load_time) AS last_load, load_status
FROM marketing_daily_cohort_load_log
WHERE table_name = 'marketing_daily_cohort';

SELECT COUNT(*) AS rows FROM marketing_daily_cohort WHERE cohort_date = CURRENT_DATE - 1;

SELECT
  SUM(CASE WHEN email_hash IS NULL OR cohort_id IS NULL THEN 1 ELSE 0 END) AS null_count,
  COUNT(*) AS total
FROM marketing_daily_cohort
WHERE cohort_date = CURRENT_DATE - 1;

-- count nulls per column in analytics table
SELECT
  count(*) AS total_rows,
  SUM(CASE WHEN col_a IS NULL THEN 1 ELSE 0 END) AS col_a_nulls,
  SUM(CASE WHEN col_b IS NULL THEN 1 ELSE 0 END) AS col_b_nulls
FROM analytics.events;

-- number of dashboard tiles referencing each column (from metadata)
SELECT column_name, COUNT(DISTINCT dashboard_id) AS dashboards
FROM catalog.column_references
WHERE table_name='analytics.events'
GROUP BY column_name;

-- number of dependent models/jobs (e.g., dbt lineage or job deps)
SELECT column_name, COUNT(DISTINCT model_name) AS dependent_models
FROM lineage.column_usage
WHERE table_name='analytics.events'
GROUP BY column_name;

-- recent query volume that filters/aggregates by column
SELECT column_name, SUM(query_count) AS queries_last_30d
FROM metrics.column_query_stats
WHERE table_name='analytics.events' AND ts >= now() - INTERVAL '30 days'
GROUP BY column_name;

-- sample timing for updating 1k rows
EXPLAIN ANALYZE
UPDATE analytics.events
SET col_a = computed_value
WHERE col_a IS NULL
LIMIT 1000;

SELECT
  COUNT(*) AS rows,
  SUM(CASE WHEN important_col IS NULL THEN 1 ELSE 0 END) AS nulls,
  MIN(updated_at), MAX(updated_at)
FROM upstream.table
WHERE date = '2025-12-05';

SELECT dim, SUM(amount) AS total
FROM upstream.table
WHERE date BETWEEN '2025-12-04' AND '2025-12-05'
GROUP BY dim
ORDER BY total DESC;

SELECT * EXCEPT(hash), MD5(TO_JSON_STRING(t)) AS rowhash FROM model_output_prev
EXCEPT
SELECT * EXCEPT(hash), MD5(TO_JSON_STRING(t)) AS rowhash FROM model_output_curr;