DP-203 - Data Engineering on Microsoft Azure

Discover how data fuels ai, how to ingest from multiple sources, manage velocity and volume, and transform data for analysis in a data lake or sql data warehouse.

Explore data formats from csv to json and structured tables, and how source and target formats influence design, with Azure Data Lake Storage for semi-structured and unstructured data.

Create and manage a container in an Azure storage account, upload a CSV as a blob, view the blob's URL, and enable anonymous blob read access for testing.

Demonstrates a Python program using pandas to access and read a CSV file stored in an Azure storage account, with setup in Visual Studio Code.

Explore how Udemy stores course metadata and videos on Azure—relational tables for courses and purchases, blob storage for videos—and analyzes purchases to improve student services.

Learn how data lakes store raw streams for AI training and why a central repository matters. See how Azure Data Lake Gen2 storage accounts host semi-structured and unstructured data.

Learn how to visualize data from an Azure storage account using Power BI Desktop by installing and setting up the tool on Windows.

Install Azure Data Studio on a Windows laptop by downloading the x64 installer, accepting the license, and launching the interface to connect to a relational database system.

Learn how to download, install, and start Azure Data Studio on macOS, connect to Microsoft SQL Server, and explore the appdb database and its tables.

Explore the PARTITION BY clause in SQL and compare it with GROUP BY, using the OVER clause to compute total order quantity by ProductID while preserving all rows.

Explore how to use the LEAD and LAG window functions in SQL to access next and previous row values, with examples ordering by product ID and comparing order quantities.

Learn how to use the with clause to create common table expressions and reuse a derived result set in subsequent SQL statements, without creating a database object.

Create a simple student table using create table, define student id and name as varchar, enforce not null and a primary key on student id, then insert and query data.

Learn how to implement foreign key constraints between Course, Student, and Orders tables, linking CourseID and StudentID, and compare SQL constraints with Azure Synapse's lack of foreign keys.

Explore the cost breakdown of using Azure Synapse, focusing on the dedicated SQL pool and storage, and learn how pausing compute can minimize expenses.

Explore Azure Synapse Analytics and data engineering workflows that ingest from data lake Gen2, process csv, parquet, json, and visualize with PowerBI before storing in SQL data warehouse for analytics.

Explore how a SQL data warehouse separates OLAP from OLTP workloads, enabling analysis of existing and historical data without impacting the transactional system.

Connect a CSV file from Azure Data Lake Gen2 to a Synapse workspace via a linked service and serverless SQL pool, then resolve authorization using openrowset.

Assign permissions using Microsoft Entra ID and role-based access control to Azure resources, defining users and groups and granting built-in roles like storage blob data reader.

Create an external table over a Parquet file by configuring a private storage with a SAS-based external data source and a database scope credential, secured by a master encryption key.

Learn to query external tables across multiple parquet files by uploading new parquet files, dropping and recreating the table, and using wildcards to read all files.

Read json files from Azure storage using the openrowset bulk function, configure access with blob data reader, and map json content to columns with json_value.

Host a SQL data warehouse in Azure Synapse with a dedicated SQL pool, leveraging compute and storage, external tables, and columnar storage to boost performance and reduce costs.

Define external tables in a dedicated sql pool to load csv data from an Azure data lake, using the appropriate external data source and format, then query the loaded data.

Learn how to create an external table over a Parquet-based file in a dedicated SQL pool using native driver, data source, and SAS-based token.

Create an external table for Azure Data Lake Gen2 and read data across folders. Exclude hidden items starting with underscore or period from being read by PolyBase or Hadoop.

Pause your dedicated SQL pool to stop compute charges for the day and resume tomorrow. Deleting the pool causes data loss; recover with SQL scripts.

Load data efficiently with pipelines in Azure Synapse by copying parquet data from a data lake Gen2 storage account to a dedicated SQL pool using the copy data wizard.

Demonstrate copying the sales order detail table from an Azure SQL database to a dedicated SQL pool via a data pipeline, covering connection setup, column mapping, and monitoring.

Distribute fact tables by hash distribution and replicate dimension tables across 60 compute nodes to enable parallel joins, speed up query results.

Explore how Azure Synapse distributes data with hash, round robin, and replicated options and default clustered columnstore indexes for fast fact-table queries. Learn when heap and non-clustered indexes optimize performance.

