Skip to content

README.md

Latest commit

 

History

History
107 lines (82 loc) · 3.14 KB

README.md

File metadata and controls

107 lines (82 loc) · 3.14 KB
Raw

SQL Data Warehouse Project (Medallion Architecture)

Overview

This project focuses on building a modern SQL-based Data Warehouse using Medallion Architecture (Bronze → Silver → Gold) on Microsoft SQL Server.

The pipeline ingests raw data from CRM and ERP systems (CSV files) and processes it using batch processing with a full load strategy.

The goal is to design a scalable, modular, and production-ready data warehouse that demonstrates real-world data engineering practices.

Architecture

Medallion Architecture Layers

Data Architecture

Bronze Layer (Raw Data)

  • Stores raw, unprocessed data from source systems
  • Data is ingested as-is from CSV files
  • Acts as the single source of truth

Silver Layer (Cleaned, Standardized Data)

  • Data is cleaned, standardized, and validated
  • Handles missing values, duplicates, and data type corrections
  • Prepares structured data for business logic

Gold Layer (Business-Ready Data)

  • Contains aggregated and business-ready data
  • Optimized for reporting and analytics
  • Used for dashboards and decision-making

Tech Stack

  • Database: Microsoft SQL Server
  • Data Sources: CRM & ERP (CSV files)
  • Processing Type: Batch Processing
  • Loading Strategy: Full Load (Truncate and Load)
  • Architecture: Medallion Architecture
  • Design Tool: Draw.io

Naming Convention

Tables

All names must strat with the source system name, and table names must match their original names without renaming. <sourcesystem>_<entity>

  • <sourcesystem>: Source system name (e.g., erp, crm)

  • <entity>: Exact table name from the source system

  • Example: crm_customer_info (Customer information from CRM system)

Stored Procedure

load_<layer>

  • <layer> represents the layer being loaded, such as bronze, silver or gold
  • Example: load_bronze (Stored Procedure for loading data into the Bronze Layer)

Project Structure

SQL-Data-Warehouse-Project/
├── LICENSE
├── README.md
├── datasets/
│   ├── source_crm/
│   │   ├── cust_info.csv
│   │   ├── prd_info.csv
│   │   └── sales_details.csv
│   └── source_erp/
│       ├── CUST_AZ12.csv
│       ├── LOC_A101.csv
│       └── PX_CAT_G1V2.csv
├── docs/
│   ├── data_architecture.png
│   ├── data_catalog.md
│   ├── data_flow_diagram.png
│   ├── data_model.png
│   └── integration_model.png
├── scripts/
│   ├── bronze/
│   │   ├── ddl_bronze.sql
│   │   └── procedure_load_bronze.sql
│   ├── gold/
│   │   └── ddl_gold.sql
│   ├── init_database.sql
│   └── silver/
│       ├── ddl_silver.sql
│       └── procedure_load_silver.sql
└── tests/
    ├── quality_checks_gold.sql
    └── quality_checks_silver.sql

License

This Project is licensed under the MIT License. Feel Free to use and modify. :-)