SQL Server Data Warehouse — Guided Implementation

Classification: Guided learning implementation
Portfolio role: Foundational warehouse practice—not an original flagship project

A guided SQL Server data-warehouse implementation in which I worked through how CRM and ERP extracts move from source-aligned tables to a model that can support analysis.

This repository is based on the Data With Baraa SQL Data Warehouse project. I followed the guided architecture and implementation to practise ingestion, cleansing, integration, dimensional modelling and data-quality checks. It should be read as an attributed learning implementation, not as an original production system.

What I practised

• loading source CSV files into bronze tables with T-SQL stored procedures and BULK INSERT;
• cleaning and standardising CRM and ERP attributes in the silver layer;
• integrating customer, product and sales data across sources;
• exposing dimension and fact views in a gold-layer star schema;
• checking uniqueness, referential integrity and transformation outcomes.

Architecture

Layer	Purpose	Main evidence
Bronze	Preserve source-aligned CRM and ERP tables	DDL and repeatable bulk-load procedure
Silver	Clean, standardise and integrate fields	Transformation DDL/procedure and quality checks
Gold	Publish analytics-ready dimensions and facts	Customer/product dimensions, sales fact and relationship checks

Repository map

.
├── datasets/
│   ├── source_crm/          # Customer, product and sales CSV extracts
│   └── source_erp/          # Customer, location and category CSV extracts
├── docs/
│   ├── architecture/       # Data flow, integration and model diagrams
│   └── reference/          # Data catalog, naming rules and course notes
├── scripts/
│   ├── bronze/              # Raw table DDL and source-loading procedure
│   ├── silver/              # Cleansing/integration DDL and procedure
│   ├── gold/                # Star-schema views
│   └── init_database.sql    # Database and schema setup
└── tests/                   # Silver- and gold-layer quality queries

Key implementation decisions

Repeatable bronze loads

The bronze procedure truncates target tables before each full load, records load duration and wraps the batch in error handling. Source paths in the script are local examples and must be changed for another machine.

Silver-layer standardisation

The transformation layer addresses source-system differences before integration. The companion quality queries inspect duplicates, invalid values, date logic and other data-quality conditions.

Analytics-ready gold model

The gold layer exposes:

• gold.dim_customers
• gold.dim_products
• gold.fact_sales

Surrogate keys are generated for the dimensions, CRM and ERP attributes are reconciled, and the fact view joins sales transactions to customer and product dimensions.

Validation

The repository includes explicit SQL checks for:

• duplicate customer and product keys;
• fact rows without matching dimensions;
• silver-layer quality and standardisation conditions;
• expected uniqueness of gold surrogate keys.

A query returning no rows is used where the expectation is that no exception exists.

How to explore the project

1. Review the architecture diagram and data catalog.
2. Adjust the database name and CSV paths in the setup and bronze-load scripts.
3. Run scripts/init_database.sql.
4. Run the bronze DDL and load procedure.
5. Run the silver DDL and transformation procedure.
6. Create the gold views.
7. Execute the quality checks in tests/.

Current boundaries

• This is a guided learning implementation using the tutorial's supplied data and architecture.
• The source paths are not parameterised.
• The pipeline is batch-oriented and does not implement orchestration, incremental loading, slowly changing dimensions or automated CI tests.
• The repository demonstrates the warehouse build; it does not yet include a finished Power BI reporting layer.

Next improvements

• parameterise source paths and environment settings;
• add executable setup notes with expected outputs;
• convert validation queries into automated tests;
• implement incremental-load and historisation patterns;
• add a small reporting layer or analytical query pack;
• document which implementation choices I changed or extended beyond the guide.

Concepts and implementation

The project centres on source integration, repeatable batch loading, data standardisation, dimensional modelling, surrogate keys, referential integrity and data-quality validation. The implementation uses SQL Server, T-SQL stored procedures, layered schemas and star-schema views.

Data and privacy

The repository uses the public learning dataset supplied with the referenced tutorial. It contains no employer data, internal screenshots, credentials or confidential architecture.

Attribution and ownership

The architecture, source dataset and primary implementation path come from the linked Data With Baraa course. Personal evidence is limited to the work actually followed, run, inspected, documented or independently extended.