This project-oriented book presents a hands-on approach to identifying migration and performance issues with experience drawn from real-world examples. As you work through the book, you will develop skills, knowledge, and deep understanding of Snowflake tuning options and capabilities while preparing for later incorporation of additional Snowflake features as they become available. Your Snowflake platform will cost less to run and will improve your customer experience. Written by a seasoned Snowflake practitioner, this book is full of practical, hands-on guidance and advice specifically designed to further accelerate your Snowflake journey. Tuning the Snowflake Data Cloud provides you a pathway to success by equipping you with the skills, knowledge, and expertise needed to elevate your Snowflake experience. The book shows you how to leverage what you already know, adds what you don't, and helps you apply it toward delivering for your Snowflake accounts. Read this book to embark on a voyage of advancement and equip your organization to deliver consistent Snowflake performance. What You Will Learn Recognize and understand the root cause of performance bottlenecks Know how to resolve performance issues Develop a deep understanding of Snowflake performance tuning options Reduce expensive mistakes, remediate poorly performing code Manage Snowflake costs.

Intro -- Table of Contents -- About the Author -- About the Technical Reviewer -- Acknowledgments -- Chapter 1: Tuning the Snowflake Data Cloud -- Setting the Scene -- Use Cases for Snowflake -- Provision or Consumption Model -- Refactor or Redesign -- Application Migration to Snowflake -- Migration Guides -- Migration Options -- SnowConvert -- Manual Schema Conversion -- Functionality Lift and Shift -- Greenfield Development -- Replication Considerations -- Tune the Design -- Your First Optimization -- Optimizer Approach -- Query Parsing Order -- FROM Clause -- WHERE Clause -- GROUP BY Clause -- HAVING Clause -- SELECT Clause -- DISTINCT Clause -- ORDER BY Clause -- LIMIT/OFFSET -- SQL Joins -- Explicit Join Notation -- Implicit Join Notation -- Forward Declaration Errors -- Introspection Calls -- Optimizer Statistics -- Summary -- Chapter 2: The Query Optimizer -- Query Lifecycle -- Query Overview -- Query Failure -- Query Compilation -- Tokenization -- Parsing -- Semantic Analysis -- Referential Integrity -- Logical Rewriter -- Micro-Partition Pruner -- Initial Plan Generation -- Plan Rewriter -- Cost-Based Join Ordering -- Physical Query Plan -- Query Execution -- Warehouses -- Single Instruction, Multiple Data (SIMD) -- Compression -- Vectorization -- Flow Control -- Summary -- Chapter 3: The Query Profiler -- Query Profile Overview -- Approach -- Setup -- TPC Data Model -- Initial Population -- Query Profiles -- Accessing Query Profiles -- Running the Query -- Completed Query -- Query History -- Get Query Operator Stats -- Example Query -- Expected Result Count -- Developing an Example Query -- Profiling Your Example Query -- Materializing Your Example Query -- A Good Query Profile -- Build Side -- Probe Side -- Right Deep Join Tree -- Bloom Filter -- Explain Plan -- GET_QUERY_OPERATOR_STATS -- Bad Query Profiles -- Notes on Data Capture. Join Explosion -- What Is a Cartesian Join? -- Identifying Cartesian Joins -- Cartesian Join and Join Explosion Costs -- Remediating Cartesian Joins and Join Explosions -- Long Compilation Time -- What Is Long Compilation Time? -- Identifying Long Compilation Time -- Long Compilation Time Costs -- Remediating Long Compilation Time Queries -- Long Execution Time -- What Is Long Execution Time? -- Identifying Long Execution Time -- Long Execution Time Costs -- Remediating Long Execution Time Queries -- Long Table Scan -- What Is Long Table Scan? -- Identifying Long Table Scans -- Long Table Scan Costs -- Remediating Long Table Scan Queries -- Spills to Disk and Out of Memory -- What Causes a Spill to Disk and OOM? -- Identifying Spills to Disk -- Spills to Disk and OOM Costs -- Remediating Spills to Disk and OOM Queries -- Join Order -- Why Is Join Order Important? -- Identifying Join Order Issues -- Poor Join Order Costs -- Remediating Poor Join Order Issues -- Common Table Expressions -- Simple CTE Use Case -- Reusing CTEs -- CTE Costs -- Remediating CTEs -- Summary -- Chapter 4: Micro-partitions -- Setup -- Foundational Information -- Centralized Storage -- Direct Storage Access -- Storage Costs -- Block Devices -- Database and Table Storage -- Stages -- Micro-partition Overview -- What Are Micro-partitions? -- Immutable Micro-partitions -- Micro-partition Metadata -- Accessing Table Metadata -- Using the Information Schema -- Using the Account Usage Store -- Query Profile -- GET_QUERY_OPERATOR_STATS -- systemclustering_depth and systemclustering_information -- Time Sensitivity -- Data and Micro-partition Lifecycle -- Setting a Baseline -- Data Ingestion -- Data Processing -- Data Consumption -- Time Travel -- Recovered Objects -- Fail-Safe -- Cloned Objects -- Data Sharing and Replication -- Micro-partitions End to End -- Micro-partition Pitfalls. Summary -- Chapter 5: Cluster Keys -- Foundational Information -- Cardinality -- Micro-partition Counts -- Clustering Ratio -- Cluster Width -- Cluster Depth -- Illustrating Cluster Width and Cluster Depth -- Cluster Key Basics -- What Is a Cluster Key? -- Facts Relating to Cluster Keys -- Cluster Keys and Unique Indexes -- Logical Structure and Physical Storage -- Cluster Key Management -- Investigating Unclustered Tables -- Default Clustering on Data Load -- Attribute Cardinality -- Cluster Key Lifecycle -- Investigating a Cluster Key -- average_depth -- average_overlaps -- total_partition_count -- total_constant_partition_count -- Good and Bad Partition Depth Histograms -- Defining a Cluster Key -- Alternative Cluster Keys -- Materialized Views -- Dynamic Tables -- MV and DT Considerations -- Materialized View Query Rewrite -- Automatic Clustering -- Workflow -- Reclustering -- Cost Monitoring -- Summary -- Chapter 6: Warehouses -- Foundational Information -- Memory and Compute -- Warehouse Types -- Warehouse Initialization -- Declaring Warehouses -- Using Warehouses -- Warehouse Capacity -- Warehouse Size and Use Considerations -- Warehouse Scaling -- Scale Up -- Scale Out -- Scale Across -- Query History -- Background Processes -- Query Tags -- Understanding Workloads -- Typical Consumption Pattern -- Default Warehouse Sizing -- Segregating Workload -- Size Matters -- Dynamic Resizing of Warehouses -- Tuning the Design -- Serial or Parallel Logging -- Event Table Implementation -- Event Logging Integration -- Troubleshooting -- Workload Predictability -- Workload Monitoring -- Workload Queueing -- Causes of Queuing -- Measuring Queueing -- Resolving Concurrency Issues -- Reducing Warehouse Concurrency -- Using Summaries, Aggregates, Filters -- Re-timing Processes -- Auto-Suspend Setting -- Snowpipe File Size. Artificial Warehouse Size Constraint -- Object Locking -- Consolidating Workloads -- Load Testing -- Snowflake and CSP Improvements -- Performance Evaluation -- Parallel Loading -- Snowflake-Supplied Sample Load Test -- Tasks and Streams -- External Parallelism Explained -- Create an External Parallelism Component -- Testing External Parallelism -- Monitoring Queueing -- Restricting Resource Consumption -- STATEMENT_TIMEOUT_IN_SECONDS -- STATEMENT_QUEUED_TIMEOUT_IN_SECONDS -- USER_TASK_TIMEOUT_MS -- MAX_CONCURRENCY_LEVEL -- Resource Monitors -- Serverless Compute -- Snowpipe -- Tasks -- Query Acceleration Service -- Summary -- Chapter 7: Search Optimization Service -- Search Optimization Service Explained -- Optimal Use Scenarios -- Excluded Use Scenarios -- Search Optimization Implementation -- Estimating Table Search Optimization Costs -- Enabling Table Search Optimization -- Enabling Attribute Search Optimization -- Table Type Support -- Standard Table -- Dynamic Table -- Transient Table -- Temporary Table -- Conclusion -- Disabling Table Search Optimization -- Timeliness -- Best Practices -- Summary -- Chapter 8: Parallelization -- Foundational Information -- Data Products -- Ingest -- Curate -- Produce -- Distribution Venues -- Snowflake Marketplaces -- Snowflake Regions and CSPs -- CSP Marketplaces -- Iceberg, Platforms, and S3-Compatible Storage Support -- Logging -- Optimizing Data Processing -- Problem Statement -- Warehouse Factors -- Ingest Factors -- Curation Factors -- Parallel Processing -- Setting Up Application Tables -- Testing Core Table Load -- Core Table Segmentation -- Concurrent Warehouse Processing -- Stream Interaction -- Testing Streams -- Creating Stored Procedures -- Testing a Single Load -- Grant Entitlement -- Create Tasks -- Purging a Stream -- Suspend Tasks -- Load Testing -- Concluding Steps -- Temporal Loads. Real-World Impact -- Summary -- Chapter 9: Client Expectations -- Entitlement Models -- Embedded Entitlement Model -- Prefiltered Entitlement -- Filter Engine Overview -- External Entitlement Component -- Entitlement Data Model -- Source Data Feeds -- Curated Data Product -- Filter Engine -- Client-Specific Shares -- Unentitled Data Sharing -- Creating Managed Accounts -- Creating Share Containers -- Unentitled Objects -- Importing a Share -- Entitled Data Sharing -- Designing a Filter Engine -- Filter Engine Requirements -- Filter Engine Model -- Client -- Client Object -- Client Filter -- Client Mask -- Denormalize Client Information -- SQL Statement -- Client SQL Statement View -- Building a Filter Engine -- Deploying Generated Code -- Setting the Standard -- Imported Database Entitlement -- Sample SQL for Common Use Cases -- Client Collaboration -- Historized Data -- Data Model -- Data Catalog -- Shared Tag References -- Multiple Shares of Same Data -- Hydration Approach -- Summary -- Chapter 10: Optimizing Performance -- Early Design Decisions -- Snowflake Edition Costs -- Data Model Approach -- Platform Differences -- Logging -- Role-Based Access Control -- Declare Constraints -- Transient or Permanent Tables? -- Warehouse Considerations -- Workload Monitoring -- Managed (or Reader) Accounts -- Replication -- Multiplatform Distribution -- Consumption Monitoring -- Optimizing Consumption -- Benchmark CSP Performance -- Query Performance -- Warehouse Monitor -- Cost Management Screen -- Query History -- Query Profile -- Explain Plan -- GET_QUERY_OPERATOR_STATS -- Optimizing Code -- Time Travel Setting -- Use Clones -- Warehouse AUTO_SUSPEND -- Warehouse Size -- Warehouse Usage -- Warehouse Scaling Policy -- Warehouse Mode -- Bind Variables -- Eliminate SELECT * -- Eliminate DISTINCT -- Examine Common Table Expressions (CTEs) -- Window Functions. Returned Query Attributes.