Azure SQL Data Warehouse Synapse Analytics Service

Cloud Data Warehouse in Azure Synapse Analytics Service (formerly Azure SQL Data Warehouse). [Covered: DP-200, DP-201]

What you'll learn
Azure SQL Data warehouse
Azure Synapse Analytics Service
PolyBase
Cloud Data Warehouse
Microsoft SQL Data Warehouse basics

Requirements
Basic knowledge of Database and SQL
Free Azure Subscription

Description
Why Azure Synapse Analytics Service (formerly Azure SQL Data Warehouse)

Azure Synapse Analytics truly is a game-changer in Data processing and Analytics.

In the most recent study conducted by GigaOm in January 2019 for the TPC-H benchmark report shows that Synapse Analytics is 14 times fast and still 94% cheaper than any other leading service in the market.

And that's why clients are shifting to Azure and Azure is growing at nearly twice the rate of Amazon Web Services.

According to a 2019 Dice report, there was an 88% year-over-year growth in job postings for data engineers, which was the highest growth rate among all technology jobs.

If you are interested in this domain, there cannot be a better time to start learning Azure Synapse Analytics Data Warehouse then now.

Expected Outcomes

In this course

You will learn the difference between Traditional vs Modern vs Synapse Data warehouse architecture

You will learn why Microsoft Synapse Analytics service is going to be a game-changer in the Data Analytics

You will learn how to provision, configure and scale Azure Synapse Analytics service

You will learn Cloud Data Warehouse MPP architecture, table types, partitioning, distribution key, and many other important concepts.

You will learn different migration techniques and advantage of PolyBase over other techniques with lots of Demos

You will learn Security, Configuration, backups, monitoring and other important topics with lots of Demos

By the end of this course, you will have a fairly good understanding of Synapse Service and you can directly start working in a Production environment.

100% Syllabus covered for DP200 and DP201 certification exam for Azure Data warehouse (Synapse)

What if I am new in Data Warehouse?

I have included a module on Data Warehouse Basics (Crash course to speed up with Cloud warehousing)

Level

Beginners & intermediate

Intended Audience

Beginners in Azure Platform

Data Warehouse developers/ admins

Database and BI developers

Database Administrators

Data Engineers

Data Scientist

Data Analyst or similar profiles

On-Premises Database related profiles who want to learn how to implement these technologies in Azure Cloud.

Anyone who is looking forward to starting his career as an Azure Data Engineer.

Prerequisites

Basic T-SQL and Database concepts

Azure Free trial Subscription

Language

English

If you are not comfortable in English, please do not take the course, captions are not good enough to understand the course.

What's inside

Video lectures, PPTs, Demo Resources, Quiz, Assignment, other important links

Full lifetime access with all future updates

Certificate of course completion

30-Day Money-Back Guarantee

Course In Detail

Introduction

Microsoft has recently released this brand new service, which is a big success for the data team at Microsoft. Synapse contained very rich features, not only to the engine itself to increase performance, but also to add new functionality in providing a unified analytics experience for diff data teams.

In the most recent study conducted by GigaOm in January 2019 for the TPC-H benchmark report shows that Synapse Analytics is 14 times fast and still 94% cheaper than any other leading service in the market.

And that's why clients are shifting to Azure and Azure is growing at nearly twice the rate of Amazon Web Services. Azure Synapse Analytics truly is a game-changer in Data processing and Analytics.

According to a 2019 Dice report, there was an 88% year-over-year growth in job postings for data engineers, which was the highest growth rate among all technology jobs. If you are interested in this domain, there cannot be a better time to start learning Azure Synapse Analytics Data Warehouse then now.

I hope you will join me on this exciting journey of learning this technology.

Azure Synapse Analytics Service

Why we should consider warehousing solutions in the cloud?

And then we'll discuss Microsoft's brand new Azure Synapse analytics service, and how this service brings together enterprise data warehousing and Big Data analytics, and provide a unified experience to ingest, prepare, manage, and serve data for immediate BI and machine learning needs.

Advantages of Synapse analytics service over other cloud-based analytics services.

We will discuss the difference between Traditional vs Modern vs Synapse Architecture.

You will also see Azure Synapse studio which provides a unified experience for all Data Professionals. So, whether you are Data Engineer, Data Scientists, Database administrator, business analysts or any other IT professional, you will find your space in Synapse studio.

