SQL Server Data Warehouse – End-to-End Project

1. Overview

An end-to-end SQL Server data warehouse project demonstrating core data engineering and analytics engineering concepts, from data ingestion and SQL-based ETL to dimensional modeling and analytical querying.

This project focuses on building a clean, analytics-ready schema rather than visualization or BI tooling.

This README is simple on purpose. Happy to explain the rest in person.

---

2. Architecture & Data Flow

The project follows a Medallion-style architecture (Bronze, Silver, Gold) implemented in SQL Server, as shown in the diagram above.

Data from simulated CRM and ERP sources (CSV files) is ingested into the Bronze layer, refined in the Silver layer, and modeled into analytics-ready datasets in the Gold layer.

• Bronze: Raw source data for traceability
• Silver: Cleansed and standardized data
• Gold: Star schema models and aggregations for analytics

Architecture Notes & Design Considerations

• Batch ETL with full loads is used to keep transformation logic simple and transparent.
• All transformations are implemented using SQL stored procedures.
• The Gold layer is implemented using views to centralize business logic.
• The design prioritizes clarity and analytical usability over production-scale optimization.

3. Gold Layer & Analytics Use Cases

The Gold layer contains analytics-ready datasets modeled using a star schema to support efficient analytical querying.

This layer integrates data from CRM and ERP sources into business-focused views that enable analysis across core domains such as:

• Customers
• Products
• Sales performance

Typical use cases include trend analysis, performance comparisons, and exploratory analysis using SQL.

The datasets in this layer are designed for consumption by analytics teams and business stakeholders through ad-hoc SQL queries or BI tools.

Additional details are documented in docs.
If you’re curious beyond that, we’ll probably need a conversation.

4. Repository Structure

The repository is organized by data layers and documentation to clearly separate ingestion, transformation, and analytics.

data-warehouse-project/
│
├── datasets/
│   └── (ERP and CRM raw datasets in CSV format)
│
├── docs/
│   ├── data_architecture.drawio    # Overall data warehouse architecture
│   ├── data_flow.drawio            # End-to-end data flow diagram
│   ├── data_models.drawio          # Star schema and dimensional models
│   ├── data_catalog.md             # Dataset catalog with field descriptions
│   └── naming-conventions.md       # Table, column, and file naming standards
│
├── scripts/
│   ├── bronze/                     # Raw data ingestion (as-is)
│   ├── silver/                     # Data cleansing and standardization
│   └── gold/                       # Analytics-ready models (facts & dimensions)
│
├── tests/
│   └── (Data quality and validation scripts)
│
├── README.md
├── LICENSE
└── .gitignore

About This Project

This project is a hands-on implementation of a modern SQL Server–based data warehouse, built to demonstrate practical data modeling, ETL design, and analytics workflows.
Everything is intentionally concise here — the rest is better discussed in a conversation.