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

Content preview from Data Modeling with Snowflake

Part 3: Solving Real-World Problems with Transformational Modeling

This part focuses on advanced transformational modeling techniques and covers the theory and practice of putting them into action in Snowflake. First, we explore slowly changing dimensions—critical for maintaining historical data accuracy and ensuring consistency across different versions of data. Then, we learn how to model facts for rapid analysis, including best practices for optimizing query performance. We then explore semi-structured data formats and hierarchies, which are becoming increasingly prevalent and which Snowflake is so adept at handling. This part also covers the basics of Data Vault methodology for building agile, scalable, and auditable architectures, as ...