DP-203 - Data Engineering on Microsoft Azure

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.

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.

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.

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 how a SQL data warehouse separates OLAP from OLTP workloads, enabling analysis of existing and historical data without impacting the transactional system.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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

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.