Combine integration and quality checks into trusted sales reporting.

CREATE TEMP TABLE integrated_customers AS
WITH crm AS(
  WITH ranked AS (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY TRIM(customer_id) ORDER BY updated_at DESC,source_row DESC) AS rn
    FROM crm_people
    WHERE customer_id IS NOT NULL AND TRIM(customer_id)<>''
  )
  SELECT
    TRIM(customer_id) AS customer_id,
    TRIM(name) AS name,
    CASE UPPER(TRIM(gender))
      WHEN 'F' THEN 'Female'
      WHEN 'M' THEN 'Male'
      ELSE NULL
    END AS crm_gender
  FROM ranked
  WHERE rn=1
),erp AS(
  SELECT
    CASE
      WHEN SUBSTR(TRIM(erp_id),1,3)='NAS' THEN SUBSTR(TRIM(erp_id),4)
      ELSE TRIM(erp_id)
    END AS customer_id,
    CASE UPPER(TRIM(gender))
      WHEN 'F' THEN 'Female'
      WHEN 'FEMALE' THEN 'Female'
      WHEN 'M' THEN 'Male'
      WHEN 'MALE' THEN 'Male'
      ELSE NULL
    END AS erp_gender,
    birth_date
  FROM erp_people
),loc AS(
  SELECT
    REPLACE(TRIM(erp_id),'-','') AS customer_id,
    CASE UPPER(TRIM(country_code))
      WHEN 'US' THEN 'United States'
      WHEN 'USA' THEN 'United States'
      WHEN 'GB' THEN 'United Kingdom'
      WHEN 'UK' THEN 'United Kingdom'
      WHEN 'IND' THEN 'India'
      ELSE 'Unknown'
    END AS country
  FROM erp_locations
)
SELECT
  c.customer_id,
  c.name,
  COALESCE(c.crm_gender,e.erp_gender,'Unknown') AS gender,
  COALESCE(l.country,'Unknown') AS country,
  CASE
    WHEN e.birth_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
      AND date(e.birth_date,'+0 days')=e.birth_date
      AND e.birth_date<='2025-04-01'
      THEN e.birth_date
    ELSE NULL
  END AS birth_date
FROM crm c
LEFT JOIN erp e ON e.customer_id=c.customer_id
LEFT JOIN loc l ON l.customer_id=c.customer_id;
CREATE TEMP TABLE built_sales AS
SELECT
  b.row_id AS sale_key,
  c.customer_key,
  p.product_key,
  CAST(b.quantity AS INTEGER) AS quantity,
  CAST(b.quantity AS INTEGER)*p.price AS sales,
  b.order_date
FROM bronze_sales b
JOIN dim_customer c ON c.customer_id=TRIM(b.customer_id)
JOIN dim_product p ON p.product_id=TRIM(b.product_id)
WHERE TRIM(b.quantity)<>''
  AND TRIM(b.quantity) NOT GLOB '*[^0-9]*'
  AND CAST(b.quantity AS INTEGER)>0
  AND b.order_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
  AND date(b.order_date,'+0 days')=b.order_date;
SELECT
  i.customer_id,
  i.name,
  i.gender,
  i.country,
  COUNT(s.sale_key) AS sales_lines,
  COALESCE(SUM(s.sales),0) AS total_sales
FROM integrated_customers i
JOIN dim_customer c ON c.customer_id=i.customer_id
LEFT JOIN built_sales s ON s.customer_key=c.customer_key
GROUP BY i.customer_id,i.name,i.gender,i.country;