Data Migration with Azure Synapse, PySpark, and Delta Tables

This project sets up an end-to-end data pipeline that transforms and processes historical data from a SQL Database into a structured format in Azure Synapse Analytics, utilizing Azure Data Lake Storage (ADLS) and Delta Tables for efficient storage and querying.

Below is the architecture diagram showcasing how data flows through the different components:

Tech Stack

• Python
• Azure SQL Database
• T-SQL (Transact-SQL)
• Azure Synapse Analytics
• Azure Data Lake Storage (ADLS)
• Azure Logic App
• Azure Notebook
• PySpark
• Delta Tables

Table of Contents

1. Pipeline Overview
2. Prerequisites
3. Project Structure
4. How to Run
5. Pipeline Activities
6. Parameters
7. Pipeline Steps
8. Validation Checklist
9. Summary of Flow

Pipeline Overview

The data pipeline consists of the following activities:

For detailed activity descriptions, see Pipeline Activities.

The data pipeline consists of the following stages:

• Bronze Layer: Raw, unprocessed data directly coming from each table stored in the Azure SQL database. All these tables are stored in Parquet format in Azure Data Lake Storage (ADLS) for further processing.
• Silver Layer: Cleaned and transformed data stored as Delta Tables for optimized querying and performance.
• Gold Layer: The final, optimized dataset containing dimension and fact tables, designed for high-performance analytics and reporting.

Prerequisites

Ensure the following services and tools are available before running this project:

• Azure SQL Database with a server and database where the fintech schema can be created.
• Azure Data Lake Storage (ADLS Gen2) with containers/folders for bronze, silver, and gold layers.
• Azure Synapse Analytics workspace with pipeline and notebook support.
• Apache Spark pool in Synapse for notebook execution.
• Azure Logic App configured to send email notifications for success/failure events.
• Permission access to create/edit SQL objects, Synapse pipelines/notebooks, and ADLS data paths.

Project Structure

Fintech_GitHub_Project/
├── assets/images/                     # Architecture, pipeline, and notification screenshots
├── notebooks/
│   ├── BronzeToSilver_ETL.ipynb       # ETL from raw parquet to delta (silver)
│   └── SilverToGold_ETL.ipynb         # ETL from silver delta to analytics-ready gold
├── src/
│   ├── pipeline/pipeline_definition.json
│   └── utils/query_tables_list_for_lookup_activity.sql
├── sql/
│   ├── create_fintech_schema.sql
│   ├── Accounts/
│   ├── Customers/
│   ├── Loans/
│   ├── Payments/
│   └── Transactions/
└── README.md

How to Run

Use this order to deploy and execute the full pipeline.

1) Prepare SQL source data

1. Run sql/create_fintech_schema.sql.
2. For each table folder under sql/ (Accounts, Customers, Loans, Payments, Transactions):
   • Run create_table_<table_name>.sql.
   • Run insert_records_<table_name>.sql.

2) Configure storage layers

1. In ADLS, create (or confirm) bronze/silver/gold paths.
2. Ensure Synapse has access to these storage paths.

3) Deploy pipeline definition

1. Use src/pipeline/pipeline_definition.json in Synapse.
2. Confirm pipeline parameters in the Parameters section below.

4) Configure notebooks in Synapse

1. Import or attach notebooks/BronzeToSilver_ETL.ipynb.
2. Import or attach notebooks/SilverToGold_ETL.ipynb.
3. Ensure notebook activities are correctly referenced in the pipeline.

5) Configure notifications

1. Create/update Logic App endpoint(s) for success and failure notifications.
2. Connect the corresponding Web activities in the pipeline.

6) Execute and schedule

1. Trigger one manual run to validate the flow end-to-end.
2. Configure a schedule trigger in Synapse for automated execution.

Pipeline Activities

The pipeline includes the following main activities:

1. GetTableListFromSqlDatabase:

   • A Lookup activity that queries the SQL database to retrieve a list of base tables from the fintech schema.
   • View the SQL query configuration

2. CopyEachTableToBronzeLayer:

   • A ForEach activity that iterates over the tables fetched in the previous step and copies each table from SQL to the Bronze layer in ADLS using the Copy activity.
     
     
   

3. BronzeToSilver_ETL:

   • A SynapseNotebook activity that processes the data in the Bronze layer and performs ETL transformations, storing the results in the Silver layer.
   • View the Azure Notebook code

4. SilverToGold_ETL:

   • A SynapseNotebook activity that further transforms the data from the Silver layer and writes the final dataset to the Gold layer.
   • View the Azure Notebook code

