lesson 78 Warehouse & reporting projects · advanced

Integrate CRM + ERP with source authority

Combine CRM and ERP using clear source ownership rules.

the idea

Integration follows a written contract. Here: keep the latest CRM version per trimmed key (source_row breaks equal timestamps); normalize ERP keys by removing a leading NAS and location keys by removing hyphens; CRM gender wins when it is F or M, ERP gender is the fallback; countries come from the ISO code table; birth dates must be valid and not after 2011-12-31. Every keyed CRM customer is kept.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Strip NAS from ERP ids. Some erp_id values start with NAS; the contract removes that prefix so ERP keys align with CRM customer_id.the pattern

   example.sql Run

   sql›

   SELECT
     erp_id,
     CASE
       WHEN substr(TRIM(erp_id), 1, 3) = 'NAS' THEN substr(TRIM(erp_id), 4)
       ELSE TRIM(erp_id)
     END AS customer_id
   FROM erp_people
   LIMIT 10;

2. 2

   Remove hyphens from location ids. erp_locations uses hyphenated ids; REPLACE lets you join locations to the same normalized customer_id as people.

   step-2.sql Run

   sql›

   SELECT erp_id, REPLACE(erp_id, '-', '') AS customer_id, country_code
   FROM erp_locations
   LIMIT 10;

3. 3

   Group ERP gender spellings. ERP uses FEMALE and MALE while CRM uses F and M—you need explicit mapping before COALESCE can pick a fallback.

   step-3.sql Run

   sql›

   SELECT UPPER(TRIM(gender)) AS erp_gender, COUNT(*) AS customers
   FROM erp_people
   GROUP BY UPPER(TRIM(gender));

common mistake

COALESCE(crm_gender, erp_gender) only works if sentinel values like 'n/a' and '' are first turned into NULL.