How to Optimize Snowflake Queries with Materialized Views

Day 69/100 – Snowflake SnowPro Core Question Question: What is the primary use of Tasks in Snowflake? A) To mask sensitive data in real-time B) To schedule and automate SQL statements C) To store semi-structured JSON data D) To monitor credit usage across warehouses 💡 Answer: B – To schedule and automate SQL statements ✅ Explanation: Tasks let you schedule recurring SQL statements (e.g., MERGE, INSERT, transformations). Can run standalone or chained into DAGs (Directed Acyclic Graphs). Often used together with Streams for incremental ELT automation.

Snowflake quietly introduced a powerful new feature last month that takes Dynamic Tables to the next level: the IMMUTABLE WHERE constraint 🎯 This feature lets you lock specific portions of your dynamic tables, ensuring they remain untouched during refreshes. ✅ Lower recomputation and refresh costs ✅ Easier pipeline evolution and migration ✅ Safer updates to base tables and schema changes 💡 When to use IMMUTABLE WHERE • You need to delete data from base tables without affecting downstream dynamic tables • You want to modify a dynamic table’s definition (like adding new columns) without recomputing existing rows • You want to skip reprocessing historical records when a dimension table changes • You’re migrating an existing pipeline to dynamic tables and want to preserve computed data This small but mighty feature can significantly simplify how you manage refresh strategies in Snowflake. Read More: https://lnkd.in/dCMEtxmh #Snowflake #DataEngineering #DynamicTables #SQL #DataPipelines #SnowflakeSquad

#Snowflake Dynamic Tables (DT) have been one of the most popular features I've seen us release, they've saved countless hours of data engineering time. Instead of using Streams, Tasks, Stored Procedures, etc. (STSPs) and the management / monitoring that comes with them, you simply define your end state in a SQL statement and we take care of the rest. They shouldn't be used everywhere, not every problem is a nail and you may need STSPs for more control, but they can help your data teams deliver more data products with less effort, increasing their productivity. One thing that caught me out was even though we call them "Dynamic Tables", in one respect I think they behaved more like views, in that if the source tables the DT's rely on are truncated, the next time the DT runs you'll get zero records. This meant you had to keep that "raw" data in Snowflake, when perhaps you knew that it would never be needed / accessed, which is annoying... Well, not any more, we now have immutability constraints for Dynamic Tables! This means you can lock specific portions of your Dynamic Tables so they never change during refreshes. From our docs: "By marking specific portions of a dynamic table as immutable, you can perform the following tasks: - Prevent propagation of updates or deletions to existing data. - Restrict inserts, updates, and deletes for rows that meet a condition. - Limit future modifications while still enabling incremental updates to other parts of the table." To find out more check our documentation here https://lnkd.in/erRtUVKT and see our tech blog here https://lnkd.in/eRbwJVUD

Snowflake just made Dynamic Tables even more powerful — introducing Immutability and Backfill! Dynamic Tables in Snowflake already simplify pipelines — define your logic once, and Snowflake automatically keeps it fresh with incremental updates. Now, Snowflake is adding two features that will cut down on recomputation costs and simplify pipeline evolutiondramatically: 1. Immutability — Lock data that never needs to change With the new IMMUTABLE WHERE constraint, you can lock parts of your Dynamic Tables so they never get recomputed or updated during refreshes. That means less data churn, faster refreshes, and lower compute costs. Example : CREATE DYNAMIC TABLE location_statistics TARGET_LAG='1 minute' WAREHOUSE=my_wh IMMUTABLE WHERE (hour < CURRENT_TIMESTAMP() - INTERVAL '1 day') AS SELECT location, DATE_TRUNC(HOUR, txn_time) AS hour, SUM(amount) AS total_amount FROM transactions JOIN customer_info USING (customer_id) GROUP BY location, hour; Here, aggregates older than a day are immutable — no need to recompute them when dimension data changes. 2. Backfill — Seamlessly migrate or evolve pipelines You can now backfill a Dynamic Table from an existing table without reprocessing all historical data. Example: CREATE DYNAMIC TABLE location_statistics TARGET_LAG='1 minute' WAREHOUSE=my_wh IMMUTABLE WHERE (hour < CURRENT_TIMESTAMP() - INTERVAL '1 day') BACKFILL FROM historical_statistics AS SELECT location, DATE_TRUNC(HOUR, txn_time) AS hour, SUM(amount) AS total_amount, AVG(amount) AS avg_amount FROM transactions JOIN customer_info USING (customer_id) GROUP BY location, hour; This is a huge win for teams migrating legacy pipelines to Dynamic Tables — or adding new metrics (like AVG(amount)) without having to rebuild everything from scratch. Snowflake Dynamic Tables + Immutability + Backfill = 🔹 Less recomputation 🔹 Lower refresh cost 🔹 Easier pipeline evolution 🔹 Seamless migration https://lnkd.in/g_zSNRP3 #Snowflake #DataEngineering #ETL #DynamicTables #Immutability #Backfill #DataPipelines #CloudData #DataInnovation #ModernDataStack

❄Snowflakers ❄📢 Filtering specific elements inside your JSON values using SQL! 🚨🧐 We will see that FILTER lambda function will help you to automatically KEEP specific information elements that follow your business rules in ARRAY element! 📋In this example, you can see how you have to apply the FILTER function into your ARRAY JSON formatted column values, in order to keep all your item elements that accomplish that quantity*subtotal (total) > 800. 🫵 Forget to using complex SQL queries! ♾️ Follow me here and Medium https://lnkd.in/dZjfenTm for more tips! May the ❄ be with you! Amilee Alesna #Snowflake #DataSuperhero #DataEngineering #Monitoring #Snowsight #SQL #snowflakedevelopers #dataengineering #superdataheroes

Let's build a real-time Change Data Capture (CDC) pipeline from PostgreSQL to Snowflake using Snowflake Openflow. This quickstart uses a full demo dataset (healthcare appointments) to show you how to: - Set up PostgreSQL Logical Replication for CDC. - Configure the Openflow connector to stream initial snapshots and incremental changes. - Run real-time analytics on transactional data in Snowflake. - Unlock immediate access to your live business data for faster insights. Get started: https://lnkd.in/g5hzGjJ8

Check this out → we just published cohort & retention SQL templates for Snowflake & BigQuery. If you ever need to measure which users stick (and why), these could save you hours. 🔗 https://lnkd.in/eXsCYg9g

Another fantastic blog from Ganesh Bharathan on query optimization for Snowflake! Read his analysis with example code below! In the blog he provides a deep dive into the following topics: 🏆 Data modeling and query compilation 🏆 Concurrency and warehouse sizing 🏆 Join explosion 🏆 Large table scans 🏆 Data retention and archiving 🏆 Disk spillage 🏆.Complex queries https://lnkd.in/ergJqeQf

Is your data scattered across multiple files of the same type in #Databricks, and you’re not sure which ones contain the records you need? Here’s a quick fix — use the _metadata.file_path field to easily identify the source files. Give it a try! #Databricks #SQL #DeltaLakehouse #datacalalog