Evaluate load methods: copy command, PolyBase, and bulk insert for a dedicated SQL pool; bulk insert is simple but slower, while PolyBase enables external tables and higher throughput.

Explore date-based partitions in a dedicated SQL pool in Azure Synapse, covering distributions, partition switching, and guidance on partition counts to optimize performance and data organization.

Recreate a table from parquet-based pool activity log data in Azure Data Lake Gen2, converting the time column to datetime, then implement range-based partitions with April and May 2024 boundaries.

Explore partition switching to relocate data by date ranges, moving April data from PoolActivityLog to PoolActivityLog_new and keeping May data in the main table, avoiding costly deletes.

Learn to host a SQL data warehouse with a dedicated SQL pool, and manage compute costs by pausing or deleting and recreating pools for exam readiness.

Evaluate costing in Azure Data Factory by analyzing mapping data flow and data flow debug, and optimize subscription spend by turning off unused infrastructure.

Create a from-scratch Azure Data Factory pipeline that copies data from csv to parquet, then ingests parquet data into a dedicated sql pool, with dataset setup and publish steps.

Build a complete two-step data pipeline from a CC-based file to Parquet, then load Parquet into the PoolActivityLog table in Azure Synapse, with schema mapping and monitoring.

Change the source schema by dropping and recreating the target table with a datetime time column, then import schemas, update mappings, publish the pipeline, and re-run to verify parquet output.

Learn about the copy data activity and related transformations in Azure Data Factory and mapping data flow, and review supported data stores and formats via Microsoft documentation.

Create a pipeline that uses a mapping data flow to move data from Azure SQL to the fact_sales table, with a staging service and an Apache Spark cluster for transformations.

Automate data deletion before running the mapping data flow in Azure Data Factory by using a stored procedure to clear fact and dimension tables, enabling clean incremental loads.

Derive a new total due column in a mapping data flow by summing subtotal, tax amount, and freight using the derive column transformation and expression builder.

Explore the data flow debug feature in Azure Data Factory's mapping data flow, enabling end-to-end and step-by-step validation with an on-demand debug environment and data previews.

Explore cache sink in a mapping data flow within Azure Data Factory, storing temporary data on the Spark cluster and adding a surrogate key CustomerSK to the dimension customer table.

Demonstrates implementing cache sync in Azure Data Factory to manage surrogate keys by deriving the current max CustomerSK from the dimension table and loading the next batch.

Learn to process JSON arrays by flattening a courses array into separate rows using a data flow in Azure Synapse, from JSON source to the customer course sink.

Explore processing json objects in a dedicated SQL pool by extracting details.mobile and details.city from nested json and mapping them to separate columns.

Demonstrates building an Azure virtual machine as a self-hosted runtime to transfer files to Azure Data Factory, using Windows Server 2022 and per-minute billing.

Use a conditional split in a new mapping data flow to filter ActivityLog.csv by Resourcegroup = app-grp, creating app-grp and others streams, and load the app-grp data into PoolActivityLog.

Work with the aggregate transformation in a mapping data flow by grouping by Resourcegroup and counting Operationname from a CSV stored in Azure Data Lake Gen2.

Explore schema drift in mapping data flows, enable drift handling and infer drifted column types, and use auto mapping to align source schemas with destinations, noting errors with SQL destinations.

Demonstrate using the get metadata activity to extract items from a data lake gen 2 container and list blobs, then publish and run the pipeline to view ActivityLog01.csv and ActivityLog02.csv.

Pass through get metadata to enumerate blob items, loop with a for each activity, and copy each file using a parameterized data set to a dedicated SQL pool.

Examine the visual and json representations of a pipeline, including the GetMetadata activity, dataset references, and the json view of each activity's source code.

Define and run a stored procedure in a pipeline that accepts a customer id and returns company name from the dimension customer table, noting the activity cannot return output values.

Explore using the lookup activity in a data engineering pipeline to call a stored procedure, fetch the first row, set pipeline variables, handle errors, and use defaults.

Define a storage events trigger to run a pipeline that ingests csv files from a data lake gen2, converts to parquet, and loads into a dedicated sql pool.

Create and manage a schedule-based trigger that runs a data pipeline every five minutes, converts CSV to Parquet, and loads data into a dedicated SQL pool table.

