100 Snowflake Cost Optimization Techniques

by World-Class Snowflake Expert, former Data Superhero and SnowPro Certification SME

What you'll learn

Create and run cost-effective queries

Consolidate underutilized warehouses

Monitor and improve query performance

How using a bigger warehouse could cost you less

Watch over longest running and frequently executed queries

Serverless features in detail: cost and how they work

Avoid cost traps by setting different parameter values

How to combine different Snowflake editions

Save on costs with parallel data transfer and processing

Design and architect applications with cost impact in mind

Requirements

Basic knowledge of the Snowflake Data Cloud

Basic knowledge of SQL

Optional knowledge of some basic programming in Python

Description

What You Will Learn About SpendingHow to use even bigger virtual warehouses for less expensive queries.How to avoid huge cost traps in Snowflake by changing different parameter values.How to optimize queries, compute, storage and overall costs in Snowflake.How to properly consolidate underutilized warehouses.How each serverless feature in Snowflake works and how to estimate their cost.How to use parallel data transfer and processing everywhere you can.How to combine different Snowflake editions in your organization.How to use better data visualizations to estimate spending.How to reduce your spending on all your Snowflake accounts.I also offer over 300 high-quality presentation slides with the summary on each tip or technique!Where You May Use This KnowledgeDrastically reduce costs on your own Snowflake accounts.Create more Snowflake accounts - with different editions - and use them efficiently.Help your clients reduce spending on their Snowflake accounts.Help your employer reduce costs on Snowflake in your own organization.Learn to recognize the traps most people fall into when using Snowflake.Create not just highly performant, but also cost-effective SQL queries.Learn how to get cheaper queries with huge warehouses, using hundreds of nodes.I also offer an open-source GitHub repository with dozens of hands-on exercises and experiments!What You Will Learn About SnowflakeLearn all about the virtual warehouses.Learn how to monitor and optimize queries.Learn about resource monitors and budgets.Learn query acceleration and search optimizationEstimate efficient automatic clustering and maintenance of materialized views.Learn all that matters about time travel and zero-copy cloning.Learn how to efficiently deploy and combine Snowflake with other applications.I have many brief but focused hands-on presentations of different Snowflake features!Who I AmThe only world-class expert from Canada selected for the Snowflake Data Superhero program in 2021.SnowPro Certification SME (Subject Matter Expert) - many SnowPro exam questions have been created by me.Passed four SnowPro certification exams to date (with no retakes): Core, Architect, Data Engineer, Data Analyst.Specialized in Snowflake for the past few years: I worked for Snowflake Partner companies, I served dozens of clients in this capacity or as an independent consultant, today I share my knowledge with highly specialize courses on Snowflake.This is truly "the" Bible on Snowflake spending!No other course, book or documentation around will offer as much insights, hands-on experiments and knowledge transfer on optimizing the cost on Snowflake as my course here, guaranteed!Enroll today, and keep this course forever!

Overview

Section 1: Introduction

Lecture 1 Course Structure and Content

Lecture 2 Best Ways to Benefit from this Course

Lecture 3 Create a Free Trial Snowflake Account

Lecture 4 Free Hands-On Project Setup

Section 2: Virtual Warehouses

Lecture 5 Introduction to Virtual Warehouses

Lecture 6 Tip #1: Larger Virtual Warehouses May Actually Cost You Less

Lecture 7 Tip #2: Auto-Suspend Any Warehouse After One Minute

Lecture 8 Tip #3: Any Resumed Warehouse Will Cost You at Least One Minute

Lecture 9 Tip #4: Never Auto-Suspend Any Warehouse After Less Than One Minute

Lecture 10 Tip #5: X-Small Warehouses Could Be Powerful Enough

Lecture 11 Tip #6: Resized Warehouses are for More Complex Queries

Lecture 12 Tip #7: Multi-Cluster Warehouses are for Multiple Users and Concurrency

Lecture 13 Tip #8: Multi-Cluster Warehouses Should Always Have Min Clusters 1

