Mastering Snowflake Solutions : Supporting Analytics and Data Sharing

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 customers using 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 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