SQL Projects / CRM + ERP Integration

Project 05 · CRM + ERP Integration

Connect business data with confidence.

Integrate customer and operational records into reliable sales reporting.

The brief

The business question: Follow extraction reconciliation, authoritative integration, date quarantine, quality gates, stable keys, replay-safe loads, and atomic promotion. Finish with a customer-grain capstone and field-level lineage.
What you’ll deliver: Reconciled source data, stable keys, historical versions and a validated reporting refresh.Exported as a report with business questions, checked SQL, result evidence and your written findings. Labeled Draft until every milestone has a finding.
Skills: Integration · Incremental loading · Data quality
Scope: Advanced level · 12 milestones · 16 tables

Available data

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.

crm_people5 columns · 6 rows · source_row, customer_id, name, gender, …

source_rowintegerPK
customer_idtext
nametext
gendertext
updated_attext

First 5 of 6 rows · original sample data

#	source_row	customer_id	name	gender	updated_at
1	1	C001	Alice	F	2025-01-01
2	2	C001	Alice Nguyen	F	2025-01-05
3	3	C002	Ben	n/a	2025-01-02
4	4	C003	Cara	F	2025-01-03
5	5	C004	Dev	NULL	2025-01-04

erp_people3 columns · 5 rows · erp_id, gender, birth_date

erp_idtext
gendertext
birth_datetext

First 5 of 5 rows · original sample data

#	erp_id	gender	birth_date
1	NASC001	Male	1990-05-12
2	NASC002	Male	1988-02-29
3	NASC003	Female	2028-01-01
4	NASC004	Other	1995-06-20
5	NASC999	Female	1980-01-01

erp_locations2 columns · 4 rows · erp_id, country_code

erp_idtext
country_codetext

First 4 of 4 rows · original sample data

#	erp_id	country_code
1	C-001	USA
2	C-002	GB
3	C-003	UK
4	C-004	IND

load_manifest2 columns · 2 rows · source_name, expected_rows

source_nametextPK
expected_rowsinteger

First 2 of 2 rows · original sample data

#	source_name	expected_rows
1	CRM	6
2	ERP	5

product_versions5 columns · 4 rows · version_id, product_id, name, cost, …

version_idintegerPK
product_idtext
nametext
costreal
valid_fromtext

First 4 of 4 rows · original sample data

#	version_id	product_id	name	cost	valid_from
1	1	P01	Notebook	8	2024-01-01
2	2	P01	Notebook	9	2025-02-01
3	3	P02	Desk lamp	30	2024-01-01
4	4	P02	Desk lamp	32	2025-03-01

raw_dates2 columns · 8 rows · row_id, raw_date

row_idintegerPK
raw_datetext

First 5 of 8 rows · original sample data

#	row_id	raw_date
1	1	20250115
2	2	20250230
3	3	0
4	4	20240229
5	5	20251301

dq_sales5 columns · 5 rows · line_id, customer_key, product_key, quantity, …

line_idinteger
customer_keyinteger
product_keyinteger
quantityinteger
salesreal

First 5 of 5 rows · original sample data

#	line_id	customer_key	product_key	quantity	sales
1	1	1	1	2	24
2	2	999	1	1	12
3	3	2	999	1	80
4	4	2	3	0	0
5	1	1	1	2	24

customer_history5 columns · 3 rows · version_key, customer_id, country, valid_from, …

version_keyintegerPK
customer_idtext
countrytext
valid_fromtext
valid_totext

First 3 of 3 rows · original sample data

#	version_key	customer_id	country	valid_from	valid_to
1	101	C001	United States	2024-01-01	2025-02-01
2	102	C001	Canada	2025-02-01	NULL
3	103	C002	United Kingdom	2024-01-01	NULL

incoming_people3 columns · 3 rows · customer_id, name, updated_at

customer_idtextPK
nametext
updated_attext

First 3 of 3 rows · original sample data

#	customer_id	name	updated_at
1	C001	Alice New	2025-02-01
2	C002	Ben stale	2024-01-01
3	C006	Finn	2025-02-02

current_people4 columns · 2 rows · customer_key, customer_id, name, updated_at

customer_keyintegerPK
customer_idtext
nametext
updated_attext

First 2 of 2 rows · original sample data

#	customer_key	customer_id	name	updated_at
1	1	C001	Alice Nguyen	2025-01-05
2	2	C002	Ben	2025-01-02

published_totals2 columns · 2 rows · customer_id, total_sales

customer_idtextPK
total_salesreal

First 2 of 2 rows · original sample data

#	customer_id	total_sales
1	C001	289
2	C002	116

refresh_stage2 columns · 2 rows · customer_id, total_sales

customer_idtext
total_salesreal

First 2 of 2 rows · original sample data

#	customer_id	total_sales
1	C001	300
2	C002	120

warehouse_catalog5 columns · 7 rows · object_name, column_name, meaning, source_object, …

object_nametext
column_nametext
meaningtext
source_objecttext
ruletext

First 5 of 7 rows · original sample data

