Azure Synapse Analytics: Beginner to Expert

Compare relational and non-relational databases, highlighting SQL-based relational schemas with tables and rows, and NoSQL systems designed for scalable, unstructured data across key-value, document, column family, and graph models.

Differentiate OLTP from OLAP by understanding OLTP as high-volume transactional processing with ACID guarantees. Store historical data in OLAP optimized for analytical queries, keeping separate databases to avoid performance degradation.

Learn to query a relational database with SQL and create an SQL database in the Azure cloud. Follow instructions to set up an Azure free account.

Create an Azure SQL database in the Azure portal by provisioning a new logical server, configuring Microsoft and SQL authentication, and setting admin credentials with sample data.

Explore sql fundamentals by querying an Azure SQL DB in the portal with the select statement, using select star to view all data or specific columns from the customer table.

Use the where clause with the select statement to filter data for the company Active Life Choice, returning chosen columns like first name, last name, company name, email, and phone.

Learn how to use the order by clause with a select statement to sort the company name column in ascending and descending order, and observe the resulting data order.

Use select distinct to return unique values for a column, eliminate duplicates, apply in and not in filters, handle nulls, and order by ascending results.

Learn to use sql case statements to create a price group for products, classifying list prices as low, medium, or high, using the product table.

Learn how to perform SQL aggregations on the list price column in the products table using max, min, and sum functions with aliases, while the next lesson covers group by.

Learn to apply the group by clause with aggregations, counting products per category and summing sales totals to compute net and gross sales by order or product IDs.

Explore the having clause by filtering aggregated results after grouping sales orders, using sum(line total) as net sales, and understand why the where clause cannot filter aggregates.

Master sql joins to retrieve product names and gross sales by linking the sales order detail to the product table, including inner, left, right, and full outer joins.

Explore partition by in sql to rank sales order headers within each status and order date by subtotal, using row_number over partition by and order by subtotal desc.

Create a new table from an existing products table by using a case statement to classify list price into a price group, then populate the price group table.

Create a table from scratch in sql, define an auto incrementing person_id with identity, and set nullable and non-nullable columns before using insert into to populate it.

Learn how SQL indexes speed up queries by directing the database to an index and then retrieving table records, and distinguish clustered from non-clustered indexes.

Learn how clustered indexes use a B-tree to physically sort data in a database, and how to create, drop, and use single or composite clustered indexes.

Explore non-clustered indexes and how a separate B-tree points to row addresses. See why this speeds queries on columns like name, yet remains slower than clustered indexes.

Learn about primary keys and foreign keys in database tables within Azure Synapse Analytics, defining unique, non-null identifiers and referential links that ensure data integrity.

Explore dimension and fact tables, including primary keys and foreign keys, to link products, customers, regions, and dates through an entity relationship diagram.

Discover Azure Synapse Analytics architecture, including data storage options, data lake, dedicated and serverless SQL pools, and spark pools. Learn workspace creation, Azure Data Factory integration, and monitoring and security.

Register the Azure Synapse Analytics resource provider and storage, create a workspace in UK South, attach a data lake storage, and configure authentication with local and Microsoft identity.

Take a tour of the Azure Synapse Analytics Studio, navigating ingest, explore, analyze, and visualize, while learning to use serverless SQL pools to query data lake Gen2.

Explore the dedicated SQL pool in Azure Synapse Analytics, also known as the Azure Data Warehouse or the Microsoft Data Warehouse solution in the cloud.

Explore data warehousing and ETL fundamentals, and distinguish OLTP from OLAP for accurate reporting purposes. Learn about star and snowflake schemas with dimension and fact tables.

Explore how the dedicated SQL pool in Azure Synapse Analytics uses MPP architecture to run parallel queries across compute nodes, coordinated by a control node and the data movement service.

Understand how to assign hash, replicated, and round robin distributions to tables in the dedicated SQL pool to optimize MPP architecture, parallel processing, and data movement.

Explain indexing for tables in the dedicated SQL pool, covering clustered columnstore, clustered and non-clustered indexes, and heap, and how row groups and delta store enable fast lookups.

Create an empty dedicated SQL pool in the Azure Synapse workspace via Synapse Studio, name it, deploy online, and plan to populate tables using Synapse Link.

Create an Azure SQL Database with sample data and link it to a dedicated SQL pool via Synapse Link to understand data flow into the pool.

Configure a synapse link to populate a dedicated sql pool from azure sql db, creating a linked service with system-assigned identity and selecting distribution and index options.

