Walmart Azure/Fabric Data Engineering Project With Databricks SDP And DAB

Project Overview

This project is an end-to-end, production-oriented data engineering solution built using Azure Data Factory, Azure Data Lake Storage Gen2, Azure Databricks, and Microsoft Fabric. It demonstrates metadata-driven ingestion, incremental and idempotent processing, data quality, monitoring, CI/CD, automated CDC/SCD handling, testing, and cross-platform data consumption.

Architecture & Ingestion

Azure SQL Database acts as the source, with Azure Data Factory (ADF) orchestrating metadata-driven and incremental ingestion into ADLS Gen2. A JSON-based watermark using load_date identifies new and changed records, allowing the same framework to dynamically process multiple tables without hard-coded pipelines. ADF is integrated with GitHub for version control and CI/CD, while Azure Logic Apps provide automated failure monitoring and email alerts.

Data is incrementally ingested from ADLS into the Databricks Bronze layer using Auto Loader. A reusable loop-based framework supports multiple datasets while providing incremental and idempotent ingestion and reducing unnecessary reprocessing.

Silver Layer

The Silver layer performs cleansing, standardisation, enrichment, and preparation for downstream processing. Transformation logic is implemented using Python and OOP, with reusable classes improving modularity, maintainability, and testability.

Key transformations include:

• Null handling and standardisation

• Deduplication

• CDC timestamp enrichment

• Incremental upserts using Delta Lake MERGE INTO

Gold Layer

The Gold layer uses Spark Declarative Pipelines (SDP) and Databricks Auto CDC Flow to automate production-ready curation and implement SCD Type 2 processing for dimensional data. Data quality expectations validate curated datasets before downstream consumption.

Two consumption patterns are provided:

• Star schema: Fact and Dimension tables for BI, reporting, and analytics

• One Big Table (OBT): Denormalised dataset supporting Data Science and Machine Learning workloads

Deployment, Testing & Consumption

Databricks Asset Bundles provide version-controlled deployment of notebooks, configurations, and resources. Unit tests validate the reusable Python transformation classes, while Databricks SQL Warehouse is used to validate curated datasets and demonstrate analytical consumption through dashboards.

The curated Gold data is also exposed through Microsoft Fabric using IAM permissions and OneLake Shortcuts, avoiding unnecessary data duplication. The data is validated within the Fabric Lakehouse and made available through a Fabric Data Warehouse for SQL-based analysis and reporting.

Outcome

The project demonstrates a complete Bronze → Silver → Gold lakehouse architecture, combining metadata-driven ingestion, incremental/idempotent processing, reusable OOP transformations, automated CDC/SCD processing, data quality, monitoring, unit testing, CI/CD, and cross-platform consumption. The platform supports both traditional BI/analytics and Data Science/ML workloads.

Architecture

Overall Project Impact

Key Outcomes & Engineering Value

• End-to-End Automation: Automated metadata-driven and incremental ingestion through transformation and delivery using Azure Data Factory, Databricks Auto Loader, and Spark Declarative Pipelines, reducing manual intervention and improving pipeline reliability.

• Version Control & CI/CD: Managed ADF pipelines, Databricks notebooks, code, and configurations through GitHub, with Databricks Asset Bundles enabling reproducible and consistent deployments across environments.

• Scalability & Maintainability: Built a metadata-driven ingestion framework with Auto Loader supporting incremental processing and schema evolution, alongside reusable OOP transformation classes for modular Silver-layer processing.

• Data Quality & Historical Tracking: Used Delta MERGE for incremental Silver-layer updates and Auto CDC Flow with SCD Type 2 in Gold to maintain reliable historical data while supporting data quality validation.

• Analytics & ML Enablement: Delivered both a Fact/Dimension star schema for BI and analytics and a denormalised One Big Table (OBT) for Data Science and Machine Learning workloads.

• Monitoring & Reliability: Integrated Azure Logic Apps for automated pipeline failure alerts, improving operational visibility and incident response.

• Cross-Platform Consumption: Delivered curated datasets through Databricks SQL Warehouse and Microsoft Fabric, using OneLake Shortcuts to enable cross-platform access without unnecessary data duplication.

Technologies Used