Explore the tumbling window trigger in data pipelines, see how it processes files within fixed five-minute windows, avoids duplicates, supports backfill with last-modified incremental loads, and contrasts with schedule-based triggers.

Explore integrating Azure Data Factory with Git and GitHub for versioning pipelines. Learn to create a GitHub account and publish code to a repository.

Integrate GitHub with Azure Data Factory by configuring a repository and branches, authorizing access, and publishing ARM templates to the publish branch, preserving pipelines, data sets, and data flows.

Create a Git-backed Azure Data Factory pipeline with a copy data activity to move delimited text from a data lake to Synapse, saving the json and publishing arm templates.

Learn how to use Git with Azure Data Factory by creating a feature branch, adding a schedule-based trigger, and merging changes through a pull request for a production main branch.

Explore costing for streaming services, covering event hubs throughput units and streaming units costs, and guidance on starting with basics and budgeting for Azure Matrix workloads.

Create an event hub inside a namespace with 24-hour retention and partitions, then send data in JSON via .NET, Python, or Java SDKs and read it with Azure Stream Analytics.

Define the stream analytics input from Azure Event Hub and stream data into a table in a dedicated SQL pool in Azure Synapse. Use JSON serialization for the input data.

Define output for a stream analytics job by routing event hub input to a SQL pool in Synapse, creating a table with make, model, vehicle type, state, toll amount.

Define the stream analytics query to select make, model, vehicle type, state, and toll amount from the event input and write to VehicleTollBooth, then test, save, and start the job.

Learn how to stream blob data from Azure Data Lake Gen2 into a dedicated SQL pool using stream analytics, including inputs, outputs, aliases, and testing with sample JSON files.

Configure diagnostic settings to stream Azure Data Lake Gen 2 storage logs and metrics to an event hub or log analytics, including read, write, and delete operations.

Formulate stream analytics queries to extract JSON records from a blobhub and flatten arrays with cross apply. Create a blob diagnostics table and output to Azure Synapse or Azure SQL.

Activate and configure Azure Event Hubs Capture to persist streaming data to Azure Blob Storage or Data Lake using Avro, Parquet, or Delta Lake formats; manage tier and scheduling.

Debug your Azure Stream Analytics jobs by understanding json data formats, validating input and output, and using the job diagram to diagnose errors and monitor the job.

Discover how windowing functions in Azure Stream Analytics segment data into non-overlapping time windows, focusing on the tumbling window to support time-based aggregations over streaming events.

Learn to implement a tumbling window in Azure Stream Analytics to aggregate events from an Azure Event Hub, computing counts of database operations grouped by ActionName and ClientIP.

Explore tumbling, hopping, and sliding windows in Azure Stream Analytics, learning how to compute counts, sums, and averages within time-based windows and when events may belong to multiple windows.

Spin up an azure linux vm to collect and query NSG flow logs, learning how to extract information from json-formatted traffic data using network security group rules.

Enable NSG flow logs on the network security group for virtual machine to capture inbound and outbound traffic, storing logs in the Azure Data Lake Gen2 storage with seven-day retention.

Explore the NSG flow log structure stored in insights-logs, with hourly json files, nested flows and flowTuples, including macAddress, source IP, and destination IP, in Azure Data Lake Gen2.

Formulate a multi-step query for energy flow logs by defining an Azure data lake input in stream analytics, then use cross apply to flatten json arrays into records.

Form a multi-stage sql query using the with clause to drill into nested json data, extracting recordedtime, rule name, flows, and flow tuples.

Finalize the query to extract source IP, destination IP, and source port from NSG flow logs. Use a JavaScript UDF to parse flowTuples array and verify results in SQL pool.

Direct Azure Stream Analytics outputs to the Power BI service and build reports beyond Power BI Desktop. Learn to sign up for free Power BI licenses via Microsoft Entra ID.

Explore processing energy flow logs with Azure Data Factory data flow, including flattening json arrays, extracting recorded time, rule names, and flowtuples to a sql sink.

Build an Azure Data Factory pipeline with a mapping data flow, set json per line, use data previews and a staging copy to process NSG flow logs via tumbling window.

Explore common query patterns in Azure Stream Analytics, including multiple outputs, lag function calculations over past events, and joining the last event in a tumbling window using datediff.

