# mohammad-raselr/data_warehouse_sql_project architecture

> Architecture diagram of the GitHub repository mohammad-raselr/data_warehouse_sql_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/mohammad-raselr/data_warehouse_sql_project
- Repository: https://github.com/mohammad-raselr/data_warehouse_sql_project

## Overview

1. **Repository purpose**
   - This repo is a **data warehouse + analytics portfolio project**. It is centered on consolidating **ERP CSVs** and **CRM CSVs** into a warehouse model for reporting on customer behavior, product performance, and sales trends.

2. **Current implementation scope**
   - The visible codebase is **mostly SQL scaffolding** rather than a full pipeline implementation.
   - The architecture currently supports **database setup** and a **bronze-layer schema**, while later transformation/analytics layers are not present in the tree.

3. **Entry point / environment bootstrap**
   - `scripts/init_database.sql` is the main bootstrap artifact.
   - This suggests the project assumes a relational database target where the warehouse schema is initialized before loading data.

4. **Raw data domain**
   - `datasets/` is the landing area for source files.
   - Based on the README, this is where the two source systems are represented:
     - **ERP CSV exports**
     - **CRM CSV exports**
   - The project focuses on the **latest dataset only**, so the raw zone appears to be a snapshot-oriented input, not a historized event store.

5. **Bronze layer**
   - `scripts/bronze/ddl_bronze.sql` defines the **bronze layer** schema.
   - This is the first warehouse boundary where raw CSV-shaped data is likely landed in a structured form with minimal transformation.
   - In architecture terms, bronze is the **system-of-record staging area** for source-aligned tables before cleansing/integration.

6. **Data quality and standardization boundary**
   - The README explicitly calls out cleansing of:
     - missing values
     - duplicate records
     - inconsistent formats
   - That implies a later transformation boundary between raw/bronze and the analytical model, even though the repo tree does not yet show the actual cleansing scripts.

7. **Analytical model boundary**
   - The repository’s stated goal is to “combine both source systems into a single, user-friendly analytical data model.”
   - Since only bronze DDL is visible, the higher-level **curated/silver/gold** modeling layer is implied by the README but not implemented in the checked-in structure.

8. **Analytics and reporting layer**
   - The target consumers are SQL-based analytics workloads for:
     - customer behavior
     - product performance
     - sales trends
   - This means the warehouse is intended to be queried directly by analysts/BI tools, likely from a final reporting schema or star-style model.

9. **Documentation boundary**
   - `docs/` exists but currently contains only a placeholder.
   - That indicates the intended architecture includes **data model documentation** for business and analytics stakeholders, but the repository does not yet expose the actual diagrams or data dictionary.

10. **Testing / validation boundary**
   - `tests/` exists but contains only a placeholder.
   - Architecturally, this suggests the project intends some level of validation for schema or transformation logic, but there are no concrete automated tests visible yet.

11. **Notable omissions**
   - There is no visible ETL/ELT orchestration code, no application layer, and no BI dashboard code in the tree.
   - So the repo currently represents the **warehouse schema design and setup layer**, not the full ingestion-to-dashboard delivery path.

12. **How to draw the architecture**
   - A faithful diagram would show:
     - **ERP CSVs** and **CRM CSVs** as source systems
     - a **raw dataset landing area**
     - a **database initialization step**
     - a **bronze warehouse layer**
     - an implied **transformation/modeling layer**
     - and a downstream **SQL analytics/reporting consumer**
   - The strongest concrete boundary in the repo today is between **raw source files** and **warehouse schema definitions** in `scripts/`.

