book

Data Modeling with Snowflake

by Serge Gershkovich

May 2023

Intermediate to advanced

324 pages

8h 16m

English

Packt Publishing

Read now

Unlock full access

ForewordContributorsAbout the authorAbout the reviewers
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesConventions usedGet in touchShare Your ThoughtsDownload a free PDF copy of this book
Technical requirementsModeling with purposeLeveraging the modeling toolkitThe benefits of database modelingOperational and analytical modeling scenariosA look at relational and transformational modelingWhat modeling looks like in operational systemsWhat modeling looks like in analytical systemsSummaryFurther readingReferences
Design and processUbiquitous modelingConceptualWhat it isWhat it looks likeLogicalWhat it isWhat it looks likePhysical modelingWhat it isWhat it looks likeTransformationalWhat it isWhat it looks likeSummaryFurther reading
Traditional architecturesShared-disk architectureShared-nothing architectureSnowflake’s solutionSnowflake’s three-tier architectureStorage layerCompute layerServices layerSnowflake’s featuresZero-copy cloningTime TravelHybrid Unistore tablesBeyond structured dataCosts to considerStorage costsCompute costsService costsSaving cash by using cacheServices layerWarehouse cacheStorage layerSummaryFurther reading
StagesFile formatsTablesPhysical tablesStage metadata tablesSnowflake viewsCachingSecurityMaterialized viewsStreamsLoading from streamsChange trackingTasksCombining tasks and streamsSummaryReferences
Entities as tablesHow Snowflake stores dataClusteringAttributes as columnsSnowflake data typesStoring semi-structured dataConstraints and enforcementIdentifiers as primary keysBenefits of a PKSpecifying a PKKeys taxonomySequencesAlternate keys as unique constraintsRelationships as foreign keysBenefits of an FKMandatory columns as NOT NULL constraintsSummary
A history of relational modelingRM versus entity-relationship diagramVisual modeling conventionsDepicting entitiesDepicting relationshipsAdding conceptual context to Snowflake architectureThe benefit of synchronized modelingSummary

Embarking on conceptual designDimensional modelingUnderstanding dimensional modelingSetting the record straight on dimensional modelingStarting a conceptual model in four easy stepsFrom bus matrix to a conceptual modelModeling in reverseIdentify the facts and dimensionsEstablish the relationshipsPropose and validate the business processesSummaryFurther reading
Expanding from conceptual to logical modelingAdding attributesCementing the relationshipsMany-to-many relationshipsWeak entitiesInheritanceSummary
An overview of database normalizationData anomaliesUpdate anomalyInsertion anomalyDeletion anomalyDomain anomalyDatabase normalization through examples1NF2NF3NFBCNF4NF5NFDKNF6NFData models on a spectrum of normalizationSummary
Naming conventionsCaseObject namingSuggested conventionsOrganizing a Snowflake databaseOrganization of databases and schemasOLTP versus OLAP database structuresDatabase environmentsSummary
Technical requirementsConsiderations before starting the implementationPerformanceCostData quality and integrityData securityNon-considerationsExpanding from logical to physical modelingPhysicalizing the logical objectsDefining the tablesDeploying a physical modelCreating an ERD from a physical modelSummary
Technical requirementsSeparating the model from the objectShaping transformations through relationshipsJoin elimination using constraintsWhen to use RELY for join eliminationWhen to be careful using RELYJoins and set operatorsPerformance considerations and monitoringCommon query problemsAdditional query considerationsPutting transformational modeling into practiceGathering the business requirementsReviewing the relational modelBuilding the transformational modelSummary
Technical requirementsDimensions overviewSCD typesExample scenarioRecipes for maintaining SCDs in SnowflakeSetting the stageType 1 – mergeType 2 – Type 1-like performance using streamsType 3 – one-time updateSummary
Technical requirementsFact table typesFact table measuresGetting the facts straightThe world’s most versatile transactional fact tableThe leading method for recovering deleted recordsType 2 slowly changing factsMaintaining fact tables using Snowflake featuresBuilding a reverse balance fact table with StreamsRecovering deleted records with leading load datesHandling time intervals in a Type 2 fact tableSummary
Technical requirementsThe benefits of semi-structured data in SnowflakeGetting hands-on with semi-structured dataSchema-on-read != schema-no-needConverting semi-structured data into relational dataSummary
Technical requirementsUnderstanding and distinguishing between hierarchiesA fixed-depth hierarchyA slightly ragged hierarchyA ragged hierarchyMaintaining hierarchies in SnowflakeRecursively navigating a ragged hierarchyHandling changesSummary
Technical requirementsDemystifying Data Vault 2.0Building the Raw VaultLoading with multi-table insertsModeling the data martsStar schemaSnowflake schemaDiscovering Data MeshStart with the businessAdopt governance guidelinesEmphasize data qualityEncourage a culture of data sharingSummary
Technical requirementsThe exceptional time travelerThe secret column type Snowflake refuses to documentRead the functional manual (RTFM)Summary
Other Books You May EnjoyPackt is searching for authors like youShare Your ThoughtsDownload a free PDF copy of this book

Overview

This comprehensive guide, "Data Modeling with Snowflake", is your go-to resource for mastering the art of efficient data modeling tailored to the capabilities of the Snowflake Data Cloud. In this book, you will learn how to design agile and scalable data solutions by effectively leveraging Snowflake's unique architecture and advanced features.

What this Book will help me do

Understand the core principles of data modeling and how they apply to Snowflake's cloud-native environment.
Learn to use Snowflake's features, such as time travel and zero-copy cloning, to create efficient data solutions.
Gain hands-on experience with SQL recipes that outline practical approaches to transforming and managing Snowflake data.
Discover techniques for modeling structured and semi-structured data for real-world business needs.
Learn to integrate universal modeling frameworks like Star Schema and Data Vault into Snowflake implementations for scalability and maintainability.

Author(s)

The author, Serge Gershkovich, is a seasoned expert in database design and Snowflake architecture. With years of experience in the data management field, Serge has dedicated himself to making complex technical subjects approachable to professionals at all levels. His insights in this book are informed by practical applications and real-world experience.

Who is it for?

This book is targeted at data professionals, ranging from newcomers to database design to seasoned SQL developers seeking to specialize in Snowflake. If you are looking to understand and apply data modeling practices effectively within Snowflake's architecture, this book is for you. Whether you're refining your modeling skills or getting started with Snowflake, it provides the practical knowledge you need to succeed.