Book Image

Data Modeling for Azure Data Services

By : Peter ter Braake
Book Image

Data Modeling for Azure Data Services

By: Peter ter Braake

Overview of this book

Data is at the heart of all applications and forms the foundation of modern data-driven businesses. With the multitude of data-related use cases and the availability of different data services, choosing the right service and implementing the right design becomes paramount to successful implementation. Data Modeling for Azure Data Services starts with an introduction to databases, entity analysis, and normalizing data. The book then shows you how to design a NoSQL database for optimal performance and scalability and covers how to provision and implement Azure SQL DB, Azure Cosmos DB, and Azure Synapse SQL Pool. As you progress through the chapters, you'll learn about data analytics, Azure Data Lake, and Azure SQL Data Warehouse and explore dimensional modeling, data vault modeling, along with designing and implementing a Data Lake using Azure Storage. You'll also learn how to implement ETL with Azure Data Factory. By the end of this book, you'll have a solid understanding of which Azure data services are the best fit for your model and how to implement the best design for your solution.

Related Content you might be interested in

Current Title:

Small book image
Data Modeling for Azure Data Services
Peter ter Braake
Jul, 2021 | 428 pages
Small book image
Cloud Scale Analytics with Azure Data Services
Patrik Borosch
Jul, 2021 | 520 pages
Small book image
Limitless Analytics with Azure Synapse
Prashant Kumar Mishra
Jun, 2021 | 392 pages
Small book image
Azure Synapse Analytics Cookbook
Meenakshi Muralidharan | Gaurav Agarwal
Apr, 2022 | 238 pages
Table of Contents (16 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Get in touch
Share Your Thoughts
1
Section 1 – Operational/OLTP Databases
Section 1 – Operational/OLTP Databases
Free Chapter
2
Chapter 1: Introduction to Databases
Chapter 1: Introduction to Databases
Overview of relational databases
Introduction to Structured Query Language
Impact of intended usage patterns on database design
Understanding relational theory
Keys
Types of workload
Summary
3
Chapter 2: Entity Analysis
Chapter 2: Entity Analysis
Scope
Understanding entity relationship diagrams
Entities
Relationships
Creating your first ERD
Context of an ERD
Summary
Exercises
4
Chapter 3: Normalizing Data
Chapter 3: Normalizing Data
When to use normalization as a design strategy
Preventing redundancy
The normalization steps
An alternative approach to normalizing data
Integrating separate results
Entity relationship diagram
Summary
Exercises
5
Chapter 4: Provisioning and Implementing an Azure SQL DB
Chapter 4: Provisioning and Implementing an Azure SQL DB
Technical requirements
Understanding SQL Server data types
Quantifying the data model
Provisioning an Azure SQL database
Connecting to the database
Data definition language
Inserting data
Indexing
Summary
6
Chapter 5: Designing a NoSQL Database
Chapter 5: Designing a NoSQL Database
Understanding big data
Understanding big data clusters
Getting to know Cosmos DB
Key-value databases
Other NoSQL databases
Extra considerations
Summary
Exercise
7
Chapter 6: Provisioning and Implementing an Azure Cosmos DB Database
Chapter 6: Provisioning and Implementing an Azure Cosmos DB Database
Technical requirements
Provisioning a Cosmos DB database
Creating a container
Uploading documents to a container
Cosmos DB container settings
Importing data using the Azure Cosmos DB Data Migration tool
Summary
8
Section 2 – Analytics with a Data Lake and Data Warehouse
Section 2 – Analytics with a Data Lake and Data Warehouse
9
Chapter 7: Dimensional Modeling
Chapter 7: Dimensional Modeling
Background to dimensional modeling
Understanding dimensional modeling
Steps in dimensional modeling
Designing dimensions
Designing fact tables
Using a Kimball data warehouse versus data marts
Summary
Exercise
10
Chapter 8: Provisioning and Implementing an Azure Synapse SQL Pool
Chapter 8: Provisioning and Implementing an Azure Synapse SQL Pool
Overview of Synapse Analytics
Provisioning a Synapse Analytics workspace
Creating a dedicated SQL pool
Implementing tables in Synapse SQL pools
Understanding workload management
Using PolyBase to load data
Connecting to and using a dedicated SQL pool
Summary
11
Chapter 9: Data Vault Modeling
Chapter 9: Data Vault Modeling
Background to Data Vault modeling
Designing Hub tables
Designing Link tables
Designing Satellite tables
Using hash keys
Designing a Data Vault structure
Designing business vaults
Implementing a Data Vault
Summary
Exercise
12
Chapter 10: Designing and Implementing a Data Lake Using Azure Storage
Chapter 10: Designing and Implementing a Data Lake Using Azure Storage
Technical requirements
Background of data lakes
Modeling a data lake
Using different file formats
Choosing the proper file size
Provisioning an Azure storage account
Creating a data lake filesystem
Creating multiple storage accounts
Summary
13
Section 3 – ETL with Azure Data Factory
Section 3 – ETL with Azure Data Factory
14
Chapter 11: Implementing ETL Using Azure Data Factory
Chapter 11: Implementing ETL Using Azure Data Factory
Technical requirements
Introducing Azure Data Factory
Introducing the main components of Azure Data Factory
Using the copy activity
Implementing a data flow
Executing SQL code from Data Factory
Summary
Why subscribe?
15
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts

Creating a dedicated SQL pool

A dedicated SQL pool is the Synapse name for a database. As we explained in the Introducing SQL pools section, you use dedicated SQL pools to implement data marts and data warehouses. We want to implement the Sales data mart of the Northwind database. You designed this data mart in the exercise of Chapter 7, Dimensional Modeling. You will still be able to follow along if you didn't do that exercise.

The first step is to create a dedicated SQL pool. You can do that directly from the Overview blade of a Synapse Analytics workspace. In this book, however, we will use Synapse Studio to implement our Northwind data mart:

  1. If necessary, open the Azure portal.
  2. Navigate to the Synapse Analytics workspace blade.
  3. Click on Open Synapse Studio.

    Synapse Studio will open in a new browser tab. You will get an error if your browser blocks popups and third-party cookies. You may have to log in. You can use your Azure account to log in to Synapse...

Previous Section
End of Section 4
Next Section