How anandvai/sql_data_warehouse_project works
Project type and purpose
• This is a Data Warehouse & Analytics project built with a Medallion Architecture (Bronze, Silver, Gold).
• Purpose: Ingest raw ERP/CRM CSV data, cleanse/transform it through staged layers, model into a star-schema data warehouse, and produce SQL‐based reports for analytics.
File structure
• Top‐level “datasets/” holds source CSV files (ERP & CRM).
• “scripts/bronze , scripts/silver , scripts/gold” contain ETL SQL scripts for each layer.
• “ETL_Updated_Data/bronze, silver, gold” directories mirror the layered data outputs as CSV exports.
• “Fact_&_Dim_Report_final_Data/gold” holds final report exports.
• “tests/” contains SQL quality‐check scripts for Silver and Gold.
README insights
• Confirms usage of SQL Server Express for hosting the DW.
• Highlights the Bronze → Silver → Gold ETL pipeline, star schema modeling for dimensions (“dim_customers”, “dim_products”) and fact table (“fact_sales”), and reporting scripts.
Main components & interactions
a. Source Systems (CSV files)
• datasets/erp_*.csv and datasets/crm_*.csv
b. Bronze Layer (Raw Ingestion)
• SQL scripts: scripts/bronze/ddl_bronze.sql & procedure_load_bronze.sql
• Storage: ETL_Updated_Data/bronze/*.csv and staging tables in SQL Server
c. Silver Layer (Cleansed & Standardized)
• SQL scripts: scripts/silver/ddl_silver.sql & procedure_load_silver.sql
• Storage: ETL_Updated_Data/silver/*.csv and intermediate tables
• Quality checks: tests/quality_checks_silver.sql
d. Gold Layer (Analytics‐Ready Star Schema)
• SQL scripts: scripts/gold/ddl_gold.sql (creates dim and fact tables)
• Storage: ETL_Updated_Data/gold/*.csv & SQL Server permanent tables
• Quality checks: tests/quality_checks_gold.sql
e. Reporting / BI
• SQL-based reports: Fact_&_Dim_Report_final_Data/gold/*.csv
• Consumers: SSMS, BI dashboards
f. Supporting components
• SQL Server Express (database engine)
• SSMS (GUI)
• Notion (project management)
Architectural patterns & key principles
• Medallion Architecture: clear layering (Bronze raw, Silver curated, Gold modeled)
• Star schema modeling in Gold for high‐performance analytics
• Modular ETL: each layer has its own SQL scripts, schema, and data stores
• Data quality enforced by separate test scripts
• Version control for scripts and docs
7. Docs/drawio files off to the side, showing architecture and data flow documentation.