This project is an end-to-end Data Engineering and Data Warehouse project built using SQL Server and SQL Server Management Studio (SSMS).
The project takes raw data from CRM and ERP sources, processes it through a Medallion Architecture (Bronze → Silver → Gold), performs data cleaning and transformation, integrates related datasets, and creates a business-ready analytical model in the Gold layer.
The final Gold layer is organized as a Star Schema with:
gold.dim_customersgold.dim_productsgold.fact_sales
The project was implemented using SQL in SQL Server / SSMS.
CRM DATA ERP DATA
│ │
└─────────┬──────────┘
▼
┌─────────────────┐
│ BRONZE LAYER │
│ Raw Data │
└────────┬────────┘
│
SQL Transformations
│
▼
┌─────────────────┐
│ SILVER LAYER │
│ Cleaned & │
│ Standardized │
│ Data │
└────────┬────────┘
│
SQL Transformations
│
▼
┌─────────────────┐
│ GOLD LAYER │
│ Star Schema │
└────────┬────────┘
│
▼
Analytics / Reporting
The project combines data from two source systems: CRM and ERP.
The CRM source contains:
- Customer information
- Product information
- Sales transaction information
bronze.crm_cust_info
bronze.crm_prd_info
bronze.crm_sales_details
The ERP source contains:
- Customer information
- Customer location information
- Product category information
bronze.erp_cust_az12
bronze.erp_loc_a101
bronze.erp_px_cat_g1v2
The Bronze layer is the raw data layer of the project.
Source data is loaded into Bronze tables before applying the main transformation and cleansing logic.
| Table | Description |
|---|---|
bronze.crm_cust_info |
CRM customer data |
bronze.crm_prd_info |
CRM product data |
bronze.crm_sales_details |
CRM sales data |
bronze.erp_cust_az12 |
ERP customer data |
bronze.erp_loc_a101 |
ERP location data |
bronze.erp_px_cat_g1v2 |
ERP product category data |
scripts/bronze/
├── ddl_bronze.sql
└── proc_load_bronze.sql
The Silver layer contains cleaned, standardized, and transformed data.
The Bronze-to-Silver ETL process is implemented through:
silver.load_silver
The procedure refreshes the Silver tables and loads transformed data from the Bronze layer.
For silver.crm_cust_info, the project:
- Removes duplicate customer records
- Keeps the latest customer record using
ROW_NUMBER() - Removes unwanted spaces from first and last names
- Standardizes marital status
- Standardizes gender values
- Handles missing or unknown values
S → Single
M → Married
Other → n/a
F → Female
M → Male
Other → n/a
For silver.crm_prd_info, the project:
- Extracts and transforms category IDs
- Transforms product keys
- Handles missing product costs
- Standardizes product-line values
- Converts product dates to the required date format
- Creates product validity periods using
LEAD()
The product end date is derived from the next product start date.
Current Product Start Date
↓
Next Product Start Date
↓
End Date = Next Start Date - 1 day
For silver.crm_sales_details, the project:
- Validates order, shipping, and due dates
- Converts valid dates into date values
- Handles invalid date values
- Validates sales amounts
- Recalculates incorrect sales amounts using quantity and price
- Handles invalid prices
- Derives price when required
The sales validation follows the relationship:
Sales Amount = Quantity × Price
For silver.erp_cust_az12, the project:
- Standardizes customer IDs
- Removes the
NASprefix where required - Handles invalid future birth dates
- Standardizes gender values
Example:
NAS12345
↓
12345
For silver.erp_loc_a101, the project:
- Removes hyphens from customer IDs
- Standardizes country values
- Handles missing country values
Examples:
DE → Germany
US → United States
USA → United States
For silver.erp_px_cat_g1v2, the project loads the product category information into the Silver layer for integration with CRM product data.
The Silver loading process follows a full-refresh approach.
Bronze Tables
↓
TRUNCATE Silver Tables
↓
Transform & Clean Data
↓
Insert Into Silver Tables
↓
Silver Layer
The procedure also includes:
- Batch start and end time
- Table-level load duration
- Error handling using
TRY...CATCH
scripts/silver/
├── ddl_silver.sql
└── proc_load_silver.sql
The Gold layer contains the final business-ready analytical views.
The Gold layer combines information from the Silver tables and presents it in a Star Schema structure.
gold.dim_customers
gold.dim_products
gold.fact_sales
The customer dimension combines data from:
silver.crm_cust_info
+
silver.erp_cust_az12
+
silver.erp_loc_a101
It contains:
- Customer key
- Customer ID
- Customer number
- First name
- Last name
- Country
- Marital status
- Gender
- Birth date
- Customer creation date
A customer_key is generated using:
ROW_NUMBER() OVER (ORDER BY cst_id)This provides a warehouse-level key for analytical relationships.
The Gold customer view uses CRM gender first and falls back to ERP gender when the CRM value is unavailable.
CRM Gender
↓
If unavailable
↓
ERP Gender
↓
If unavailable
↓
n/a
The product dimension combines:
silver.crm_prd_info
+
silver.erp_px_cat_g1v2
It contains:
- Product key
- Product ID
- Product number
- Product name
- Category ID
- Category
- Subcategory
- Maintenance
- Cost
- Product line
- Start date
Only currently active products are included:
WHERE prd_end_dt IS NULLA product_key is generated using ROW_NUMBER().
The sales fact combines sales transactions with the customer and product dimensions.
silver.crm_sales_details
│
├──────────────► gold.dim_products
│ │
│ ▼
│ product_key
│
└──────────────► gold.dim_customers
│
▼
customer_key
The fact contains:
| Column | Description |
|---|---|
order_number |
Sales order number |
product_key |
Product dimension key |
customer_key |
Customer dimension key |
order_date |
Order date |
shipping_date |
Shipping date |
due_date |
Due date |
sales_amount |
Sales amount |
quantity |
Quantity sold |
price |
Selling price |
┌────────────────────┐
│ dim_customers │
│────────────────────│
│ customer_key │
│ customer_id │
│ customer_number │
│ first_name │
│ last_name │
│ country │
│ marital_status │
│ gender │
│ birthdate │
│ create_date │
└─────────┬──────────┘
│
│ customer_key
▼
┌───────────────┐
│ fact_sales │
│───────────────│
│ order_number │
│ product_key │
│ customer_key │
│ order_date │
│ shipping_date │
│ due_date │
│ sales_amount │
│ quantity │
│ price │
└───────┬───────┘
│
│ product_key
▼
┌────────────────────┐
│ dim_products │
│────────────────────│
│ product_key │
│ product_id │
│ product_number │
│ product_name │
│ category_id │
│ category │
│ subcategory │
│ maintenance │
│ cost │
│ product_line │
│ start_date │
└────────────────────┘
The Gold layer is implemented as SQL views that provide a Star Schema-style analytical model over the Silver data.
CRM + ERP Source Data
↓
Bronze Layer
↓
Data Cleaning
↓
Data Standardization
↓
Data Validation
↓
Data Integration
↓
Silver Layer
↓
Customer Dimension
Product Dimension
Sales Fact
↓
Gold Layer
↓
Analytics / Reporting
- Microsoft SQL Server
- SQL Server Management Studio (SSMS)
- SQL
- Stored Procedures
- SQL Views
- GitHub
- Draw.io for architecture diagrams
The complete ETL and transformation logic for this project was implemented using SQL in SQL Server.
.
├── architecture/
│ └── data_architecture.png
│
├── datasets/
│
├── docs/
│ ├── data_flow_diagram.drawio (1).png
│ └── placeholder
│
├── scripts/
│ ├── bronze/
│ │ ├── ddl_bronze.sql
│ │ └── proc_load_bronze.sql
│ │
│ ├── silver/
│ │ ├── ddl_silver.sql
│ │ └── proc_load_silver.sql
│ │
│ ├── gold/
│ │ └── ddl_gold.sql
│ │
│ └── init_database.sql
│
└── README.md
This project demonstrates practical experience with:
- ETL pipeline development
- Data Warehousing
- Medallion Architecture
- Bronze / Silver / Gold layers
- Data cleaning
- Data validation
- Data standardization
- Data integration
- CRM and ERP data integration
- Stored procedures
- SQL views
- Fact and dimension modeling
- Star Schema
- Surrogate keys
- Business keys
- Window functions
- Full-refresh data loading
- Error handling
- Batch/load monitoring
The Gold layer can support analysis such as:
- Customer distribution by country
- Customer demographics
- Customer sales contribution
- Customer segmentation
- Product performance
- Category performance
- Subcategory performance
- Product-line analysis
- Product cost analysis
- Total sales
- Sales by customer
- Sales by product
- Sales trends
- Quantity sold
- Price analysis
Possible future improvements include:
- Incremental data loading
- Change Data Capture (CDC)
- ETL audit and logging tables
- Automated data-quality checks
- Query optimization and indexing
- Pipeline scheduling
- Power BI reporting
- Monitoring and alerting
Siddhartha Reddy
Aspiring Data Engineer
Skills: SQL • Data Engineering • ETL • Data Warehousing • Data Analytics
This project demonstrates an end-to-end SQL-based Data Engineering pipeline that transforms CRM and ERP source data into clean, integrated, and analytics-ready data using:
Bronze → Silver → Gold
The final Gold layer provides a Star Schema-style analytical model consisting of:
gold.dim_customers
gold.dim_products
gold.fact_sales