Azure Data Factory +Synapse Analytics End to End ETL project

Set up the Azure environment from scratch by provisioning Data Lake, Data Factory, Synapse Analytics, and SQL Database, and learn to install Power BI with a valid subscription.

Set up an Azure budget in the portal by selecting a subscription, defining a threshold, and receiving email alerts at 50%, 75%, and 100% of actual usage.

Log in to portal.azure.com and create a resource group in East US with a naming convention to organize all Azure services for the Data Factory and Synapse Analytics ETL project.

Create an Azure data factory as the orchestration hub for the end-to-end ETL project, configure naming, region, and resource group, and validate the deployment.

Create an Azure Data Lake Storage Gen2 account with hierarchical namespace to receive and store data in containers, enabling structured, unstructured, and semi-structured data for Data Factory workflows.

Install and open Power BI Desktop to analyze trends from data processed through an end-to-end ETL pipeline with Azure Data Factory and Synapse Analytics.

Learn how to ingest data from on premise to Azure cloud, build pipelines, and implement incremental daily loading for the end-to-end ETL project.

Ingest data from on-premise file stores to Azure Data Lake using Azure Data Factory, and explore integration runtimes: Azure, self-hosted, and Azure SSIS, with emphasis on self-hosted runtime.

Explore how an on-premise data source on a virtual machine generates daily csv files of movies and tv shows, copied to Azure Data Lake via a Data Factory pipeline.

Create a self-hosted integration runtime in Azure Data Factory, download and install it on an on premise machine, register with the authentication key, and confirm status.

Create an Azure Key Vault to securely store data lake keys and on-premise credentials, then configure access policies for Azure Data Factory to retrieve secrets in link services.

Fix the host is not allowed error by disabling local folder path validation in the self-hosted integration runtime, enabling access to local files via a PowerShell hotfix and DMG commands.

Create a pipeline in Azure Data Factory that copies on-premise files to Azure Data Lake with a copy activity, using wildcard paths and validation to confirm 19 files moved.

Learn to implement incremental loading using last modified date in Azure Data Factory, boosting efficiency by processing only new files and reducing Spark compute versus full loads.

Learn to implement incremental load by file name in Azure Data Factory, extract the date with substring, compare to today, and copy using get metadata and for each.

Explore data transformation using Azure Synapse Analytics to transform data from the Azure Data Lake with PySpark, and load results into a SQL database.

Explore Azure Synapse Analytics as an enhanced data warehouse with dedicated and serverless pools, Spark notebooks, and pipelines. Read from data lake, transform with PySpark, and load to SQL.

Demonstrates etl by reading csv files from Azure data lake in a synapse notebook, using a link service with system assigned managed identity and grants storage blob data contributor access.

Create a link service in Azure Synapse Analytics to access the data lake with a system assigned managed identity, grant storage blob data contributor access, test, and publish for transformation.

Create a spark pool in Azure Synapse Analytics for in-memory parallel processing of ETL tasks; configure memory-optimized nodes, autoscaling, and idle timeout, then attach to notebooks.

Read csv data from Azure Data Lake with a Synapse Notebook and Spark pool, configuring a link service authentication and loading data with header and inferred schema.

Group the data and count occurrences to identify duplicates (count > 1). Remove duplicates using a built-in drop duplicates function or conditional logic in the Synapse notebook ETL workflow.

Identify null values and drop completely null rows, then replace partial nulls with unknown in selected columns using pyspark to ensure accurate analytics in the end-to-end etl workflow.

Identify, count, and remove or replace null values in an end-to-end Azure Data Factory and Synapse ETL project; drop fully null rows and fill remaining nulls with unknown.

Create a new rating category column from IMDb rating, classifying movies into very low, low, medium, and high, using a transformation in the end-to-end etl project.

Shows converting release date and added date from string to date type to prevent SQL mapping issues in an end-to-end Azure Data Factory and Synapse Analytics ETL project.

Convert release date and added date from string to date using to_date with existing format, and rename columns to remove spaces for integration with Azure Data Factory and Synapse Analytics.

Demonstrates end-to-end etl by writing transformed data to the data lake, creating a refined container and data folder, and saving as parquet with overwrite and inferred schema.

Demonstrates calling a Synapse notebook from Azure Data Factory to automate transformations on new data using the notebook activity, with managed identity and admin access, plus link service and pipeline.

Learn how to call a Synapse notebook from Azure Data Factory, configure access, create a link service, and monitor Spark jobs to orchestrate data ingestion and transformation.

Complete the transformation by reading and transforming data and calling the Synapse notebook from Azure Data Factory. Next, load the data into the SQL database, create views, and enable reporting.

Load data from the Azure data lake into an Azure SQL database using Azure Data Factory, moving from refined to gold layer for clean data and reporting.

Install ssms, connect to Azure SQL Database with SQL Server authentication using server name from the Azure portal, and prepare for copying data from data lake with auto-created tables.

Illustrates moving data from a data lake to SQL database with Azure Data Factory, using parquet data, data lake Gen2, and auto create table for reporting.

Fix linked service errors for Azure SQL Database by updating networking, adding your client IPv4 address, and enabling Azure services access to test the connection in Azure Data Factory.

Explore enhancements to your end-to-end etl pipeline by ingesting on premise data, transforming with synapse analytics, and loading transformed data from synapse to SQL database.

Enhance copy data from on-premise to Azure Data Lake by organizing today's files into a date-stamped folder, enabling incremental copies and improved data auditing.

Enhance the on-premise ingest pipeline by organizing files into a date-based folder under ingest and copying today's files there toward ADLS, using a date parameter and UTC now formatting.

Demonstrates enhancing a synapse notebook by setting the source path to today’s date folder so only today’s files are transformed, reducing compute and execution time with spark.

enhance the synapse notebook to process only today's ingested file by appending the current date to the source path, using date formatting and string conversion for an end-to-end etl workflow.

Execute end-to-end etl by running the data factory and synapse pipelines to extract on-premise data, transform with spark, and load into sql for reporting.

Explore the reporting section by connecting the SQL database to Power BI, turning an automated ETL pipeline into actionable business decisions.

Connect Power BI to Azure SQL database, import data, and build visuals—bar charts by genre, a pie chart by IMDb category, and a line chart by month with filters.

Set up continuous integration and continuous deployment for Azure Data Factory from scratch using a new Data Factory and an Azure DevOps kit. Explore automated building, merging, and deploying.

Explore continuous integration in Azure Data Factory using Azure DevOps or GitHub, create feature branches, open pull requests, and publish changes to deploy pipelines, datasets, and linked services.

Understand continuous deployment with Azure DevOps, moving dev data factory pipelines to UAT and prod via release pipelines. Learn continuous integration, approvals, and managing data lake and SQL DB environments.

Configure ci/cd (continuous integration and deployment) for azure data factory using azure devops git, and test a dev to uat copy pipeline that copies a file from source to destination.

Push code via a pull request to merge Tony's directory into main, obtain Shanmukh's approval, publish to generate an ARM template, and deploy through CI/CD to the live environment.

Complete an end-to-end project using Azure services. Stay updated with Azure updates to continue learning and apply your new skills.