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

Chapter 1. Getting Started

Cloud computing is characterized by on-demand availability of data storage and computing power. A primary benefit of cloud computing is that it doesn’t require users to be directly or actively involved in the management of those computer system resources. Other benefits include access to unlimited storage capacity, automatic software updates, instant scalability, high speed, and cost reductions. As expected, the recent explosion of cloud computing led by AWS Redshift, Google BigQuery, and Microsoft Azure Data Warehouse resulted in the decline of on-premises data centers.

Many of the major data warehouse providers, such as Oracle and IBM, that were created as a traditionally hosted solution later adapted to the cloud environment. Unlike those traditional solutions, Snowflake was built natively for the cloud from the ground up. While Snowflake originated as a disruptive cloud data warehouse, it has evolved over time, and today it is far more than an innovative modern data warehouse.

Along the way, Snowflake earned some impressive recognition. Snowflake won first place at the 2015 Strata + Hadoop World startup competition and was named a “Cool Vendor” in Gartner’s Magic Quadrant 2015 DBMS report. In 2019, Snowflake was listed as number 2 on Forbes magazine’s Cloud 100 list and was ranked number 1 on LinkedIn’s U.S. list of Top Startups. On September 16, 2020, Snowflake became the largest software initial public offering (IPO) in history.

Today the Snowflake ...