#	object_name	column_name	meaning	source_object	rule
1	integrated_customers	customer_id	Normalized natural customer identifier	crm_people.customer_id	Trim; latest updated_at then source_row
2	integrated_customers	gender	Customer gender under agreed source authority	crm_people.gender + erp_people.gender	Meaningful CRM first, ERP fallback, otherwise Unknown
3	integrated_customers	country	Country display name	erp_locations.country_code	Normalize ERP key and map codes
4	integrated_customers	birth_date	Validated birth date	erp_people.birth_date	Valid calendar date; no future dates at 2025-04-01
5	built_sales	sale_key	One source sales line	bronze_sales.row_id	Unique and not null

bronze_sales6 columns · 13 rows · row_id, customer_id, product_id, quantity, …

row_idintegerPK
customer_idtext
product_idtext
quantitytext
salestext
order_datetext

First 5 of 13 rows · original sample data

#	row_id	customer_id	product_id	quantity	sales	order_date
1	1	C001	P01	2	24	2025-01-01
2	2	C002	P03	1	80	2025-01-02
3	3	C001	P02	1	45	2025-01-15
4	4	C003	P04	2	240	2025-02-01
5	5	C004	P03	2	160	2025-02-05

dim_customer4 columns · 5 rows · customer_key, customer_id, name, country

customer_keyintegerPK
customer_idtext
nametext
countrytext

First 5 of 5 rows · original sample data

#	customer_key	customer_id	name	country
1	1	C001	Alice Nguyen	United States
2	2	C002	Ben	United Kingdom
3	3	C003	Cara	United Kingdom
4	4	C004	Dev	India
5	5	C005	Elena	Canada

dim_product5 columns · 4 rows · product_key, product_id, name, category, …

product_keyintegerPK
product_idtext
nametext
categorytext
pricereal

First 4 of 4 rows · original sample data

#	product_key	product_id	name	category	price
1	1	P01	Notebook	Stationery	12
2	2	P02	Desk lamp	Home	45
3	3	P03	Keyboard	Tech	80
4	4	P04	Headphones	Tech	120

Open a table to see its columns and sample rows. You do not need to read every table before starting a milestone.

Each run starts with the original sample data. Changes to tables do not carry over to later runs or milestones. Findings are optional for continuing — required only for a complete report.

Milestones

Start

1 · Land the sources

Check that every source row arrived before you trust it.

Reconcile extraction & Bronze ingestion

Reconcile source counts with ingested records.

Not started

2 · Integrate and validate

Combine CRM and ERP by source authority, then validate dates and quarantine bad rows.

Integrate CRM + ERP with source authority

Combine CRM and ERP using clear source ownership rules.

Not started

Product validity with LEAD & half-open intervals

Define product validity using the next change date.

Not started

Validate calendar dates before converting

Validate real calendar dates before converting text.

Not started

Quarantine bad rows & reconcile the batch

Separate invalid records and reconcile the whole batch.

Not started

3 · Quality gates and stable keys

Automate tests and keep surrogate keys stable across loads.

Automated star-schema quality tests

Test relationships and data quality in a star schema.

Not started

Durable surrogate keys across loads

Keep generated keys stable across repeated loads.

Not started

4 · History and safe loading

Track change over time and load incrementally without corrupting published data.

Latest-state reporting versus SCD Type 2

Compare current-state reporting with preserved history.

Not started

Idempotent incremental UPSERT & stale updates

Repeat incremental updates safely and reject stale changes.

Not started

Stage, validate & atomically promote a refresh

Validate staged data before replacing a published result.

Not started

5 · Trusted reporting

Report at the right grain, then bring everything together in the capstone.

Protect fact grain when joining versions

Join historical versions without multiplying fact rows.

Not started

Capstone: integrated customers to trusted sales

Combine integration and quality checks into trusted sales reporting.

Not started

Project report

Loading saved progress…

Waiting for saved progress to load.

How a data warehouse is built

This project follows the standard layers: understand source data → load raw records (Bronze) → clean and validate (Silver) → build reporting tables (Gold) → produce reports.

sources

Source contract

Structured fictional CRM, ERP and sales extracts. The foundation keeps latest state. Confirm source ownership, extraction counts, key formats, grain and refresh expectations before choosing architecture.

bronze

Bronze · preserve evidence

Raw source-shaped records retain spaces, sentinels, duplicates and malformed values. Compare extraction counts, inspect column alignment, and retain source row identity for replay and diagnosis.

silver

Silver · standardize with explicit rules

Latest CRM row wins with a deterministic source_row tie-breaker. Normalize ERP keys under its format contract. Validate calendar dates, quarantine failures and reconcile every source row.

gold

Gold · integrate business entities

CRM owns meaningful gender; ERP supplies fallback and country. Stable dimension keys link each validated sales line. Choose current-state or effective-time semantics before reporting. Views or stored tables are workload choices.

report

Reports · declare the metric

Customer totals use one row per customer and preserve zero-activity entities. Northstar sales means positive quantity × catalog price under a stated teaching rule, not an automatic accounting correction.