Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

Microsoft

Work with Data Warehouses using Azure Synapse Analytics

Microsoft via Microsoft Learn

Overview

  • Module 1: Design a Modern Data Warehouse 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
  • Module 2: Design a multidimensional schema to optimize analytical workloads
  • In this module, you will:

    • Design and implement a star schema
    • Design and implement a snowflake schema
    • Design and implement a time dimension table
  • Module 3: Use data loading best practices in Azure Synapse Analytics
  • 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
  • Module 4: Optimize data warehouse query performance in Azure Synapse Analytics
  • 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
  • Module 5: Integrate SQL and Apache Spark pools in Azure Synapse Analytics
  • 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
  • Module 6: Understand data warehouse developer features of 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
  • Module 7: Manage and monitor data warehouse activities in Azure Synapse Analytics
  • 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
  • Module 8: Analyze and optimize data warehouse storage in Azure Synapse Analytics
  • 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
  • Module 9: Secure a data warehouse in Azure Synapse Analytics
  • 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

Syllabus

  • Module 1: Design a Modern Data Warehouse using 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
  • Module 2: Design a multidimensional schema to optimize analytical workloads
    • 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
  • Module 3: Use data loading best practices in Azure Synapse Analytics
    • 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
  • Module 4: Optimize data warehouse query performance in Azure Synapse Analytics
    • 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
  • Module 5: Integrate SQL and Apache Spark pools in Azure Synapse Analytics
    • 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
  • Module 6: Understand data warehouse developer features of Azure Synapse Analytics
    • 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
  • Module 7: Manage and monitor data warehouse activities in Azure Synapse Analytics
    • 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
  • Module 8: Analyze and optimize data warehouse storage in Azure Synapse Analytics
    • 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
  • Module 9: Secure a data warehouse in Azure Synapse Analytics
    • 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

Reviews

Start your review of Work with Data Warehouses using Azure Synapse Analytics

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.