Dmitry Anoshin, Dmitry Shirokov and Donna Strok

Jumpstart Snowflake

A Step-by-Step Guide to Modern Cloud Analytics

Dmitry Anoshin
British Columbia, Canada
Dmitry Shirokov
Burnaby, BC, Canada
Donna Strok
Seattle, WA, USA
ISBN 978-1-4842-5327-4e-ISBN 978-1-4842-5328-1
https://doi.org/10.1007/978-1-4842-5328-1
© Dmitry Anoshin, Dmitry Shirokov, Donna Strok 2020
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.
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], 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.

To my wife Lana and my kids, Vasily, Anna, and Michael. Without your strong support and love, this book would not exist.

—Dmitry Anoshin

Acknowledgments

I would like to thank the Apress team for the opportunity to deliver this book. Specifically, thank you to Susan McDermott, Laura Berendson, Rita Fernando, and Jessica Vakili for your support, editing, and guidance. We couldn’t have done it without you.

A big thank-you to Kent Graziano who reviewed our book and added a lot of value to it as well as helped us edit and fine-tune the Snowflake information.

—Dmitry Anoshin, Dmitry Shirokov, and Donna Strok

Table of Contents

Chapter 1:​ Getting Started with Cloud Analytics 1
Time to Innovate 2
Key Cloud Computing Concepts 7
Meet Snowflake 14
Summary 17
Chapter 2:​ Getting Started with Snowflake 19
Planning 19
Deciding on a Snowflake Edition 20
Choosing a Cloud Provider and Region 20
Examining Snowflake’s Pricing Model 22
Examining Types of Snowflake Tools 24
Creating a Snowflake Account 26
Connecting to Snowflake 28
Logging Into the Snowflake Web Interface 28
Touring the Web Interface 30
Summary 34
Chapter 3:​ Building a Virtual Warehouse 35
Overview of Snowflake Virtual Warehouses 35
Warehouse Sizes and Features 36
Multicluster Virtual Warehouses 38
Virtual Warehouse Considerations 41
Query Design 41
Caching Impacts 42
Scaling 42
Building a Snowflake Virtual Warehouse 42
Creating a Virtual Warehouse 43
Starting, Resuming, Suspending, and Resizing 45
Using a Warehouse 47
Setting Up Load Monitoring 47
Summary 51
Chapter 4:​ Loading Bulk Data into Snowflake 53
Overview of Bulk Data Loading 54
What Is Bulk Data Loading?​ 54
Bulk Load Locations 55
Supported File Formats and Encoding 55
Compression Handling 56
Encryption Options 57
Bulk Data Loading Recommendations 57
File Preparation 57
File Staging 61
Loading 63
Querying Staged Files 64
Bulk Loading with the Snowflake Web Interface 65
Summary 68
Chapter 5:​ Getting Started with SnowSQL 69
Installing SnowSQL 69
Configuring SnowSQL 73
Connection Settings 74
Configuration Variables 76
SnowSQL Commands 80
Multiple Connection Names 83
Data Loading with SnowSQL 86
Summary 87
Chapter 6:​ Continuous Data Loading with Snowpipe 89
Loading Data Continuously 94
Snowpipe Auto-Ingest 94
Snowpipe REST API Using AWS Lambda 104
Summary 105
Chapter 7:​ Snowflake Administration 107
Administering Roles and Users 108
Enforcement Model 110
Working with Roles and Users 111
Administering Resource Consumption 113
Virtual Warehouse Usage 114
Data Storage Usage 115
Data Transfer Usage 116
Administering Databases and Warehouses 117
Managing Warehouses 117
Managing Databases 118
Zero-Copy Cloning 119
Administering Account Parameters 121
Administering Database Objects 122
Administering Data Shares 123
Administering Clustered Tables 124
Snowflake Materialized Views 126
Summary 128
Chapter 8:​ Snowflake Security Overview 129
Snowflake Security Reference Architecture 130
Virtual Private Cloud 132
Physical Security 133
Network and Site Access 133
Account and User Authentication 134
Object Security 135
Data Security 136
Security Validation 138
Snowflake Audit and Logging 138
Penetration Testing 144
Summary 144
Chapter 9:​ Working with Semistructured Data 147
Supported File Formats 148
Advanced Data Types 149
Working with XML 151
Working with JSON 161
Working with AVRO 167
Working with Parquet 171
Summary 175
Chapter 10:​ Secure Data Sharing 177
Secure Data Sharing 178
Secure Table Sharing 181
Data Sharing Using a Secure View 186
Summary 193
Chapter 11:​ Designing a Modern Analytics Solution with Snowflake 195
Modern Analytics Solution Architecture 196
Snowflake Partner Ecosystem 198
Building Analytics Solutions 200
Summary 211
Chapter 12:​ Snowflake and Data Science 213
Snowflake and the Analytics Ecosystem 214
Snowflake and Apache Spark 215
Connector for Apache Spark 218
Working with Databricks 220
Summary 228
Chapter 13:​ Migrating to Snowflake 229
Data Warehouse Migration Scenarios 230
Startup or Small Business Analytics Scenario 230
On-Premise Analytics Scenario for Enterprises and Large Organizations 231
Cloud Analytics Modernization with Snowflake 233
Data Warehouse Migration Process 234
Organizational Part of the Migration Project 235
Technical Aspects of a Migration Project 244
Real-World Migration Project 246
Additional Resources 248
Summary 249
Chapter 14:​ Time Travel 251
Summary 259
Index 261

About the Authors

Dmitry Anoshin

is a data-centric technologist and recognized expert in building and implementing cloud analytics solutions. He has more than ten years of experience with data engineering and analytics and has worked across different industries in Europe and North America delivering end-to-end analytics solutions in a wide range of industries. His main interest now is cloud analytics with AWS, Azure, and GCP. He helps companies to modernize analytics solutions with cloud technologies and migrate legacy solutions to the cloud.

Moreover, he is the author of five books about various BI tools with PacktPub (SAP Lumira Essentials, Learning Hunk (Splunk), Mastering BI with Microstrategy 10, Tableau Cookbook 2019, and Tableau Desktop Certitfication Guide ). He often presents at Tableau user groups, AWS and Azure user groups, and huge data conferences like Enterprise Data World and Data Summit. He leads the Tableau user group in Victoria, BC, and the Snowflake user groups in Vancouver and Toronto. In addition, he contributes to the leading Canadian analytics firm Rock Your Data ( https://rockyourdata.cloud ) and writes blog posts at https://medium.com/rock-your-data .

Finally, he mentors students in the data analytics program at the University of Victoria and shares cloud analytics knowledge, best practices, and real-world use cases.

 
Dmitrii Shirokov

is a Data Architect & Cloud Analytics Consultant at Rock Your Data, focused on digital transformation, design analytics solutions, data integration and migration, data governance, and cloud/in-house infrastructure. With over 10 years of experience in data analytics and big data, he has the breadth and depth of experience needed to build mature analytical solutions. Before joining Rock Your Data in early 2018, he worked in different companies in tech consulting and banking sectors. Previously, he held various data-engineering positions focusing on data-driven business transformation.

 
Donna Strok

loves all things data and for over 10 years has worked in the field with companies such as Expedia Group, JPMorgan Chase and most recently Amazon. She earned her Bachelors degree in Computer Science and her Masters in Computer Information Systems.

She resides in beautiful Seattle, WA with her cat Dwayne Johnson and in her free time enjoys the wanderlust of world travel. She’s always on the on the hunt for exploring unique grocery stores and amazing hole-in the-wall restaurants, where some of her most memorable meals have been had.

 
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.97.187