Explore Apache Spark fundamentals, including installation, Spark clusters and notebooks, and its integration with cloud services like Azure Databricks. Learn in-memory processing of batch and streaming data with multi-language support.

Explore the Apache Spark architecture, including driver, Spark context, and executors, and see how partitions enable parallel, in-memory transformations in Spark pools on Azure Synapse and Azure Databricks.

Build an Azure Windows Server VM and set up Spark, installing Python, Java, and Binutils, configuring environment variables for interactive notebooks via remote desktop.

Install spark on a Windows machine by installing the Java Development Kit, configuring environment variables, setting spark home and Hadoop, and integrating vin utils to run Spark Shell.

Learn how to run Python on Spark using PySpark, including installing an older Python version, configuring PySpark environment variables, and verifying the setup before using notebooks.

Learn to set up and run notebooks for Spark using Jupyter, install Jupyter notebooks and the Fine Spark library, and launch a Spark session within Azure Synapse or Azure Databricks.

Explore python data types in notebooks, including string, integer, float, and boolean, and learn to define variables, print values, and map data to types while not targeting spark yet.

Explore Python collections by creating and printing lists, indexing elements, appending items, and looping over courses, preparing you for notebooks and Spark work in Azure Synapse.

Learn to set up Spark in Azure, run Python notebooks, and create a data frame from an orders dataset using a struct-based schema, then apply Spark functions for transformation.

Learn how to use Spark with Python to read a comma separated csv file, infer schema, and build a data frame from orders.csv stored in a data folder.

Discover how to run Scala code in notebooks targeting Spark by installing a Scala kernel via pip and selecting it in Jupyter, with Scala's Java Virtual Machine powering Azure notebooks.

Define immutable variables in Scala, run the Scala interpreter, if-else blocks that print whether the value of I is less than ten or more than ten in Spark session.

Explore Spark with Scala by building data frames from a resilient distributed dataset, defining a schema for an orders dataset, and converting an RDD to a dataframe using Spark session.

Explore reading a csv into a spark data frame using spark.read in Scala, infer schema, and handle delimited data from the D drive files, while observing notebooks and cleanup steps.

Explore Azure Synapse spark pools and Spark on Databricks, including serverless pools with compute only during sessions. Configure three nodes with 4 vcores and 32 GB each, managed by pool.

Learn to run Python-based code in a spark pool notebook by switching from Scala to PySpark, creating and displaying a data frame, and understanding driver and executor nodes.

Learn to read data from Azure Data Lake using a Spark pool in Azure Synapse, loading CSV and Parquet files with header options and proper RBAC configuration.

Explore Spark pool basics for loading parquet files and manipulating a data frame with select, where filters, not null checks, and counting results using Spark commands.

Explore how to group results in spark dataframes using the group by method, including aggregations and counting rows per resource group, with visualization in chart view within your spark pool.

Save streaming or batch data from a data lake to a delta table using delta format and a path like /delta/activity_log, with underlying data stored in parquet based format.

Explore Azure Synapse database templates, blueprints of table definitions and relationships, and browse the airline template to quickly instantiate employees, orders, and customers.

Explore Azure Databricks, built on Apache Spark, to govern data and AI through a data lake house that combines data lake and data warehouse capabilities.

Explore Azure Databricks concepts by creating a workspace and compute clusters, using interactive notebooks and job-based clusters, and understanding the Databricks file system, databases, tables, and views.

Create an Azure Databricks workspace in a specified resource group and region, choose a trial pricing tier with data brick units, and note underlying compute costs for virtual machines.

Create a single-node Azure Databricks compute cluster to run notebooks with Spark, using single-user access to minimize costs for simple scripts.

Terminate and restart your Azure Databricks compute cluster as needed, view its JSON configuration, and pin the cluster to keep its all-purpose compute details beyond 30 days after termination.

Upload a parquet base file to the Databricks file system, enable the file browser, read it with spark.read in a notebook, and display the dataframe.

Learn to read parquet data from Azure Data Lake Gen2 using Databricks, configure access with an account key, and explore secure authorization with Microsoft Entra ID and Azure Key Vault.

Explore Azure Databricks spark pool basics and practice filtering data frames by resource group, using where conditions and not null checks, and displaying results.