Layer	Technology	Description
Ingestion	Azure Data Factory	Orchestrates metadata-driven and incremental batch ingestion from source systems into the data lake
Storage	Azure Data Lake Storage Gen2	Scalable cloud storage for raw, processed, and curated data across the medallion architecture
Bronze Ingestion	Databricks Auto Loader	Incrementally ingests new files into Databricks with scalable file discovery and schema evolution
Processing	Azure Databricks / Apache Spark	Distributed data processing and transformation across the Bronze, Silver, and Gold layers
Transformations	PySpark & OOP Classes	Reusable, modular transformation logic implemented using object-oriented programming in the Silver layer
Incremental Processing	Delta Lake MERGE	Handles inserts and updates within the Silver layer to maintain current and consistent datasets
Gold Curation	Spark Declarative Pipelines	Manages production-grade Gold transformations and curated datasets
CDC / SCD Handling	Auto CDC Flow / SCD Type 2	Automates change data capture and maintains historical versions of dimension records in the Gold layer
Data Modeling	Star Schema & One Big Table	Provides dimensional Fact/Dimension models for BI and an OBT for Data Science and ML workloads
Monitoring	Azure Logic Apps	Provides pipeline monitoring, failure notifications, and operational alerting
Data Warehouse	Databricks SQL Warehouse & Microsoft Fabric Data Warehouse	Serves curated datasets for SQL analytics, BI, reporting, and downstream consumption
Data Sharing	Microsoft Fabric OneLake Shortcuts	Enables Fabric to consume curated data without unnecessary duplication of the underlying datasets
CI/CD	GitHub & Databricks Asset Bundles	Provides version control and automated, reproducible deployment of Databricks code and resources
Architecture	Medallion Architecture	Organises data into Bronze, Silver, and Gold layers to separate ingestion, transformation, and curated consumption

Phase 1 Data Factory Ingestion

The project began with the creation of a Git repository to enable version control and support the structured deployment of Azure Data Factory artifacts. A dedicated development branch was established to manage feature enhancements and isolate ongoing updates from the main production branch, ensuring a controlled, organized, and collaborative development workflow.

Repository Setup

Linked Service

Git Branch

During the pipeline development, I implemented a JSON-based watermarking strategy to manage the transition from an initial historical backfill to ongoing incremental ingestion.

I created two JSON files to manage this process. The first, cdc.json, stores the current watermark value used by the ingestion pipeline. For the initial load, I configured the watermark to 1900-01-01, a date intentionally set before the earliest available source record, allowing the pipeline to perform the initial historical backfill.

The second JSON file was created as an empty watermark file to support the transition from the initial historical backfill into the incremental ingestion process. Once the historical data had been successfully processed, the watermark could be updated based on the latest processed load_date, allowing subsequent pipeline runs to retrieve only newly available or changed records.

This approach provided a controlled mechanism for moving from historical backfill → incremental ingestion, while maintaining the pipeline's processing state and preventing previously ingested records from being unnecessarily reprocessed.

cdc_json

First Iteration

An IF Condition controls the ingestion workflow based on data availability. Using the @greater(activity('SQLToLake').output.dataRead, 0) expression, the pipeline only processes new records, preventing full dataset reloads and duplicate data during scheduled reruns. The MAX CDC output is used by the update_cdc activity to update last_load.json with the latest processed load_date, maintaining an accurate ingestion watermark. Combined with pipeline parameters, dynamic SQL, JSON watermarking, and ForEach, this creates a reusable framework capable of processing multiple SQL tables with minimal manual intervention.

For Each Pipeline

cdc_column

The pipeline successfully updated the CDC JSON watermark based on the latest process date. I implemented two execution approaches: a manual pipeline allowing users to specify the target table through parameters, and an automated pipeline capable of processing datasets without manual intervention. This created a reusable and scalable framework for multiple Azure SQL tables, supporting incremental processing, state tracking, and controlled data movement while preventing previously processed records from being re-ingested.

Hard Coded Pipeline different from the ForLoop Pipeline

Phase 1.1 Monitoring with Logic Apps

Following the development of the ingestion pipelines, I integrated Azure Logic Apps to provide automated monitoring and alerting for scheduled pipeline runs. This enables pipeline execution status to be tracked and potential failures to be identified promptly, improving the overall reliability and operational visibility of the ingestion process.

Following the development of the ingestion pipelines, I integrated Azure Logic Apps via an API call to monitor the status of scheduled Azure Data Factory pipeline runs. When a pipeline execution fails, the Logic App is triggered and automatically sends an email notification, providing immediate visibility of failures and enabling issues to be identified and addressed promptly.

