lokesh-141/sql-data-warehouse-project

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.