Microsoft DP-203 Certified: Azure Data Engineer Associate

Explore how data engineering builds systems for collecting, storing, and processing big data across structured, semi-structured, and unstructured formats to enable visualization and insights.

Explore the four storage types inside an Azure storage account: blob for binary files, table services for structured NoSQL data, file services for shared files, and queuing services for messaging.

Log in to the Azure portal, create a storage account by selecting subscription, resource group, region, and performance and redundancy options, then review and deploy.

Explore Azure Data Lake Gen2 storage, built on blob storage, enabling hierarchical namespace for terabytes to petabytes of data, optimized for big data, scalable, pay-as-you-go storage, with diverse data formats.

Discover how to create an Azure Data Lake Gen2 storage account with hierarchical namespace enabled, compare it to a simple storage account, and enable access control lists for security.

Explore Azure SQL pricing tiers and configurations, including DTU-based basics, standard, premium, and vcore-based general purpose, serverless, hyperscale, and business critical options; plus Azure hybrid benefits for discounts.

Learn to create Azure SQL database by logging into the portal, selecting a resource group and server, configuring admin credentials, choosing pricing tiers, and using elastic pools to optimize costs.

Learn to use Azure Data Studio to query AdventureWorks product table with schema, select columns, alias name as product name, save results as csv, json, or markdown, and visualize chart.

Master the T-SQL order by clause and group by clause, filter with where weight < 1000, and sort by list price in ascending or descending (DSC).

Apply the group by clause on product id and compute the sum of list price as total price, illustrating grouping of orders by product id.

Explore the T-SQL partition by statement using sales order details and product IDs to produce per-row totals, enabling drill-down analysis and percentage calculations without collapsing to a single group.

Explore how lead and lag functions in t-sql derive next and previous order quantities by product id, enabling simple forecast analyses.

Master how to use common table expressions with the with clause in T-SQL to name a query and reuse its result across subsequent SQL statements.

Explore Cosmos DB, a globally distributed NoSQL database that stores JSON key-value data and supports multiple APIs, enabling regional deployment for low-latency, in-sync data worldwide.

Leverage Cosmos DB for global distribution, adding regions to reduce latency while automatically syncing data across multi-region deployments, with lower cost than traditional relational databases.

Explore the Cosmos DB pricing structure, including storage capacity and request units per second, and compare standard provisioned throughput, autoscaling, and serverless models to optimize costs.

Explore Cosmos DB APIs, including the MongoDB API and six options—SQL API, Mongo database API, Mongo database, table API, Cassandra API, and Gremlin API—and how they support flexible data modeling.

Explore data modeling styles and Cosmos DB APIs, including document model with SQL and MongoDB APIs, JSON key-value form, table API, Cassandra columnar API, and Gremlin graph API.

Explore Cosmos DB APIs and use cases across SQL API, MongoDB API, Table API, Cassandra API, and Gremlin API, including JSON document support, key-value data, column data, and graph data.

Create a cosmos db account using the NoSQL API, explore APIs such as MongoDB, table, Gremlin, Cassandra, and PostgreSQL, then configure region, resource group, and provisioned throughput before creating.

Create a new Cosmos DB database using the SQL API, select provisioned throughput with auto scale or manual, set the max RU, and prep to create a container.

Explore how Cosmos DB automatically partitions data into physical partitions within a container, distributing items across partitions with up to 10,000 request units per second and 50 GB per partition.

Explore how Azure Cosmos DB uses partitioning to scale containers with logical partitions defined by the partition key, and learn how physical partitions host multiple logical partitions through horizontal scaling.

Learn how to set and manage Cosmos DB consistency levels, including eventual, consistent prefix, session, strong, and bounded staleness, by adjusting settings and specifying maximum lag time for read-write synchronization.

Master Cosmos DB basics, pricing, throughput units, and Mongo, table, and Cassandra APIs; learn containers, items, partitions, consistency levels, and global scaling through hands-on labs.

Explore Azure Synapse Analytics, an OLAP platform unifying data ingestion, storage, and analytics from sources, with query across data lake gen2, sql data warehouse, and spark pools for machine learning.

Explore the compute power of Azure Synapse Analytics, including Synapse SQL data warehouse, Apache Spark for big data, and data explorer with ingestion connectors managed by Microsoft.

Discover how Azure Synapse Analytics SQL data warehouse enables analytical processing for historical and current data. Compare it with SQL database, highlighting OLTP vs OLAP, complex queries, and multi-source analysis.

Explore serverless SQL pools in Azure Synapse to query data directly from Azure Data Lake Gen2 with SQL-like queries, and understand charges based on data processed.

Understand Synapse SQL architecture with dedicated and serverless pools, a control node using MPP to allocate 60 compute nodes, with storage scaled separately and distributions like hash, round robin, replicated.

Set admin access for the data lake by configuring access control and a role assignment to enable reading data from external services through Azure Synapse and serverless SQL pools.

