Azure SQL Data Warehouse Synapse Analytics Service

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