This Data Engineering project showcases the extensive capabilities of Azure Databricks, Azure Data Factory (ADF), and Azure Data Lake, while highlighting key data engineering practices such as Slowly Changing Dimensions SCD Type 1(Manually) and Type 2(Automatic via DLT), Star Schema design, and incremental loading. My approach not only involved ingesting source data efficiently using Spark Structured Streaming but also designing robust SCD Type 1 pipelines. After transforming and curating the data from the staging layer to the core layer, the final datasets were delivered to both Azure Synapse Analytics and Databricks SQL Warehouse, making them fully prepared for analytics and reporting. Azure Services: Azure Databricks Azure Data Factory (ADF) Azure Data Lake Storage (ADLS Gen2) Azure Synapse Analytics Databricks Components: Delta Lake & Delta Tables Delta Live Tables (DLT for SCD Type 2 automation) Databricks SQL Warehouse Languages & Tools: Python (PySpark / notebooks) SQL Spark Structured Streaming (for incremental loads) For the detailed Storytelling of this project visit this link: https://www.jesseportfolio.co.uk/post/azure-databricks-end-to-end-dataengineering-project-with-azure-devops Group 5

## Creating And Ingesting Orders Source Data with ADF and Azure Devops Screenshot 2025-10-06 at 16 51 30

To kickstart the project, I first logged into the Azure DevOps organization page, where I set up the project details and created a dedicated branch to begin ingesting the source raw data with Azure Data Factory. ## Configuring The Repository In DataFactory After creating the branch, I switched back to DataFactory to configure the repository setup, preparing the environment for the data ingestion process. Screenshot 2025-10-06 at 16 53 38

I ingested the data using the Lookup and ForEach activities in Azure Data Factory, transforming the source files from CSV to Parquet format during the process. Once the ingestion workflow was successfully executed, I planned to merge this branch back into the main repository. Screenshot 2025-10-06 at 17 39 51

## Pipeline Template Screenshot 2025-10-06 at 17 49 52

## Commits Screenshot 2025-10-06 at 17 42 58

## MergeIllustration Screenshot 2025-10-06 at 18 07 46

Now that our source data is stored in the Data Lake, it’s time to kickstart the main phase of the project by loading the data into the staging layer — our Bronze container — where raw datasets are initially processed and prepared for further transformation. ### Phase 1 Ingesting Data to the Staging Layer Using Spark Structured Streaming Before kickstarting the ingestion process in Databricks, I first created a Catalog to organize and manage our project’s data assets. Additionally, I configured External Locations to establish secure connections between Databricks and our containers within the Azure Data Lake(Unity Catalog is already enabled in my databricks as shown in the image). Screenshot 2025-10-07 at 19 43 05

## Ingestion With AutoLoaders The ingestion of data using Spark Structured Streaming was successful on the first attempt, as I was able to load data from the source container into the bronze container. One of the key advantages of Spark Structured Streaming is its support for incremental data loading, which I implemented in this project. By setting the trigger to once=True, the stream processes new data automatically as soon as it lands in the Data Lake, while previously processed data is tracked and managed through the defined Checkpoint Location. Screenshot 2025-10-07 at 21 43 48

## First Ingestion(Our Fact Table) The first data ingestion focused on our fact table—though not fully defined at this stage. As shown, the initial dataset contained 9,990 rows. To demonstrate incremental loading, I added a new file with 10 additional rows to the bronze container in the Data Lake. Once the new file landed, Spark Structured Streaming automatically detected and processed it, seamlessly updating the dataset incrementally without reprocessing existing data. Screenshot 2025-10-07 at 22 00 30

## After adding Our Data After the addition of our new data Spark Streaming Ingested the Incremental load successfully also ofcourse creating a new column for rescued data(For data that are not aligned to the schema of our table) Screenshot 2025-10-07 at 22 03 47

## Preparing Incremental Data Screenshot 2025-10-07 at 23 31 56

## Ingesting our other Data With Parameter Notebooks Using dbutils.widgets.text(), I parameterized the notebook to enable flexible and efficient ingestion of all datasets in a single run. By leveraging a ForEach loop, I automated the loading of data into the Data Lake, ensuring each table—including newly added datasets—was ingested into its respective location. The Regions table, however, was excluded from this process, as it lacked meaningful context and relevance for our curated tables and core layer. Screenshot 2025-10-07 at 22 30 23

## Our Load Was Successful Screenshot 2025-10-07 at 23 26 42

As shown with the Region table, no changes were detected since no new data was added—demonstrating that our incremental load process worked successfully. Screenshot 2025-10-07 at 23 38 25

### Phase 2 Transformations After performing the necessary transformations for our enriched (Silver) layer, particularly on the Orders (Fact) table and two of our Dimension tables, I proceeded to implement Slowly Changing Dimension (SCD) Type 1. In this case, I chose to handle it manually by splitting the DataFrames into old and new datasets to create a pseudo table. This approach allowed me to efficiently track Dimension Keys during incremental loads and easily determine the maximum DimensionKey value without complications. ## Manual SCD TYPE 1(UPSERT) Screenshot 2025-10-09 at 03 48 18

## Automatic SCD TYPE 2 Using Delta Live Tables (DLT), a declarative ETL framework, I was able to automate SCD Type 2 logic with ease. Unlike Type 1, which I handled manually using incremental loads and a pseudo table, DLT simplified the process for Type 2, ensuring reliable and efficient tracking of historical changes whilst also setting data quality checks to improve data standards. Despite the manual approach for Type 1, the solution was successful and met the project requirements. As seen there were no dropped data so our data quality check is a success Screenshot 2025-10-09 at 06 34 09

### Phase 3 Serving(Star Schema) With our two Dimension tables loaded, the next step was to finalize the Star Schema by preparing the Fact Table. Once complete, the curated datasets were delivered to Databricks SQL Warehouse and Azure Synapse Analytics, making them fully ready for analysis, reporting, and business intelligence. After the finalisation of Our Star Schema I applied SCD Type 1 Logic on it for Upserts as it is a big table and updating and inserting of new data is plausible Screenshot 2025-10-09 at 11 50 21

Now that our data has been loaded, transformed, and fully curated, it is ready for analysis and reporting. ### Databricks SQL Warehouse I tested out our curated datsets with the SQL Warehouse playing with Queries and creating dashboards. Screenshot 2025-10-09 at 07 46 53

### BI Partner Connect Thanks to Databricks Partner Connect, I was able to provide the BI connector to Data Analysts, enabling them to directly query and visualize the cleaned data in Power BI with ease—without needing to rely on the SQL Data Warehouse. Followed after was loading my data in Synapse Warehouse thus, the conclusion of my project. Screenshot 2025-10-09 at 07 48 18

### Loading In Synapse To load our curated data, I used CETAS functions and OPENROWSET to create External Tables, carefully defining the data sources and file formats to ensure accurate and efficient table creation. Code-> --- CREATE MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = '' ---- Database Scoped Credentials CREATE DATABASE SCOPED CREDENTIAL jesscreds WITH IDENTITY = 'Managed Identity' ---External File Format CREATE EXTERNAL FILE FORMAT parquet WITH ( FORMAT_TYPE = PARQUET, DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec' ) CREATE EXTERNAL FILE FORMAT delta WITH ( FORMAT_TYPE = DELTA ) Screenshot 2025-10-09 at 09 00 48

Screenshot 2025-10-09 a![Uploading Screenshot 2025-10-09 at 09.00.48.png…](https://github.com/JessePepple/Azure-Databricks-End-to-End-DataEngineering-Project-With-Azure-Devops/raw/main/)
t 08 57 19