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

Choosing the proper file size

Azure Data Lake Storage Gen2 and the compute engines from Spark (Databricks and Synapse Analytics Spark pools) as well as Data Factory are optimized to perform better on larger file sizes. When queries need to work with a lot of small files, the extra overhead can quickly become a performance bottleneck.

Apart from performance considerations, reading a 16 MB file is cheaper than reading four 4 MB files. Reading the first block of a file incurs more costs than reading subsequent blocks.

Optimal file sizes are between 64 MB and 1 GB per file. This can be a challenge in the RAW zone where you may not have a lot of control over file sizes.

Good testing to find the optimum number of files and file sizes for the compute you use is key here.

After learning the basics of the setup of a data lake, let's start implementing one by provisioning an Azure storage account.

Previous Section
End of Section 6
Next Section