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

Overview

Snowflake's ability to eliminate data silos and run workloads from a single platform creates opportunities to democratize data analytics, allowing users at all levels within an organization to make data-driven decisions. Whether you're an IT professional working in data warehousing or data science, a business analyst or technical manager, or an aspiring data professional wanting to get more hands-on experience with the Snowflake platform, this book is for you.

You'll learn how Snowflake users can build modern integrated data applications and develop new revenue streams based on data. Using hands-on SQL examples, you'll also discover how the Snowflake Data Cloud helps you accelerate data science by avoiding replatforming or migrating data unnecessarily.

You'll be able to:

Efficiently capture, store, and process large amounts of data at an amazing speed
Ingest and transform real-time data feeds in both structured and semistructured formats and deliver meaningful data insights within minutes
Use Snowflake Time Travel and zero-copy cloning to produce a sensible data recovery strategy that balances system resilience with ongoing storage costs
Securely share data and reduce or eliminate data integration costs by accessing ready-to-query datasets available in the Snowflake Marketplace