Web Activity Pipeline Expression

Once the pipeline development was completed, I committed the final implementation to GitHub through our feature branch. I then created a Pull Request (PR) from the development branch into the main branch, allowing the changes to be reviewed and validated before being merged into the main codebase. This provided a controlled deployment workflow and ensured that the completed pipeline was version-controlled and maintained within the project repository.

Creating PR

Phase 2 Bronze Ingestion With AutoLoaders

With the ADF ingestion framework completed, I prepared the Databricks environment for incremental Bronze-layer ingestion using Auto Loader. I established the required Unity Catalog catalogs, external locations, and IAM permissions to provide secure, controlled access to ADLS Gen2. I also initialised Databricks Asset Bundles to manage notebooks, configurations, and resources through version control and consistent deployments across environments. With the environment configured, Databricks was ready for Auto Loader ingestion into Bronze Delta tables.

After creating IAM roles and permissions I proceeded to create external locations in our Databricks external locations.

Gold External Locations

Silver External Locations

Bronze External Locations

I implemented Databricks Auto Loader for Bronze-layer ingestion using a loop-based framework that dynamically processes multiple datasets with the same reusable ingestion logic. Auto Loader provides incremental and idempotent processing, ensuring only new files are ingested while previously processed data is not unnecessarily reprocessed. This created a scalable and automated Bronze ingestion framework that reduced repetitive code and manual intervention while consistently loading datasets into their respective Bronze tables.

For Loop Ingestion With AutoLoaders

Ingestion Graph

Phase 3 Silver Transformation & Enrichment

With the Bronze ingestion successfully implemented using Auto Loader, I moved into the next stage of the project: cleaning, standardising, and enriching the raw datasets within the Silver layer.

To make the transformation process reusable and maintainable, I implemented the transformation logic using Python classes and Object-Oriented Programming (OOP). This allowed common transformation operations to be encapsulated into reusable methods and applied consistently across multiple datasets, reducing code duplication and simplifying future maintenance.

The Silver transformations included handling null values, adding a CDC timestamp column, and deduplicating records to improve the quality and consistency of the data. These transformations prepared the raw Bronze datasets for downstream analytical processing and Gold-layer curation.

For incremental updates, I implemented Delta Lake MERGE INTO operations to perform reliable upserts into the Silver tables. This allowed new records to be inserted while existing records could be updated based on the relevant business keys, ensuring that the Silver layer maintained an accurate and current representation of the source data.

Overall, this stage established a clean, enriched, and incrementally maintained Silver layer, while the use of reusable Python classes provided a modular foundation for applying consistent transformation logic across the entire data platform.

Silver Utils

Functions in our class

Products Enrichment

Customers Enrichment

Employees Enrichment

Stores Enrichment

Orders Enrichment

OrderItems Enrichment

Phase 4 Gold Curated Data With SDP And SCD Type 2 and 1

With the Silver layer successfully created and incrementally maintained, I moved into the Gold layer, where the cleaned datasets were curated into analytics-ready structures for downstream consumption.

I created both Fact and Dimension tables using a dimensional star schema, alongside a One Big Table (OBT) to support alternative analytical and Data Science workloads. For the Dimension tables, I implemented SCD Type 2 to preserve historical changes and maintain a complete history of dimensional records. For the Fact tables and OBT, I implemented SCD Type 1-style upserts, ensuring that the datasets maintained the latest available values without retaining historical versions.

To implement this efficiently, I utilised Spark Declarative Pipelines (SDP) to define and manage the Gold-layer transformations and data flows. This provided a structured and maintainable approach to building the curated datasets while supporting automated processing.

I also implemented data quality expectations within the pipelines to validate the data before it was made available for analytical consumption. These expectations provided controls around the quality and integrity of the curated datasets, helping prevent poor-quality records from being served to downstream consumers.

Overall, this stage transformed the clean Silver datasets into business-ready analytical models, providing both historical dimensional analysis through SCD Type 2 and current-state Fact/OBT datasets for efficient downstream consumption. I also made the Data optimised in the pipeline, using the autoOptimized initaion for SDP

Dim_Customers

Dim Customer's Data Quality

Dim Employee

Dim Employees Data Quality Check

Dim Order

Dim Order's Data Quality Check

Dim Product

