runtime
CRM + ERP Integration · brief

project 05 CRM + ERP Integration

Connect business data with confidence.

Integrate customer and operational records into reliable sales reporting.

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.
you deliver
Reconciled source data, stable keys, historical versions and a validated reporting refresh. Exported as a report with your checked SQL, result evidence and written findings.
skills
Integration · Incremental loading · Data quality
scope
12 milestones · 16 tables · advanced
Start the first milestone

milestones

  1. 01Reconcile extraction & Bronze ingestionReconcile source counts with ingested records.
  2. 02Integrate CRM + ERP with source authorityCombine CRM and ERP using clear source ownership rules.
  3. 03Product validity with LEAD & half-open intervalsDefine product validity using the next change date.
  4. 04Validate calendar dates before convertingValidate real calendar dates before converting text.
  5. 05Quarantine bad rows & reconcile the batchSeparate invalid records and reconcile the whole batch.
  6. 06Automated star-schema quality testsTest relationships and data quality in a star schema.
  7. 07Durable surrogate keys across loadsKeep generated keys stable across repeated loads.
  8. 08Latest-state reporting versus SCD Type 2Compare current-state reporting with preserved history.
  9. 09Idempotent incremental UPSERT & stale updatesRepeat incremental updates safely and reject stale changes.
  10. 10Stage, validate & atomically promote a refreshValidate staged data before replacing a published result.
  11. 11Protect fact grain when joining versionsJoin historical versions without multiplying fact rows.
  12. 12Capstone: integrated customers to trusted salesCombine integration and quality checks into trusted sales reporting.

data Northstar CRM + ERP · 16 tables

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_rowinteger · keycustomer_idtextnametextgendertextupdated_attext
1 C001 AliceF2025-01-01
2C001Alice NguyenF2025-01-05
3C002Benn/a2025-01-02
4C003CaraF2025-01-03
5C004DevNULL2025-01-04

showing 5 of 6 rows

erp_people3 columns · 5 rows
erp_idtextgendertextbirth_datetext
NASC001Male1990-05-12
NASC002Male1988-02-29
NASC003Female2028-01-01
NASC004Other1995-06-20
NASC999Female1980-01-01

all 5 rows

erp_locations2 columns · 4 rows
erp_idtextcountry_codetext
C-001USA
C-002GB
C-003UK
C-004IND

all 4 rows

load_manifest2 columns · 2 rows
source_nametext · keyexpected_rowsinteger
CRM6
ERP5

all 2 rows

product_versions5 columns · 4 rows
version_idinteger · keyproduct_idtextnametextcostrealvalid_fromtext
1P01Notebook82024-01-01
2P01Notebook92025-02-01
3P02Desk lamp302024-01-01
4P02Desk lamp322025-03-01

all 4 rows

raw_dates2 columns · 8 rows
row_idinteger · keyraw_datetext
120250115
220250230
30
420240229
520251301

showing 5 of 8 rows

dq_sales5 columns · 5 rows
line_idintegercustomer_keyintegerproduct_keyintegerquantityintegersalesreal
111224
29991112
32999180
42300
111224

all 5 rows

customer_history5 columns · 3 rows
version_keyinteger · keycustomer_idtextcountrytextvalid_fromtextvalid_totext
101C001United States2024-01-012025-02-01
102C001Canada2025-02-01NULL
103C002United Kingdom2024-01-01NULL

all 3 rows

incoming_people3 columns · 3 rows
customer_idtext · keynametextupdated_attext
C001Alice New2025-02-01
C002Ben stale2024-01-01
C006Finn2025-02-02

all 3 rows

current_people4 columns · 2 rows
customer_keyinteger · keycustomer_idtextnametextupdated_attext
1C001Alice Nguyen2025-01-05
2C002Ben2025-01-02

all 2 rows

published_totals2 columns · 2 rows
customer_idtext · keytotal_salesreal
C001289
C002116

all 2 rows

refresh_stage2 columns · 2 rows
customer_idtexttotal_salesreal
C001300
C002120

all 2 rows

warehouse_catalog5 columns · 7 rows
object_nametextcolumn_nametextmeaningtextsource_objecttextruletext
integrated_customerscustomer_idNormalized natural customer identifiercrm_people.customer_idTrim; latest updated_at then source_row
integrated_customersgenderCustomer gender under agreed source authoritycrm_people.gender + erp_people.genderMeaningful CRM first, ERP fallback, otherwise Unknown
integrated_customerscountryCountry display nameerp_locations.country_codeNormalize ERP key and map codes
integrated_customersbirth_dateValidated birth dateerp_people.birth_dateValid calendar date; no future dates at 2025-04-01
built_salessale_keyOne source sales linebronze_sales.row_idUnique and not null

showing 5 of 7 rows

bronze_sales6 columns · 13 rows
row_idinteger · keycustomer_idtextproduct_idtextquantitytextsalestextorder_datetext
1C001P012242025-01-01
2 C002 P031802025-01-02
3C001P021452025-01-15
4C003P0422402025-02-01
5C004P0321602025-02-05

showing 5 of 13 rows

dim_customer4 columns · 5 rows
customer_keyinteger · keycustomer_idtextnametextcountrytext
1C001Alice NguyenUnited States
2C002BenUnited Kingdom
3C003CaraUnited Kingdom
4C004DevIndia
5C005ElenaCanada

all 5 rows

dim_product5 columns · 4 rows
product_keyinteger · keyproduct_idtextnametextcategorytextpricereal
1P01NotebookStationery12
2P02Desk lampHome45
3P03KeyboardTech80
4P04HeadphonesTech120

all 4 rows