I turn scattered CRM and ERP data into a warehouse people can actually trust.
This is a SQL Server data warehouse I built end to end: raw CSV exports from two disconnected source systems, cleaned and reconciled through a Bronze → Silver → Gold pipeline, and modeled into a star schema ready for reporting. No frameworks, no shortcuts — just T-SQL, a clear architecture, and a lot of attention to the details that make a warehouse trustworthy.
One warehouse, two messy source systems
The scenario behind this project is a common one: a company runs a CRM system for customer and sales data and a separate ERP system for product and location data. Neither system was designed to talk to the other, so before anyone can build a dashboard, someone has to reconcile them — mismatched keys, inconsistent codes, duplicate customer records, and sales figures that don't always add up.
I built this warehouse to do exactly that: pull the raw CSV exports in as-is, clean and standardize them, and land them in a business-ready model that a BI tool or an analyst can query directly, without needing to know anything about the mess underneath.
Everything here runs on Microsoft SQL Server using plain T-SQL — no orchestration platform, no external ETL tool. The goal was to show that the fundamentals — clean SQL, a sound architecture, and rigorous testing — hold up on their own.
-
01
Data Sources
Import raw CSV exports from two independent source systems — CRM and ERP. -
02
Data Quality
Catch and resolve nulls, duplicates, inconsistent formats and invalid values before they reach reporting. -
03
Integration
Reconcile mismatched keys and codes across systems into one coherent, analysis-ready model. -
04
Analytics-Ready
Expose a star schema plus reporting views built specifically for customer, product and sales analysis.
A Medallion architecture, kept honest
Every layer has one job and a clear boundary. Nothing downstream ever reaches back past the layer right before it.
Raw ingestion
Six source tables loaded byte-for-byte from CSV via BULK INSERT, wrapped in a single stored procedure with per-table timing and a full truncate-and-reload on every run. No transformation happens here on purpose — Bronze is the audit trail.
bronze.load_bronze
TRY / CATCH
Cleansed & conformed
Deduplication with deterministic tie-breaking, type-safe date parsing, standardized codes (gender, marital status, country, product line), and self-healing sales figures — all inside one transaction that rolls back completely on failure.
silver.load_silver
ROW_NUMBER()
TRY_CONVERT
Business-ready
A star schema exposed as views — two dimensions and one fact — plus two reporting views built for customer and product analysis. This is the only layer any report or query is meant to touch.
gold.dim_customers
gold.fact_sales
A star schema built around one grain
One row per sales order line. Two dimensions. Every foreign key resolves to something — even when the source data doesn't cooperate.
Why a star, not a snowflake
Two dimensions and one fact don't justify normalizing further. A star schema keeps every report a single join away and matches how BI tools expect to consume data.
Unknown-member rows
Sales that can't be matched to a customer or product don't get dropped or left with a null key — they resolve to an explicit key of -1, so nothing in the fact table is ever silently unjoinable.
History where it matters
Product versions are tracked with start/end dates derived from the source data itself, so Gold always resolves to the currently active version without losing that history in Silver.
The full design reasoning — grain, key strategy, and the SCD tradeoffs behind it — is written up in docs/data_model.md.
The parts that don't show up in a diagram
A handful of real decisions from the codebase — the kind of thing that separates a warehouse that looks right from one that actually is.
Deterministic deduplication silver.load_silver
Duplicate customer records are ranked by recency, then by how complete the record is, so re-running the load always keeps the same row — not whichever one happened to load first.
ROW_NUMBER() OVER ( PARTITION BY cst_id ORDER BY cst_create_date DESC, completeness_score DESC, cst_key_clean DESC ) AS row_number -- keep only row_number = 1 per customer
Self-healing measures silver.load_silver
When sales, price and quantity don't agree, price and sales are recalculated from each other instead of trusting a possibly-corrupt source value, within a small tolerance for rounding.
CASE WHEN raw_sales IS NULL OR raw_sales <= 0 THEN raw_quantity * corrected_price WHEN ABS(raw_sales - (raw_quantity * corrected_price)) > 0.01 THEN raw_quantity * corrected_price ELSE raw_sales END
Version history from raw data silver.load_silver
The source system only tells you when a product version starts. LEAD() derives when it ends — one day before the next version begins — without a second source of truth.
LEAD(product_start_date) OVER ( PARTITION BY original_prd_key ORDER BY product_start_date, prd_id ) AS next_product_start_date -- end_date = DATEADD(DAY, -1, next_start_date)
No orphaned facts gold.dim_customers
Every dimension carries an explicit "Unknown" member with key -1. A sale that can't be matched to a real customer still lands somewhere countable, instead of vanishing from totals.
SELECT CAST(-1 AS BIGINT) AS customer_key, NULL AS customer_id, 'UNKNOWN' AS customer_number, 'Unknown Customer' AS customer_name -- unioned into the dimension view
All-or-nothing loads silver.load_silver
The entire Silver load runs inside one transaction. If any of the six tables fails, everything rolls back — no partial refresh where some tables are current and others are stale.
SET XACT_ABORT ON; BEGIN TRANSACTION; -- ...six table loads... COMMIT TRANSACTION; -- CATCH block: IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
Source-priority merge logic gold.dim_customers
Customer gender exists in both CRM and ERP and they don't always agree. CRM wins when it has a value — it's the system closer to the actual sales relationship — ERP fills the gaps.
COALESCE( NULLIF(ci.cst_gndr, 'n/a'), NULLIF(ca.gen, 'n/a'), 'n/a' ) AS gender
What the model is actually for
A dimensional model is only as good as what you can ask of it. These are the analysis patterns built on top of the Gold layer.
Change over time
Monthly and yearly sales trends to spot seasonality and growth, not just totals.
Cumulative analysis
Running totals and moving averages to track momentum across the order history.
Performance analysis
Year-over-year product performance against its own average and prior-year baseline.
Part-to-whole
Which product categories actually drive revenue, as a share of total sales.
Magnitude & ranking
Top and bottom performers by revenue, quantity and customer count.
Segmentation
Customers and products grouped into VIP / Regular / New and cost-based tiers.
gold.report_customers
One row per customer: lifespan, recency, order count, total spend, average order value and a VIP / Regular / New segment — the kind of view a CRM or retention dashboard would sit directly on top of.
- recency
- avg_order_value
- avg_monthly_spend
- customer_segment
gold.report_products
One row per product: total revenue, units sold, unique customers reached, and a High / Mid / Low performance tier based on lifetime sales.
- avg_order_revenue
- avg_monthly_revenue
- product_segment
Everything is there to read
No slide deck required — the SQL, the tests and the docs are the whole story.
exploratory data analysis/
Every analysis pattern above as a standalone, runnable SQL script.
Browse →Looking for a data engineer who cares about the details.
I'm open to data engineering and analytics engineering roles. If you want to talk about this project, or anything else, my inbox is open.