Lecture 14 Tip #9: Use Economy Scaling Policy To Save Money

Lecture 15 Tip #10: When to Use Snowpark-Optimized Warehouses

Section 3: Compute Workloads

Lecture 16 Introduction to Compute Workloads

Lecture 17 Tip #11: Use Resource Monitors

Lecture 18 Tip #12: Use Account-Level Budgets

Lecture 19 Tip #13: Prevent Never-Ending Queries

Lecture 20 Tip #14: Manually Kill Running Queries

Lecture 21 Tip #15: Reduce Warehouse Sizes

Lecture 22 Tip #16: Consolidate All Warehouses

Lecture 23 Tip #17: Use Parallel Jobs for Batch Transformations

Lecture 24 Tip #18: Avoid Checking Too Much on Metadata

Lecture 25 Tip #19: Charts for Warehouse Monitoring

Lecture 26 Tip #20: Revisit the Main Traps with Warehouses

Section 4: Snowflake Accounts

Lecture 27 Introduction to Snowflake Accounts

Lecture 28 Tip #21: What to Choose for a Free Trial Account

Lecture 29 Tip #22: When to Use a Free Trial Account

Lecture 30 Tip #23: Understand Price Tables for Virtual Warehouse Compute Services

Lecture 31 Tip #24: Understand Price Tables for Cloud and Serverless Services

Lecture 32 Tip #25: Understand Price Tables for Storage and Data Transfer

Lecture 33 Tip #26: Use the Account Overview Interface in Snowsight

Lecture 34 Tip #27: Use Organization Accounts

Lecture 35 Tip #28: Limit Warehouse Changes with Access Control

Lecture 36 Tip #29: Adjust Default Values of Account-Level Parameters

Lecture 37 Tip #30: Careful with Reader Accounts

Section 5: Snowflake Editions

Lecture 38 Introduction to Snowflake Editions

Lecture 39 Tip #31: When to Choose Enterprise over Standard Edition

Lecture 40 Tip #32: How to Avoid Multi-Cluster Warehouses

Lecture 41 Tip #33: When to Use Incremental Materializations

Lecture 42 Tip #34: How to Emulate Materialized Views

Lecture 43 Tip #35: The Case for Extended Time Travel

Lecture 44 Tip #36: Use Standard Edition Account for Analytics

Lecture 45 Tip #37: Use Separate Standard Edition Account for Common Queries

Lecture 46 Tip #38: How to Reduce Costs to Zero for an Inactive Paid Account

Lecture 47 Tip #39: When to Choose the Business Critical Edition

Lecture 48 Tip #40: When to Choose the Virtual Private Snowflake (VPS) Edition

Section 6: Query Monitoring

Lecture 49 Introduction to Query Monitoring

Lecture 50 Tip #41: Monitor Longest Running Queries

Lecture 51 Tip #42: Interpret Query History

Lecture 52 Tip #43: More Charts for Query Monitoring

Lecture 53 Tip #44: Use Query Tags

Lecture 54 Tip #45: Reduce Frequency of Simple Queries

Lecture 55 Tip #46: Reduce Frequency of Metadata Queries

Lecture 56 Tip #47: Reduce Frequency of SHOW Commands

Lecture 57 Tip #48: Clone Less Frequently

Lecture 58 Tip #49: Change Query Schedules

Lecture 59 Tip #50: Parallel over Sequential Transfer and Processing

Section 7: Query Optimization

Lecture 60 Introduction to Query Optimization

Lecture 61 Tip #51: Use the Query Profile

Lecture 62 Tip #52: Use the Explain Statement

Lecture 63 Tip #53: Use Data Caching

Lecture 64 Tip #54: Queries on Data Lakes

Lecture 65 Tip #55: Use Vectorized Python UDFs

Lecture 66 Tip #56: Use Batch Commands to Prevent Transaction Locks

Lecture 67 Tip #57: Reduce Query Complexity and Compilation Time

Lecture 68 Tip #58: Check for Cross Joins and Exploding Joins

Lecture 69 Tip #59: Process Only New or Updated Data

