book

Snowflake: The Definitive Guide

by Joyce Kay Avila

August 2022

Intermediate to advanced

465 pages

12h 20m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Origin of the BookWho Is This Book For?Goals of the BookNavigating this BookUsing Code ExamplesConventions Used in This BookO’Reilly Online LearningHow to Contact UsAcknowledgments
Snowflake Web User InterfacesPrep WorkSnowsight OrientationSnowsight PreferencesNavigating Snowsight WorksheetsContext SettingImproved ProductivitySnowflake CommunitySnowflake CertificationsSnowday and Snowflake Summit EventsImportant Caveats About Code Examples in the BookCode CleanupSummaryKnowledge Check
Prep WorkTraditional Data Platform ArchitecturesShared-Disk (Scalable) ArchitectureShared-Nothing (Scalable) ArchitectureNoSQL AlternativesThe Snowflake ArchitectureThe Cloud Services LayerManaging the Cloud Services LayerBilling for the Cloud Services LayerThe Query Processing (Virtual Warehouse) Compute LayerVirtual Warehouse SizeScaling Up a Virtual Warehouse to Process Large Data Volumes and Complex QueriesScaling Out with Multicluster Virtual Warehouses to Maximize ConcurrencyCreating and Using Virtual WarehousesSeparation of Workloads and Workload ManagementBilling for the Virtual Warehouse LayerCentralized (Hybrid Columnar) Database Storage LayerIntroduction to Zero-Copy CloningIntroduction to Time TravelBilling for the Storage LayerSnowflake CachingQuery Result CacheMetadata CacheVirtual Warehouse Local Disk CacheCode CleanupSummaryKnowledge Check
Prep WorkCreating and Managing Snowflake DatabasesCreating and Managing Snowflake SchemasINFORMATION_SCHEMAACCOUNT_USAGE SchemaSchema Object HierarchyIntroduction to Snowflake TablesCreating and Managing ViewsIntroduction to Snowflake Stages: File Format IncludedExtending SQL with Stored Procedures and UDFsUser-Defined Function (UDF): Task IncludedSecure SQL UDTF That Returns Tabular Value (Market Basket Analysis Example)Stored ProceduresIntroduction to Pipes, Streams, and SequencesSnowflake Streams (Deep Dive)Snowflake Tasks (Deep Dive)Code CleanupSummaryKnowledge Check
Prep WorkWorking with SQL Commands in SnowflakeDDL CommandsDCL CommandsDML CommandsTCL CommandsDQL CommandSQL Query Development, Syntax, and Operators in SnowflakeSQL Development and ManagementQuery SyntaxQuery OperatorsLong-Running Queries, and Query Performance and OptimizationSnowflake Query LimitsIntroduction to Data Types Supported by SnowflakeNumeric Data TypesString and Binary Data TypesDate and Time Input/Output Data TypesSemi-Structured Data TypesUnstructured Data TypesHow Snowflake Supports Unstructured Data UseSnowflake SQL Functions and Session VariablesUsing System-Defined (Built-In) FunctionsCreating SQL and JavaScript UDFs and Using Session VariablesExternal FunctionsCode CleanupSummaryKnowledge Check
Prep WorkCreating Snowflake ObjectsSnowflake System-Defined RolesCreating Custom RolesFunctional-Level Business and IT RolesSystem-Level Service Account and Object Access RolesRole Hierarchy Assignments: Assigning Roles to Other RolesGranting Privileges to RolesAssigning Roles to UsersTesting and Validating Our WorkUser ManagementRole ManagementSnowflake Multi-Account StrategyManaging Users and Groups with SCIMCode CleanupSummaryKnowledge Check
Prep WorkBasics of Data Loading and UnloadingData TypesFile FormatsData File CompressionFrequency of Data ProcessingSnowflake Stage ReferencesData SourcesData Loading ToolsSnowflake Worksheet SQL Using INSERT INTO and INSERT ALL CommandsWeb UI Load Data WizardSnowSQL CLI SQL PUT and COPY INTO CommandsData PipelinesThird-Party ETL and ELT ToolsAlternatives to Loading DataTools to Unload DataData Loading Best Practices for Snowflake Data EngineersSelect the Right Data Loading Tool and Consider the Appropriate Data Type OptionsAvoid Row-by-Row Data ProcessingChoose the Right Snowflake Virtual Warehouse Size and Split Files as NeededTransform Data in Steps and Use Transient Tables for Intermediate ResultsCode CleanupSummaryKnowledge Check
Prep WorkSnowflake SecurityControlling Account AccessMonitoring Activity with the Snowflake ACCESS_HISTORY Account Usage ViewData Protection and RecoveryReplication and FailoverDemocratizing Data with Data Governance ControlsINFORMATION_SCHEMA Data DictionaryObject TaggingClassificationData MaskingRow Access Policies and Row-Level SecurityExternal TokenizationSecure Views and UDFsObject DependenciesCode CleanupSummaryKnowledge Check
Prep WorkSnowflake Monthly BillStorage FeesData Transfer CostsCompute Credits ConsumedCreating Resource Monitors to Manage Virtual Warehouse Usage and Reduce CostsResource Monitor Credit QuotaResource Monitor Credit UsageResource Monitor Notifications and Other ActionsResource Monitor Rules for AssignmentsDDL Commands for Creating and Managing Resource MonitorsUsing Object Tagging for Cost CentersQuerying the ACCOUNT_USAGE ViewUsing BI Partner Dashboards to Monitor Snowflake Usage and CostsSnowflake Agile Software DeliveryWhy Do We Need DevOps?Continuous Data Integration, Continuous Delivery, and Continuous DeploymentWhat Is Database Change Management?How Zero-Copy Cloning Can Be Used to Support Dev/Test EnvironmentsCode CleanupSummaryKnowledge Check
Prep WorkAnalyzing Query PerformanceQUERY_HISTORY ProfilingHASH() FunctionWeb UI HistoryUnderstanding Snowflake Micro-Partitions and Data ClusteringPartitions ExplainedSnowflake Micro-Partitions ExplainedSnowflake Data Clustering ExplainedClustering Width and DepthChoosing a Clustering KeyCreating a Clustering KeyReclusteringPerformance Benefits of Materialized ViewsExploring Other Query Optimization TechniquesSearch Optimization ServiceQuery Optimization Techniques ComparedSummaryCode CleanupKnowledge Check

