lesson 88 Warehouse & reporting projects · advanced

Capstone: integrated customers to trusted sales

Combine integration and quality checks into trusted sales reporting.

the idea

Build two reusable temporary outputs in one script: integrated CRM/ERP customers and validated sales lines with existing dimension keys. Report at customer grain, preserving customers with no valid sales. The catalog states field authority and exercise sales rules. This is a runnable miniature of the architecture, not a deployed enterprise pipeline.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

crm_people5 columns · 6 rows

source_rowinteger · key	customer_idtext	nametext	gendertext	updated_attext
1	C001	Alice	F	2025-01-01
2	C001	Alice Nguyen	F	2025-01-05
3	C002	Ben	n/a	2025-01-02
4	C003	Cara	F	2025-01-03
5	C004	Dev	NULL	2025-01-04

showing 5 of 6 rows

erp_people3 columns · 5 rows

erp_idtext	gendertext	birth_datetext
NASC001	Male	1990-05-12
NASC002	Male	1988-02-29
NASC003	Female	2028-01-01
NASC004	Other	1995-06-20
NASC999	Female	1980-01-01

all 5 rows

erp_locations2 columns · 4 rows

erp_idtext	country_codetext
C-001	USA
C-002	GB
C-003	UK
C-004	IND

all 4 rows

bronze_sales6 columns · 13 rows

row_idinteger · key	customer_idtext	product_idtext	quantitytext	salestext	order_datetext
1	C001	P01	2	24	2025-01-01
2	C002	P03	1	80	2025-01-02
3	C001	P02	1	45	2025-01-15
4	C003	P04	2	240	2025-02-01
5	C004	P03	2	160	2025-02-05

showing 5 of 13 rows

dim_customer4 columns · 5 rows

customer_keyinteger · key	customer_idtext	nametext	countrytext
1	C001	Alice Nguyen	United States
2	C002	Ben	United Kingdom
3	C003	Cara	United Kingdom
4	C004	Dev	India
5	C005	Elena	Canada

all 5 rows

dim_product5 columns · 4 rows

product_keyinteger · key	product_idtext	nametext	categorytext	pricereal
1	P01	Notebook	Stationery	12
2	P02	Desk lamp	Home	45
3	P03	Keyboard	Tech	80
4	P04	Headphones	Tech	120

all 4 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

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;

Watch for: The final quality checks and catalog belong to the deliverable. A report alone cannot prove lineage, key stability, or replay safety.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Understand the source contract. Fictional extracts, load counts, invalid dates, versioned dimensions, refresh staging and a data catalog. The foundation project is latest-state; separate history and incremental labs demonstrate extensions.
step-1.sql
sql›
```
SELECT *
FROM warehouse_catalog;
```

Execute the transformation. Build two reusable temporary outputs in one script: integrated CRM/ERP customers and validated sales lines with existing dimension keys. Report at customer grain, preserving customers with no valid sales. The catalog states field authority and exercise sales rules. This is a runnable miniature of the architecture, not a deployed enterprise pipeline.

step-2.sql

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;

Test another question. The final quality checks and catalog belong to the deliverable. A report alone cannot prove lineage, key stability, or replay safety.

step-3.sql

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.country,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.country