5. PipelineSuccessNotification:

   • The WebActivity sends an HTTP request to the Logic App upon pipeline success. The Logic App is configured to receive the request containing email details such as recipient, subject, and body, and send an email using Outlook.
     
     
   

   Above: Logic App workflow triggered by the HTTP request from WebActivity.

   • Logic App Workflow:
     • Trigger: The Logic App is triggered by an HTTP request from the WebActivity within the pipeline.
     • Action: Once triggered, the Logic App sends an email to the specified recipient, using the subject and body details defined in the request.
       
       
   

   Above: Example of the email sent upon pipeline success.

6. PipelineFailedNotification:

   • Similarly, another WebActivity is used to send an email notification if the pipeline fails. This triggers a different Logic App workflow to notify the relevant recipients about the failure.
     
     
   

   Above: Example of the email sent upon pipeline failure.

Parameters

This pipeline includes the following parameters for customizable notifications:

• to: Email recipient.
• emailSubjectSuccess and emailBodySuccess: Customizable email subject and body for success notifications.
• emailSubjectFailed and emailBodyFailed: Customizable email subject and body for failure notifications.

These parameters help configure notification settings to keep stakeholders informed on pipeline status.

Pipeline Steps

The following are the implementation details for each stage of the pipeline:

1. SQL Database Preparation

Create and structure a SQL database with tables containing historical data that will serve as the source for further processing. First, a schema named fintech is created within the SQL database, which includes the following tables:

• Accounts
• Customers
• Loans
• Payments
• Transactions

In the sql folder, each table has a dedicated subfolder containing the SQL files necessary for:

• Creating the table structure.
• Inserting initial records for testing and further processing.

Each subfolder includes create_table_<table_name>.sql and insert_records_<table_name>.sql files for its respective table.

• View SQL Folder

2. Data Pipeline Setup in Azure Synapse

The pipeline configuration is available in the pipeline_definition.json file.

Set up a dynamic data pipeline in Azure Synapse to move data from the SQL Database to the Bronze Layer in Azure Data Lake Storage (ADLS). Refer to the Pipeline Activities and Parameters sections for details on each pipeline activity and parameter.

The Bronze Layer handles raw data storage for further transformations. Each table from the SQL database is stored in Parquet format under the following structure:

bronze/
└── fintech/
    └── <table_name>/
        └── parquet files

3. Bronze to Silver Transformation (PySpark)

Create and configure a PySpark notebook to read the raw data from the Bronze Layer and transform it into a more refined format, which is then written into the Silver Layer as Delta Tables.

• View BronzeToSilver Notebook

The Silver layer structure is organized as follows:

silver/
└── fintech/
    └── <table_name>/
        └── delta files

4. Silver to Gold Transformation (PySpark)

Develop and configure another PySpark notebook to read the transformed data from the Silver Layer, apply further processing, and write the final, optimized dataset into the Gold Layer as Delta Tables for high-quality data.

• View SilverToGold Notebook

The Gold layer structure is organized as follows:

gold/
└── fintech/
    └── <table_name>/
        └── delta files

5. Notification Configuration

Add WebActivity steps in the pipeline to send email notifications via Logic Apps to monitor pipeline success and failure:

• PipelineSuccessNotification: Set up a WebActivity to trigger a Logic App that sends an email notification upon successful pipeline completion.

• PipelineFailedNotification: Set up another WebActivity to trigger a Logic App that sends an email notification if the pipeline fails.

6. Automated Pipeline Execution

Enable an automation setup within Azure Synapse Analytics to schedule the existing pipeline. This configuration allows all pipeline activities - from data extraction and transformation to notifications - to run automatically and sequentially, ensuring smooth end-to-end data processing.

Validation Checklist

After running the pipeline, verify the following outcomes:

• Lookup + copy stage succeeds and all source tables are landed in bronze as parquet.
• Bronze to silver notebook succeeds and delta tables are created in the silver layer.
• Silver to gold notebook succeeds and curated dimension/fact outputs exist in the gold layer.
• Pipeline notifications work: success emails on successful run, failure emails on failed run.
• Pipeline history is clean in Synapse monitor (no unresolved failed activity in the latest run).
• Data sanity checks pass (for example: non-empty outputs and expected table counts per layer).

Summary of Flow

• Data Retrieval: The pipeline starts by fetching table names from the SQL database.
• Data Migration to Bronze: The tables are copied to the Bronze layer in ADLS.
• ETL Processes: Data is transformed in the Bronze layer, moved to the Silver layer, and then to the Gold layer.
• Notification: The pipeline sends email notifications based on the success or failure of the pipeline execution.

This setup ensures that data moves seamlessly from SQL to ADLS, undergoes transformation, and finishes with a notification of success or failure.