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

Appendix A. Answers to the Knowledge Check Questions

Chapter 1

The feature to create a support case from within Snowflake is not available for trial or reader accounts. However, there are still plenty of ways to get help (see the following answer).
If you are unable to create a support case within Snowflake, you have several options:
- Consult the Snowflake documentation.
- Consult the Snowflake Knowledge Base.
- Solicit support from the Snowflake Community.
- Review the Stack Overflow Q&A.
- Obtain direct support (for activation, password, billing, and cancellation requests only) from within the Snowflake Community:
  1. Log in to the Snowflake Data Heroes Community.
  2. Click the “Submit a Case” Quicklink.
  3. Select the account type “I am a 30-day trial user”.
Setting the context for a Snowflake worksheet means that you’ll need to let Snowflake know which role, virtual warehouse, and database you want to use in the worksheet. You can set the context through drop-down menus or with the USE statements in the worksheet (i.e., USE ROLE, USE WAREHOUSE, USE DATABASE).
The “Format query” option, available in the drop-down menu, gives you the ability to neatly format your SQL code. It does not correct spelling errors.
A Snowflake certification is valid for two years, after which time a recertification exam must be passed. Note that passing any advanced Snowflake certification resets the clock on the SnowPro core certification, so you will have two more years before you’ll need to recertify.
You can execute ...