Table Of ContentMastering
Snowf lake
Solutions
Supporting Analytics and Data Sharing
—
Adam Morton
Mastering Snowflake
Solutions
Supporting Analytics and
Data Sharing
Adam Morton
Mastering Snowflake Solutions: Supporting Analytics and Data Sharing
Adam Morton
Sydney, NSW, Australia
ISBN-13 (pbk): 978-1-4842-8028-7 ISBN-13 (electronic): 978-1-4842-8029-4
https://doi.org/10.1007/978-1-4842-8029-4
Copyright © 2022 by Adam Morton
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the
material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation,
broadcasting, reproduction on microfilms or in any other physical way, and transmission or information
storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now
known or hereafter developed.
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with
every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an
editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the
trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not
identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to
proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication,
neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or
omissions that may be made. The publisher makes no warranty, express or implied, with respect to the
material contained herein.
Managing Director, Apress Media LLC: Welmoed Spahr
Acquisitions Editor: Jonathan Gennick
Development Editor: Laura Berendson
Coordinating Editor: Jill Balzano
Copyeditor: Mary Behr
Cover image designed by Freepik (www.freepik.com)
Distributed to the book trade worldwide by Springer Science+Business Media LLC, 1 New York Plaza, Suite
4600, New York, NY 10004. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm.
com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner)
is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware
corporation.
For information on translations, please e-mail [email protected]; for reprint, paperback,
or audio rights, please e-mail [email protected].
Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and
licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales
web page at www.apress.com/bulk-sales.
Any source code or other supplementary material referenced by the author in this book is available to
readers on GitHub at https://github.com/Apress/mastering-snowflake-solutions.
Printed on acid-free paper
Table of Contents
About the Author �����������������������������������������������������������������������������������������������������xi
About the Technical Reviewer �������������������������������������������������������������������������������xiii
Acknowledgments ���������������������������������������������������������������������������������������������������xv
Introduction �����������������������������������������������������������������������������������������������������������xvii
Chapter 1: Snowflake Architecture ���������������������������������������������������������������������������1
Technology and Data Are Inseparable �������������������������������������������������������������������������������������������1
Unlocking Business Value ��������������������������������������������������������������������������������������������������������1
Business Agility Is More Important Than Ever �������������������������������������������������������������������������2
All Hail the Cloud! ��������������������������������������������������������������������������������������������������������������������3
Decisions, Decisions, Decisions! ���������������������������������������������������������������������������������������������������4
Snowflake Architecture �����������������������������������������������������������������������������������������������������������������4
Database Storage ��������������������������������������������������������������������������������������������������������������������������6
Micro Partitions �����������������������������������������������������������������������������������������������������������������������������6
What Is the Benefit of Micro Partitioning? ������������������������������������������������������������������������������������8
Partitioning in the Pre-Snowflake World ���������������������������������������������������������������������������������������9
Data Clustering �����������������������������������������������������������������������������������������������������������������������������9
Virtual Warehouses �����������������������������������������������������������������������������������������������������������������������9
Caching ���������������������������������������������������������������������������������������������������������������������������������������10
Result Cache ��������������������������������������������������������������������������������������������������������������������������11
Local Disk Cache �������������������������������������������������������������������������������������������������������������������11
Configuring Virtual Warehouses ��������������������������������������������������������������������������������������������������12
Number of Clusters ����������������������������������������������������������������������������������������������������������������12
Scaling Policy ������������������������������������������������������������������������������������������������������������������������14
Auto Suspend ������������������������������������������������������������������������������������������������������������������������15
iii
Table of ConTenTs
Query Processing ������������������������������������������������������������������������������������������������������������������������15
Cloud Services ����������������������������������������������������������������������������������������������������������������������������16
Authentication �����������������������������������������������������������������������������������������������������������������������16
Infrastructure Management ���������������������������������������������������������������������������������������������������16
Metadata Management ���������������������������������������������������������������������������������������������������������16
Query Parsing and Execution ������������������������������������������������������������������������������������������������17
Access Control �����������������������������������������������������������������������������������������������������������������������17
Summary�������������������������������������������������������������������������������������������������������������������������������������18
Chapter 2: Data Movement �������������������������������������������������������������������������������������19
Stages �����������������������������������������������������������������������������������������������������������������������������������������19
External Stages ���������������������������������������������������������������������������������������������������������������������20
External Tables and Data Lakes ���������������������������������������������������������������������������������������������20
Internal Stages ����������������������������������������������������������������������������������������������������������������������22
File Formats ��������������������������������������������������������������������������������������������������������������������������������22
The COPY INTO Command �����������������������������������������������������������������������������������������������������������24
COPY INTO Syntax ������������������������������������������������������������������������������������������������������������������25
Transformations ���������������������������������������������������������������������������������������������������������������������26
Data Loading Considerations ������������������������������������������������������������������������������������������������������27
File Preparation ���������������������������������������������������������������������������������������������������������������������27
Semistructured Data��������������������������������������������������������������������������������������������������������������28
Dedicated Virtual Warehouses �����������������������������������������������������������������������������������������������29
Partitioning Staged Data ��������������������������������������������������������������������������������������������������������30
Loading Data �������������������������������������������������������������������������������������������������������������������������31
Loading Using the Web UI ������������������������������������������������������������������������������������������������������31
Unloading Data from Snowflake �������������������������������������������������������������������������������������������������32
Bulk vs� Continuous Loading �������������������������������������������������������������������������������������������������������32
Continuous Data Loads Using Snowpipe ������������������������������������������������������������������������������������33
Streams and Tasks ����������������������������������������������������������������������������������������������������������������������34
Change Tracking Using Streams ��������������������������������������������������������������������������������������������35
Stream Metadata Columns ����������������������������������������������������������������������������������������������������36
Tasks �������������������������������������������������������������������������������������������������������������������������������������36
iv
Table of ConTenTs
Bringing It All Together ����������������������������������������������������������������������������������������������������������������39
The Example Scenario �����������������������������������������������������������������������������������������������������������39
Steps �������������������������������������������������������������������������������������������������������������������������������������40
Summary�������������������������������������������������������������������������������������������������������������������������������������52
Chapter 3: Cloning ��������������������������������������������������������������������������������������������������53
A Word on Performance Testing ��������������������������������������������������������������������������������������������������53
Testing with Data ������������������������������������������������������������������������������������������������������������������������54
Forget the Past!���������������������������������������������������������������������������������������������������������������������������55
Sensitive Data �����������������������������������������������������������������������������������������������������������������������������56
Why Clone an Object? �����������������������������������������������������������������������������������������������������������������57
Working with Clones �������������������������������������������������������������������������������������������������������������������58
Which Objects Can Be Cloned? ���������������������������������������������������������������������������������������������59
Clone Permissions �����������������������������������������������������������������������������������������������������������������60
Bringing It All Together ����������������������������������������������������������������������������������������������������������������62
The Example Scenario �����������������������������������������������������������������������������������������������������������63
Steps �������������������������������������������������������������������������������������������������������������������������������������63
Summary�������������������������������������������������������������������������������������������������������������������������������������65
Chapter 4: Managing Security and Access Control ������������������������������������������������67
Roles �������������������������������������������������������������������������������������������������������������������������������������������67
Role Hierarchy �����������������������������������������������������������������������������������������������������������������������69
Inheritance ����������������������������������������������������������������������������������������������������������������������������70
Objects ����������������������������������������������������������������������������������������������������������������������������������������72
Extending the Role Hierarchy ������������������������������������������������������������������������������������������������������72
User and Application Authentication �������������������������������������������������������������������������������������������75
Multi-Factor Authentication ���������������������������������������������������������������������������������������������������75
MFA Caching ��������������������������������������������������������������������������������������������������������������������������80
Security Assertion Markup Language ������������������������������������������������������������������������������������80
OAuth �������������������������������������������������������������������������������������������������������������������������������������81
Key Pair Authentication ���������������������������������������������������������������������������������������������������������82
v
Table of ConTenTs
Storage Integration ���������������������������������������������������������������������������������������������������������������������83
Network Policies �������������������������������������������������������������������������������������������������������������������������83
Option 1: Native Network Security �����������������������������������������������������������������������������������������84
Option 2: Network Policies ����������������������������������������������������������������������������������������������������84
Option 3: Cloud Service Provider Capabilities �����������������������������������������������������������������������85
Handling PII Data �������������������������������������������������������������������������������������������������������������������������85
Separately Storing PII Data ����������������������������������������������������������������������������������������������������86
Removing Data in Bulk ����������������������������������������������������������������������������������������������������������86
Auditing ���������������������������������������������������������������������������������������������������������������������������������87
Controlling Access to PII Data ������������������������������������������������������������������������������������������������87
Row Access Policies �������������������������������������������������������������������������������������������������������������������93
Example Scenario ������������������������������������������������������������������������������������������������������������������93
Steps �������������������������������������������������������������������������������������������������������������������������������������93
Advanced Snowflake Security Features��������������������������������������������������������������������������������������97
Future Grants �������������������������������������������������������������������������������������������������������������������������97
Managed Access Schemas ����������������������������������������������������������������������������������������������������97
Summary�������������������������������������������������������������������������������������������������������������������������������������98
Chapter 5: Protecting Data in Snowflake ����������������������������������������������������������������99
Data Encryption �������������������������������������������������������������������������������������������������������������������������100
Encryption Key Management �����������������������������������������������������������������������������������������������101
Customer Managed Keys �����������������������������������������������������������������������������������������������������102
Time Travel ��������������������������������������������������������������������������������������������������������������������������������104
Data Retention Periods ��������������������������������������������������������������������������������������������������������104
Querying Historical Data ������������������������������������������������������������������������������������������������������105
Dropping and Undropping Historical Data ���������������������������������������������������������������������������106
Fail-safe ������������������������������������������������������������������������������������������������������������������������������������107
Underlying Storage Concepts ����������������������������������������������������������������������������������������������������107
Temporary and Transient Tables ������������������������������������������������������������������������������������������108
Bringing It All Together ��������������������������������������������������������������������������������������������������������108
Summary�����������������������������������������������������������������������������������������������������������������������������������111
vi
Table of ConTenTs
Chapter 6: Business Continuity and Disaster Recovery ����������������������������������������113
Regions and Availability Zones ��������������������������������������������������������������������������������������������������114
Data Replication, Failover, and Failback������������������������������������������������������������������������������������114
Primary and Secondary Databases �������������������������������������������������������������������������������������115
Promoting Databases ����������������������������������������������������������������������������������������������������������116
Client Redirect ���������������������������������������������������������������������������������������������������������������������116
Business Continuity ������������������������������������������������������������������������������������������������������������������117
Process Flow �����������������������������������������������������������������������������������������������������������������������117
Monitoring Replication Progress �����������������������������������������������������������������������������������������121
Reconciling the Process ������������������������������������������������������������������������������������������������������122
Data Loss �����������������������������������������������������������������������������������������������������������������������������123
Bringing It All Together ��������������������������������������������������������������������������������������������������������������123
The Example Scenario ���������������������������������������������������������������������������������������������������������124
Steps �����������������������������������������������������������������������������������������������������������������������������������125
Summary�����������������������������������������������������������������������������������������������������������������������������������129
Chapter 7: Data Sharing and the Data Cloud ��������������������������������������������������������131
The Data Cloud ��������������������������������������������������������������������������������������������������������������������������132
Data Sharing �����������������������������������������������������������������������������������������������������������������������������135
The Data Marketplace ���������������������������������������������������������������������������������������������������������136
Providers and Consumers����������������������������������������������������������������������������������������������������137
What Is a Share? �����������������������������������������������������������������������������������������������������������������137
Reader Accounts������������������������������������������������������������������������������������������������������������������140
Using a Dedicated Database for Data Sharing ��������������������������������������������������������������������140
Data Clean Rooms ���������������������������������������������������������������������������������������������������������������������141
Bringing It All Together ��������������������������������������������������������������������������������������������������������������142
The Example Scenario ���������������������������������������������������������������������������������������������������������142
Summary�����������������������������������������������������������������������������������������������������������������������������������148
vii
Table of ConTenTs
Chapter 8: Programming ��������������������������������������������������������������������������������������149
Creating New Tables �����������������������������������������������������������������������������������������������������������������149
Create Table Like �����������������������������������������������������������������������������������������������������������������149
Create Table as Select ���������������������������������������������������������������������������������������������������������150
Create Table Clone ���������������������������������������������������������������������������������������������������������������150
Copy Grants �������������������������������������������������������������������������������������������������������������������������151
Stored Procedures ��������������������������������������������������������������������������������������������������������������������152
User-Defined Functions �������������������������������������������������������������������������������������������������������������154
Scalar Functions ������������������������������������������������������������������������������������������������������������������154
Table Functions �������������������������������������������������������������������������������������������������������������������156
SQL Variables ����������������������������������������������������������������������������������������������������������������������������156
Transactions ������������������������������������������������������������������������������������������������������������������������������158
Transactions Within Stored Procedures �������������������������������������������������������������������������������159
Locking and Deadlocks �������������������������������������������������������������������������������������������������������161
Transaction Tips �������������������������������������������������������������������������������������������������������������������161
Bringing It All Together ��������������������������������������������������������������������������������������������������������������162
The Example Scenario ���������������������������������������������������������������������������������������������������������162
Steps �����������������������������������������������������������������������������������������������������������������������������������162
Summary�����������������������������������������������������������������������������������������������������������������������������������166
Chapter 9: Advanced Performance Tuning �����������������������������������������������������������167
Designing Tables for High Performance ������������������������������������������������������������������������������������167
Data Clustering ��������������������������������������������������������������������������������������������������������������������168
Designing High-Performance Queries ���������������������������������������������������������������������������������������172
Optimizing Queries ��������������������������������������������������������������������������������������������������������������172
Materialized Views ��������������������������������������������������������������������������������������������������������������178
Search Optimization Service �����������������������������������������������������������������������������������������������180
Optimizing Warehouse Utilization ���������������������������������������������������������������������������������������������183
Warehouse Utilization Patterns �������������������������������������������������������������������������������������������186
Leveraging Caching �������������������������������������������������������������������������������������������������������������192
viii
Table of ConTenTs
Monitoring Resources and Account Usage �������������������������������������������������������������������������������193
Resource Monitors ��������������������������������������������������������������������������������������������������������������193
Query History �����������������������������������������������������������������������������������������������������������������������195
Useful References ���������������������������������������������������������������������������������������������������������������197
Summary�����������������������������������������������������������������������������������������������������������������������������������198
Chapter 10: Developing Applications in Snowflake ����������������������������������������������201
Introduction to SnowSQL ����������������������������������������������������������������������������������������������������������201
Versions and Updates ����������������������������������������������������������������������������������������������������������203
Config File ����������������������������������������������������������������������������������������������������������������������������204
Authentication ���������������������������������������������������������������������������������������������������������������������205
Using SnowSQL �������������������������������������������������������������������������������������������������������������������206
Data Engineering �����������������������������������������������������������������������������������������������������������������������210
Java User-Defined Functions �����������������������������������������������������������������������������������������������210
Snowpark ����������������������������������������������������������������������������������������������������������������������������211
Combining Snowpark and UDFs ������������������������������������������������������������������������������������������213
Connectors ��������������������������������������������������������������������������������������������������������������������������������215
Snowflake Connector for Python �����������������������������������������������������������������������������������������215
Querying Data ����������������������������������������������������������������������������������������������������������������������216
Snowflake Connector for Kafka �������������������������������������������������������������������������������������������217
A Solution Architecture Example �����������������������������������������������������������������������������������������217
Summary�����������������������������������������������������������������������������������������������������������������������������������219
Index ���������������������������������������������������������������������������������������������������������������������221
ix