How mohammad-raselr/data_warehouse_sql_project works
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 Dataset landing · init_database.sql Init DB · ddl_bronze.sql Bronze DDL · docs/placeholder Docs
Components 12
Source systems
External CSV inputs feeding the warehouse project.
- ERP CSVs · ERP export files representing operational sales and product data.
- CRM CSVs · CRM export files representing customer and interaction data.
- Dataset landing · Landing area for the latest source CSV datasets.
datasets/placeholder
Warehouse build
Database bootstrap and bronze-layer schema definitions.
- Init DB · Database initialization script that sets up the warehouse target.
scripts/init_database.sql - Bronze DDL · Bronze-layer table definitions for minimally transformed source-aligned data.
scripts/bronze/ddl_bronze.sql - Bronze layer · First warehouse boundary where raw CSV-shaped data is organized for loading and analysis.
- Cleansing · Implied standardization step for missing values, duplicates, and inconsistent formats.
- Analytical model · Implied integrated warehouse model combining ERP and CRM data for reporting.
Downstream use
Analytical and documentation consumers of the warehouse model.
- SQL analytics · Query layer for customer behavior, product performance, and sales trend analysis.
- Reporting · Downstream reporting and dashboard-style consumption of warehouse data.
Connections 12
- 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.