lesson 78 Warehouse & reporting projects · advanced

Integrate CRM + ERP with source authority

Combine CRM and ERP using clear source ownership rules.

the idea

Normalize source identifiers under a declared contract: strip one leading NAS in ERP people and hyphens in ERP locations. Keep the latest CRM customer; source_row breaks equal timestamps. CRM gender wins when meaningful; ERP supplies a fallback. Preserve every valid CRM customer. Validate birth dates against fixed 2025-04-01.

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

example

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

example.sql

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

Watch for: COALESCE does not know whether n/a is meaningful. Normalize sentinel codes to NULL before applying authority. Each ERP key must be unique or the join can fan out.

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 crm_people;
```

Execute the transformation. Normalize source identifiers under a declared contract: strip one leading NAS in ERP people and hyphens in ERP locations. Keep the latest CRM customer; source_row breaks equal timestamps. CRM gender wins when meaningful; ERP supplies a fallback. Preserve every valid CRM customer. Validate birth dates against fixed 2025-04-01.

step-2.sql

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

Test another question. COALESCE does not know whether n/a is meaningful. Normalize sentinel codes to NULL before applying authority. Each ERP key must be unique or the join can fan out.

step-3.sql

WITH integrated 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
)
SELECT gender,COUNT(*) AS customer_count
FROM integrated
GROUP BY gender