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.

Create and configure an Azure Synapse Analytics workspace, attach a data lake storage, set authentication, and explore built-in SQL pools and Spark pools for end-to-end ETL.

Create an Azure SQL database with an SQL server and SQL database to support data loading, and save costs by provisioning it during the loading phase for ingestion and transformation.

Create an Azure SQL database by provisioning a new SQL server, configure SQL authentication with admin, choose the basic service tier for a one GB sample, and set firewall access.

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.

Learn how the self-hosted integration runtime connects on premise storage to Azure Data Factory, enabling end-to-end ETL from on premise to cloud storage such as Azure Data Lake.

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.

Learn to create a key vault link service to store secrets, authenticate azure data factory, and connect on premise file storage to the data lake through a copy pipeline.

Create a link service with a self-hosted integration runtime to connect Azure Data Factory to on premise file storage, then create a delimited text dataset.

Walks through creating an Azure virtual machine, configuring a private network and endpoint. Installs self-hosted integration runtime and tests a file system link service in Azure Data Factory.

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 linked service for Azure Data Lake Gen2 and a CSV dataset for the ingest container, using an account key from Azure Key Vault.

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.

Implement incremental loading by using get metadata and for each to copy today’s on-prem files to azure data lake via parameterized datasets and an if condition.

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.

Learn how to stop the spark session to save cost by stopping a running cluster, using the stop button to move it from running to stopping to stopped.

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 and remove duplicate rows in an end-to-end ETL workflow using group by, count, and drop duplicates in a dataframe, handling nulls and validating duplicates.

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.

Learn to create a column IMDB category from IMDB rating using withColumn and when and otherwise, mapping ratings to very low, low, medium, or high in a no-null data frame.

Create a new column for runtime in hours by dividing the runtime in minutes by 60, enabling visualization to compare movie durations using Azure Data Factory and Synapse Analytics.

Create a new runtime in hours column from minutes, using the width column function, divide by 60, and round to two decimals, then drop the original runtime column.

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.

We write transformed data to the Azure Data Lake in parquet format, leveraging columnar storage for compression, and plan to copy it to an SQL database via Azure Data Factory.

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.

Automate end-to-end etl by copying daily on-premise files to Azure Data Lake and triggering a Synapse Analytics notebook from Azure Data Factory to execute transformation logic without manual work.

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.

demonstrates loading data from Azure Data Lake to Azure SQL database using Azure Data Factory and Synapse end-to-end workflow; create dataset, link service, and pipeline with auto create option.

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.

Conclude the data loading section by confirming your data sits in an Azure SQL database and preview upcoming pipeline enhancements to reduce compute and cost.

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.

orchestrate end-to-end ETL projects by automating pipelines from on-prem data readiness to data lake transformation, loading into SQL database.

Orchestrate end-to-end ETL pipelines with Azure Data Factory and Synapse Analytics by automating extract, transform, and load steps from on premise ingestion to SQL load.

Orchestrate all pipelines in Azure Data Factory to automate end-to-end ETL: extract, transform, and load from on premise to data lake to SQL database, using a tumbling window trigger.

Configure an automated alert system that sends an email when an ADF pipeline fails, including the pipeline name, data factory, and run ID to identify the faulty run daily.

Set up logic apps to automatically send an alert email when a data factory pipeline fails, using http post requests, dynamic content, and high-priority notifications.

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.

Create a release pipeline with artifacts and stages, authorize a service principal, and deploy data factory templates from dev to U8 environments incrementally.

Demonstrate end-to-end ci/cd for Azure Data Factory and Synapse Analytics, showcasing release pipelines, git pull requests, and publishing changes from dev to live while validating data copy.

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