FinWealth Advisors required a centralized cloud-based analytics platform to consolidate wealth management data from multiple operational systems into a scalable Data Warehouse for reporting and business intelligence.
This project demonstrates the complete implementation of an end-to-end Azure Data Engineering Solution using:
- Microsoft Azure
- Azure Data Factory (ADF)
- Azure SQL Database
- Azure Blob Storage
- SQL Stored Procedures
- ADF Mapping Data Flows
- Power BI Ready Analytical Views
The platform ingests raw CSV data, validates it, transforms it into a Star Schema Data Warehouse, and prepares analytical datasets for reporting and dashboarding.
CSV Files
↓
Azure Blob Storage
↓
ADF Copy Pipelines
↓
Staging Tables
↓
Validation Layer
↓
Dimension Loading (Stored Procedures)
↓
Fact Loading (ADF Mapping Data Flows)
↓
Star Schema Data Warehouse
↓
Power BI Analytical Views
| Technology | Purpose |
|---|---|
| Azure Blob Storage | Store raw CSV source files |
| Azure SQL Database | Data Warehouse & Staging Layer |
| Azure Data Factory | ETL Orchestration |
| SQL Stored Procedures | Dimension Loading Logic |
| ADF Mapping Data Flows | Fact Table Transformations |
| Power BI | Analytics & Dashboarding |
| GitHub | Version Control |
The project processes 12 CSV files containing wealth management data.
| File | Description |
|---|---|
| Clients.csv | Client information |
| Advisors.csv | Advisor details |
| Accounts.csv | Account information |
| Securities.csv | Security master data |
| Trades.csv | Buy/Sell transactions |
| Holdings.csv | Portfolio holdings |
| Cashflows.csv | Deposits & Withdrawals |
| Fees.csv | Advisory & Management fees |
| Prices.csv | Daily security prices |
| Benchmarks.csv | Benchmark index data |
| Performance.csv | Monthly performance metrics |
| DataDictonary.csv | About What Files Are there |
- Dim_Client
- Dim_Advisor
- Dim_Account
- Dim_Security
- Dim_Benchmark
- dim_date
- Fact_Trades
- Fact_Cashflows
- fact_fees
- fact_holdings
- Fact_Performance
Raw CSV data is loaded into staging tables using ADF Copy Activities.
- Pre-copy TRUNCATE scripts
- Batch tracking
- Source file tracking
- Audit columns
A dedicated validation stored procedure checks:
- NULL primary keys
- Duplicate IDs
- Referential integrity
- Invalid business values
- Row count thresholds
- Identified and removed 400 duplicate TradeIDs from source data.
Dimension tables are loaded using SQL Stored Procedures implementing:
- SCD Type 1 Logic
- Surrogate Key Generation
- Derived Columns
- NULL Handling
- Idempotent Loading
Fact tables are loaded using ADF Mapping Data Flows.
- Multi-source joins
- Lookup transformations
- Derived columns
- Date surrogate key generation
- Business rule implementation
| Data Type | Replacement |
|---|---|
| Text | 'Unknown' |
| Date | '1900-01-01' |
| Numeric | 0 |
| Key Columns | Row Rejected |
- ClientTier
- ExperienceBand
- CurrencyGroup
- AssetClassGroup
- TradeCategory
- TradeSize
- FlowDirection
- PerformanceBand
- Loads CSV files to staging
- Executes dimension stored procedures
- Runs staging validation logic
- Writes to audit/error logs
- Executes all Mapping Data Flows
Master orchestration pipeline controlling the full ETL workflow.
Created 13 analytical SQL views including:
- AUM Summary
- Performance Dashboard
- Advisor Performance
- Product Mix Analysis
- Trade Summary
- Fee Summary
- Risk Analysis
These views are optimized for direct Power BI consumption.
| Table | Rows |
|---|---|
| Dim_Client | 8,000 |
| Dim_Advisor | 350 |
| Dim_Account | 12,000 |
| Dim_Security | 3,000 |
| Fact_Trades | 100,000 |
| Fact_Cashflows | 30,000 |
| fact_fees | 18,000 |
| fact_holdings | 96,027 |
| Fact_Performance | 110,000 |
✅ End-to-End Azure Data Engineering Project ✅ Fully Automated ETL Pipelines ✅ Star Schema Data Warehouse ✅ Surrogate Key Implementation ✅ SCD Type 1 Dimensions ✅ Data Validation Framework ✅ Error Logging & Audit Tracking ✅ Power BI Ready Data Model ✅ Idempotent Pipeline Design ✅ Scalable Architecture
- Hybrid ETL approach using Stored Procedures + Data Flows
- Spark optimization techniques in ADF
- Handling duplicate and bad source data
- Designing scalable star schema models
- Building enterprise-grade validation frameworks
- Creating reusable analytical views
- Azure Data Factory
- Azure SQL Database
- Azure Blob Storage
- ETL Pipelines
- Data Warehousing
- Star Schema
- Slowly Changing Dimensions
- Surrogate Keys
- Data Validation
- Incremental-safe Loads
Helly Patel Data Engineering Project – FinWealth Advisors Wealth Analytics Platform
- Incremental Fact Loading
- SCD Type 2 Implementation
- CI/CD Deployment Pipelines
- Azure Synapse Integration
- Real-time Streaming Support
- Monitoring Dashboards
This repository showcases practical implementation skills in:
- Azure Data Engineering
- ETL Development
- Data Warehousing
- SQL Optimization
- ADF Data Flows
- Analytics Engineering
This project is for academic and portfolio purposes.