Create a summary data frame with group by on the spark pool and visualize it with tabular, bar, or pie charts, and compare suitable Azure services for data engineering needs.

Use spark date functions to extract year, month, and day from a time column in a csv dataset, with a defined data schema and csv loading from a data lake.

Upload json files to Azure Databricks, load them into a dataframe, and expand course arrays with explode; then access details like city and mobile from nested objects using implode.

Load a parquet file into a data frame in Azure Databricks, then save it as a table using overwrite or append modes, and query the activity log table.

Use the copy into command to load data from a parquet base file into a table in Azure Databricks, creating a database and log data table while merging the schema.

Stream data from Azure data lake into Databricks using readStream with cloud files format for csv, defining checkpoint and schema locations, and save results to a table.

Stream data from Azure data lake blobs into an Azure Databricks table using delta format with append mode. Enable checkpoint location and schema merge to grow the activity log data.

Learn how to change the incoming data schema and stream it into a table by recreating the activity log table, adding a timestamp to the time column, and validating ingestion.

Stream data from Databricks into an Azure Synapse SQL dedicated pool using write stream with the jdbc driver, filtering non-null resource group, staging in a data lake, and checkpointing.

Query data from an Azure Synapse dedicated SQL pool using a Databricks notebook with spark.read, loading a table into a data frame and handling errors.

Learn how delta tables enable versioning with history and multiple versions, supported by auto loader for schema inference and handling new columns in source data.

Learn to run an automated job on Azure Databricks using a job cluster and schedule, appending data from an activity log parquet base file into an app db table.

Learn how to run an Azure Databricks notebook in an Azure Data Factory pipeline by configuring a shared cluster, setting IAM permissions, creating a link service, and triggering the pipeline.

Ingest data from Azure Event Hubs into Databricks by installing the Event Hubs library on a Spark cluster, configuring a connection, and streaming with Spark read stream.

Cast the Azure Event Hub body from binary to a string, then parse it as JSON to access fields like entry time, car model, and toll amount.

Terminate the Azure Databricks clusters and, if desired, delete the Azure Databricks resource, noting that deleting may erase managed and delta tables, while notebooks and code can be recreated.

Explore security and authorization options for Azure Data Lake Gen2, including hierarchical namespace, account keys, shared access signatures, and Azure Active Directory RBAC for data access in Synapse and apps.

Learn to use the Azure Storage Explorer to access and manage data in Azure Data Lake Gen2, including blobs, files, queues, and tables, via account key authentication.

Learn to replace full access keys with shared access signatures for fine-grained, time-bound permissions on Azure data lake gen 2, specifying blob service access, containers, objects, expiry, and IP addresses.

Authenticate users with a username and password, then authorize access through permissions to Azure resources, using Microsoft Entra ID as the cloud identity provider for authentication and authorization.

Create a new Microsoft Entra ID user to authorize access to storage accounts, exploring the free tenant setup, basic user properties, and password change on first login.

Configure RBAC with Microsoft Entra ID to grant storage blob data reader access on a Gen2 storage account, manage role assignments, and enforce least privilege through scoped permissions.

Grant access using Microsoft Entra ID and RBAC by assigning storage blob data reader role to a storage user, and add a reader role for the storage account control plane.

Learn to apply access control lists to Azure Data Lake with the hierarchical namespace, granting read, write, and execute at container, folder, and file levels, demonstrated using Storage Explorer.

Enable transparent data encryption to protect data at rest in the dedicated sql pool, encrypting databases, backups, and logs with no application changes.

Enable double encryption for an Azure Synapse workspace with a customer managed key from Azure Key Vault, including key creation and workspace security configuration.

Explore logging in as a database admin defined in Microsoft Enter ID, using MFA, and querying data in the dedicated SQL pool with proper RBAC boundaries.

Utilize virtual network service endpoints to restrict storage accounts to a virtual network, enabling private communication within the Microsoft network instead of the public internet.

Deploy a Windows Server 2022 virtual machine in a new resource group and virtual network, then restrict storage account access via firewall rules and connect with Azure Storage Explorer.

Explore configuring Azure storage accounts through network and firewall settings using virtual network service endpoints, enabling private connectivity from a VM to storage data.

