Data Modeling with Snowflake

Data Modeling with Snowflake

by Serge Gershkovich
Released May 2023
Publisher(s): Packt Publishing
ISBN: 9781837634453

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Discover how Snowflake's unique objects and features can be used to leverage universal modeling techniques through real-world examples and SQL recipes Purchase of the print or Kindle book includes a free PDF eBook

Key Features

  • Learn core modeling techniques tied to practical examples using native Snowflake architecture
  • Adopt a universal modeling language to communicate business value to functional teams
  • Go beyond physical modeling with SQL recipes to transform and shape your Snowflake data

Book Description

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.

What you will learn

  • Discover the time-saving benefits and applications of data modeling
  • Learn about Snowflake’s cloud-native architecture and its features
  • Understand and apply modeling techniques using Snowflake objects
  • Universal modeling concepts and language through Snowflake objects
  • Get comfortable reading and transforming semistructured data
  • Learn directly with pre-built recipes and examples
  • Learn to apply modeling frameworks from Star to Data Vault

Who this book is for

This book is for developers working with SQL who are looking to build a strong foundation in modeling best practices and gain an understanding of where they can be effectively applied to save time and effort. Whether you’re an ace in SQL logic or starting out in database design, this book will equip you with the practical foundations of data modeling to guide you on your data journey with Snowflake. Developers who’ve recently discovered Snowflake will be able to uncover its core features and learn to incorporate them into universal modeling frameworks.

