Warehouse project, 3 of 3

Unified Sales Warehouse

A SQL Server warehouse that combines CRM and ERP sales data into one star schema.

Problem

Sales data lived in two systems with different keys and formats for the same customers and products.

What I built

SSIS loads six raw source tables into a Bronze layer, stored procedures clean and standardize them in Silver, and Gold exposes a star schema with fact_sales, dim_customers and dim_products as views.

Result

One consistent model for sales reporting, where each number can be traced back a layer at a time.

The numbers, and where each one comes from

6source tables

Three CRM tables (sales, customers, products) and three ERP tables (customers, locations, categories).

from codescripts/bronze/ddl_bronze.sql

18,494customer rows

Raw CRM customer rows before deduplication in Silver.

from codedata/crm/cust_info.csv

Bronze, Silver, Goldlayers

One schema per layer in SQL Server.

from codescripts/init_database.sql

1 fact, 2 dimsGold model

fact_sales with dim_customers and dim_products, created as views.

from codescripts/gold/ddl_gold.sql

Architecture

T-SQLscripts/gold/ddl_gold.sql

CREATE VIEW gold.dim_customers AS ...
CREATE VIEW gold.dim_products  AS ...
CREATE VIEW gold.fact_sales    AS ...
-- Gold is views over Silver: a model change needs no reload

Decisions and trade-offs

Medallion layers
Bronze keeps files as they arrived, Silver applies the business rules, Gold is what analysts query.
Gold as views
The star schema is views over Silver, so a model change needs no reload.
Full reload in Silver
TRUNCATE and INSERT keeps the procedures simple at this size. Incremental loads are the next step.

Stack

SQL ServerSSIST-SQLData Modeling