Discover how managed identities replace account keys for secure authorization of Azure Data Lake Gen2 from Azure Data Factory using a security principal in Azure AD.

Use a managed identity to access a data lake from Azure Synapse, creating a database scoped credential and ABFSS external data source, with a storage blob data reader role.

Discover and classify sensitive data in Azure SQL and Azure Synapse, apply built-in information types and sensitivity labels, and enable auditing with Log Analytics alerts.

Encrypt data in Azure Data Factory using a customer managed key from Azure Key Vault, with purge protection and access granted to the data factory's managed identity, encrypted at rest.

Access your data lake from Azure Databricks securely by replacing hardcoded credentials with a Key Vault secret. Register Azure AD application and use its secret in notebooks to authorize access.

Securely access Azure Data Lake Gen2 from Azure Databricks by configuring Key Vault access policy, creating a Databricks secret scope, and using an application object with OAuth.

Explore how Microsoft Purview serves as a data governance tool for security and compliance, helping engineers locate assets across Azure sources with a data map, metadata, catalog, and lineage.

Create a Microsoft Purview account, configure the basic settings, and deploy it to a resource group. Explore integrating Purview with Azure Data Lake, Azure Synapse, and Azure Data Factory.

Register a new collection in Microsoft Purview, register Azure Data Lake Gen2 as a data source, and run a one-time scan to catalog data lake assets and folders.

Register Azure Synapse in Purview by assigning access, create a Purview user, register a data source, and run a scan to capture assets.

Explore how Microsoft Purview connects with Azure Data Factory to catalog data assets, search across the data catalog, and trace lineage from a data lake to a dedicated SQL pool.

Discover how Azure Data Lake Gen2 access tiers, including hot, cool, cold, and archive, control costs. Note minimum durations (30 days for cool, 90 for cold) and access costs.

Explore Azure Data Lake Gen2 access tiers, from default hot or cool at the storage account level to object-level cool, cold, and archive, with rehydration to access archived data.

Use Azure Data Lake Gen2 lifecycle management rules to automatically move base blobs to the cool tier or delete them when not modified for seven days.

Discover how Azure Monitor serves as the central service to monitor resources like Synapse, SQL pool, and Azure Storage, view metrics, and review activity logs to set alerts.

Create an Azure Monitoring Service alert on an Azure Data Factory resource using failed pipeline runs metric, triggering when the count exceeds one, with an action group for email notification.

Learn to retain Azure Data Factory monitoring data beyond 45 days by sending logs to a Log Analytics workspace, with separate tables for pipeline, activity, and trigger runs.

Learn how to add user-defined annotations to Azure Data Factory pipelines and use them to filter monitor results by annotated values such as department, enabling targeted views of pipeline runs.

Explore Azure Data Factory's integration runtime, which scales automatically for copy activities in a managed serverless environment, while self-hosted runtimes on Azure VM require you to manage data integration units.

Explore common errors in Azure Data Factory pipelines and learn troubleshooting approaches. Discover how the binary copy option copies data as is to avoid delimiter errors.

Explore the monitoring features in Azure Synapse, including monitoring in Synapse Studio, tracking CPU and memory usage for dedicated and serverless SQL pools, and viewing SQL requests, pipelines, and runs.

Explore Azure Synapse dynamic management views to monitor workload in a dedicated SQL pool, including sessions, execution request, query plan, data movement across workers, waits, and deadlocks or blocking.

Enable result set caching in a dedicated SQL pool to reuse query results from the cache, while noting exclusions such as built-in functions, user defined functions, and large data sets.

Examine data skew in Azure Synapse dedicated SQL pool by reviewing hash vs round robin distributions and how uneven rows across 60 distributions indicate skew.

enable diagnostic settings for a dedicated SQL pool to stream SQL requests and steps to a log analytics workspace, view them in Synapse SQL pool tables, and manage the setting.

Increase streaming units to prevent memory exhaustion and monitor backlog to scale up, while partitioning data across Azure Event Hub input and output to enable parallel processing.

Explore how time governs Azure Stream Analytics, comparing application time, arrival time, and ingestion into data lake, and how watermark delay handles late and early events.

Explore monitoring Azure Databricks by inspecting cluster configuration, libraries, logs, spark UI, and dag visualizations, and send application logs to Azure Monitoring using a GitHub open source library.