DP-203 - Data Engineering on Microsoft Azure

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.

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.

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.

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

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.

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.

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.

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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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 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.

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.

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.

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.

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 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 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.

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 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.

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.

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.

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 Azure Synapse in Purview by assigning access, create a Purview user, register a data source, and run a scan to capture assets.

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.

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 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.

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.

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.