# lokesh-141/sql-data-warehouse-project architecture

> Architecture diagram of the GitHub repository lokesh-141/sql-data-warehouse-project, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-27). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/lokesh-141/sql-data-warehouse-project
- Repository: https://github.com/lokesh-141/sql-data-warehouse-project

## Overview

1. **Project type and runtime**
   - This is a **SQL Server–based data warehouse** project, not an application service.
   - The architecture is a **batch ETL/ELT pipeline** that moves CSV data through warehouse layers: **bronze → silver → gold**.

2. **Source systems**
   - There are two upstream source domains:
     - **CRM**: `cust_info.csv`, `prd_info.csv`, `sales_details.csv`
     - **ERP**: `CUST_AZ12.csv`, `LOC_A101.csv`, `PX_CAT_G1V2.csv`
   - These represent separate operational systems whose data is consolidated into one analytical model.

3. **Landing / bronze layer**
   - The `scripts/bronze` folder contains:
     - `ddl_bronze.sql` for raw landing tables
     - `proc_load_bronze.sql` for loading source files
   - This layer is the **ingestion boundary**: it keeps source-shaped data with minimal transformation, suitable for traceability and reprocessing.

4. **Cleansing / silver layer**
   - The `scripts/silver` folder contains:
     - `ddl_silver.sql` for cleaned/standardized tables
     - `proc_load_silver.sql` for transformation logic
   - This layer is where source data is **validated, standardized, and integrated** across ERP and CRM.
   - It is the main **data harmonization boundary** before analytics.

5. **Analytics / gold layer**
   - The `scripts/gold` folder contains `ddl_gold.sql`.
   - The gold layer is the **presentation/semantic layer** for reporting and analysis.
   - Based on the README, it supports analytics around:
     - customer behavior
     - product performance
     - sales trends

6. **Database bootstrap**
   - `scripts/init_database.sql` is the setup entry point for creating the database environment before loading any layer.
   - In architecture terms, this is the **platform initialization step** that precedes the warehouse pipeline.

7. **Data flow across layers**
   - The intended flow is:
     - source CSVs in `datasets/`
     - load raw data into bronze tables
     - transform and reconcile into silver tables
     - publish analytics-ready structures in gold
   - The project explicitly focuses on the **latest dataset only**, so there is **no historization/SCD requirement** in the design.

8. **Data quality and testing**
   - The `tests` folder contains:
     - `quality_checks_silver.sql`
     - `quality_checks_gold.sql`
   - These are SQL-based validation checks that enforce **data quality gates** after transformation and before analytical use.
   - They act as the control boundary between “loaded” data and “trusted” data.

9. **Documentation and data model guidance**
   - The `docs` folder includes:
     - `data_catalog.md`
     - `data_layers.pdf`
     - `naming_conventions.md`
     - `Project_Notes_Sketches.pdf`
   - These documents support the warehouse design by defining **layering, naming, and business context**, which is important for drawing the logical model accurately.

10. **Repository structure and boundaries**
   - The repository is organized by **warehouse layer**, not by application feature.
   - The key architectural boundaries are:
     - **input boundary**: CSV source files
     - **raw storage boundary**: bronze
     - **curation boundary**: silver
     - **analytics boundary**: gold
     - **validation boundary**: SQL quality checks

11. **What to show in an architecture diagram**
   - A good diagram would include:
     - ERP and CRM CSV sources on the left
     - SQL Server warehouse in the center
     - Bronze, Silver, Gold layers stacked or sequenced
     - Data quality checks attached to Silver/Gold
     - Documentation artifacts as supporting metadata, not data-processing components

12. **Overall intent**
   - The repo demonstrates a classic **analytics warehouse pattern**: ingest source data, standardize it, model it for reporting, and validate it with SQL checks.
   - It is best understood as a **batch data engineering pipeline with a dimensional/analytical consumption layer**, built entirely around SQL scripts and static source files.

Main entry points: [`scripts/silver`](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/scripts/silver) (Silver), [`scripts/init_database.sql`](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/init_database.sql) (Init DB), [`docs`](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/docs) (Design docs), [`scripts/bronze/proc_load_bronze.sql`](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/bronze/proc_load_bronze.sql) (Bronze load)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_grp_sources["Source systems"]
  node_n_crm["CRM files<br/>source set"]
  node_n_erp["ERP files<br/>source set"]
end

subgraph group_grp_platform["Warehouse pipeline"]
  node_n_init["Init DB<br/>bootstrap sql<br/>[init_database.sql]"]
  node_n_bronze_ddl["Bronze DDL<br/>raw tables<br/>[ddl_bronze.sql]"]
  node_n_bronze_load["Bronze load<br/>ingestion proc"]
  node_n_bronze[("Bronze<br/>raw landing")]
  node_n_silver_ddl["Silver DDL<br/>clean tables<br/>[ddl_silver.sql]"]
  node_n_silver_load["Silver load<br/>transform proc"]
  node_n_silver[("Silver<br/>curated layer")]
  node_n_gold_ddl["Gold DDL<br/>analytics model<br/>[ddl_gold.sql]"]
  node_n_gold[("Gold<br/>semantic layer")]
end

subgraph group_grp_controls["Validation"]
  node_n_qc_silver["Silver checks<br/>quality sql"]
  node_n_qc_gold["Gold checks<br/>quality sql"]
end

subgraph group_grp_docs["Documentation"]
  node_n_docs["Design docs<br/>guidance"]
  node_n_readme["README<br/>overview<br/>[README.md]"]
end

