Work with Data Warehouses using Azure Synapse Analytics

In this module, you will:

• Describe a Modern Data Warehouse
• Define a Modern Data Warehouse Architecture
• Design ingestion patterns for a Modern Data Warehouse
• Understand data storage for a Modern Data Warehouse
• Understand file formats and structure for a modern data warehouse
• Prepare and transform data with Azure Synapse Analytics
• Serve data for analysis with Azure Synapse Analytics

In this module, you will:

• Design and implement a star schema
• Design and implement a snowflake schema
• Design and implement a time dimension table

In this module, you will:

• Understand data loading design goals
• Explain loading methods into Azure Synapse Analytics
• Manage source data files
• Manage singleton updates
• Set-up dedicated data loading accounts
• Manage concurrent access to Azure Synapse Analytics
• Implement Workload Management
• Simplify ingestion with the Copy Activity

In this module, you will:

• Understand performance issues related to tables
• Understand table distribution design
• Use indexes to improve query performance
• Create statistics to improve query performance
• Improve query performance with Materialized Views
• Use read committed snapshot for data consistency
• Optimize common queries with result-set caching

After completing this module, you will be able to:

• Describe the integration methods between SQL and Spark Pools in Azure Synapse Analytics
• Understand the use-cases for SQL and Spark Pools integration
• Authenticate in Azure Synapse Analytics
• Transfer data between SQL and Spark Pool in Azure Synapse Analytics
• Authenticate between Spark and SQL Pool in Azure Synapse Analytics
• Integrate SQL and Spark Pools in Azure Synapse Analytics
• Externalize the use of Spark Pools within Azure Synapse workspace
• Transfer data outside the Synapse workspace using SQL Authentication
• Transfer data outside the Synapse workspace using the PySpark Connector
• Transform data in Apache Spark and write back to SQL Pool in Azure Synapse Analytics

In this module, you will:

• Explore the development tools for Azure Synapse Analytics
• Understand Transact-SQL language capabilities for Azure Synapse Analytics
• Work with Windowing functions
• Work with approximate execution
• Work with JSON data in SQL Pools
• Encapsulate Transact-SQL logic with stored procedures

In this module, you will:

• Scale compute resources in Azure Synapse Analytics
• Pause compute in Azure Synapse Analytics
• Manage workloads in Azure Synapse Analytics
• Use Azure Advisor to review recommendations
• Use Dynamic Management Views to identify and troubleshoot query performance

In this module, you will:

• Understand skewed data and space usage
• Understand column store storage details
• Understand the impact of wrong choices for column data types
• Describe the impact of materialized views
• Understand rules for minimally logged operations

In this module, you will:

• Understand network security options for Azure Synapse Analytics
• Configure Conditional Access
• Configure Authentication
• Manage authorization through column and row level security
• Manage sensitive data with Dynamic Data masking
• Implement encryption in Azure Synapse Analytics

• Introduction
• Describe a modern data warehouse
• Define a modern data warehouse architecture
• Exercise - Identify modern data warehouse architecture components
• Design ingestion patterns for a modern data warehouse
• Understand data storage for a modern data warehouse
• Understand file formats and structure for a modern data warehouse
• Prepare and transform data with Azure Synapse Analytics
• Serve data for analysis with Azure Synapse Analytics
• Knowledge check
• Summary

• Introduction
• Design and implement a star schema
• Exercise - Create a star schema
• Design and implement a snowflake schema
• Exercise - Create a snowflake schema
• Design and implement a time dimension table
• Exercise - Create and populate a time dimension table
• Knowledge check
• Summary

• Introduction
• Understand data load design goals
• Explain load methods into Azure Synapse Analytics
• Manage source data files
• Manage singleton updates
• Set-up dedicated data load accounts
• Implement workload management
• Exercise - implement workload management
• Simplify ingestion with the Copy Activity
• Knowledge check
• Summary

• Introduction
• Understand performance issues related to tables
• Exercise - Understand performance issues related to tables
• Understand table distribution design
• Use indexes to improve query performance
• Exercise - Use table distribution and indexes to improve performance
• Create statistics to improve query performance
• Improve query performance with materialized views
• Use read committed snapshot for data consistency
• Optimize common queries with result-set caching
• Knowledge check
• Summary

• Introduction
• Describe the integration methods between SQL and spark pools in Azure Synapse Analytics
• Understand the use-cases for SQL and spark pools integration
• Authenticate in Azure Synapse Analytics
• Transfer data between SQL and spark pool in Azure Synapse Analytics
• Authenticate between spark and SQL pool in Azure Synapse Analytics
• Exercise: Integrate SQL and spark pools in Azure Synapse Analytics
• Externalize the use of spark pools within Azure Synapse Workspace
• Transfer data outside the synapse workspace using the PySpark connector
• Knowledge check
• Summary

• Introduction
• Explore the development tools for Azure Synapse Analytics
• Understand transact-SQL language capabilities for Azure Synapse Analytics
• Work with windowing functions
• Exercise - work with windowing functions
• Work with approximate execution
• Exercise - work with approximate execution
• Work with JSON data in SQL pools
• Encapsulate transact-SQL logic with stored procedures
• Knowledge check
• Summary

• Introduction
• Scale compute resources in Azure Synapse Analytics
• Pause compute in Azure Synapse Analytics
• Manage workloads in Azure Synapse Analytics
• Use Azure Advisor to review recommendations
• Use dynamic management views to identify and troubleshoot query performance
• Knowledge check
• Summary

• Introduction
• Understand skewed data and space usage
• Exercise - Check for skewed data and space usage
• Understand column store storage details
• Exercise - View column store storage details
• Understand the impact of wrong choices for column data types
• Exercise - Compare storage requirements between optimal and sub-optimal column data types
• Describe the impact of materialized views
• Exercise - Improve the execution plan of a query with a materialized view
• Understand rules for minimally logged operations
• Exercise - Optimize a delete operation
• Knowledge check
• Summary

• Introduction
• Understand network security options for Azure Synapse Analytics
• Configure Conditional Access
• Configure authentication
• Manage authorization through column and row level security
• Exercise - Manage authorization through column and row level security
• Manage sensitive data with Dynamic Data Masking
• Implement encryption in Azure Synapse Analytics
• Knowledge check
• Summary