Introduction xxi
Assessment Test xxxiii
Answers to Assessment Test xxxviii
Chapter 1: Introduction and Overview 1
Introducing Snowflake 2
Snowflake Journey 3
Snowflake Certifications 3
Signing Up for a Snowflake Trial 5
Summary 13
Exam Essentials 14
Review Questions 15
Chapter 2: Snowflake Architecture 17
Traditional Database Architectures 18
Snowflake's Hybrid Architecture 19
The Three Layers of Snowflake Architecture 20
The Database Storage Layer: Micro-Partitions and Clustering 22
The Query Processing Layer: The Concept of a Virtual
Warehouse 26
Configuring a Virtual Warehouse 30
Summary 32
Exam Essentials 33
Review Questions 34
Chapter 3: Interfaces and Connectivity 39
Snowflake Web UI 40
Web Interface Components 41
Snowflake Partners 46
Snowflake Connectors and Drivers 46
User Defined Functions 47
SnowSQL 47
New Snowflake Web Interface 50
Summary 53
Exam Essentials 53
Review Questions 54
Chapter 4: Loading Data 57
Data Loading in Snowflake 59
Supported File Formats for Loading Data 60
Compression 60
Encryption 60
What Is a Stage? 61
External Stage 62
Internal Stages 62
Data Loading via Internal Stages 64
Loading On-premises Data via the Table Stage 64
Loading On-premises Data via the User Stage 67
Loading On-premises Data via the Named Internal Stage 70
Data Ingestion Using the Named External Stage 73
Loading Data from Cloud Storage via the External Stage 74
Loading Data via the Snowflake Web UI 75
Basic Data Transformations While Ingesting 77
External Tables 79
Semi-Structured Data 81
The VARIANT Data Type and Semi-Structured Data 81
Loading NDJSON Data 82
Loading JSON Data 85
Unloading Data from Snowflake 88
File Formats Supported for Unloading 89
Compression 89
Encryption 90
Unload Data to an On-premises System via an Internal Stage 90
Load Near-Real- Time Streaming Data 91
How Snowpipe Works 92
Optimizing Data Loading and Unloading 93
Bulk Load Optimization 93
Optimizing Snowpipe Loads 93
Optimizing Data Unloading 93
Summary 94
Exam Essentials 94
Review Questions 95
Chapter 5 Data Pipelines 99
Introducing Tasks 100
Scheduling a Task 100
Connecting Multiple Tasks in a Task Tree 104
User-Managed and Serverless Tasks 107
Introducing Streams 110
Capture Changes Using Streams 110
Summary 114
Exam Essentials 114
Review Questions 115
Chapter 6 Continuous Data Protection 117
Components of Continuous Data Protection 118
Data Encryption 118
Access Control 118
Network Policies 119
Time Travel 119
Fail-safe 119
Time Travel and Fail-safe 119
Time Travel in Action 122
Undrop Using Time Travel 127
Time Travel and Fail-Safe Storage Costs 128
Temporary Tables 129
Transient Tables 129
Summary 129
Exam Essentials 130
Review Questions 131
Chapter 7 Cloning and Data Sharing 133
Zero-Copy Cloning 134
How Zero-Copy Cloning Works 135
Database, Schema, and Table Cloning 138
Cloning with Time Travel 142
Secure Data Sharing in Snowflake 143
Direct Sharing 144
Snowflake Data Marketplace 150
Data Exchange 151
Summary 152
Exam Essentials 152
Review Questions 154
Chapter 8 Performance 157
Snowflake Performance Considerations 158
Virtual Warehouse Configuration 160
Virtual Warehouse Scaling Up and Down 160
Scaling Out Using Multi-cluster Virtual Warehouses 161
Caching in Snowflake 164
Metadata Cache 165
Query Result Cache 166
Virtual Warehouse Cache 167
Micro-partition Pruning 168
Micro-partitions and Data Clustering 169
What Happens Behind the Scenes 169
Clustering Keys 170
Search Optimization 171
Materialized Views 171
Summary 172
Exam Essentials 173
Review Questions 175
Chapter 9 Security 179
Data Encryption at Rest 180
Key Rotation and Rekeying 181
Tri-Secret Secure 181
Authentication 181
Multifactor Authentication (MFA) 181
Key Pair Authentication 183
SSO via SAML 2.0 Federated Authentication 183
Password Policies 183
User Provisioning Through SCIM 184
Authorization 184
Access Control in Snowflake 185
Column-Level Security 188
Row-Level Security 188
Secure Views and Secure UDFs 189
User Access Audit Log 189
Network 189
Network Policies 189
Support for Private Connectivity 190
Encryption for All Communication 190
Compliance with Standards 190
Summary 191
Exam Essentials 192
Review Questions 194
Chapter 10 Account and Resource Management 197
Resource Monitors 198
System Usage and Billing 205
The ACCOUNT_USAGE Schema 206
The INFORMATION_SCHEMA Schema 209
Snowflake Releases 214
Phased Release Process for Full Releases 214
Summary 214
Exam Essentials 215
Review Questions 216
Appendix Answers to the Review Questions 219
Chapter 1: Introduction and Overview 220
Chapter 2: Snowflake Architecture 220
Chapter 3: Interfaces and Connectivity 222
Chapter 4: Loading Data 224
Chapter 5: Data Pipelines 226
Chapter 6: Continuous Data Protection 226
Chapter 7: Cloning and Data Sharing 227
Chapter 8: Performance 229
Chapter 9: Security 231
Chapter 10: Account and Resource Management 232
Index 235