node_n_crm -->|"load"| node_n_bronze_load
node_n_erp -->|"load"| node_n_bronze_load
node_n_init -->|"create"| node_n_bronze_ddl
node_n_init -->|"create"| node_n_silver_ddl
node_n_init -->|"create"| node_n_gold_ddl
node_n_bronze_ddl -->|"define"| node_n_bronze
node_n_bronze_load -->|"populate"| node_n_bronze
node_n_bronze -->|"feed"| node_n_silver_load
node_n_silver_ddl -->|"define"| node_n_silver
node_n_silver_load -->|"populate"| node_n_silver
node_n_silver -->|"model"| node_n_gold_ddl
node_n_gold_ddl -->|"define"| node_n_gold
node_n_silver -.->|"check"| node_n_qc_silver
node_n_gold -.->|"check"| node_n_qc_gold
node_n_docs -.->|"guide"| node_n_silver
node_n_docs -.->|"guide"| node_n_gold
node_n_readme -.->|"summarize"| node_n_docs

click node_n_crm "https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/datasets/source_crm"
click node_n_erp "https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/datasets/source_erp"
click node_n_init "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/init_database.sql"
click node_n_bronze_ddl "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/bronze/ddl_bronze.sql"
click node_n_bronze_load "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/bronze/proc_load_bronze.sql"
click node_n_bronze "https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/scripts/bronze"
click node_n_silver_ddl "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/silver/ddl_silver.sql"
click node_n_silver_load "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/silver/proc_load_silver.sql"
click node_n_silver "https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/scripts/silver"
click node_n_gold_ddl "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/gold/ddl_gold.sql"
click node_n_gold "https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/scripts/gold"
click node_n_qc_silver "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/tests/quality_checks_silver.sql"
click node_n_qc_gold "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/tests/quality_checks_gold.sql"
click node_n_docs "https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/docs"
click node_n_readme "https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/README.md"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_n_crm,node_n_erp toneBlue
class node_n_init,node_n_bronze_ddl,node_n_bronze_load,node_n_bronze,node_n_silver_ddl,node_n_silver_load,node_n_silver,node_n_gold_ddl,node_n_gold toneAmber
class node_n_qc_silver,node_n_qc_gold toneMint
class node_n_docs,node_n_readme toneRose
```

## Components

### Source systems

Upstream CSV domains feeding the warehouse

- [CRM files](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/datasets/source_crm): source set · CRM CSV inputs for customer, product, and sales data · `datasets/source_crm`
- [ERP files](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/datasets/source_erp): source set · ERP CSV inputs for customer, location, and category data · `datasets/source_erp`

### Warehouse pipeline

SQL Server staging, cleansing, and presentation layers

- [Init DB](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/init_database.sql): bootstrap sql · Database creation and setup entry point · `scripts/init_database.sql`
- [Bronze DDL](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/bronze/ddl_bronze.sql): raw tables · Creates raw landing tables shaped like the source files · `scripts/bronze/ddl_bronze.sql`
- [Bronze load](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/bronze/proc_load_bronze.sql): ingestion proc · Loads CRM and ERP CSV data into the raw layer · `scripts/bronze/proc_load_bronze.sql`
- [Bronze](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/scripts/bronze): raw landing · Minimal-transform landing zone for traceable reprocessing · `scripts/bronze`
- [Silver DDL](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/silver/ddl_silver.sql): clean tables · Defines standardized and integrated warehouse tables · `scripts/silver/ddl_silver.sql`
- [Silver load](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/silver/proc_load_silver.sql): transform proc · Cleanses, validates, and reconciles source data · `scripts/silver/proc_load_silver.sql`
- [Silver](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/scripts/silver): curated layer · Standardized integration layer before analytics · `scripts/silver`
- [Gold DDL](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/scripts/gold/ddl_gold.sql): analytics model · Defines presentation-ready structures for reporting · `scripts/gold/ddl_gold.sql`
- [Gold](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/scripts/gold): semantic layer · Analytics-ready consumption layer for reporting and analysis · `scripts/gold`

### Validation

SQL quality gates for trusted outputs

- [Silver checks](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/tests/quality_checks_silver.sql): quality sql · Validation gates for curated data after transformation · `tests/quality_checks_silver.sql`
- [Gold checks](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/tests/quality_checks_gold.sql): quality sql · Validation gates for analytics-ready outputs · `tests/quality_checks_gold.sql`

### Documentation

Design and naming guidance

- [Design docs](https://github.com/lokesh-141/sql-data-warehouse-project/tree/main/docs): guidance · Catalog, layering, naming, and project notes · `docs`
- [README](https://github.com/lokesh-141/sql-data-warehouse-project/blob/main/README.md): overview · Project summary and warehouse intent · `README.md`

## Connections

- CRM files → Bronze load: load · CRM CSVs are ingested into the raw layer
- ERP files → Bronze load: load · ERP CSVs are ingested into the raw layer
- Init DB → Bronze DDL: create · Bootstrap prepares the database before raw tables are created
- Init DB → Silver DDL: create · Bootstrap prepares the database before curated tables are created
- Init DB → Gold DDL: create · Bootstrap prepares the database before analytics tables are created
- Bronze DDL → Bronze: define · Raw table definitions belong to the bronze layer
- Bronze load → Bronze: populate · Loading procedure populates bronze tables
- Bronze → Silver load: feed · Raw data is transformed into standardized silver structures
- Silver DDL → Silver: define · Silver DDL defines the curated integration layer
- Silver load → Silver: populate · Transformation procedure loads curated tables
- Silver → Gold DDL: model · Curated data informs the analytical presentation model
- Gold DDL → Gold: define · Gold DDL defines reporting-ready structures
- Silver → Silver checks: check · Silver outputs are validated before downstream use
- Gold → Gold checks: check · Gold outputs are validated before analytical consumption
- Design docs → Silver: guide · Documentation supports layer design and naming conventions
- Design docs → Gold: guide · Documentation supports analytical model interpretation
- README → Design docs: summarize · README provides the project-level narrative