How aditya-7-7/sql-data-warehouse-analytics works
Project type and purpose
• This is a Data Warehouse + Analytics project built on Microsoft SQL Server.
• It implements a Medallion (Bronze/Silver/Gold) architecture, ingesting CSV files from CRM and ERP sources, performing ETL, modeling data into star schemas, and supporting analytics/reporting.
File structure
• datasets/ : raw CSV files organized by source (data-analytics, source_crm, source_erp).
• scripts/
• bronze/ – DDL + load procedures for Bronze layer.
• silver/ – DDL + load procedures for Silver layer.
• gold/ – DDL for modeling Gold tables (dimensions + fact).
• analytics/ – SQL scripts generating reports and CSV snapshots from Gold.
• init_database.sql – creates database/schema scaffolding.
• tests/ : quality check SQL scripts for Silver and Gold layers.
• docs/ : documentation placeholder.
• data-catalogue.md : metadata/catalog definitions.
Key README insights
• ETL Automation via T-SQL stored procedures.
• Bronze: raw ingestion; Silver: cleansing/enrichment; Gold: star schemas & aggregations.
• Analytics scripts produce business-ready CSV outputs.
• Tech stack: SQL Server, T-SQL, CSV, star schema modeling.
Main components
a. Data Sources
• CRM system → CSV dumps
• ERP system → CSV dumps
b. Ingestion / Bronze Layer
• Stored procedures (scripts/bronze) load raw CSV into bronze tables.
c. Transformation / Silver Layer
• Procedures (scripts/silver) perform cleansing, normalization, enrichment.
d. Modeling / Gold Layer
• DDL (scripts/gold) builds dim_customers, dim_products, fact_sales in star schema.
e. Analytics & Reporting
• SQL scripts (scripts/analytics) generate aggregated outputs and CSVs for BI.
f. Metadata & QA
• tests/ quality_checks_silver.sql, quality_checks_gold.sql.
g. BI consumers (Power BI, Tableau, Excel) pulling Gold/CVS outputs.
Relationships & interactions
• Bronze tables → Silver transformation → Silver tables.
• Silver tables → Gold modeling → Gold tables/views.
• Gold tables → Analytics scripts → CSV outputs → BI tools.
• QA scripts run against Silver & Gold tables; metadata docs reference each layer.
Architectural patterns & principles
• Medallion Architecture (bronze/silver/gold layered).
• Star schema in Gold (dimension + fact tables for performance).
• Modular ETL stored procedures and DDL scripts.
• Automated quality checks for data integrity and lineage.
Technologies & frameworks
• Microsoft SQL Server (database / engine).
• T-SQL stored procedures and scripts.
• CSV file ingestion.
• BI integration: CSV snapshots for downstream tools.
Element checklist
• Clear component labels (e.g., “Bronze Raw Tables”).
• Callouts for test scripts and data catalog.
• External services / BI consumers shown at the edge.
• Versioning or script folders noted for traceability.