End-to-End Azure Data Engineering Project

Introduction

• Aim: Create an end-to-end Azure Data Engineering project.
• Focus: Cover all possible skills used in real-time data engineering.
• Tools Used:
  • SQL Server
  • Azure Data Factory
  • Databricks
  • PySpark
  • Power BI

Project Agenda

1. Understand business requirements.
2. Analyze data to solve problems.
3. Practical implementation of the project.
4. Create a dashboard in Power BI at the end.

Business Requirements

• Data Migration: Move data from on-premises SQL Server to the cloud.
• Pipeline Creation: Build a pipeline to transfer data daily.
• Data Analysis:
  • Connect Azure Data Factory with Databricks.
  • Build aggregates for reporting and KPIs for business stakeholders.

Setup Instructions

SQL Server Setup

1. Create a new database named sales database.
2. Upload a CSV file to create a table pizza sales.
3. Verify data availability in the table using SELECT * FROM pizza sales;.*

Azure Storage Setup

1. Create a new Azure Storage Account.
2. Create a container named raw to store data.

Azure Data Factory Setup

1. Create a new Data Factory.
2. Open Data Factory Studio to create a new pipeline.
3. Set up integration runtime for on-premises SQL Server data transfer.
4. Configure copy activity from SQL Server to Azure Blob Storage.

Data Pipeline Creation

1. Select SQL Server as the source.
2. Create a link service to connect the SQL Server.
3. After successful connection, preview data to ensure it's ready for transfer.
4. Set up blob storage as the sink.
5. Publish and test the pipeline to ensure successful data movement.

Databricks Setup

1. Create a cluster in Databricks.
2. Create a notebook and mount the Blob Storage to Databricks using dbutils.fs.mount.
3. Read the data from the mounted storage into a DataFrame.
4. Create a temporary view to enable SQL queries on the DataFrame.

Aggregate Table Creation

1. Define the required KPIs based on business needs.
2. Build an aggregate table by selecting important columns and performing necessary calculations (e.g., totals, averages) using SQL.
3. Store the aggregate data for reporting.

Power BI Visualization

1. Connect Power BI to the Databricks environment.
2. Create KPIs and visualizations based on the aggregate data.
3. Build dashboards that summarize sales, order counts, and other relevant metrics.

KPI Examples

• Total pizzas sold, total orders, total sales.
• Sales trends over time (monthly, daily, hourly).
• Sales breakdown by pizza category and size.

Conclusion

• Recap of the Azure Data Engineering project steps.
• Importance of each tool and technique used.
• Encourage further exploration and practice of Azure Data Engineering skills.