Azure Data Engineering for Data Engineers: DP-203 and Beyond

Explore the differences between relational SQL databases and NoSQL systems, focusing on schema, data structure, scalability, performance, and consistency needs. Identify four NoSQL types—key-value, document, column-family, and graph.

Learn to query a relational database using SQL and create an SQL database in the Azure cloud, guided by a free Azure account with a subscription.

Learn how to create an Azure SQL Database in the portal by configuring subscription, resource group, server, authentication, networking, and sample data, then review and deploy.

Explore sql fundamentals by using the select statement to view all columns or fetch only specific columns from an azure sql db via the query editor, with authentication options.

Learn to use the select statement with the where clause to filter the customer table for a specific company, returning first name, last name, and phone.

Learn how the order by clause sorts the company name column in a select statement, first ascending then descending, with a preview of the next lesson on distinct.

learn to use SQL case statements to categorize products by list price into low, medium, and high, creating a price_group column and view the top 1000 rows.

Learn how to perform aggregations in SQL by applying max, min, and sum to the list price column in the products table.

Explore the SQL having clause to filter aggregated results, using net sales by sales order ID, and distinguish it from the where clause when applying group by.

Apply inner, left, right, and full outer joins to bring product names into sales data and show gross sales by joining sales order detail with product table on product_id.

Use subqueries to identify unsold products by filtering the product table against the sales order detail table, selecting product IDs not appearing in sales records.

Partition the sales order header data by status and order date. Rank each order by subtotal within those partitions using row_number and order by subtotal descending.

Create a new table from an existing table using a select and case to assign a price group, then verify the product id, name, and price group.

Explore how SQL indexes speed up data retrieval by using the index to locate matching rows, compare clustered and non-clustered indexes, and boost query performance.

Create non-clustered indexes to speed up frequent name queries on the students table in Azure SQL DB, using a B-tree structure to store keys and row addresses.

Explore dimension tables that describe products, customers, and other entities, and fact tables that record sales events using primary and foreign keys. Visualize connections with an entity relationship diagram.

Explore Azure Synapse Analytics architecture, workspace creation, data storage in dedicated SQL pools, and data lake storage, plus serverless SQL pools and data factory integration.

Tour the Azure Synapse Analytics workspace, from Synapse Studio navigation to serverless SQL pool queries on Data Lake Gen2, linked services, and pipeline development.

Explore the dedicated SQL pool in Azure Synapse Analytics, the cloud-based Azure data warehouse solution, and its role in modern data engineering workflows.

Explore data warehousing and etl fundamentals, including oltp vs olap, staging, and loading into dimension and fact tables with surrogate keys and star or snowflake schemas.

Explore how to optimize dedicated SQL pool performance using replicated, round robin, and hash distributions. Learn to select distribution keys to balance data and minimize movements in MPP workloads.

Explore indexing in dedicated SQL pool, including clustered columnstore, clustered and non-clustered indexes, and heap. Learn how row groups, delta store, and bulk loads influence index choices.

Create a dedicated SQL pool in the Synapse workspace using Synapse Studio, selecting the lowest WVU units and an empty pool, then deploy and confirm the pool is online.

Create an Azure SQL database and connect it to a dedicated SQL pool using Synapse Link, configuring authentication, networking, storage, and loading sample data.

Explore a dedicated sql pool in an Azure synapse workspace, run top 100 rows on hash distributed tables, and validate distribution using product model id.

Create a staging table with the product dimension schema, use heap and round robin distribution, and load via Azure Data Factory or copy into parquet files from the data lake.

Load dimension tables with slowly changing dimension type one. Merge staging data into the product dimension table by product id, updating or inserting to avoid duplication.

Load the fact table from staging sales using order date key, customer key, product key, and store key; apply type two slowly changing dimension and perform post-load optimization.

Create a partitioned table in a dedicated SQL pool using a clustered columnstore index with hash distribution on the product key and partition by the order date key.

Learn partition splitting and switching in Azure data engineering by example, demonstrating partition boundaries, switching partitions, and creating new tables to extend partitions.

Split and switch partitions in the fact internet sales table using alter table, create a second table with the same partition boundaries, and move data to form three partitions.

Explore dynamic management views to monitor the dedicated SQL pool using Transact-SQL, diagnosing performance issues by examining connections, activity, queries, indexes, blocking, data movement, and errors.

Identify active connections and running queries in a dedicated SQL pool by querying dynamic management views like dm_exec_sessions and dm_exec_requests, filtering out your login and non-active statuses, ordered by submit_time.

Compare standard views and materialized views in a dedicated sql pool, highlighting pre-processed content, storage trade-offs, and faster data retrieval for complex queries.

Use explain with recommendations in the dedicated sql pool to optimize long running queries, returning an xml plan with recommendations, including a materialized view called view one.

Learn to implement workload management in a dedicated sql pool by creating a workload group and classifier for etl role, then load data from data lake into a synapse table.

Learn how conditional access secures a data warehouse in Azure Synapse Analytics by using signals like device type and location to require multi-factor authentication.

Explore dynamic data masking to prevent unauthorized access by masking sensitive data for users, using default, partial, and custom masking functions across strings, numeric data, timestamps, emails, and credit cards.

Explore dynamic data masking in Azure Synapse dedicated SQL pool by building a masked membership table with partial, default, and email masking, and test with a test user.

Explore column level security in azure synapse dedicated sql pool by creating a user, granting select on four columns excluding SSN, and validating access with explicit column queries.

Implement row level security in a dedicated sql pool with a security predicate inline table valued function and security policy. The manager sees all data; sales reps see their rows.

Enable transparent data encryption for the Azure Synapse dedicated SQL pool to encrypt data at rest and backups in real time, without app changes, via the Azure portal.

Explore how the Openrowset function retrieves data from files in a data lake via serverless sql pool, specifying bulk path, format, and schema to produce a tabular rowset.

Query csv, json, and parquet files from the serverless sql pool using data lake storage. Leverage openrowset, parser version, header options, and json value to extract and shape data.

Create external objects in the serverless sql pool to query data lake files via an external table with openrowset, constructing the database, external data source, external file format, and credentials.

Create external database objects including an external table and data source in a serverless SQL pool, configuring credentials and a CSV format to read from a storage container.

Transform data in a serverless sql pool to generate yearly customer totals from a csv in azure data lake storage, using openrowset and external tables.