Book Image

Data Modeling with Snowflake

By : Serge Gershkovich
5 (2)
Book Image

Data Modeling with Snowflake

5 (2)
By: Serge Gershkovich

Overview of this book

The Snowflake Data Cloud is one of the fastest-growing platforms for data warehousing and application workloads. Snowflake's scalable, cloud-native architecture and expansive set of features and objects enables you to deliver data solutions quicker than ever before. Yet, we must ensure that these solutions are developed using recommended design patterns and accompanied by documentation that’s easily accessible to everyone in the organization. This book will help you get familiar with simple and practical data modeling frameworks that accelerate agile design and evolve with the project from concept to code. These universal principles have helped guide database design for decades, and this book pairs them with unique Snowflake-native objects and examples like never before – giving you a two-for-one crash course in theory as well as direct application. By the end of this Snowflake book, you’ll have learned how to leverage Snowflake’s innovative features, such as time travel, zero-copy cloning, and change-data-capture, to create cost-effective, efficient designs through time-tested modeling principles that are easily digestible when coupled with real-world examples.

Related Content you might be interested in

Current Title:

Small book image
Data Modeling with Snowflake
Serge Gershkovich
May, 2023 | 324 pages
Small book image
Snowflake Cookbook
Hammad Sharif | Hamid Mahmood Qureshi
Feb, 2021 | 330 pages
Small book image
Data Modeling for Azure Data Services
Peter ter Braake
Jul, 2021 | 428 pages
Small book image
Data Engineering with dbt
Roberto Zagni
Jun, 2023 | 578 pages
Table of Contents (24 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
Conventions used
Get in touch
Share Your Thoughts
Download a free PDF copy of this book
1
Part 1: Core Concepts in Data Modeling and Snowflake Architecture
Part 1: Core Concepts in Data Modeling and Snowflake Architecture
Free Chapter
2
Chapter 1: Unlocking the Power of Modeling
Chapter 1: Unlocking the Power of Modeling
Technical requirements
Modeling with purpose
Leveraging the modeling toolkit
The benefits of database modeling
Operational and analytical modeling scenarios
A look at relational and transformational modeling
Summary
Further reading
References
3
Chapter 2: An Introduction to the Four Modeling Types
Chapter 2: An Introduction to the Four Modeling Types
Design and process
Ubiquitous modeling
Conceptual
Logical
Physical modeling
Transformational
Summary
Further reading
4
Chapter 3: Mastering Snowflake’s Architecture
Chapter 3: Mastering Snowflake’s Architecture
Traditional architectures
Snowflake’s solution
Snowflake’s three-tier architecture
Snowflake’s features
Costs to consider
Saving cash by using cache
Summary
Further reading
5
Chapter 4: Mastering Snowflake Objects
Chapter 4: Mastering Snowflake Objects
Stages
File formats
Tables
Snowflake views
Materialized views
Streams
Change tracking
Tasks
Summary
References
6
Chapter 5: Speaking Modeling through Snowflake Objects
Chapter 5: Speaking Modeling through Snowflake Objects
Entities as tables
Attributes as columns
Constraints and enforcement
Identifiers as primary keys
Alternate keys as unique constraints
Relationships as foreign keys
Mandatory columns as NOT NULL constraints
Summary
7
Chapter 6: Seeing Snowflake’s Architecture through Modeling Notation
Chapter 6: Seeing Snowflake’s Architecture through Modeling Notation
A history of relational modeling
RM versus entity-relationship diagram
Visual modeling conventions
The benefit of synchronized modeling
Summary
8
Part 2: Applied Modeling from Idea to Deployment
Part 2: Applied Modeling from Idea to Deployment
9
Chapter 7: Putting Conceptual Modeling into Practice
Chapter 7: Putting Conceptual Modeling into Practice
Embarking on conceptual design
Modeling in reverse
Summary
Further reading
10
Chapter 8: Putting Logical Modeling into Practice
Chapter 8: Putting Logical Modeling into Practice
Expanding from conceptual to logical modeling
Adding attributes
Cementing the relationships
Summary
11
Chapter 9: Database Normalization
Chapter 9: Database Normalization
An overview of database normalization
Data anomalies
Database normalization through examples
Data models on a spectrum of normalization
Summary
12
Chapter 10: Database Naming and Structure
Chapter 10: Database Naming and Structure
Naming conventions
Organizing a Snowflake database
Summary
13
Chapter 11: Putting Physical Modeling into Practice
Chapter 11: Putting Physical Modeling into Practice
Technical requirements
Considerations before starting the implementation
Expanding from logical to physical modeling
Deploying a physical model
Creating an ERD from a physical model
Summary
14
Part 3: Solving Real-World Problems with Transformational Modeling
Part 3: Solving Real-World Problems with Transformational Modeling
15
Chapter 12: Putting Transformational Modeling into Practice
Chapter 12: Putting Transformational Modeling into Practice
Technical requirements
Separating the model from the object
Shaping transformations through relationships
Join elimination using constraints
Joins and set operators
Performance considerations and monitoring
Putting transformational modeling into practice
Summary
16
Chapter 13: Modeling Slowly Changing Dimensions
Chapter 13: Modeling Slowly Changing Dimensions
Technical requirements
Dimensions overview
Recipes for maintaining SCDs in Snowflake
Summary
17
Chapter 14: Modeling Facts for Rapid Analysis
Chapter 14: Modeling Facts for Rapid Analysis
Technical requirements
Fact table types
Fact table measures
Getting the facts straight
Maintaining fact tables using Snowflake features
Summary
18
Chapter 15: Modeling Semi-Structured Data
Chapter 15: Modeling Semi-Structured Data
Technical requirements
The benefits of semi-structured data in Snowflake
Getting hands-on with semi-structured data
Schema-on-read != schema-no-need
Converting semi-structured data into relational data
Summary
19
Chapter 16: Modeling Hierarchies
Chapter 16: Modeling Hierarchies
Technical requirements
Understanding and distinguishing between hierarchies
Maintaining hierarchies in Snowflake
Summary
20
Chapter 17: Scaling Data Models through Modern Techniques
Chapter 17: Scaling Data Models through Modern Techniques
Technical requirements
Demystifying Data Vault 2.0
Modeling the data marts
Discovering Data Mesh
Summary
21
Index
Index
Why subscribe?
22
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
Download a free PDF copy of this book
Appendix
Appendix
Technical requirements
The exceptional time traveler
The secret column type Snowflake refuses to document
Read the functional manual (RTFM)
Summary

Technical requirements

This book focuses on data modeling specifically for the Snowflake Data Cloud. While modeling includes many system-agnostic terms and conventions, this book will leverage unique features of Snowflake architecture, data types, and functions when building physical models and Structured Query Language (SQL) transformations.

To follow along with the exercises in the following chapters, you will need a Snowflake account with access to a sandbox area for creating schemas, objects, and loading data.

You can sign up for a 30-day free trial of Snowflake (https://signup.snowflake.com/) if you do not already have access.

This book will frequently use visual modeling diagrams as part of the modeling process. While a diagram can be drawn by hand and constructed in PowerPoint or Lucidchart, a tool that supports common database modeling features is recommended. The exercises in this book will take the reader from conceptual database-agnostic diagrams to deployable and runnable Snowflake code. For this reason, a tool that supports various modeling types and can forward engineer Snowflake syntax is recommended.

The diagrams in this book were generated using the SqlDBM online database modeling tool (https://sqldbm.com/Home/), which supports the previously mentioned features and offers a 2-week free trial.

Previous Section
End of Section 2
Next Section