Explore the overview of dedicated SQL pools, compare with serverless options, and learn how dedicated compute and columnar storage enable external and normal tables for data warehousing.

Compare data loading methods: copy command, PolyBase, and bulk insert, highlighting speed, compute node usage, and suitability for large volumes, external tables, and Data Lake Gen2 transfers.

Learn to create external tables in Azure Synapse by configuring external data sources and formats for serverless and dedicated pools, with authentication via a master key and database scoped credential.

Learn how to move data from data lake storage into a dedicated sql pool using external tables for csv format, including hadoop vs native types and sas token authentication.

Identify the roles of fact and dimension tables in data warehousing, where fact tables hold quantitative data like sales amount and units sold, and dimension tables provide context.

Explore distribution types in Azure Synapse: hash distribution for fact tables and replicated distribution for dimension tables, optimizing joins across 60 compute nodes with a storage trade-off.

Learn to create table distributions in a SQL pool by switching from round robin to a hash distribution on a chosen column, and note replicated distribution for a cap table.

Learn to monitor query performance in the dedicated SQL pool, identify slow operations, and optimize by using hash distribution on group-by columns to speed up joins and aggregates.

Explore slowly changing dimensions in dimension tables, detailing SCD types one, two, three, four, and six, including start date, end date, is_active flags, and history tables to track changes.

Understand how a business key and surrogate key support slowly changing dimensions in dimension tables, using identity columns, non-null constraints, and proper mapping during data integration.

Create a dim product using a business key, configure a query-based ingestion pipeline, stage data in a Data Lake Gen2, and run the Synapse load to populate the table.

Boost query performance using indexes, including cluster columnstore indexes, distribution types, and where clause optimization; choose heap for temporary data and plan when to use clustered or non-clustered indexes.

Partition tables in a dedicated SQL pool in Synapse Analytics by date to create monthly partitions and improve data maintenance and query performance through partition switching.

Create a user-defined restore point for a dedicated SQL pool in synapse analytics, then restore the pool using the saved settings and tables.

Explore Azure Synapse database templates by industry to quickly generate a data warehouse. Learn how to select automotive templates, add inventory tables, and configure primary and foreign keys.

Apache Spark is a powerful in-memory data processing engine that supports Python, Scala, Java, and R, with batch and streaming capabilities and data science libraries.

Explore spark's master-slave architecture with a driver and executors across a cluster. Understand how spark context coordinates execution using resilient distributed datasets and a directed acyclic graph.

Discover spark pools in Azure Synapse: creating a pool costs nothing upfront, with three clusters of 4 vcores and 32 GB each; running code launches a driver and two executors.

Learn spark sql in python notebooks using spark pool to select columns, apply where filters, and compute aggregations with group by, handling nulls.

Transfer data from a csv file in the data lake storage to a dedicated sql pool in Synapse using spark and a sql connector, defining a schema, staging, and credentials.

Explore spark history and in-memory processing within Synapse Analytics, using spark pools and languages Python, spark SQL, R, and Scala, with labs on reading and writing data to SQL pools.

Explore the overview of Azure Data Factory, a cloud-based ETL tool for data integration. Learn how pipelines, activities, linked services, and integration runtime enable code-free data flows and monitoring.

Master the ETL process: extract data from sources like data lakes or SQL databases, transform it, and load into Azure Data Lake Gen2 or Azure Synapse for analytics.

Configure and deploy an Azure Data Factory in the portal by selecting a resource group, location, and networking options, then create and launch the Data Factory Studio to manage pipelines.

Learn to process JSON data with arrays and objects in Azure Data Factory data flow, using flatten and mapping to load into Synapse SQL from a data lake.

Select the appropriate integration runtime for Azure Data Factory and Synapse Pipelines—Azure, self-hosted, or Azure SSIS—based on data location and the need for data flow, data movement, or dispatch.

Install and configure a self-hosted integration runtime for Azure Data Factory, including downloading the installer, completing setup, registering with authentication keys, and understanding its data flows limitations.

Copy data from your pc to a data lake with a self-hosted integration runtime and a file system source, then publish and verify the json file.

Explore Azure Data Factory triggers, including schedule, storage event, and tumbling window, and learn how each type schedules, auto-triggers on blob creation, or processes incremental batches.

Discover how azure data factory components are stored in a git repository as json files, with pipelines, activities, data set, and linked services represented in code.

Configure git for Azure Data Factory via the manage tab, connect to a GitHub repository, set develop as the main branch, and manage changes, approvals, and merges.

Configure git with GitHub, connect to the adf db 203 repository, and manage the develop and publish branches; import existing resources, edit pipelines in JSON, and track changes with commits.

Learn how to simulate a team workflow in Azure Data Factory by creating a branch, making changes, and opening a pull request for approval before merging into the main branch.

Celebrate completing this section covering copy data activities, pipelines, mapping data flow, stored procedure activity, integration runtimes, and git integration in Azure Data Factory for exam readiness.