Lecture 70 Tip #60: Remote Spillage Optimization

Section 8: Serverless Features

Lecture 71 Introduction to Serverless Features

Lecture 72 Tip #61: Monitor the Cost of Automated Jobs

Lecture 73 Tip #62: Estimate Cost of Scheduled Tasks

Lecture 74 Tip #63: When to Use Serverless Tasks

Lecture 75 Tip #64: Replace Snowpipe with Snowpipe Streaming

Lecture 76 Tip #65: Estimate Cost of Automatic Clustering on Tables

Lecture 77 Tip #66: Estimate Cost of the Query Acceleration Service (QAS)

Lecture 78 Tip #67: Estimate Cost of the Search Optimization Service (SOS)

Lecture 79 Tip #68: Reduce Materialized Views Maintenance Cost

Lecture 80 Tip #69: Reduce Database Replication Cost

Lecture 81 Tip #70: Estimate Cost of Hybrid Tables

Section 9: Data Storage

Lecture 82 Introduction to Data Storage

Lecture 83 Tip #71: Use On-Demand Storage When You Don’t Know Your Spending Pattern

Lecture 84 Tip #72: Copy and Keep Less Data

Lecture 85 Tip #73: Lower Data Retention with No Time Travel

Lecture 86 Tip #74: Estimate Storage Cost of the Fail-Safe

Lecture 87 Tip #75: Use Transient or Temporary Tables

Lecture 88 Tip #76: Use Zero-Copy Cloning

Lecture 89 Tip #77: Clone Less Data

Lecture 90 Tip #78: Ensure Tables Are Clustered Correctly

Lecture 91 Tip #79: Drop Unused Tables and Other Objects

Lecture 92 Tip #80: Remove Old Files from Stage Areas

Section 10: Data Transfer

Lecture 93 Introduction to Data Transfer

Lecture 94 Tip #81: Data In is Free, Data Out is Expensive

Lecture 95 Tip #82: Choose the same Provider and Region Where Your Data Is

Lecture 96 Tip #83: External Access Integrations vs External Functions

Lecture 97 Tip #84: Use Data Compression

Lecture 98 Tip #85: Use Batch Transfer with Path Partitioning

Lecture 99 Tip #86: Use Bulk Loads instead of Single-Row Inserts

Lecture 100 Tip #87: Use Parallel Data Uploading

Lecture 101 Tip #88: Design Cost-Effective Data Pipelines

Lecture 102 Tip #89: Use External Tables in a Data Lake

Lecture 103 Tip #90: Query Parquet Files instead of CSV

Section 11: Snowflake Apps

Lecture 104 Introduction to Snowflake Apps

Lecture 105 Tip #91: Estimate Cost Impact of Data Sharing in Snowflake

Lecture 106 Tip #92: Estimate Cost Impact of Client and Server (Snowpark) Applications

Lecture 107 Tip #93: Estimate Cost Impact of Streamlit in Snowflake and Native Applications

Lecture 108 Tip #94: Estimate Cost Impact of Data Science Applications

Lecture 109 Tip #95: Check All Connected Applications

Lecture 110 Tip #96: Third-Party Apps Saving Money Will Spend Money

Lecture 111 Tip #97: Free Marketplace Native Apps Will Cost Money

Lecture 112 Tip #98: Keep App Versions Updated

Lecture 113 Tip #99: Cache Data in Third-Party Tools

Lecture 114 Tip #100: Auto-Abort Running Queries from Disconnected Apps

Section 12: Wrapping Up

Lecture 115 Congratulations, You Made It!

Lecture 116 Bonus Lecture

Snowflake account owners who want to reduce their spending,Snowflake consultants who want to advise their clients on Snowflake spending,Anyone using Snowflake in their organization who wants to control their costs,Managers looking to cut their Snowflake spending by half,Technical persons who want to learn about all ways to cut spending on this data warehouse,Data Engineers and Architects looking to create cost-effective apps around Snowflake,Data Scientists who want to avoid spending traps when using Snowflake for Data Science apps