Table of Contents

Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii

1. Getting Started. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Snowflake Web User Interfaces 3
Prep Work 3
Snowsight Orientation 6
Snowsight Preferences 6
Navigating Snowsight Worksheets 8
Context Setting 8
Improved Productivity 14
Snowflake Community 17
Snowflake Certifications 19
Snowday and Snowflake Summit Events 19
Important Caveats About Code Examples in the Book 19
Code Cleanup 21
Summary 21
Knowledge Check 22

2. Creating and Managing the Snowflake Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Prep Work 23
Traditional Data Platform Architectures 24
Shared-Disk (Scalable) Architecture 24
Shared-Nothing (Scalable) Architecture 25
NoSQL Alternatives 25
The Snowflake Architecture 26
The Cloud Services Layer 27
Managing the Cloud Services Layer 28
Billing for the Cloud Services Layer 28
The Query Processing (Virtual Warehouse) Compute Layer 29

v
 Virtual Warehouse Size 30
Scaling Up a Virtual Warehouse to Process Large Data Volumes and
Complex Queries 31
Scaling Out with Multicluster Virtual Warehouses to Maximize
Concurrency 35
Creating and Using Virtual Warehouses 38
Separation of Workloads and Workload Management 42
Billing for the Virtual Warehouse Layer 44
Centralized (Hybrid Columnar) Database Storage Layer 45
Introduction to Zero-Copy Cloning 46
Introduction to Time Travel 46
Billing for the Storage Layer 46
Snowflake Caching 46
Query Result Cache 47
Metadata Cache 48
Virtual Warehouse Local Disk Cache 49
Code Cleanup 50
Summary 50
Knowledge Check 51

3. Creating and Managing Snowflake Securable Database Objects. . . . . . . . . . . . . . . . . . . . . . . 53

Prep Work 54
Creating and Managing Snowflake Databases 54
Creating and Managing Snowflake Schemas 63
INFORMATION_SCHEMA 65
ACCOUNT_USAGE Schema 69
Schema Object Hierarchy 70
Introduction to Snowflake Tables 70
Creating and Managing Views 76
Introduction to Snowflake Stages: File Format Included 79
Extending SQL with Stored Procedures and UDFs 82
User-Defined Function (UDF): Task Included 84
Secure SQL UDTF That Returns Tabular Value (Market Basket Analysis
Example) 87
Stored Procedures 89
Introduction to Pipes, Streams, and Sequences 95
Snowflake Streams (Deep Dive) 96
Snowflake Tasks (Deep Dive) 102
Code Cleanup 108
Summary 108
Knowledge Check 109

vi | Table of Contents
4. Exploring Snowflake SQL Commands, Data Types, and Functions. . . . . . . . . . . . . . . . . . . . 111
Prep Work 112
Working with SQL Commands in Snowflake 112
DDL Commands 113
DCL Commands 113
DML Commands 114
TCL Commands 114
DQL Command 114
SQL Query Development, Syntax, and Operators in Snowflake 115
SQL Development and Management 115
Query Syntax 117
Query Operators 124
Long-Running Queries, and Query Performance and Optimization 125
Snowflake Query Limits 126
Introduction to Data Types Supported by Snowflake 126
Numeric Data Types 127
String and Binary Data Types 129
Date and Time Input/Output Data Types 130
Semi-Structured Data Types 131
Unstructured Data Types 137
How Snowflake Supports Unstructured Data Use 138
Snowflake SQL Functions and Session Variables 141
Using System-Defined (Built-In) Functions 141
Creating SQL and JavaScript UDFs and Using Session Variables 144
External Functions 144
Code Cleanup 145
Summary 145
Knowledge Check 146

5. Leveraging Snowflake Access Controls. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

Prep Work 148
Creating Snowflake Objects 150
Snowflake System-Defined Roles 154
Creating Custom Roles 155
Functional-Level Business and IT Roles 157
System-Level Service Account and Object Access Roles 158
Role Hierarchy Assignments: Assigning Roles to Other Roles 159
Granting Privileges to Roles 162
Assigning Roles to Users 165
Testing and Validating Our Work 166
User Management 169
Role Management 176
Snowflake Multi-Account Strategy 178

Table of Contents | vii

 Managing Users and Groups with SCIM 178
Code Cleanup 179
Summary 180
Knowledge Check 180

6. Data Loading and Unloading. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183

Prep Work 184
Basics of Data Loading and Unloading 184
Data Types 185
File Formats 185
Data File Compression 189
Frequency of Data Processing 189
Snowflake Stage References 190
Data Sources 191
Data Loading Tools 192
Snowflake Worksheet SQL Using INSERT INTO and INSERT ALL
Commands 192
Web UI Load Data Wizard 204
SnowSQL CLI SQL PUT and COPY INTO Commands 210
Data Pipelines 212
Third-Party ETL and ELT Tools 221
Alternatives to Loading Data 222
Tools to Unload Data 222
Data Loading Best Practices for Snowflake Data Engineers 223
Select the Right Data Loading Tool and Consider the Appropriate Data
Type Options 224
Avoid Row-by-Row Data Processing 224
Choose the Right Snowflake Virtual Warehouse Size and Split Files as
Needed 225
Transform Data in Steps and Use Transient Tables for Intermediate Results 225
Code Cleanup 225
Summary 225
Knowledge Check 226