Explore a dedicated SQL pool in the Synapse workspace, inspect hash distributed tables, run top 100 row queries, and validate distribution quality with indices and Azure Synapse Link.

Explore the ETL workflow in a dedicated SQL pool, from data extraction to loading staging tables, then transforming and loading dimension and fact tables into a data warehouse.

Create staging tables with a schema identical to the product dimension, using heap and round-robin distribution for loads; load data via Azure Data Factory or copy into from data lake.

Explore slowly changing dimensions in the data warehouse, comparing type 1, type 2, and type 3 methods for updating and preserving history in dimension tables.

Load the product dimension table using a type one slowly changing dimension by merging daily staging data into the product dimension table, updating or inserting to prevent duplicates.

Load the fact table from staging data in Azure Synapse, link to dimension tables via foreign keys, handle type 1 or type 2 slowly changing dimensions, and perform post-load optimization.

Partition tables in a dedicated sql pool to boost query performance and maintenance, supported on all table types with hash and round-robin distributions that help skip unnecessary rows.

Create a partitioned table in a dedicated SQL pool with a clustered column store index and hash distribution on the product key, partitioned by order date key using date boundary.

Learn to perform partition switching and splitting in the dedicated SQL pool with alter table, ensure boundary matches, and split partitions containing data.

Demonstrates partition splitting and switching in Azure Synapse Analytics, building a three-partition fact internet sales table by creating a compatible table and swapping partitions.

Learn to split and switch partitions in azure synapse analytics using order date boundaries, creating empty partitions, and safely moving data between tables.

Discover how dynamic management views monitor the dedicated SQL pool with Transact-SQL, diagnose performance issues, and troubleshoot activity across connections, queries, blocking, data movement, service activity, and errors.

Use dynamic management views to identify active connections and monitor running queries in a dedicated sql pool, filtering out the current session and focusing on active requests.

Identify and troubleshoot query performance in Azure Synapse by using dynamic management views to locate the longest running queries, inspect specific steps, and view execution plans to optimize data distribution.

Explore materialized views in Azure Synapse Analytics, compare them with standard views in a dedicated SQL pool, and learn how pre-storing query results boosts performance at the cost of storage.

Explore explain with recommendations to optimize long running SQL queries in the dedicated SQL pool, applying it to optimizable statements like select, insert, update, and more.

Explore workload management in a dedicated SQL pool by classifying workloads as load or query, setting importance levels, and using isolation to reserve resources.

Demonstrate workload management in the dedicated SQL pool by creating a workload group and classifier for the ETL role, then load data from the data lake via a pipeline.

Learn how to secure an Azure Synapse Analytics data warehouse with conditional access, using signals like device type and location to grant, block, or require multi-factor authentication.

Explore dynamic data masking to limit sensitive data exposure for non-privileged users using default, email, and credit card masking. Demonstrate partial and custom masking to reveal select characters.

Implement dynamic data masking in a dedicated SQL pool within Azure Synapse by creating a data schema and membership table with masked columns, then test masking with a dedicated user.

Demonstrates implementing column level security in an Azure Synapse Analytics dedicated SQL pool by creating a user, granting selective column access (excluding SSN), and querying with specific column lists.

Learn to implement row level security in a dedicated SQL pool by using a security predicate inline table valued function and a security policy to restrict data by user.

Enable transparent data encryption for the dedicated SQL pool in Azure Synapse, protecting data at rest with real-time encryption of the database, backups, and transaction logs via the Azure portal.

Query data lake files with serverless sql pool without loading them into a database, and explore, transform, and design a logical data warehouse using external tables.

Openrowset enables querying data lake files from a serverless sql pool, returning a rowset with options for schema, format, and top 100 rows, file URL, wildcards, and delimited csv text.

Learn to query CSV, JSON, and Parquet files from the serverless SQL pool in Azure Synapse by using OPENROWSET, adjusting parser version and headers, against data lake storage.

Learn to create external database objects in a serverless SQL pool by configuring data sources, credentials, and CSV-based external tables on a data lake.

Transform data using serverless sql pool to filter, rename fields, convert data types, derive fields, and group and aggregate, then persist results as a file in the data lake.

Learn to transform data with CETAS using serverless sql pool in azure synapse, loading sales orders from a data lake, and computing quantity, total sales, and order count by year.

Parameterize a Synapse stored procedure to process data by year, use dynamic sql to drop and create external tables, and orchestrate with a for each loop in a pipeline.