Explore Databricks, a cloud service by Apache Spark creators for big data analytics and data engineering, featuring the data lake house for scalable governance on Azure and AWS.

Explore the benefits of Databricks, including ACID-compliant transactional support with atomicity, consistency, isolation, and durability, plus Delta Lake’s unified storage for diverse data formats and cost savings.

Create an Azure Databricks workspace and configure interactive and job-based clusters to run notebooks, then explore the Databricks file system and pinned compute to prevent 30-day deletion.

Explore compute cluster types in Azure Databricks, including single user and shared clusters, with driver and executor architectures, supported languages (Python, Scala, R), and cost considerations.

Create a compute in the databricks workspace to run notebooks and workflows, selecting all purpose compute, single node, spark 3.5.0, and appropriate storage, then monitor inactivity termination.

Enable the Dbfs file browser, browse Databricks File System, select a directory, copy the path, and load and display a dataframe with spark.read.format.

Learn to read data from an Azure Data Lake Storage account with Spark config, specifying the data lake path, account key, and authentication identity.

Save a dataframe to a Databricks table using write with overwrite, append, or update modes, save as a JSON table with a name, and execute SQL via notebook magic commands.

Learn to create a test database and table in a data lake, copy data with sql, merge the schema, and handle csv and parquet formats with header options.

Demonstrates streaming csv data from a data lake with Databricks, using spark.readStream and cloud files to process new files, validate schema, and update checkpoints.

Append streaming data to the delta table test dot Amazon by appending a filtered dataframe (date last updated, product) with non-null values and merging the schema with a checkpoint location.

Write streaming data from Databricks to Azure Synapse dedicated SQL pool, using a spark stream write to populate the DBO.Amazon table with a checkpoint for reliable processing.

Read data from a synapse dedicated sql pool using spark.read.format with a select from dbo.amazon, configuring workspace url, sql pool, credentials, staging, and master key for encryption.

Learn how delta tables version history works in a data lake, with parquet-based storage and automatic management, including querying six versions and rolling back to a chosen version.

Learn to schedule runs and notebooks by creating schedules, selecting days and times, and using job clusters that auto terminate to save costs and automate notebook workflows.

Create an Azure Event Hub namespace resource for use with Databricks by navigating the marketplace, choosing the basic tier with three units, validating, and deploying the namespace.

Create an event hub inside a namespace, set a six-hour retention, and create a partition to host events in this hands-on Azure data engineer training video.

Delete unused resources to save costs by removing databases, Databricks resource groups, and virtual machines used for clusters, as demonstrated in this hands-on cleanup.

Explore Azure event hubs as a data ingestion system for streaming data from IoT devices and apps. It routes to real-time analytics and Power BI via producers and consumers.

Create an Event Hub inside an Event Hub namespace to ingest events, set a name and partition count, apply a 24-hour retention, then review and create the hub.

Ingest data in real time into an Event Hub using Data Explorer, sending custom or demo payloads like Yellow Taxi, and monitor event counts and partitions for real-world applications.

Learn how to enable and configure the capture feature in Azure Event Hub, select Avro or Parquet formats, choose storage destinations, set capture window, and understand retention benefits.

Discover how Azure Stream Analytics processes data in real time with SQL-like queries and routes results to destinations such as blob, Synapse SQL, Azure SQL, or Power BI.

Explore Azure Stream Analytics cost: no upfront costs, pay-for-use streaming units, scalable on demand; monitor utilization to decide when to increase units.

Define inputs for a stream analytics job by configuring an event hub input, selecting json serialization, testing the connection, and preparing to route data to outputs.

Define a stream analytics output by sending data to a synapse dedicated sql pool, creating a synapse workspace and pool, and configuring input columns like vendor id and trip distance.

Create a table inside a dedicated sql pool in synapse sql, using vendor id, trip distance, passenger count, and total amount, preserving main table data types to prevent errors.

Define the staging location in Stream Analytics by selecting the Synapse storage account in Settings and applying the change, then proceed to configure with Synapse in the next step.

Define a query by selecting vendor_id, passenger_count, trip_distance, and total_amount, then start the streaming job to the Synapse taxi table from the input data hub or events hub.

Stream data from a json file in data lake storage gen2 with stream analytics and delta lake; configure input and output in synapse and verify data loads.

Explore how to stream diagnostic data from Azure services to an Event Hub using diagnostic settings, Stream Analytics, and a dedicated SQL pool for real-time analytics.

Define a diagnostic input in Azure Stream Analytics by adding an Event Hub, testing the connection, and inspecting the JSON data format to design the query and outputs.

Define an output in stream analytics by building a table with category, operation name, and account name from the nested data; connect to Azure Synapse Analytics and test the connection.

Formulate a query using cross apply and get array value to extract nested fields like category and identity account name from JSON and Avro data in stream analytics.

Demonstrate setting up a tumbling window in stream analytics by creating a tumbling window table in Synapse SQL, counting operations, and validating results from event data.