A simple ELT pipeline — Azure Architecture for fun
Over the past few months, I pushed myself to explore more in Azure and Power Platform. The first step was to certify myself and brush up those fragmented concepts and now I felt confident to get my hands dirty. The aim of this project was to implement all possible Azure services and not to focus on the project itself.
Following are the services used for this project:
- Azure SQL Database
- Azure Data Factory
- Azure Data Lake Gen2
- Azure Databricks
- Microsoft Power BI
Approach
Step 1: Using Copy Data Tool in Azure Data Factory, table is copied from Azure SQL Database into Azure Data Lake Gen2.
Step 2: The stored file is then transformed using Azure Databricks. In order to proceed, the Data Lake is mounted on to the Azure Databricks using the storage account access key. The transformed data is stored in temporary table inside Azure Databricks. The Databricks Notebook is
Step 3: Using Power BI, this table is taken as the data source and used for analyzing and visualizing the data.
Azure Architecture
Dashboard
GitHub
https://github.com/adityansnair/incDataMigrationAzure.git
References
Dataset: Maven Analytics Data Playground
Azure Data Lake Storage Gen2 — Azure Databricks — Workspace | Microsoft Docs
Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks (mssqltips.com)
Databases and tables — Databricks Documentation
Azure Databricks Secret Scopes Tutorial | Secure your notebook secrets
Connect Azure Databricks data to Power BI Desktop (sqlshack.com)