7. Implementing Data Governance, Account Security, and Data Protection and Recovery. . 227
Prep Work 228
Snowflake Security 230
Controlling Account Access 231
Monitoring Activity with the Snowflake ACCESS_HISTORY Account
Usage View 235
Data Protection and Recovery 237
Replication and Failover 243
Democratizing Data with Data Governance Controls 244

viii | Table of Contents

 INFORMATION_SCHEMA Data Dictionary 245
Object Tagging 245
Classification 249
Data Masking 251
Row Access Policies and Row-Level Security 253
External Tokenization 256
Secure Views and UDFs 257
Object Dependencies 257
Code Cleanup 258
Summary 258
Knowledge Check 258

8. Managing Snowflake Account Costs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261

Prep Work 262
Snowflake Monthly Bill 262
Storage Fees 264
Data Transfer Costs 264
Compute Credits Consumed 265
Creating Resource Monitors to Manage Virtual Warehouse Usage and
Reduce Costs 266
Resource Monitor Credit Quota 268
Resource Monitor Credit Usage 268
Resource Monitor Notifications and Other Actions 269
Resource Monitor Rules for Assignments 269
DDL Commands for Creating and Managing Resource Monitors 271
Using Object Tagging for Cost Centers 276
Querying the ACCOUNT_USAGE View 276
Using BI Partner Dashboards to Monitor Snowflake Usage and Costs 277
Snowflake Agile Software Delivery 278
Why Do We Need DevOps? 278
Continuous Data Integration, Continuous Delivery, and
Continuous Deployment 279
What Is Database Change Management? 280
How Zero-Copy Cloning Can Be Used to Support Dev/Test Environments 281
Code Cleanup 284
Summary 284
Knowledge Check 285

9. Analyzing and Improving Snowflake Query Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . 287

Prep Work 287
Analyzing Query Performance 287
QUERY_HISTORY Profiling 288
HASH() Function 288

Table of Contents | ix
 Web UI History 289
Understanding Snowflake Micro-Partitions and Data Clustering 291
Partitions Explained 291
Snowflake Micro-Partitions Explained 294
Snowflake Data Clustering Explained 299
Clustering Width and Depth 299
Choosing a Clustering Key 303
Creating a Clustering Key 305
Reclustering 306
Performance Benefits of Materialized Views 306
Exploring Other Query Optimization Techniques 308
Search Optimization Service 308
Query Optimization Techniques Compared 309
Summary 310
Code Cleanup 310
Knowledge Check 310

10. Configuring and Managing Secure Data Sharing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313

Snowflake Architecture Data Sharing Support 314
The Power of Snowgrid 314
Data Sharing Use Cases 314
Snowflake Support for Unified ID 2.0 315
Snowflake Secure Data Sharing Approaches 316
Prep Work 317
Snowflake’s Direct Secure Data Sharing Approach 318
Creating Outbound Shares 318
How Inbound Shares Are Used by Snowflake Data Consumers 330
How to List and Shop on the Public Snowflake Marketplace 333
Snowflake Marketplace for Providers 335
Standard Versus Personalized Data Listings 337
Harnessing the Power of a Snowflake Private Data Exchange 340
Snowflake Data Clean Rooms 341
Important Design, Security, and Performance Considerations 342
Share Design Considerations 342
Share Security Considerations 343
Share Performance Considerations 343
Difference Between Database Sharing and Database Cloning 343
Data Shares and Time Travel Considerations 344
Sharing of Data Shares 344
Summary 344
Code Cleanup 345
Knowledge Check 345

x | Table of Contents
11. Visualizing Data in Snowsight. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Prep Work 347
Data Sampling in Snowsight 348
Fixed-Size Sampling Based on a Specific Number of Rows 349
Fraction-Based Sampling Based on Probability 349
Previewing Fields and Data 349
Sampling Examples 352
Using Automatic Statistics and Interactive Results 353
Snowsight Dashboard Visualization 358
Creating a Dashboard and Tiles 358
Working with Chart Visualizations 361
Aggregating and Bucketing Data 363
Editing and Deleting Tiles 366
Collaboration 367
Sharing Your Query Results 368
Using a Private Link to Collaborate on Dashboards 368
Summary 369
Code Cleanup 369
Knowledge Check 370

12. Workloads for the Snowflake Data Cloud. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371

Prep Work 372
Data Engineering 372
Data Warehousing 374
Data Vault 2.0 Modeling 374
Transforming Data within Snowflake 377
Data Lake 377
Data Collaboration 378
Data Monetization 379
Regulatory and Compliance Requirements for Data Sharing 379
Data Analytics 380
Advanced Analytics for the Finance Industry 380
Advanced Analytics for the Healthcare Industry 381
Advanced Analytics for the Manufacturing Industry and Logistics Services 382
Marketing Analytics for Retail Verticals and the Communications and
Media Industry 382
Data Applications 383
Data Science 385
Snowpark 385
Streamlit 388
Cybersecurity Using Snowflake as a Security Data Lake 388
Overcoming the Challenges of a SIEM-Only Architecture 389
Search Optimization Service Versus Clustering 392

Table of Contents | xi
 Unistore 401
Transactional Workload Versus Analytical Workload 401
Hybrid Tables 402
Summary 403
Code Cleanup 403
Knowledge Check 404

A. Answers to the Knowledge Check Questions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405

B. Snowflake Object Naming Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423

C. Setting Up a Snowflake Trial Account. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427

Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431

xii | Table of Contents