Table of contents

  1. Data Modeling with Snowflake
  2. Foreword
  3. Contributors
  4. About the author
  5. About the reviewers
  6. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
    4. Download the example code files
    5. Conventions used
    6. Get in touch
    7. Share Your Thoughts
    8. Download a free PDF copy of this book
  7. Part 1: Core Concepts in Data Modeling and Snowflake Architecture
  8. Chapter 1: Unlocking the Power of Modeling
    1. Technical requirements
    2. Modeling with purpose
    3. Leveraging the modeling toolkit
    4. The benefits of database modeling
    5. Operational and analytical modeling scenarios
    6. A look at relational and transformational modeling
      1. What modeling looks like in operational systems
      2. What modeling looks like in analytical systems
    7. Summary
    8. Further reading
    9. References
  9. Chapter 2: An Introduction to the Four Modeling Types
    1. Design and process
    2. Ubiquitous modeling
    3. Conceptual
      1. What it is
      2. What it looks like
    4. Logical
      1. What it is
      2. What it looks like
    5. Physical modeling
      1. What it is
      2. What it looks like
    6. Transformational
      1. What it is
      2. What it looks like
    7. Summary
    8. Further reading
  10. Chapter 3: Mastering Snowflake’s Architecture
    1. Traditional architectures
      1. Shared-disk architecture
      2. Shared-nothing architecture
    2. Snowflake’s solution
    3. Snowflake’s three-tier architecture
      1. Storage layer
      2. Compute layer
      3. Services layer
    4. Snowflake’s features
      1. Zero-copy cloning
      2. Time Travel
      3. Hybrid Unistore tables
      4. Beyond structured data
    5. Costs to consider
      1. Storage costs
      2. Compute costs
      3. Service costs
    6. Saving cash by using cache
      1. Services layer
      2. Warehouse cache
      3. Storage layer
    7. Summary
    8. Further reading
  11. Chapter 4: Mastering Snowflake Objects
    1. Stages
    2. File formats
    3. Tables
      1. Physical tables
      2. Stage metadata tables
    4. Snowflake views
      1. Caching
      2. Security
    5. Materialized views
    6. Streams
      1. Loading from streams
    7. Change tracking
    8. Tasks
      1. Combining tasks and streams
    9. Summary
    10. References
  12. Chapter 5: Speaking Modeling through Snowflake Objects
    1. Entities as tables
      1. How Snowflake stores data
      2. Clustering
    2. Attributes as columns
      1. Snowflake data types
      2. Storing semi-structured data
    3. Constraints and enforcement
    4. Identifiers as primary keys
      1. Benefits of a PK
      2. Specifying a PK
      3. Keys taxonomy
      4. Sequences
    5. Alternate keys as unique constraints
    6. Relationships as foreign keys
      1. Benefits of an FK
    7. Mandatory columns as NOT NULL constraints
    8. Summary
  13. Chapter 6: Seeing Snowflake’s Architecture through Modeling Notation
    1. A history of relational modeling
    2. RM versus entity-relationship diagram
    3. Visual modeling conventions
      1. Depicting entities
      2. Depicting relationships
      3. Adding conceptual context to Snowflake architecture
    4. The benefit of synchronized modeling
    5. Summary
  14. Part 2: Applied Modeling from Idea to Deployment
  15. Chapter 7: Putting Conceptual Modeling into Practice
    1. Embarking on conceptual design
      1. Dimensional modeling
      2. Understanding dimensional modeling
      3. Setting the record straight on dimensional modeling
      4. Starting a conceptual model in four easy steps
      5. From bus matrix to a conceptual model
    2. Modeling in reverse
      1. Identify the facts and dimensions
      2. Establish the relationships
      3. Propose and validate the business processes
    3. Summary
    4. Further reading
  16. Chapter 8: Putting Logical Modeling into Practice
    1. Expanding from conceptual to logical modeling
    2. Adding attributes
    3. Cementing the relationships
      1. Many-to-many relationships
      2. Weak entities
      3. Inheritance
    4. Summary
  17. Chapter 9: Database Normalization
    1. An overview of database normalization
    2. Data anomalies
      1. Update anomaly
      2. Insertion anomaly
      3. Deletion anomaly
      4. Domain anomaly
    3. Database normalization through examples
      1. 1NF
      2. 2NF
      3. 3NF
      4. BCNF
      5. 4NF
      6. 5NF
      7. DKNF
      8. 6NF
    4. Data models on a spectrum of normalization
    5. Summary
  18. Chapter 10: Database Naming and Structure
    1. Naming conventions
      1. Case
      2. Object naming
      3. Suggested conventions
    2. Organizing a Snowflake database
      1. Organization of databases and schemas
      2. OLTP versus OLAP database structures
      3. Database environments
    3. Summary
  19. Chapter 11: Putting Physical Modeling into Practice
    1. Technical requirements
    2. Considerations before starting the implementation
      1. Performance
      2. Cost
      3. Data quality and integrity
      4. Data security
      5. Non-considerations
    3. Expanding from logical to physical modeling
      1. Physicalizing the logical objects
      2. Defining the tables
    4. Deploying a physical model
    5. Creating an ERD from a physical model
    6. Summary
  20. Part 3: Solving Real-World Problems with Transformational Modeling
  21. Chapter 12: Putting Transformational Modeling into Practice
    1. Technical requirements
    2. Separating the model from the object
    3. Shaping transformations through relationships
    4. Join elimination using constraints
      1. When to use RELY for join elimination
      2. When to be careful using RELY
    5. Joins and set operators
    6. Performance considerations and monitoring
      1. Common query problems
      2. Additional query considerations
    7. Putting transformational modeling into practice
      1. Gathering the business requirements
      2. Reviewing the relational model
      3. Building the transformational model
    8. Summary
  22. Chapter 13: Modeling Slowly Changing Dimensions
    1. Technical requirements
    2. Dimensions overview
      1. SCD types
      2. Example scenario
    3. Recipes for maintaining SCDs in Snowflake
      1. Setting the stage
      2. Type 1 – merge
      3. Type 2 – Type 1-like performance using streams
      4. Type 3 – one-time update
    4. Summary
  23. Chapter 14: Modeling Facts for Rapid Analysis
    1. Technical requirements
    2. Fact table types
    3. Fact table measures
    4. Getting the facts straight
      1. The world’s most versatile transactional fact table
      2. The leading method for recovering deleted records
      3. Type 2 slowly changing facts
    5. Maintaining fact tables using Snowflake features
      1. Building a reverse balance fact table with Streams
      2. Recovering deleted records with leading load dates
      3. Handling time intervals in a Type 2 fact table
    6. Summary
  24. Chapter 15: Modeling Semi-Structured Data
    1. Technical requirements
    2. The benefits of semi-structured data in Snowflake
    3. Getting hands-on with semi-structured data
    4. Schema-on-read != schema-no-need
    5. Converting semi-structured data into relational data
    6. Summary
  25. Chapter 16: Modeling Hierarchies
    1. Technical requirements
    2. Understanding and distinguishing between hierarchies
      1. A fixed-depth hierarchy
      2. A slightly ragged hierarchy
      3. A ragged hierarchy
    3. Maintaining hierarchies in Snowflake
      1. Recursively navigating a ragged hierarchy
      2. Handling changes
    4. Summary
  26. Chapter 17: Scaling Data Models through Modern Techniques
    1. Technical requirements
    2. Demystifying Data Vault 2.0
      1. Building the Raw Vault
      2. Loading with multi-table inserts
    3. Modeling the data marts
      1. Star schema
      2. Snowflake schema
    4. Discovering Data Mesh
      1. Start with the business
      2. Adopt governance guidelines
      3. Emphasize data quality
      4. Encourage a culture of data sharing
    5. Summary
  27. Appendix
    1. Technical requirements
    2. The exceptional time traveler
    3. The secret column type Snowflake refuses to document
    4. Read the functional manual (RTFM)
    5. Summary
  28. Index
    1. Why subscribe?
  29. Other Books You May Enjoy
    1. Packt is searching for authors like you
    2. Share Your Thoughts
    3. Download a free PDF copy of this book

Product information

  • Title: Data Modeling with Snowflake
  • Author(s): Serge Gershkovich
  • Release date: May 2023
  • Publisher(s): Packt Publishing
  • ISBN: 9781837634453