Snowflake Architecture Data Sharing SupportThe Power of SnowgridData Sharing Use CasesSnowflake Support for Unified ID 2.0Snowflake Secure Data Sharing ApproachesPrep WorkSnowflake’s Direct Secure Data Sharing ApproachCreating Outbound SharesHow Inbound Shares Are Used by Snowflake Data ConsumersHow to List and Shop on the Public Snowflake MarketplaceSnowflake Marketplace for ProvidersStandard Versus Personalized Data ListingsHarnessing the Power of a Snowflake Private Data ExchangeSnowflake Data Clean RoomsImportant Design, Security, and Performance ConsiderationsShare Design ConsiderationsShare Security ConsiderationsShare Performance ConsiderationsDifference Between Database Sharing and Database CloningData Shares and Time Travel ConsiderationsSharing of Data SharesSummaryCode CleanupKnowledge Check
Prep WorkData Sampling in SnowsightFixed-Size Sampling Based on a Specific Number of RowsFraction-Based Sampling Based on ProbabilityPreviewing Fields and DataSampling ExamplesUsing Automatic Statistics and Interactive ResultsSnowsight Dashboard VisualizationCreating a Dashboard and TilesWorking with Chart VisualizationsAggregating and Bucketing DataEditing and Deleting TilesCollaborationSharing Your Query ResultsUsing a Private Link to Collaborate on DashboardsSummaryCode CleanupKnowledge Check
Prep WorkData EngineeringData WarehousingData Vault 2.0 ModelingTransforming Data within SnowflakeData LakeData CollaborationData MonetizationRegulatory and Compliance Requirements for Data SharingData AnalyticsAdvanced Analytics for the Finance IndustryAdvanced Analytics for the Healthcare IndustryAdvanced Analytics for the Manufacturing Industry and Logistics ServicesMarketing Analytics for Retail Verticals and the Communications and Media IndustryData ApplicationsData ScienceSnowparkStreamlitCybersecurity Using Snowflake as a Security Data LakeOvercoming the Challenges of a SIEM-Only ArchitectureSearch Optimization Service Versus ClusteringUnistoreTransactional Workload Versus Analytical WorkloadHybrid TablesSummaryCode CleanupKnowledge Check
Chapter 1Chapter 2Chapter 3Chapter 4Chapter 5Chapter 6Chapter 7Chapter 8Chapter 9Chapter 10Chapter 11Chapter 12
General (Character Related)General (Not Character Related)Standard Label Abbreviations

Content preview from Snowflake: The Definitive Guide

Preface

Origin of the Book

Back in April 2020, I published a video series about Snowflake on YouTube to help people become Snowflake-certified. The response I received from the series confirmed that the need for such knowledge was much greater than a video series could meet. However, the Snowflake journey began well before this video series.

Snowflake burst onto the public scene in 2015 after three years of operating in stealth mode. Snowflake’s customers span several different industries including the public sector, healthcare, retail, financial services, and more. In 2017, Capital One became Snowflake’s first Virtual Private Snowflake (VPS) customer and also invested $5 million in Snowflake to help further Snowflake’s ability to deliver innovative technology to the financial services industry. In 2018, Snowflake grew its customer base by 300%, with additions including several more financial services companies.

Around this time, in 2018, I was first introduced to Snowflake when employed by a financial services company. In my role as a Salesforce developer, I designed and implemented data models in Salesforce that were then built in Snowflake by other members of the team.

I’ve always had a passion for data, a knack for data modeling, and a love of learning—especially learning about exciting new technologies. So naturally, the more I learned about Snowflake, the more I wanted to know. Throughout 2018 and 2019, I expanded my knowledge of Snowflake while continuing to gain more ...