Dim Product's Data Quality Check

Dim Stores

Dim Stores Data Quality Check

Fact OrderItems

Fact OrderItmes Data Quality Check

Walmart OBT Data

Walmart OBT Data Quality Check

Expectations were met successfully

The Spark Declarative Pipeline (SDP) executed successfully, with all configured data quality expectations and validation checks passing. This confirmed that the curated Fact, Dimension, and OBT datasets met the required quality standards and were reliable for downstream analytical, Machine Learning, and Data Science workloads.

Data Quality Expectations

Phase 5: Finishing touches and deploying to Fabric Warehouse and SQL Warehouse

With the Gold datasets validated, I completed the deployment and downstream consumption stage. Curated data was exposed through Microsoft Fabric without unnecessary duplication, while unit tests validated the reusable Python utilities and transformation components. Databricks Asset Bundles and GitHub provided version-controlled, repeatable deployments, and the curated datasets were validated in Databricks SQL Warehouse. This completed the delivery of trusted Gold data across Databricks and Microsoft Fabric for BI, analytics, and ML/Data Science workloads.

Gold DataLake write

As the final stage, I implemented an end-to-end orchestration pipeline connecting ingestion → Silver transformation → Gold curation → Gold-layer delivery. A ForEach activity dynamically iterates through the curated datasets, while dbutils.jobs.taskValues.set passes dataset information between tasks, allowing a single writing task to process multiple datasets without separate activities. This created a dynamic, reusable, and maintainable orchestration framework, successfully automating the complete data lifecycle as a unified pipeline.

Gold Datalake Write Run Was Successful

Python Class Testing

As part of deployment, I implemented unit tests for the reusable Python classes used in the Silver-layer transformations, validating individual methods and their expected outputs. All tests passed successfully, confirming the reliability, consistency, and readiness of the transformation components for production deployment.

Test Runs

Github Repository

Attached are the notebooks and Git repository of our project

Walmart Data Engineering Project

Databricks SQL Warehouse Validation

Following the Python unit tests, I validated the curated Fact, Dimension, and OBT datasets in Databricks SQL Warehouse, confirming their availability and expected data following end-to-end pipeline execution. The successful validation confirmed that the Gold datasets were ready for downstream BI, analytics, and Data Science/ML workloads.

Dashboards

Following the successful validation of the curated datasets in Databricks SQL Warehouse, I created dummy dashboards within Databricks to demonstrate the analytical consumption of the Gold-layer data.

Asset Bundles Deployment

Followed afterwards I and deployed our Databricks asset bundles

Loading to Fabric using shortcuts

I integrated the curated Gold-layer data with Microsoft Fabric by configuring the required IAM permissions for secure access to the data lake. I used OneLake Shortcuts instead of physical data replication, allowing Fabric to reference the existing curated datasets while minimising duplication and maintaining a more efficient cross-platform architecture. This successfully exposed the Gold data within Fabric for downstream analytical workloads.

Shortcut Overview

Endpoint(Don't Worry This was DeProvisoned)

DataLake Shortcut

Loading To Table

Data Validation and Dashboard Creation

Within Microsoft Fabric, I created and validated the corresponding Lakehouse tables, confirming the datasets retained their expected structure and values. I then provisioned a Fabric Data Warehouse for structured SQL-based analysis, reporting, and dashboard development, completing the validated flow from the data lake through the Fabric Lakehouse and Warehouse for downstream analytics.

Lesson Learned

The project successfully delivered an end-to-end, production-oriented data platform demonstrating metadata-driven ingestion, incremental processing, scalable transformations, automated CDC/SCD handling, CI/CD, data quality, monitoring, and cross-platform consumption across Databricks and Microsoft Fabric.

A key architectural lesson was the use of MERGE INTO in the Silver layer. While the approach worked successfully, I would structure future implementations with Silver focused primarily on cleansing, deduplication, null handling, standardisation, and business transformations, while centralising SCD Type 1/2 and historical tracking within the curated layer using Auto CDC Flow. This provides clearer separation of responsibilities and avoids duplicating CDC/SCD logic across layers.

Overall, the project strengthened my experience in reusable ingestion frameworks, maintainable transformations, automated CDC/SCD processing, data quality, CI/CD, and cross-platform data delivery, while providing architectural insights that can be applied to build cleaner and more maintainable Databricks lakehouse solutions.