Building a modern data warehouse with SQL Server and Python, including ETL processes and data modeling.
Develop a modern data warehouse using SQL Server and Python to consolidate sales data, enabling analytical reporting and informed decision-making. In the script folder, you'll find both the Python and SQL Server that code for the same tasks.
- Data Sources: Import data from two source systems (ERP and CRM) provided as CSV files.
- Data Quality: Cleanse and resolve data quality issues prior to analysis.
- Integration: Combine both sources into a single, user-friendly data model designed for analytical queries.
- Scope: Focus on the latest dataset only; historization of data is not required.
- Documentation: Provide clear documentation of the data model to support both business stakeholders and analytics teams.
The architecture is composed of 3 layer:
- Raw Layer: here there are all the table exactly as they come from the sources. This steps is necessary to help debugging and traceability. There won’t be any type of cleaning or transformation applied.
- Clean Layer: in this step each table is cleaned and transformed. There are actions of standardization, normalization and creation of derived columns. This step is necessary to prepare the data for the analysis, but not aggregation is used yet.
- Report Layer: in this step the data are aggregated in more simple and useful tables (views are used). For the data modeling, the schema used is the Star schema. This is the actual data used for reporting and analysis, where all the info are organised in the best way to support it.
This is the compete flow of the data in all 3 layers:
The 6 tables (3 from CRM and 3 from ERP) are uploaded with a Truncate & Insert method.
The 6 tables keep the same structure as the original ones, but the columns are cleaned, standardized, normalized. There is the creation of derived columns and all the ids are made consistent between the tables to permit future joins.
They are uploaded with a Truncate & Insert method.
From the 6 starting tables we’ll derive 3 views:
- crm_prd_info and erp_px_catg1v2 will describe the products
- crm_sales_details, erp_loc_a101 and erp_cust_az12 will describe the customers
- crm_sales_details will describe the orders
The Star Schema used is organized as follow:
- report.dim_customers
- Purpose: Stores customer details enriched with demographic and geographic data.
- Columns:
| Column Name | Data Type | Description |
|---|---|---|
| customer_key | INT | Surrogate key uniquely identifying each customer record in the dimension table. |
| customer_id | INT | Unique numerical identifier assigned to each customer. |
| customer_number | NVARCHAR(50) | Alphanumeric identifier representing the customer, used for tracking and referencing. |
| first_name | NVARCHAR(50) | The customer's first name, as recorded in the system. |
| last_name | NVARCHAR(50) | The customer's last name or family name. |
| country | NVARCHAR(50) | The country of residence for the customer (e.g., 'Australia'). |
| marital_status | NVARCHAR(50) | The marital status of the customer (e.g., 'Married', 'Single'). |
| gender | NVARCHAR(50) | The gender of the customer (e.g., 'Male', 'Female', 'Unknown'). |
| birthdate | DATE | The date of birth of the customer formatted as YYYY-MM-DD (e.g., 1971-10-06). |
| create_date | DATE | The date and time when the customer record was created in the system. |
- report.dim_products
- Purpose: Provides information about the products and their attributes.
- Columns:
| Column Name | Data Type | Description |
|---|---|---|
| product_key | INT | Surrogate key uniquely identifying each product record in the product dimension table. |
| product_id | INT | A unique identifier assigned to the product for internal tracking and referencing. |
| product_number | NVARCHAR(50) | A structured alphanumeric code representing the product, often used for categorization or inventory. |
| product_name | NVARCHAR(50) | Descriptive name of the product, including key details such as type, color, and size. |
| category_id | NVARCHAR(50) | A unique identifier for the product's category, linking to its high-level classification. |
| category | NVARCHAR(50) | The broader classification of the product (e.g., Bikes, Components) to group related items. |
| subcategory | NVARCHAR(50) | A more detailed classification of the product within the category, such as product type. |
| maintenance_required | NVARCHAR(50) | Indicates whether the product requires maintenance (e.g., 'Yes', 'No'). |
| cost | INT | The cost or base price of the product, measured in monetary units. |
| product_line | NVARCHAR(50) | The specific product line or series to which the product belongs (e.g., Road, Mountain). |
| start_date | DATE | The date when the product became available for sale or use, stored in the system. |
- report.fact_sales
- Purpose: Stores transactional sales data for analytical purposes.
- Columns:
| Column Name | Data Type | Description |
|---|---|---|
| order_number | NVARCHAR(50) | A unique alphanumeric identifier for each sales order (e.g., 'SO54496'). |
| product_key | INT | Surrogate key linking the order to the product dimension table. |
| customer_key | INT | Surrogate key linking the order to the customer dimension table. |
| order_date | DATE | The date when the order was placed. |
| shipping_date | DATE | The date when the order was shipped to the customer. |
| due_date | DATE | The date when the order payment was due. |
| sales_amount | INT | The total monetary value of the sale for the line item, in whole currency units (e.g., 25). |
| quantity | INT | The number of units of the product ordered for the line item (e.g., 1). |
| price | INT | The price per unit of the product for the line item, in whole currency units (e.g., 25). |
The SQL Server scripts where done following the project by Baraa on YouTube.