And then finally in Demo, we'll provision new Azure Synapse Analytics Service, we will see how to pause or resume compute node which is very important, and how to set firewall rules and connect with SQL Server Management studio

Internal and Architecture

In this module where you are going to learn Azure Data Warehouse famous MPP or Massive parallel processing architecture.

And then we'll discuss various cloud data warehousing internal but important concepts like storage and data distribution through Hash, round-robin and replicated tables

We will learn not only different Data types and table types like columnstore, heap and Clustered B-tree index, but we will also learn best practices around, how to partition our data into these diff table types.

We will discuss in detail about distribution key and how to analyses the table to find the best distribution key according to partition.

Then we'll discuss how to apply these concepts in dimensional modeling.

And then finally we'll take a case of Microsoft's famous Adventure works DW, we will download and restore it in our on-premises management studio, and we will analyze the distribution and the data types for Data Warehouse and we will prepare it to migrate to Cloud data warehousing.

Data Migration

In this module, we will learn about data loading or data migration in the Azure Data warehouse service

We'll start with learning the best practices of loading data in MPP architecture

Then we'll learn about different loading methods and we will learn the difference between Single client loading methods and Parallel readers loading methods

We will see specifically the difference between SSIS and PolyBase loading methods, and why PolyBase is preferred for large tables.

We will learn the PolyBase process in detail and go through all steps to set up the PolyBase environment.

Security

In this module, we will take a look at how we can secure our azure SQL data warehouse

Actually, without security, nothing else really matters and that's why Microsoft Azure provides 5 layers of defense to secure your Azure SQL Database

First is Threat Protection - This is a most outer layer of security, and in this Microsoft Azure constantly monitor the traffic to your Azure SQL Database and look for suspicious patterns.

Then the next comes to Network Security - Network security is to make sure only requests which are coming from valid IP addresses can access your database.

Authentication and Access control are part of Access Management

Authentication - Authentication is about validating your credentials like User Name/User ID and password to verify your identity.

And Access control or Authorization determines what kind of access the authenticated user has over particular resources.

And finally comes the Data Protection layer - Microsoft provides different information protection and encryption technologies to protect our data in your Azure SQL Database.

Configuration and Optimization

In this module, we'll examine configuration settings and common tasks that are available to us inside the Microsoft Azure Synapse Analytics service portal.

We will see it is how incredibly easy to backup and restore data warehouse in the Synapse Analytics service portal.

Then, we will take a look at price optimization and will see how you evaluate different configuration suits best for your needs.

We will learn about Managing workload, which helps solution Architects to ensure that data warehouses always have enough resources to hit SLA for classic data warehousing activities like loading, transforming and querying data.

We will learn different monitoring tools like query activity, alerts, metrics, diagnostic settings and resource health provided by Synapse portal. We will also look at the option to submit a support ticket to Microsoft in case you are not able to resolve the issue at your end.

And Finally, we are going to delete all the resources we created during Demo, this is very important to avoid any charges when you are not using the system.

Data Warehouse Crash Course

In this module, you will learn, what is Data Warehouse, Why we need it and how it is different from the traditional transactional database.

We will learn the concept of dimensional modeling which is a database design method optimized for data warehouse solutions.

Then I will explain what we mean when we say facts and their corresponding fact tables. What are dimensions and their corresponding dimension tables

how are these special kinds of tables joined together to form a star schema or snowflake schema.

This section will establish the foundation before you start my course on Azure Synapse Analytics or formally known as Azure SQL Data Warehouse.

Some students Feedback (from other courses)

One of the most amazing courses i have ever taken on Udemy. Please don't hesitate to take this course. The instructor is really professional and has a great experience about the subject of the course. - Khadija Badary

Very nicely explained most of the concepts. a must have course for beginners - Manoranjan Swain

I appreciate this course explaining everything in great detail for a beginner. This will assist me in overcoming challenges at my work - Benjamin Curtis

Good course for Beginners. Labs are really helpful to grasp the concept. Thank you - Sapna

Topics touched in this course

Microsoft SQL Server, Azure SQL Server, Azure SQL Data Warehouse, Data Factory, Data Lake, Azure Storage, Azure Synapse Analytics Service, PolyBase, Azure monitoring, Azure Security, Data Warehouse, SSIS

Who this course is for:
Beginners in Azure Platform
Database Developer
BI Developer
Database Administrators
Data Engineers
Data Scientist
Data Analyst or similar profiles