An end-to-end Azure Data Engineering / Lakehouse project that ingests insurance-related data from multiple sources, processes it through a Medallion Architecture (Bronze → Silver → Gold), and prepares curated datasets for analytics and reporting.
The project was built using Azure Data Factory (ADF), Azure Data Lake Storage Gen2 (ADLS Gen2), Azure Databricks, PySpark, SQL, Azure SQL Database, Unity Catalog, and Power BI.
The objective of this project is to build a scalable insurance data pipeline capable of integrating data from different sources and transforming it into analytics-ready datasets.
The system handles data related to:
- Insurance policies
- Customers
- Claims
- Agents
- Branches
- Weather/contextual data
The final Gold-layer datasets are designed to support business reporting and analytics in Power BI.
flowchart LR
A[REST API<br/>Weather Data]
B[CSV Files<br/>Customer Data]
C[JSON Files<br/>Policy Data]
D[Azure SQL Database<br/>Branch / Claim / Agent]
A --> E[Azure Data Factory]
B --> E
C --> E
D --> E
E --> F[ADLS Gen2<br/>Landing / Raw Data]
F --> G[Bronze Layer<br/>Raw Data]
G --> H[Azure Databricks<br/>PySpark / SQL]
H --> I[Silver Layer<br/>Cleaned & Validated Data]
I --> J[Gold Layer<br/>Business-Ready Data]
J --> K[Power BI<br/>Analytics & Reporting]
The Bronze layer stores the source data with minimal transformation.
Typical sources include:
- Customer CSV files
- Policy JSON files
- Weather API data
- Branch data
- Claim data
- Agent data
Bronze data is stored in Azure Data Lake Storage Gen2.
Example logical structure:
ADLS Gen2
│
├── landing/
│ ├── CustomerData/
│ ├── PolicyData/
│ ├── AgentData/
│ ├── BranchData/
│ └── ClaimData/
│
├── processed/
│ └── ...
│
└── bronzelayer/
├── Customer/
├── Policy/
├── Agent/
├── Branch/
└── Claim/
The exact physical folder names can vary depending on the final deployment.
The Silver layer contains cleansed, validated, standardized, and transformed datasets.
Transformations include:
- Data type standardization
- Null handling
- Data cleansing
- Filtering invalid records
- Column selection
- Data validation
- Joining related datasets
- Formatting dates
- Preparing data for downstream analytics
The Silver layer is registered in Databricks Unity Catalog.
Example:
policysystemdatabricks
│
└── silverlayer
├── agent
├── branch
├── claim
└── customer
The Gold layer contains curated datasets designed for business analysis and reporting.
Examples of analytical outputs created during the project include:
sales_by_policy_type_and_month
claims_by_policy_type_and_status
claims_analysis
These datasets can be consumed by Power BI for dashboards and business reporting.
Source Data Technology
REST API Weather data REST API CSV Customer information CSV JSON Policy information JSON Azure SQL Database Branch information Azure SQL Azure SQL Database Claim information Azure SQL Azure SQL Database Agent information Azure SQL
Used for:
- Data ingestion
- Pipeline orchestration
- Copy activities
- Connecting different data sources
- Moving data between source systems and ADLS
- Scheduling/orchestrating data workflows
Used as the central cloud storage layer for:
- Raw/landing data
- Bronze data
- Processed data
- Lakehouse datasets
The project uses the ADLS Gen2 / hierarchical namespace capability.
Used for:
- Data transformation
- Data cleansing
- PySpark processing
- SQL transformations
- Silver-layer processing
- Gold-layer analytical transformations
Used for distributed data processing and transformations inside Databricks.
Used for:
- Querying datasets
- Data validation
- Transformations
- Creating and querying analytical datasets
- Working with Azure SQL Database and Databricks SQL
Used for centralized management and discovery of Databricks data assets.
Example catalog structure:
Catalog
└── policysystemdatabricks
├── bronzelayer
├── silverlayer
└── information_schema
Used as the downstream analytics and visualization layer for the curated Gold datasets.
Source Systems
│
▼
Azure Data Factory
│
▼
ADLS Gen2 - Landing
│
▼
Bronze Layer
│
▼
Azure Databricks
│
├── PySpark
└── SQL
│
▼
Silver Layer
│
▼
Gold Layer
│
▼
Power BI
A logical representation of the project is:
Insurance-Policy-Data-System/
│
├── README.md
│
├── Azure-Data-Factory/
│ ├── Pipelines/
│ ├── Datasets/
│ ├── Linked-Services/
│ └── Triggers/
│
├── Azure-Databricks/
│ ├── Bronze/
│ │ ├── Customer/
│ │ ├── Policy/
│ │ ├── Agent/
│ │ ├── Branch/
│ │ └── Claim/
│ │
│ ├── Silver/
│ │ ├── Customer/
│ │ ├── Policy/
│ │ ├── Agent/
│ │ ├── Branch/
│ │ └── Claim/
│ │
│ └── Gold/
│ ├── sales_by_policy_type_and_month/
│ ├── claims_by_policy_type_and_status/
│ └── claims_analysis/
│
├── ADLS-Gen2/
│ ├── landing/
│ ├── processed/
│ ├── bronzelayer/
│ └── silverlayer/
│
├── Azure-SQL/
│ └── database-scripts/
│
└── Power-BI/
└── dashboards/
This represents the logical project organization; the actual Azure resource hierarchy may differ.
Azure Data Factory connects to the different source systems.
REST API
CSV
JSON
Azure SQL
│
▼
Azure Data Factory
ADF copies source data into ADLS Gen2.
Source
↓
ADLS Gen2 / Landing
Raw source data is organized into the Bronze layer.
Landing
↓
Bronze
The objective is to preserve source-level information and provide a reliable starting point for transformation.
Databricks processes the Bronze data using PySpark and SQL.
Typical operations include:
Read
↓
Clean
↓
Validate
↓
Transform
↓
Join
↓
Write
The resulting datasets are stored as Silver-layer data and registered in Unity Catalog where applicable.
Business-focused transformations are performed to create analytics-ready datasets.
Examples:
- Sales by policy type and month
- Claims by policy type and status
- Claims analysis
Gold datasets can be consumed by Power BI to build dashboards and reports.
The Databricks environment contains a catalog/workspace structure similar to:
policysystemdatabricks
│
├── bronzelayer
│
├── default
│
├── information_schema
│
└── silverlayer
│
└── Tables
├── agent
├── branch
├── claim
└── customer
The Bronze data originally exists as files in ADLS Gen2, while the Silver datasets are registered as tables in Unity Catalog.
The project demonstrates several common Data Engineering transformations:
- Reading CSV, JSON, and Parquet-based data
- Reading data from Azure SQL Database
- Processing API-based data
- Schema inspection
- Data type conversion
- Null handling
- Data filtering
- Data cleansing
- Joining datasets
- Aggregation
- Date-based analysis
- Creation of analytical datasets
- Registering datasets as tables
- Preparing curated data for BI consumption
Sales_by_policy_type_and_month
Used to analyze insurance sales/revenue across:
- Policy types
- Months
- Time periods
Claims_by_policy_type_and_status
Used to analyze claims based on:
- Policy type
- Claim status
- Claim volume
Claims_analysis
Provides a curated dataset for deeper analysis of insurance claims and related dimensions.
The project uses Azure-native access and authentication mechanisms to connect services.
Relevant Azure components include:
- Azure subscription
- Resource groups
- Azure Storage access controls
- Databricks access
- Unity Catalog
- Azure Data Factory linked services
For production environments, secrets and credentials should preferably be stored in a secure secret-management solution such as Azure Key Vault, rather than hard-coded in notebooks.
This project demonstrates practical experience with:
- ETL/ELT pipelines
- Data ingestion
- Data lake architecture
- Medallion architecture
- Data transformation
- Data quality/validation
- Data integration
- Azure Data Factory
- Azure Databricks
- ADLS Gen2
- Azure SQL Database
- Unity Catalog
- Power BI integration
- Python
- PySpark
- SQL
- DataFrame transformations
- Bronze/Silver/Gold architecture
- Lakehouse concepts
- Cloud-based data pipelines
- Separation of raw, curated, and analytical data
The project provides an end-to-end cloud data engineering solution for an insurance policy system.
The completed workflow demonstrates how data can be:
Ingested
↓
Stored
↓
Processed
↓
Validated
↓
Transformed
↓
Curated
↓
Analyzed
using Azure-native services and a scalable Lakehouse architecture.
Potential production-level improvements include:
- Implement Azure Key Vault for secret management
- Replace storage-account-key authentication with managed identity where appropriate
- Add automated data-quality checks
- Add incremental ingestion
- Implement pipeline failure/retry handling
- Add monitoring and alerting
- Add CI/CD using GitHub/Azure DevOps
- Implement Delta Lake optimization
- Add data lineage and governance
- Build Power BI dashboards on top of Gold datasets
- Introduce parameterized and metadata-driven pipelines
Insurance Policy Data System is an end-to-end Azure Data Engineering project that demonstrates the implementation of a cloud-based Medallion Lakehouse architecture.
Multiple data sources are ingested through Azure Data Factory, stored in ADLS Gen2, transformed using Azure Databricks/PySpark/SQL, organized into Bronze, Silver, and Gold layers, registered and governed using Unity Catalog, and prepared for downstream Power BI analytics.
Azure Data Factory
│
▼
Azure Data Lake Storage Gen2
│
▼
Azure Databricks
┌────┴────┐
│ │
PySpark SQL
│ │
└────┬────┘
▼
Unity Catalog
│
▼
Gold Layer
│
▼
Power BI
Urja Mehta
Azure Data Engineering Project