Mastering Snowflake Solutions: Supporting Analytics and Data Sharing

✍ Scribed by Adam Morton

• Publisher: Apress
• Year: 2022
• Tongue: English
• Leaves: 243
• Category: Library

✦ Synopsis

Design for large-scale, high-performance queries using Snowflake’s query processing engine to empower data consumers with timely, comprehensive, and secure access to data. This book also helps you protect your most valuable data assets using built-in security features such as end-to-end encryption for data at rest and in transit. It demonstrates key features in Snowflake and shows how to exploit those features to deliver a personalized experience to your customers. It also shows how to ingest the high volumes of both structured and unstructured data that are needed for game-changing business intelligence analysis.
Mastering Snowflake Solutions starts with a refresher on Snowflake’s unique architecture before getting into the advanced concepts that make Snowflake the market-leading product it is today. Progressing through each chapter, you will learn how to leverage storage, query processing, cloning, data sharing, and continuous data protection features. This approach allows for greater operational agility in responding to the needs of modern enterprises, for example in supporting agile development techniques via database cloning. The practical examples and in-depth background on theory in this book help you unleash the power of Snowflake in building a high-performance system with little to no administrative overhead. Your result from reading will be a deep understanding of Snowflake that enables taking full advantage of Snowflake’s architecture to deliver value analytics insight to your business.

What You Will Learn

• Optimize performance and costs associated with your use of the Snowflake data platform
• Enable data security to help in complying with consumer privacy regulations such as CCPA and GDPR
• Share data securely both inside your organization and with external partners
• Gain visibility to each interaction with your customersusing continuous data feeds from Snowpipe
• Break down data silos to gain complete visibility your business-critical processes
• Transform customer experience and product quality through real-time analytics

Who This Book Is for
Data engineers, scientists, and architects who have had some exposure to the Snowflake data platform or bring some experience from working with another relational database. This book is for those beginning to struggle with new challenges as their Snowflake environment begins to mature, becoming more complex with ever increasing amounts of data, users, and requirements. New problems require a new approach and this book aims to arm you with the practical knowledge required to take advantage of Snowflake’s unique architecture to get the results you need.

✦ Table of Contents

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Snowflake Architecture
Technology and Data Are Inseparable
Unlocking Business Value
Business Agility Is More Important Than Ever
All Hail the Cloud!
Decisions, Decisions, Decisions!
Snowflake Architecture
Database Storage
Micro Partitions
What Is the Benefit of Micro Partitioning?
Partitioning in the Pre-Snowflake World
Data Clustering
Virtual Warehouses
Caching
Result Cache
Local Disk Cache
Configuring Virtual Warehouses
Number of Clusters
Scaling Policy
Auto Suspend
Query Processing
Cloud Services
Authentication
Infrastructure Management
Metadata Management
Query Parsing and Execution
Access Control
Summary
Chapter 2: Data Movement
Stages
External Stages
External Tables and Data Lakes
Internal Stages
User
Table
Named
File Formats
The COPY INTO Command
COPY INTO Syntax
Transformations
Data Loading Considerations
File Preparation
Semistructured Data
Dedicated Virtual Warehouses
Partitioning Staged Data
Loading Data
Loading Using the Web UI
Unloading Data from Snowflake
Bulk vs. Continuous Loading
Continuous Data Loads Using Snowpipe
Streams and Tasks
Change Tracking Using Streams
Stream Metadata Columns
Tasks
Bringing It All Together
The Example Scenario
Steps
Summary
Chapter 3: Cloning
A Word on Performance Testing
Testing with Data
Forget the Past!
Sensitive Data
Why Clone an Object?
Working with Clones
Which Objects Can Be Cloned?
Clone Permissions
Bringing It All Together
The Example Scenario
Steps
Summary
Chapter 4: Managing Security and Access Control
Roles
Role Hierarchy
Inheritance
Objects
Extending the Role Hierarchy
User and Application Authentication
Multi-Factor Authentication
MFA Caching
Security Assertion Markup Language
OAuth
Key Pair Authentication
Storage Integration
Network Policies
Option 1: Native Network Security
Option 2: Network Policies
Option 3: Cloud Service Provider Capabilities
Handling PII Data
Separately Storing PII Data
Removing Data in Bulk
Auditing
Controlling Access to PII Data
Row Access Policies
Example Scenario
Steps
Advanced Snowflake Security Features
Future Grants
Managed Access Schemas
Summary
Chapter 5: Protecting Data in Snowflake
Data Encryption
Encryption Key Management
Customer Managed Keys
Time Travel
Data Retention Periods
Querying Historical Data
Dropping and Undropping Historical Data
Fail-safe
Underlying Storage Concepts
Temporary and Transient Tables
Bringing It All Together
Summary
Chapter 6: Business Continuity and Disaster Recovery
Regions and Availability Zones
Data Replication, Failover, and Failback
Primary and Secondary Databases
Promoting Databases
Client Redirect
Business Continuity
Process Flow
Monitoring Replication Progress
Reconciling the Process
Data Loss
Bringing It All Together
The Example Scenario
Steps
Step 1: Configure Replication and Failover
Step 2: Select an Account to Replicate the Data to
Step 3: Create a Secondary Database on the Replicated Account
Step 4: Monitor the Initial Data Refresh
Step 5: Schedule Ongoing Data Refreshes
Summary
Chapter 7: Data Sharing and the Data Cloud
The Data Cloud
Data Sharing
The Data Marketplace
Monetization
Data Exchange
Providers and Consumers
What Is a Share?
Reader Accounts
Using a Dedicated Database for Data Sharing
Data Clean Rooms
Bringing It All Together
The Example Scenario
Summary
Chapter 8: Programming
Creating New Tables
Create Table Like
Create Table as Select
Create Table Clone
Copy Grants
Stored Procedures
User-Defined Functions
Scalar Functions
Table Functions
SQL Variables
Transactions
Transactions Within Stored Procedures
Locking and Deadlocks
Transaction Tips
Bringing It All Together
The Example Scenario
Steps
Summary
Chapter 9: Advanced Performance Tuning
Designing Tables for High Performance
Data Clustering
Clustering Key
Pruning Efficiency
Clustering Depth
Reclustering
Designing High-Performance Queries
Optimizing Queries
Cardinality
Materialized Views
Search Optimization Service
Optimizing Warehouse Utilization
Warehouse Utilization Patterns
Leveraging Caching
Monitoring Resources and Account Usage
Resource Monitors
Query History
Useful References
Summary
Chapter 10: Developing Applications in Snowflake
Introduction to SnowSQL
Versions and Updates
Config File
Authentication
Using SnowSQL
Data Engineering
Java User-Defined Functions
Snowpark
Data Frames
Combining Snowpark and UDFs
Connectors
Snowflake Connector for Python
Querying Data
Asynchronous and Synchronous Queries
The Query ID
Snowflake Connector for Kafka
A Solution Architecture Example
Summary
Index