Main entry points: [`datasets/placeholder`](https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/datasets/placeholder) (Dataset landing), [`scripts/init_database.sql`](https://github.com/mohammad-raselr/data_warehouse_sql_project/blob/main/scripts/init_database.sql) (Init DB), [`scripts/bronze/ddl_bronze.sql`](https://github.com/mohammad-raselr/data_warehouse_sql_project/blob/main/scripts/bronze/ddl_bronze.sql) (Bronze DDL), [`docs/placeholder`](https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/docs/placeholder) (Docs)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_sources["Source systems"]
  node_erp_csv["ERP CSVs<br/>source files"]
  node_crm_csv["CRM CSVs<br/>source files"]
  node_datasets[("Dataset landing<br/>raw snapshot area")]
end

subgraph group_warehouse["Warehouse build"]
  node_init_db["Init DB<br/>bootstrap SQL<br/>[init_database.sql]"]
  node_bronze_ddl["Bronze DDL<br/>schema SQL<br/>[ddl_bronze.sql]"]
  node_bronze_layer[("Bronze layer<br/>staging schema")]
  node_cleansing{{"Cleansing<br/>transformation boundary"}}
  node_curated_model[("Analytical model<br/>curated schema")]
end

subgraph group_consumers["Downstream use"]
  node_analytics(("SQL analytics<br/>consumer workload"))
  node_reporting(("Reporting<br/>bi consumer"))
end

subgraph group_quality["Support"]
  node_docs["Docs<br/>documentation area"]
  node_tests["Tests<br/>validation area"]
end

node_erp_csv -->|"land"| node_datasets
node_crm_csv -->|"land"| node_datasets
node_datasets -->|"bootstrap"| node_init_db
node_init_db -->|"create schema"| node_bronze_ddl
node_bronze_ddl -->|"define"| node_bronze_layer
node_datasets -->|"load raw"| node_bronze_layer
node_bronze_layer -->|"standardize"| node_cleansing
node_cleansing -->|"integrate"| node_curated_model
node_curated_model -->|"query"| node_analytics
node_curated_model -->|"serve"| node_reporting
node_docs -.->|"document"| node_curated_model
node_tests -.->|"validate"| node_bronze_layer

click node_datasets "https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/datasets/placeholder"
click node_init_db "https://github.com/mohammad-raselr/data_warehouse_sql_project/blob/main/scripts/init_database.sql"
click node_bronze_ddl "https://github.com/mohammad-raselr/data_warehouse_sql_project/blob/main/scripts/bronze/ddl_bronze.sql"
click node_docs "https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/docs/placeholder"
click node_tests "https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/tests/placeholder"

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_erp_csv,node_crm_csv,node_datasets toneBlue
class node_init_db,node_bronze_ddl,node_bronze_layer,node_cleansing,node_curated_model toneAmber
class node_analytics,node_reporting toneMint
class node_docs,node_tests toneRose
```

## Components

### Source systems

External CSV inputs feeding the warehouse project.

- **ERP CSVs**: source files · ERP export files representing operational sales and product data.
- **CRM CSVs**: source files · CRM export files representing customer and interaction data.
- [Dataset landing](https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/datasets/placeholder): raw snapshot area · Landing area for the latest source CSV datasets. · `datasets/placeholder`

### Warehouse build

Database bootstrap and bronze-layer schema definitions.

- [Init DB](https://github.com/mohammad-raselr/data_warehouse_sql_project/blob/main/scripts/init_database.sql): bootstrap SQL · Database initialization script that sets up the warehouse target. · `scripts/init_database.sql`
- [Bronze DDL](https://github.com/mohammad-raselr/data_warehouse_sql_project/blob/main/scripts/bronze/ddl_bronze.sql): schema SQL · Bronze-layer table definitions for minimally transformed source-aligned data. · `scripts/bronze/ddl_bronze.sql`
- **Bronze layer**: staging schema · First warehouse boundary where raw CSV-shaped data is organized for loading and analysis.
- **Cleansing**: transformation boundary · Implied standardization step for missing values, duplicates, and inconsistent formats.
- **Analytical model**: curated schema · Implied integrated warehouse model combining ERP and CRM data for reporting.

### Downstream use

Analytical and documentation consumers of the warehouse model.

- **SQL analytics**: consumer workload · Query layer for customer behavior, product performance, and sales trend analysis.
- **Reporting**: bi consumer · Downstream reporting and dashboard-style consumption of warehouse data.

### Support

Validation and placeholder project areas.

- [Docs](https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/docs/placeholder): documentation area · Placeholder for model documentation and data dictionary materials. · `docs/placeholder`
- [Tests](https://github.com/mohammad-raselr/data_warehouse_sql_project/tree/main/tests/placeholder): validation area · Placeholder for schema and transformation validation. · `tests/placeholder`

## Connections

- ERP CSVs → Dataset landing: land · ERP exports are placed in the shared dataset landing area.
- CRM CSVs → Dataset landing: land · CRM exports are placed in the shared dataset landing area.
- Dataset landing → Init DB: bootstrap · Loaded after the database target is initialized.
- Init DB → Bronze DDL: create schema · Bootstrap step prepares the warehouse for bronze table creation.
- Bronze DDL → Bronze layer: define · Bronze DDL defines the staging schema.
- Dataset landing → Bronze layer: load raw · Snapshot CSV data is structured into bronze tables.
- Bronze layer → Cleansing: standardize · Bronze data is cleaned and normalized before modeling.
- Cleansing → Analytical model: integrate · Cleansed source-aligned data is combined into the analytical model.
- Analytical model → SQL analytics: query · Analysts query the curated warehouse model directly.
- Analytical model → Reporting: serve · The final model feeds reporting and BI-style consumption.
- Docs → Analytical model: document · Documentation is intended to describe the warehouse model.
- Tests → Bronze layer: validate · Validation would target schema and data-shape assumptions.