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 4. Exploring Snowflake SQL Commands, Data Types, and Functions

As we learned in previous chapters, Snowflake was built to store data in an optimized, compressed, columnar format within a relational database. Snowflake’s data end users need to access the stored data and be able to give instructions to perform tasks, call functions, and execute queries on the data. The way that can be accomplished is with the standard programming language for relational databases, Structured Query Language (SQL). Snowflake supports SQL:ANSI, the most common standardized version of SQL. In addition to SQL support for structured data, Snowflake offers native support for semi-structured data formats such as JSON and XML. Snowflake also supports unstructured data.

The main focus of this chapter is on learning the fundamentals of using Snowflake worksheets to execute a variety of SQL commands using different data types and functions. Other than using worksheets in the Snowflake web UI, it is possible to use a Snowflake-native command-line client, known as SnowSQL, to create and execute SQL commands. More detail about SnowSQL will be provided in Chapter 6.

Besides connecting to Snowflake via the web UI or SnowSQL, you can use ODBC and JDBC drivers to access Snowflake data through external applications such as Tableau and Looker. We’ll explore connections to Tableau and Looker in Chapter 12. Native connectors such as Python and Spark can also be used to develop applications for connecting to Snowflake. ...