Tag: sql

Revolutionary Declarative Data Pipelines That Transform ETL

In 2025, Snowflake Dynamic Tables have become the most powerful way to build automated data pipelines. This comprehensive guide covers everything from target lag configuration to incremental refresh strategies, with real-world examples showing how dynamic tables eliminate complex orchestration code and transform pipeline creation through simple SQL statements.

For years, building data pipelines meant wrestling with Streams, Tasks, complex scheduling logic, and dependency management. Dynamic tables changed everything. Now data engineers define the end state they want, and Snowflake handles all the orchestration automatically. The impact is remarkable: pipelines that previously required hundreds of lines of procedural code now need just a single CREATE DYNAMIC TABLE statement.

These tables automatically detect changes in base tables, incrementally update results, and maintain freshness targets—all without external orchestration tools. Leading enterprises use them to build production-ready pipelines processing billions of rows daily, achieving both faster development and lower operational costs.

---

What Are Snowflake Dynamic Tables and Why They Matter

Snowflake Dynamic Tables are specialized tables that automatically maintain query results through intelligent refresh processes. Unlike traditional tables that require manual updates, dynamic tables continuously monitor source data changes and update themselves based on defined freshness requirements.

Core Concept Explained

When you create a Snowflake Dynamic Table, you define a query that transforms data from base tables. Snowflake then takes full responsibility for refreshing the table, managing dependencies, and optimizing the refresh process. This declarative approach represents a fundamental shift from imperative pipeline coding.

The traditional approach:

sql

-- Old way: Manual orchestration with Streams and Tasks
CREATE STREAM sales_stream ON TABLE raw_sales;

CREATE TASK refresh_daily_sales
  WAREHOUSE = compute_wh
  SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('sales_stream')
AS
  MERGE INTO daily_sales_summary dst
  USING (
    SELECT product_id, 
           DATE_TRUNC('day', sale_date) as day,
           SUM(amount) as total_sales
    FROM sales_stream
    GROUP BY 1, 2
  ) src
  ON dst.product_id = src.product_id 
     AND dst.day = src.day
  WHEN MATCHED THEN UPDATE SET total_sales = src.total_sales
  WHEN NOT MATCHED THEN INSERT VALUES (src.product_id, src.day, src.total_sales);

The Snowflake Dynamic Tables approach:

sql

-- New way: Simple declarative definition
CREATE DYNAMIC TABLE daily_sales_summary
  TARGET_LAG = '5 minutes'
  WAREHOUSE = compute_wh
  AS
    SELECT product_id,
           DATE_TRUNC('day', sale_date) as day,
           SUM(amount) as total_sales
    FROM raw_sales
    GROUP BY 1, 2;

The second approach achieves the same result with 80% less code and zero orchestration logic.

How Automated Refresh Works

Snowflake Dynamic Tables use a sophisticated two-step refresh process:

Step 1: Change Detection Snowflake analyzes the dynamic table’s query and creates a Directed Acyclic Graph (DAG) based on dependencies. Behind the scenes, Snowflake creates lightweight streams on base tables to capture change metadata (only ROW_ID, operation type, and timestamp—minimal storage cost).

Step 2: Incremental Merge Only detected changes are incorporated into the dynamic table. This incremental processing dramatically reduces compute consumption compared to full table refreshes. For queries that support it (most aggregations, joins, and filters), Snowflake automatically uses incremental mode.

Real-world example: A global retailer processes 50 million daily transactions. When 10,000 new orders arrive, their Snowflake Dynamic Table refreshes in seconds by processing only those 10,000 rows—not the entire 50 million row history.

---

Understanding Target Lag Configuration

Target lag defines how fresh your data needs to be. It’s the maximum acceptable delay between changes in base tables and their reflection in the dynamic table.

Target Lag Options and Trade-offs

sql

-- High freshness (low lag) for real-time dashboards
CREATE DYNAMIC TABLE real_time_metrics
  TARGET_LAG = '1 minute'
  WAREHOUSE = small_wh
  AS SELECT * FROM live_events WHERE event_time > CURRENT_TIMESTAMP - INTERVAL '1 hour';

-- Moderate freshness for hourly reports  
CREATE DYNAMIC TABLE hourly_summary
  TARGET_LAG = '30 minutes'
  WAREHOUSE = medium_wh
  AS SELECT DATE_TRUNC('hour', ts) as hour, COUNT(*) FROM events GROUP BY 1;

-- Lower freshness (higher lag) for daily aggregates
CREATE DYNAMIC TABLE daily_rollup
  TARGET_LAG = '6 hours'
  WAREHOUSE = large_wh
  AS SELECT DATE(ts) as day, SUM(revenue) FROM sales GROUP BY 1;

Trade-off considerations:

• Lower target lag = More frequent refreshes = Higher compute costs = Fresher data
• Higher target lag = Less frequent refreshes = Lower compute costs = Older data

Using DOWNSTREAM Lag for Pipeline DAGs

For pipeline DAGs with multiple Snowflake Dynamic Tables, use TARGET_LAG = DOWNSTREAM:

sql

-- Layer 1: Base transformation
CREATE DYNAMIC TABLE customer_events_cleaned
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = compute_wh
  AS
    SELECT customer_id, event_type, event_time
    FROM raw_events
    WHERE event_time IS NOT NULL;

-- Layer 2: Aggregation (defines the lag requirement)
CREATE DYNAMIC TABLE customer_daily_summary
  TARGET_LAG = '15 minutes'
  WAREHOUSE = compute_wh
  AS
    SELECT customer_id, 
           DATE(event_time) as day,
           COUNT(*) as event_count
    FROM customer_events_cleaned
    GROUP BY 1, 2;

The upstream table (customer_events_cleaned) automatically inherits the 15-minute lag from its downstream consumer. This ensures the entire pipeline maintains consistent freshness without redundant configuration.

---

Comparing Dynamic Tables vs Streams and Tasks

Understanding when to use Dynamic Tables versus traditional Streams and Tasks is critical for optimal pipeline architecture.

When to Use Dynamic Tables

Choose Dynamic Tables when:

• You need declarative, SQL-only transformations without procedural code
• Your pipeline has straightforward dependencies that form a clear DAG
• You want automatic incremental processing without manual merge logic
• Time-based freshness (target lag) meets your requirements
• You prefer Snowflake to automatically manage refresh scheduling
• Your transformations involve standard SQL operations (joins, aggregations, filters)

Choose Streams and Tasks when:

• You need fine-grained control over exact refresh timing
• Your pipeline requires complex conditional logic beyond SQL
• You need event-driven triggers from external systems
• Your workflow involves cross-database operations or external API calls
• You require custom error handling and retry logic
• Your processing needs transaction boundaries across multiple steps

Dynamic Tables vs Materialized Views

Feature	Snowflake Dynamic Tables	Materialized Views
Query complexity	Supports joins, unions, aggregations, window functions	Limited to single table aggregations
Refresh control	Configurable target lag	Fixed automatic refresh
Incremental processing	Yes, for most queries	Yes, but limited query support
Chainability	Can build multi-table DAGs	Limited chaining
Clustering keys	Supported	Not supported
Best for	Complex transformation pipelines	Simple aggregations on single tables

Example where Dynamic Tables excel:

sql

-- Complex multi-table join with aggregation
CREATE DYNAMIC TABLE customer_lifetime_value
  TARGET_LAG = '1 hour'
  WAREHOUSE = compute_wh
  AS
    SELECT 
      c.customer_id,
      c.customer_name,
      COUNT(DISTINCT o.order_id) as total_orders,
      SUM(o.order_amount) as lifetime_value,
      MAX(o.order_date) as last_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    WHERE c.customer_status = 'active'
    GROUP BY 1, 2;

This query would be impossible in a materialized view but works perfectly in Dynamic Tables.

---

Incremental vs Full Refresh

Dynamic Tables automatically choose between incremental and full refresh modes based on your query patterns.

Understanding Refresh Modes

Incremental refresh (default for most queries):

• Processes only changed rows since last refresh
• Dramatically reduces compute costs
• Works for most aggregations, joins, and filters
• Requires deterministic queries

Full refresh (fallback for complex scenarios):

• Reprocesses entire dataset on each refresh
• Required for non-deterministic functions
• Used when change tracking isn’t feasible
• Higher compute consumption

sql

-- This uses incremental refresh automatically
CREATE DYNAMIC TABLE sales_by_region
  TARGET_LAG = '10 minutes'
  WAREHOUSE = compute_wh
  AS
    SELECT region, 
           SUM(sales_amount) as total_sales
    FROM transactions
    WHERE transaction_date >= '2025-01-01'
    GROUP BY region;

-- This forces full refresh (non-deterministic function)
CREATE DYNAMIC TABLE random_sample_data
  TARGET_LAG = '1 hour'
  WAREHOUSE = compute_wh
  REFRESH_MODE = FULL  -- Explicitly set to FULL
  AS
    SELECT * 
    FROM large_dataset
    WHERE RANDOM() < 0.01;  -- Non-deterministic

Forcing Incremental Mode

You can explicitly force incremental mode for supported queries:

sql

CREATE DYNAMIC TABLE optimized_pipeline
  TARGET_LAG = '5 minutes'
  WAREHOUSE = compute_wh
  REFRESH_MODE = INCREMENTAL  -- Explicitly set
  AS
    SELECT customer_id,
           DATE(order_time) as order_date,
           COUNT(*) as order_count,
           SUM(order_total) as daily_revenue
    FROM orders
    WHERE order_time > CURRENT_TIMESTAMP - INTERVAL '90 days'
    GROUP BY 1, 2;

---

Production Best Practices

Building reliable production pipelines requires following proven patterns.

Performance Optimization tips

Break down complex transformations:

sql

-- Bad: Single complex dynamic table
CREATE DYNAMIC TABLE complex_report
  TARGET_LAG = '15 minutes'
  WAREHOUSE = compute_wh
  AS
    -- 500 lines of complex SQL with multiple CTEs, joins, window functions
    ...;

-- Good: Multiple simple dynamic tables
CREATE DYNAMIC TABLE cleaned_events
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = compute_wh
  AS
    SELECT customer_id, event_type, CAST(event_time AS TIMESTAMP) as event_time
    FROM raw_events
    WHERE event_time IS NOT NULL;

CREATE DYNAMIC TABLE enriched_events  
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = compute_wh
  AS
    SELECT e.*, c.customer_segment
    FROM cleaned_events e
    JOIN customers c ON e.customer_id = c.customer_id;

CREATE DYNAMIC TABLE final_report
  TARGET_LAG = '15 minutes'
  WAREHOUSE = compute_wh
  AS
    SELECT customer_segment, 
           DATE(event_time) as day,
           COUNT(*) as event_count
    FROM enriched_events
    GROUP BY 1, 2;

Monitoring and Debugging

Monitor your Tables through Snowsight or SQL:

sql

-- Show all dynamic tables
SHOW DYNAMIC TABLES;

-- Get detailed information about refresh history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY('daily_sales_summary'))
ORDER BY data_timestamp DESC
LIMIT 10;

-- Check if dynamic table is using incremental refresh
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY(
  'my_dynamic_table'
))
WHERE refresh_action = 'INCREMENTAL';

-- View the DAG for your pipeline
-- In Snowsight: Go to Data → Databases → Your Database → Dynamic Tables
-- Click on a dynamic table to see the dependency graph visualization

Cost Optimization Strategies

Right-size your warehouse:

sql

-- Small warehouse for simple transformations
CREATE DYNAMIC TABLE lightweight_transform
  TARGET_LAG = '10 minutes'
  WAREHOUSE = x_small_wh  -- Start small
  AS SELECT * FROM source WHERE active = TRUE;

-- Large warehouse only for heavy aggregations  
CREATE DYNAMIC TABLE heavy_analytics
  TARGET_LAG = '1 hour'
  WAREHOUSE = large_wh  -- Size appropriately
  AS
    SELECT product_category,
           date,
           COUNT(DISTINCT customer_id) as unique_customers,
           SUM(revenue) as total_revenue
    FROM sales_fact
    JOIN product_dim USING (product_id)
    GROUP BY 1, 2;

Use clustering keys for large tables:

sql

CREATE DYNAMIC TABLE partitioned_sales
  TARGET_LAG = '30 minutes'
  WAREHOUSE = medium_wh
  CLUSTER BY (sale_date, region)  -- Improves refresh performance
  AS
    SELECT sale_date, region, product_id, SUM(amount) as sales
    FROM transactions
    GROUP BY 1, 2, 3;

---

Real-World Use Cases

Use Case 1: Real-Time Analytics Dashboard

Scenario: E-commerce company needs up-to-the-minute sales dashboards

sql

-- Real-time order metrics
CREATE DYNAMIC TABLE real_time_order_metrics
  TARGET_LAG = '2 minutes'
  WAREHOUSE = reporting_wh
  AS
    SELECT 
      DATE_TRUNC('minute', order_time) as minute,
      COUNT(*) as order_count,
      SUM(order_total) as revenue,
      AVG(order_total) as avg_order_value
    FROM orders
    WHERE order_time >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
    GROUP BY 1;

-- Product inventory status  
CREATE DYNAMIC TABLE inventory_status
  TARGET_LAG = '5 minutes'
  WAREHOUSE = operations_wh
  AS
    SELECT 
      p.product_id,
      p.product_name,
      p.stock_quantity,
      COALESCE(SUM(o.quantity), 0) as pending_orders,
      p.stock_quantity - COALESCE(SUM(o.quantity), 0) as available_stock
    FROM products p
    LEFT JOIN order_items o ON p.product_id = o.product_id
    WHERE o.order_status = 'pending'
    GROUP BY 1, 2, 3;

Use Case 2:Change Data Capture Pipelines

Scenario: Financial services company tracks account balance changes

sql

-- Capture all balance changes
CREATE DYNAMIC TABLE account_balance_history
  TARGET_LAG = '1 minute'
  WAREHOUSE = finance_wh
  AS
    SELECT 
      account_id,
      transaction_id,
      transaction_time,
      transaction_amount,
      SUM(transaction_amount) OVER (
        PARTITION BY account_id 
        ORDER BY transaction_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) as running_balance
    FROM transactions
    ORDER BY account_id, transaction_time;

-- Daily account summaries
CREATE DYNAMIC TABLE daily_account_summary
  TARGET_LAG = '15 minutes'
  WAREHOUSE = finance_wh
  AS
    SELECT 
      account_id,
      DATE(transaction_time) as summary_date,
      MIN(running_balance) as min_balance,
      MAX(running_balance) as max_balance,
      COUNT(*) as transaction_count
    FROM account_balance_history
    GROUP BY 1, 2;

Use Case 3: Slowly Changing Dimensions

Scenario: Type 2 SCD implementation for customer dimension

sql

-- Customer SCD Type 2 with dynamic table
CREATE DYNAMIC TABLE customer_dimension_scd2
  TARGET_LAG = '10 minutes'
  WAREHOUSE = etl_wh
  AS
    WITH numbered_changes AS (
      SELECT 
        customer_id,
        customer_name,
        customer_address,
        customer_segment,
        update_timestamp,
        ROW_NUMBER() OVER (
          PARTITION BY customer_id 
          ORDER BY update_timestamp
        ) as version_number
      FROM customer_changes_stream
    )
    SELECT 
      customer_id,
      version_number,
      customer_name,
      customer_address,
      customer_segment,
      update_timestamp as valid_from,
      LEAD(update_timestamp) OVER (
        PARTITION BY customer_id 
        ORDER BY update_timestamp
      ) as valid_to,
      CASE 
        WHEN LEAD(update_timestamp) OVER (
          PARTITION BY customer_id 
          ORDER BY update_timestamp
        ) IS NULL THEN TRUE
        ELSE FALSE
      END as is_current
    FROM numbered_changes;

Use Case 4:Multi-Layer Data Mart Architecture

Scenario: Building a star schema data mart with automated refresh

sql

-- Bronze layer: Data cleaning
CREATE DYNAMIC TABLE bronze_sales
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = etl_wh
  AS
    SELECT 
      CAST(sale_id AS NUMBER) as sale_id,
      CAST(sale_date AS DATE) as sale_date,
      CAST(customer_id AS NUMBER) as customer_id,
      CAST(product_id AS NUMBER) as product_id,
      CAST(quantity AS NUMBER) as quantity,
      CAST(unit_price AS DECIMAL(10,2)) as unit_price
    FROM raw_sales
    WHERE sale_id IS NOT NULL;

-- Silver layer: Business logic
CREATE DYNAMIC TABLE silver_sales_enriched
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
  AS
    SELECT 
      s.*,
      s.quantity * s.unit_price as total_amount,
      c.customer_segment,
      p.product_category,
      p.product_subcategory
    FROM bronze_sales s
    JOIN dim_customer c ON s.customer_id = c.customer_id
    JOIN dim_product p ON s.product_id = p.product_id;

-- Gold layer: Analytics-ready
CREATE DYNAMIC TABLE gold_sales_summary
  TARGET_LAG = '15 minutes'
  WAREHOUSE = analytics_wh
  AS
    SELECT 
      sale_date,
      customer_segment,
      product_category,
      COUNT(DISTINCT sale_id) as transaction_count,
      SUM(total_amount) as revenue,
      AVG(total_amount) as avg_transaction_value
    FROM silver_sales_enriched
    GROUP BY 1, 2, 3;

---

New features in 2025

Immutability Constraints

New in 2025: Lock specific rows while allowing incremental updates to others

sql

CREATE DYNAMIC TABLE sales_with_closed_periods
  TARGET_LAG = '30 minutes'
  WAREHOUSE = compute_wh
  IMMUTABLE WHERE (sale_date < '2025-01-01')  -- Lock historical data
  AS
    SELECT 
      sale_date,
      region,
      SUM(amount) as total_sales
    FROM transactions
    GROUP BY 1, 2;

This prevents accidental modifications to closed accounting periods while continuing to update current data.

CURRENT_TIMESTAMP Support for incremental mode

New in 2025: Use time-based filters in incremental mode

sql

CREATE DYNAMIC TABLE rolling_30_day_metrics
  TARGET_LAG = '10 minutes'
  WAREHOUSE = compute_wh
  REFRESH_MODE = INCREMENTAL  -- Now works with CURRENT_TIMESTAMP
  AS
    SELECT 
      customer_id,
      COUNT(*) as recent_orders,
      SUM(order_total) as recent_revenue
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY customer_id;

Previously, using CURRENT_TIMESTAMP forced full refresh. Now it works with incremental mode.

Backfill from Clone feature

New in 2025: Initialize dynamic tables from historical snapshots

sql

-- Clone existing table with corrected data
CREATE TABLE sales_corrected CLONE sales_with_errors;

-- Apply corrections
UPDATE sales_corrected SET amount = amount * 1.1 WHERE region = 'APAC';

-- Create dynamic table using corrected data as baseline
CREATE DYNAMIC TABLE sales_summary
  BACKFILL FROM sales_corrected
  IMMUTABLE WHERE (sale_date < '2025-01-01')
  TARGET_LAG = '15 minutes'
  WAREHOUSE = compute_wh
  AS
    SELECT sale_date, region, SUM(amount) as total_sales
    FROM sales
    GROUP BY 1, 2;

---

Advanced Patterns and Techniques

Pattern 1: Handling Late-Arriving Data

Handle records that arrive out of order:

sql

CREATE DYNAMIC TABLE ordered_events
  TARGET_LAG = '30 minutes'
  WAREHOUSE = compute_wh
  AS
    SELECT 
      event_id,
      event_time,
      customer_id,
      event_type,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY event_time, event_id
      ) as sequence_number
    FROM raw_events
    WHERE event_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'
    ORDER BY customer_id, event_time;

Pattern 2: Using window Functions for cumulative calculations

Build cumulative calculations automatically:

sql

CREATE DYNAMIC TABLE customer_cumulative_spend
  TARGET_LAG = '20 minutes'
  WAREHOUSE = analytics_wh
  AS
    SELECT 
      customer_id,
      order_date,
      order_amount,
      SUM(order_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) as lifetime_value,
      COUNT(*) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) as order_count
    FROM orders;

Pattern 3: Automated Data Quality Checks

Automate data validation:

sql

CREATE DYNAMIC TABLE data_quality_metrics
  TARGET_LAG = '10 minutes'
  WAREHOUSE = monitoring_wh
  AS
    SELECT 
      'customers' as table_name,
      CURRENT_TIMESTAMP as check_time,
      COUNT(*) as total_rows,
      COUNT(DISTINCT customer_id) as unique_ids,
      SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) as missing_emails,
      SUM(CASE WHEN LENGTH(phone) < 10 THEN 1 ELSE 0 END) as invalid_phones,
      MAX(updated_at) as last_update
    FROM customers
    
    UNION ALL
    
    SELECT 
      'orders' as table_name,
      CURRENT_TIMESTAMP as check_time,
      COUNT(*) as total_rows,
      COUNT(DISTINCT order_id) as unique_ids,
      SUM(CASE WHEN order_amount <= 0 THEN 1 ELSE 0 END) as invalid_amounts,
      SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) as orphaned_orders,
      MAX(order_date) as last_update
    FROM orders;

---

Troubleshooting Common Issues

Issue 1: Tables Not Refreshing

Problem: Dynamic table shows “suspended” status

Solution:

sql

-- Check for errors in refresh history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY('my_table'))
WHERE state = 'FAILED'
ORDER BY data_timestamp DESC;

-- Resume the dynamic table
ALTER DYNAMIC TABLE my_table RESUME;

-- Check dependencies
SHOW DYNAMIC TABLES LIKE 'my_table';

Issue 2: Using Full Refresh Instead of Incremental

Problem: Query should support incremental but uses full refresh

Causes and fixes:

• Non-deterministic functions: Remove RANDOM(), UUID_STRING(), CURRENT_USER()
• Complex nested queries: Simplify or break into multiple dynamic tables
• Masking policies on base tables: Consider alternative security approaches
• LATERAL FLATTEN: May force full refresh for complex nested structures

sql

-- Check current refresh mode
SELECT refresh_mode
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY('my_table'))
LIMIT 1;

-- If full refresh is required, optimize for performance
ALTER DYNAMIC TABLE my_table SET WAREHOUSE = larger_warehouse;

Issue 3: High compute Costs

Problem: Unexpected credit consumption

Solutions:

sql

-- 1. Analyze compute usage
SELECT 
  name,
  warehouse_name,
  SUM(credits_used) as total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
GROUP BY 1, 2
ORDER BY total_credits DESC;

-- 2. Increase target lag to reduce refresh frequency
ALTER DYNAMIC TABLE expensive_table 
SET TARGET_LAG = '30 minutes';  -- Was '5 minutes'

-- 3. Use smaller warehouse
ALTER DYNAMIC TABLE expensive_table 
SET WAREHOUSE = small_wh;  -- Was large_wh

-- 4. Check if incremental is being used
-- If not, optimize query to support incremental processing

---

Migration from Streams and Tasks

Converting existing Stream/Task pipelines to Dynamic Tables:

Before (Streams and Tasks):

sql

-- Stream to capture changes
CREATE STREAM order_changes ON TABLE raw_orders;

-- Task to process stream
CREATE TASK process_orders
  WAREHOUSE = compute_wh
  SCHEDULE = '10 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('order_changes')
AS
  INSERT INTO processed_orders
  SELECT 
    order_id,
    customer_id,
    order_date,
    order_total,
    CASE 
      WHEN order_total > 1000 THEN 'high_value'
      WHEN order_total > 100 THEN 'medium_value'
      ELSE 'low_value'
    END as value_tier
  FROM order_changes
  WHERE METADATA$ACTION = 'INSERT';

ALTER TASK process_orders RESUME;

After (Snowflake Dynamic Tables):

sql

CREATE DYNAMIC TABLE processed_orders
  TARGET_LAG = '10 minutes'
  WAREHOUSE = compute_wh
  AS
    SELECT 
      order_id,
      customer_id,
      order_date,
      order_total,
      CASE 
        WHEN order_total > 1000 THEN 'high_value'
        WHEN order_total > 100 THEN 'medium_value'
        ELSE 'low_value'
      END as value_tier
    FROM raw_orders;

Benefits of migration:

• 75% less code to maintain
• Automatic dependency management
• No manual stream/task orchestration
• Automatic incremental processing
• Built-in monitoring and observability

Snowflake Dynamic Tables: Comparison with Other Platforms

Feature	Snowflake Dynamic Tables	dbt Incremental Models	Databricks Delta Live Tables
Setup complexity	Low (native Snowflake)	Medium (external tool)	Medium (Databricks-specific)
Automatic orchestration	Yes	No (requires scheduler)	Yes
Incremental processing	Automatic	Manual configuration	Automatic
Query language	SQL	SQL + Jinja	SQL + Python
Dependency management	Automatic DAG	Manual ref() functions	Automatic DAG
Cost optimization	Automatic warehouse sizing	Manual	Automatic cluster sizing
Monitoring	Built-in Snowsight	dbt Cloud or custom	Databricks UI
Multi-cloud	AWS, Azure, GCP	Any Snowflake account	Databricks only

Conclusion: The Future of Data Pipeline develoment

Snowflake Dynamic Tables represent a paradigm shift in data pipeline development. By eliminating complex orchestration code and automating refresh management, they allow data teams to focus on business logic rather than infrastructure.

Key transformations enabled:

• 80% reduction in pipeline code complexity
• Zero orchestration maintenance overhead
• Automatic incremental processing without manual merge logic
• Self-managing dependencies through intelligent DAG analysis
• Built-in monitoring and observability
• Cost optimization through intelligent refresh scheduling

As data freshness requirements increase and pipeline complexity grows, dynamic tables provide the declarative approach needed to build scalable, maintainable data infrastructure.

Start with simple use cases, measure performance, and progressively migrate complex pipelines. The investment in learning this technology pays dividends in reduced maintenance burden and faster feature delivery.

External Resources and Further Reading

• Official Snowflake Dynamic Tables Documentation
• Create Dynamic Tables Reference
• Dynamic Tables Best Practices Guide
• Snowflake Quickstart: Getting Started with Dynamic Tables
• Dynamic Table Refresh History Views
• Incremental Processing Performance Optimization
• Target Lag Configuration Guide

Revolutionary SQL Features That Transform data engineering

In 2025, Snowflake has introduced groundbreaking improvements that fundamentally change how data engineers write queries. This Snowflake SQL tutorial covers the latest features including MERGE ALL BY NAME, UNION BY NAME, and Cortex AISQL. Whether you’re learning Snowflake SQL or optimizing existing code, this tutorial demonstrates how these enhancements eliminate tedious column mapping, reduce errors, and dramatically simplify complex data operations.

The star feature? MERGE ALL BY NAME—announced on September 29, 2025—automatically matches columns by name, eliminating the need to manually map every column when upserting data. This Snowflake SQL tutorial will show you how this single feature can transform a 50-line MERGE statement into just 5 lines.

But that’s not all. Additionally, this SQL tutorial covers:

• UNION BY NAME for flexible data combining
• Cortex AISQL for AI-powered SQL functions
• Enhanced PIVOT/UNPIVOT with aliasing
• Snowflake Scripting UDFs for procedural SQL
• Lambda expressions in higher-order functions

For data engineers, these improvements mean less boilerplate code, fewer errors, and more time focused on solving business problems rather than wrestling with SQL syntax.

But that’s not all. Additionally, Snowflake 2025 brings:

• UNION BY NAME for flexible data combining
• Cortex AISQL for AI-powered SQL functions
• Enhanced PIVOT/UNPIVOT with aliasing
• Snowflake Scripting UDFs for procedural SQL
• Lambda expressions in higher-order functions

For data engineers, these improvements mean less boilerplate code, fewer errors, and more time focused on solving business problems rather than wrestling with SQL syntax.

---

Snowflake SQL Tutorial: MERGE ALL BY NAME Feature

This Snowflake SQL tutorial begins with the most impactful feature of 2025…

Announced on September 29, 2025, MERGE ALL BY NAME is arguably the most impactful SQL improvement Snowflake has released this year. This feature automatically matches columns between source and target tables based on column names rather than positions.

The SQL Problem MERGE ALL BY NAME Solves

Traditionally, writing a MERGE statement required manually listing and mapping each column:

sql

-- OLD WAY: Manual column mapping (tedious and error-prone)
MERGE INTO customer_target t
USING customer_updates s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
  UPDATE SET
    t.first_name = s.first_name,
    t.last_name = s.last_name,
    t.email = s.email,
    t.phone = s.phone,
    t.address = s.address,
    t.city = s.city,
    t.state = s.state,
    t.zip_code = s.zip_code,
    t.country = s.country,
    t.updated_date = s.updated_date
WHEN NOT MATCHED THEN
  INSERT (customer_id, first_name, last_name, email, phone, 
          address, city, state, zip_code, country, updated_date)
  VALUES (s.customer_id, s.first_name, s.last_name, s.email, 
          s.phone, s.address, s.city, s.state, s.zip_code, 
          s.country, s.updated_date);

This approach suffers from multiple pain points:

• Manual mapping for every single column
• High risk of typos and mismatches
• Difficult maintenance when schemas evolve
• Time-consuming for tables with many columns

The Snowflake SQL Solution: MERGE ALL BY NAME

With MERGE ALL BY NAME, the same operation becomes elegantly simple:

sql

-- NEW WAY: Automatic column matching (clean and reliable)
MERGE INTO customer_target
USING customer_updates
ON customer_target.customer_id = customer_updates.customer_id
WHEN MATCHED THEN
  UPDATE ALL BY NAME
WHEN NOT MATCHED THEN
  INSERT ALL BY NAME;

That’s it! Just 2 lines instead of 20+ lines of column mapping.

How MERGE ALL BY NAME Works

The magic happens through intelligent column name matching:

1. Snowflake analyzes both target and source tables
2. It identifies columns with matching names
3. It automatically maps columns regardless of position
4. It handles different column orders seamlessly
5. It executes the MERGE with proper type conversion

Importantly, MERGE ALL BY NAME works even when:

• Columns are in different orders
• Tables have extra columns in one but not the other
• Column names use different casing (Snowflake is case-insensitive by default)

Requirements for MERGE ALL BY NAME

For this feature to work correctly:

• Target and source must have the same number of matching columns
• Column names must be identical (case-insensitive)
• Data types must be compatible (Snowflake handles automatic casting)

However, column order doesn’t matter:

sql

-- This works perfectly!
CREATE TABLE target (
  id INT,
  name VARCHAR,
  email VARCHAR,
  created_date DATE
);

CREATE TABLE source (
  created_date DATE,  -- Different order
  email VARCHAR,       -- Different order
  id INT,             -- Different order
  name VARCHAR        -- Different order
);

MERGE INTO target
USING source
ON target.id = source.id
WHEN MATCHED THEN UPDATE ALL BY NAME
WHEN NOT MATCHED THEN INSERT ALL BY NAME;

Snowflake intelligently matches id with id, name with name, etc., regardless of position.

Real-World Use Case: Slowly Changing Dimensions

Consider implementing a Type 1 SCD (Slowly Changing Dimension) for product data:

sql

-- Product dimension table
CREATE OR REPLACE TABLE dim_product (
  product_id INT PRIMARY KEY,
  product_name VARCHAR,
  category VARCHAR,
  price DECIMAL(10,2),
  description VARCHAR,
  supplier_id INT,
  last_updated TIMESTAMP
);

-- Daily product updates from source system
CREATE OR REPLACE TABLE product_updates (
  product_id INT,
  description VARCHAR,  -- Different column order
  price DECIMAL(10,2),
  product_name VARCHAR,
  category VARCHAR,
  supplier_id INT,
  last_updated TIMESTAMP
);

-- SCD Type 1: Upsert with MERGE ALL BY NAME
MERGE INTO dim_product
USING product_updates
ON dim_product.product_id = product_updates.product_id
WHEN MATCHED THEN
  UPDATE ALL BY NAME
WHEN NOT MATCHED THEN
  INSERT ALL BY NAME;

This handles:

• Updating existing products with latest information
• Inserting new products automatically
• Different column orders between systems
• All columns without manual mapping

Benefits of MERGE ALL BY NAME

Data engineers report significant advantages:

Time Savings:

• 90% less code for MERGE statements
• 5 minutes instead of 30 minutes to write complex merges
• Faster schema evolution without code changes

Error Reduction:

• Zero typos from manual column mapping
• No mismatched columns from copy-paste errors
• Automatic validation by Snowflake

Maintenance Simplification:

• Schema changes don’t require code updates
• New columns automatically included
• Removed columns handled gracefully

Code Readability:

• Clear intent from simple syntax
• Easy review in code reviews
• Self-documenting logic

---

Snowflake SQL UNION BY NAME: Flexible Data Combining

This section of our Snowflake SQL tutorial explores how UNION BY NAME Introduced at Snowflake Summit 2025, UNION BY NAME revolutionizes how we combine datasets from different sources by focusing on column names rather than positions.

The Traditional UNION Problem

For years, SQL developers struggled with UNION ALL’s rigid requirements:

sql

-- TRADITIONAL UNION ALL: Requires exact column matching
SELECT id, name, department
FROM employees
UNION ALL
SELECT emp_id, emp_name, dept  -- Different names: FAILS!
FROM contingent_workers;

This fails because:

• Column names don’t match
• Positions matter, not names
• Adding columns breaks existing queries
• Schema evolution requires constant maintenance

UNION BY NAME Solution

With UNION BY NAME, column matching happens by name:

sql

-- NEW: UNION BY NAME matches columns by name
CREATE TABLE employees (
  id INT,
  name VARCHAR,
  department VARCHAR,
  role VARCHAR
);

CREATE TABLE contingent_workers (
  id INT,
  name VARCHAR,
  department VARCHAR
  -- Note: No 'role' column
);

SELECT * FROM employees
UNION ALL BY NAME
SELECT * FROM contingent_workers;

-- Result: Combines by name, fills missing 'role' with NULL

Output:

ID | NAME    | DEPARTMENT | ROLE
---+---------+------------+--------
1  | Alice   | Sales      | Manager
2  | Bob     | IT         | Developer
3  | Charlie | Sales      | NULL
4  | Diana   | IT         | NULL

Key behaviors:

• Columns matched by name, not position
• Missing columns filled with NULL
• Extra columns included automatically
• Order doesn’t matter

Use Cases for UNION BY NAME

This feature excels in several scenarios:

Merging Legacy and Modern Systems:

sql

-- Legacy system with old column names
SELECT 
  cust_id AS customer_id,
  cust_name AS name,
  phone_num AS phone
FROM legacy_customers

UNION ALL BY NAME

-- Modern system with new column names
SELECT
  customer_id,
  name,
  phone,
  email  -- New column not in legacy
FROM modern_customers;

Combining Data from Multiple Regions:

sql

-- Different regions have different optional fields
SELECT * FROM us_sales        -- Has 'state' column
UNION ALL BY NAME
SELECT * FROM eu_sales        -- Has 'country' column
UNION ALL BY NAME
SELECT * FROM asia_sales;     -- Has 'region' column

Incremental Schema Evolution:

sql

-- Historical data without new fields
SELECT * FROM sales_2023

UNION ALL BY NAME

-- Current data with additional tracking
SELECT * FROM sales_2024      -- Added 'source_channel' column

UNION ALL BY NAME

SELECT * FROM sales_2025;     -- Added 'attribution_id' column

Performance Considerations

While powerful, UNION BY NAME has slight overhead:

When to use UNION BY NAME:

• Schemas differ across sources
• Evolution happens frequently
• Maintainability matters more than marginal performance

When to use traditional UNION ALL:

• Schemas are identical and stable
• Maximum performance is critical
• Large-scale production queries with billions of rows

Best practice: Use UNION BY NAME for data integration and ELT pipelines where flexibility outweighs marginal performance costs.

---

Cortex AISQL: AI-Powered SQL Functions

Announced on June 2, 2025, Cortex AISQL brings powerful AI capabilities directly into Snowflake’s SQL engine, enabling AI pipelines with familiar SQL commands.

Revolutionary AI Functions

Cortex AISQL introduces three groundbreaking SQL functions:

AI_FILTER: Intelligent Data Filtering

Filter data using natural language questions instead of complex WHERE clauses:

sql

-- Traditional approach: Complex WHERE clause
SELECT *
FROM customer_reviews
WHERE (
  LOWER(review_text) LIKE '%excellent%' OR
  LOWER(review_text) LIKE '%amazing%' OR
  LOWER(review_text) LIKE '%outstanding%' OR
  LOWER(review_text) LIKE '%fantastic%'
) AND (
  sentiment_score > 0.7
);

-- AI_FILTER approach: Natural language
SELECT *
FROM customer_reviews
WHERE AI_FILTER(review_text, 'Is this a positive review praising the product?');

Use cases:

• Filtering images by content (“Does this image contain a person?”)
• Classifying text by intent (“Is this a complaint?”)
• Quality control (“Is this product photo high quality?”)

AI_CLASSIFY: Intelligent Classification

Classify text or images into user-defined categories:

sql

-- Classify customer support tickets automatically
SELECT 
  ticket_id,
  subject,
  AI_CLASSIFY(
    description,
    ['Technical Issue', 'Billing Question', 'Feature Request', 
     'Bug Report', 'Account Access']
  ) AS ticket_category
FROM support_tickets;

-- Multi-label classification
SELECT
  product_id,
  AI_CLASSIFY(
    product_description,
    ['Electronics', 'Clothing', 'Home & Garden', 'Sports'],
    'multi_label'
  ) AS categories
FROM products;

Advantages:

• No training required
• Plain-language category definitions
• Single or multi-label classification
• Works on text and images

AI_AGG: Intelligent Aggregation

Aggregate text columns and extract insights across multiple rows:

sql

-- Traditional: Difficult to get insights from text
SELECT 
  product_id,
  STRING_AGG(review_text, ' | ')  -- Just concatenates
FROM reviews
GROUP BY product_id;

-- AI_AGG: Extract meaningful insights
SELECT
  product_id,
  AI_AGG(
    review_text,
    'Summarize the common themes in these reviews, highlighting both positive and negative feedback'
  ) AS review_summary
FROM reviews
GROUP BY product_id;

Key benefit: Not subject to context window limitations—can process unlimited rows.

Cortex AISQL Real-World Example

Complete pipeline for analyzing customer feedback:

sql

-- Step 1: Filter relevant feedback
CREATE OR REPLACE TABLE relevant_feedback AS
SELECT *
FROM customer_feedback
WHERE AI_FILTER(feedback_text, 'Is this feedback about product quality or features?');

-- Step 2: Classify feedback by category
CREATE OR REPLACE TABLE categorized_feedback AS
SELECT
  feedback_id,
  customer_id,
  AI_CLASSIFY(
    feedback_text,
    ['Product Quality', 'Feature Request', 'User Experience', 
     'Performance', 'Pricing']
  ) AS feedback_category,
  feedback_text
FROM relevant_feedback;

-- Step 3: Aggregate insights by category
SELECT
  feedback_category,
  COUNT(*) AS feedback_count,
  AI_AGG(
    feedback_text,
    'Summarize the key points from this feedback, identifying the top 3 issues or requests mentioned'
  ) AS category_insights
FROM categorized_feedback
GROUP BY feedback_category;

This replaces:

• Hours of manual review
• Complex NLP pipelines with external tools
• Expensive ML model training and deployment

---

Enhanced PIVOT and UNPIVOT with Aliases

Snowflake 2025 adds aliasing capabilities to PIVOT and UNPIVOT operations, improving readability and flexibility.

PIVOT with Column Aliases

Now you can specify aliases for pivot column names:

sql

-- Sample data: Monthly sales by product
CREATE OR REPLACE TABLE monthly_sales (
  product VARCHAR,
  month VARCHAR,
  sales_amount DECIMAL(10,2)
);

INSERT INTO monthly_sales VALUES
  ('Laptop', 'Jan', 50000),
  ('Laptop', 'Feb', 55000),
  ('Laptop', 'Mar', 60000),
  ('Phone', 'Jan', 30000),
  ('Phone', 'Feb', 35000),
  ('Phone', 'Mar', 40000);

-- PIVOT with aliases for readable column names
SELECT *
FROM monthly_sales
PIVOT (
  SUM(sales_amount)
  FOR month IN ('Jan', 'Feb', 'Mar')
) AS pivot_alias (
  product,
  january_sales,      -- Custom alias instead of 'Jan'
  february_sales,     -- Custom alias instead of 'Feb'
  march_sales         -- Custom alias instead of 'Mar'
);

Output:

PRODUCT | JANUARY_SALES | FEBRUARY_SALES | MARCH_SALES
--------+---------------+----------------+-------------
Laptop  | 50000         | 55000          | 60000
Phone   | 30000         | 35000          | 40000

Benefits:

• Readable column names
• Business-friendly output
• Easier downstream consumption
• Better documentation

UNPIVOT with Aliases

Similarly, UNPIVOT now supports aliases:

sql

-- Unpivot with custom column names
SELECT *
FROM pivot_sales_data
UNPIVOT (
  monthly_amount
  FOR sales_month IN (q1_sales, q2_sales, q3_sales, q4_sales)
) AS unpivot_alias (
  product_name,
  quarter,
  amount
);

---

Snowflake Scripting UDFs: Procedural SQL

A major enhancement in 2025 allows creating SQL UDFs with Snowflake Scripting procedural language.

Traditional UDF Limitations

Before, SQL UDFs were limited to single expressions:

sql

-- Simple UDF: No procedural logic allowed
CREATE FUNCTION calculate_discount(price FLOAT, discount_pct FLOAT)
RETURNS FLOAT
AS
$$
  price * (1 - discount_pct / 100)
$$;

New: Snowflake Scripting UDFs

Now you can include loops, conditionals, and complex logic:

sql

CREATE OR REPLACE FUNCTION calculate_tiered_commission(
  sales_amount FLOAT
)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
DECLARE
  commission FLOAT;
BEGIN
  -- Tiered commission logic
  IF (sales_amount < 10000) THEN
    commission := sales_amount * 0.05;  -- 5%
  ELSEIF (sales_amount < 50000) THEN
    commission := (10000 * 0.05) + ((sales_amount - 10000) * 0.08);  -- 8%
  ELSE
    commission := (10000 * 0.05) + (40000 * 0.08) + ((sales_amount - 50000) * 0.10);  -- 10%
  END IF;
  
  RETURN commission;
END;
$$;

-- Use in SELECT statement
SELECT
  salesperson,
  sales_amount,
  calculate_tiered_commission(sales_amount) AS commission
FROM sales_data;

Key advantages:

• Called in SELECT statements (unlike stored procedures)
• Complex business logic encapsulated
• Reusable across queries
• Better than stored procedures for inline calculations

Real-World Example: Dynamic Pricing

sql

CREATE OR REPLACE FUNCTION calculate_dynamic_price(
  base_price FLOAT,
  inventory_level INT,
  demand_score FLOAT,
  competitor_price FLOAT
)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
DECLARE
  adjusted_price FLOAT;
  inventory_factor FLOAT;
  demand_factor FLOAT;
BEGIN
  -- Calculate inventory factor
  IF (inventory_level < 10) THEN
    inventory_factor := 1.15;  -- Low inventory: +15%
  ELSEIF (inventory_level > 100) THEN
    inventory_factor := 0.90;  -- High inventory: -10%
  ELSE
    inventory_factor := 1.0;
  END IF;
  
  -- Calculate demand factor
  IF (demand_score > 0.8) THEN
    demand_factor := 1.10;     -- High demand: +10%
  ELSEIF (demand_score < 0.3) THEN
    demand_factor := 0.95;     -- Low demand: -5%
  ELSE
    demand_factor := 1.0;
  END IF;
  
  -- Calculate adjusted price
  adjusted_price := base_price * inventory_factor * demand_factor;
  
  -- Price floor: Don't go below 80% of competitor
  IF (adjusted_price < competitor_price * 0.8) THEN
    adjusted_price := competitor_price * 0.8;
  END IF;
  
  -- Price ceiling: Don't exceed 120% of competitor
  IF (adjusted_price > competitor_price * 1.2) THEN
    adjusted_price := competitor_price * 1.2;
  END IF;
  
  RETURN ROUND(adjusted_price, 2);
END;
$$;

-- Apply dynamic pricing across catalog
SELECT
  product_id,
  product_name,
  base_price,
  calculate_dynamic_price(
    base_price,
    inventory_level,
    demand_score,
    competitor_price
  ) AS optimized_price
FROM products;

---

Lambda Expressions with Table Column References

Snowflake 2025 enhances higher-order functions by allowing table column references in lambda expressions.

What Are Higher-Order Functions?

Higher-order functions operate on arrays using lambda functions:

FILTER: Filter array elements MAP/TRANSFORM: Transform each element REDUCE: Aggregate array into single value

New Capability: Column References

Previously, lambda expressions couldn’t reference table columns:

sql

-- OLD: Limited to array elements only
SELECT FILTER(
  price_array,
  x -> x > 100  -- Can only use array elements
)
FROM products;

Now you can reference table columns:

sql

-- NEW: Reference table columns in lambda
CREATE TABLE products (
  product_id INT,
  product_name VARCHAR,
  prices ARRAY,
  discount_threshold FLOAT
);

-- Use table column 'discount_threshold' in lambda
SELECT
  product_id,
  product_name,
  FILTER(
    prices,
    p -> p > discount_threshold  -- References table column!
  ) AS prices_above_threshold
FROM products;

Real-World Use Case: Dynamic Filtering

sql

-- Inventory table with multiple warehouse locations
CREATE TABLE inventory (
  product_id INT,
  warehouse_locations ARRAY,
  min_stock_level INT,
  stock_levels ARRAY
);

-- Filter warehouses where stock is below minimum
SELECT
  product_id,
  FILTER(
    warehouse_locations,
    (loc, idx) -> stock_levels[idx] < min_stock_level
  ) AS understocked_warehouses,
  FILTER(
    stock_levels,
    level -> level < min_stock_level
  ) AS low_stock_amounts
FROM inventory;

Complex Example: Price Optimization

sql

-- Apply dynamic discounts based on product-specific rules
CREATE TABLE product_pricing (
  product_id INT,
  base_prices ARRAY,
  competitor_prices ARRAY,
  max_discount_pct FLOAT,
  margin_threshold FLOAT
);

SELECT
  product_id,
  TRANSFORM(
    base_prices,
    (price, idx) -> 
      CASE
        -- Don't discount if already below competitor
        WHEN price <= competitor_prices[idx] * 0.95 THEN price
        -- Apply discount but respect margin threshold
        WHEN price * (1 - max_discount_pct / 100) >= margin_threshold 
          THEN price * (1 - max_discount_pct / 100)
        -- Use margin threshold as floor
        ELSE margin_threshold
      END
  ) AS optimized_prices
FROM product_pricing;

---

Additional SQL Improvements in 2025

Beyond the major features, Snowflake 2025 includes numerous enhancements:

Enhanced SEARCH Function Modes

New search modes for more precise text matching:

PHRASE Mode: Match exact phrases with token order

sql

SELECT *
FROM documents
WHERE SEARCH(content, 'data engineering best practices', 'PHRASE');

AND Mode: All tokens must be present

sql

SELECT *
FROM articles
WHERE SEARCH(title, 'snowflake performance optimization', 'AND');

OR Mode: Any token matches (existing, now explicit)

sql

SELECT *
FROM blogs
WHERE SEARCH(content, 'sql python scala', 'OR');

Increased VARCHAR and BINARY Limits

Maximum lengths significantly increased:

• VARCHAR: Now 128 MB (previously 16 MB)
• VARIANT, ARRAY, OBJECT: Now 128 MB
• BINARY, GEOGRAPHY, GEOMETRY: Now 64 MB

This enables:

• Storing large JSON documents
• Processing big text blobs
• Handling complex geographic shapes

Schema-Level Replication for Failover

Selective replication for databases in failover groups:

sql

-- Replicate only specific schemas
ALTER DATABASE production_db
SET REPLICABLE_WITH_FAILOVER_GROUPS = TRUE;

ALTER SCHEMA production_db.critical_schema
SET REPLICABLE_WITH_FAILOVER_GROUPS = TRUE;

-- Other schemas not replicated, reducing costs

XML Format Support (General Availability)

Native XML support for semi-structured data:

sql

-- Load XML files
COPY INTO xml_data
FROM @my_stage/data.xml
FILE_FORMAT = (TYPE = 'XML');

-- Query XML with familiar functions
SELECT
  xml_data:customer:@id::STRING AS customer_id,
  xml_data:customer:name::STRING AS customer_name
FROM xml_data;

---

Best Practices for Snowflake SQL 2025

This Snowflake SQL tutorial wouldn’t be complete without best practices…

To maximize the benefits of these improvements:

When to Use MERGE ALL BY NAME

Use it when:

• Tables have 5+ columns to map
• Schemas evolve frequently
• Column order varies across systems
• Maintenance is a priority

Avoid it when:

• Fine control needed over specific columns
• Conditional updates require different logic per column
• Performance is absolutely critical (marginal difference)

When to Use UNION BY NAME

Use it when:

• Combining data from multiple sources with varying schemas
• Schema evolution happens regularly
• Missing columns should be NULL-filled
• Flexibility outweighs performance

Avoid it when:

• Schemas are identical and stable
• Maximum performance is required
• Large-scale production queries (billions of rows)

Cortex AISQL Performance Tips

Optimize AI function usage:

• Filter data first before applying AI functions
• Batch similar operations together
• Use WHERE clauses to limit rows processed
• Cache results when possible

Example optimization:

sql

-- POOR: AI function on entire table
SELECT AI_CLASSIFY(text, categories) FROM large_table;

-- BETTER: Filter first, then classify
SELECT AI_CLASSIFY(text, categories)
FROM large_table
WHERE date >= CURRENT_DATE - 7  -- Only recent data
AND text IS NOT NULL
AND LENGTH(text) > 50;  -- Only substantial text

Snowflake Scripting UDF Guidelines

Best practices:

• Keep UDFs deterministic when possible
• Test thoroughly with edge cases
• Document complex logic with comments
• Consider performance for frequently-called functions
• Use instead of stored procedures when called in SELECT

---

Migration Guide: Adopting 2025 Features

For teams transitioning to these new features:

Phase 1: Assess Current Code

Identify candidates for improvement:

sql

-- Find MERGE statements that could use ALL BY NAME
SELECT query_text
FROM snowflake.account_usage.query_history
WHERE query_text ILIKE '%MERGE INTO%'
AND query_text ILIKE '%UPDATE SET%'
AND query_text LIKE '%=%'  -- Has manual mapping
AND start_time >= DATEADD(month, -3, CURRENT_TIMESTAMP());

Phase 2: Test in Development

Create test cases:

1. Copy production MERGE to dev
2. Rewrite using ALL BY NAME
3. Compare results with original
4. Benchmark performance differences
5. Review with team

Phase 3: Gradual Rollout

Prioritize by impact:

1. Start with non-critical pipelines
2. Monitor for issues
3. Expand to production incrementally
4. Update documentation
5. Train team on new syntax

Phase 4: Standardize

Update coding standards:

• Prefer MERGE ALL BY NAME for new code
• Refactor existing MERGE when touched
• Document exceptions where old syntax preferred
• Include in code reviews

---

Troubleshooting Common Issues

When adopting new features, watch for these issues:

MERGE ALL BY NAME Not Working

Problem: “Column count mismatch”

Solution: Ensure exact column name matches:

sql

-- Check column names match
SELECT column_name 
FROM information_schema.columns 
WHERE table_name = 'TARGET_TABLE'
MINUS
SELECT column_name 
FROM information_schema.columns 
WHERE table_name = 'SOURCE_TABLE';

UNION BY NAME NULL Handling

Problem: Unexpected NULLs in results

Solution: Remember missing columns become NULL:

sql

-- Make NULLs explicit if needed
SELECT
  COALESCE(column_name, 'DEFAULT_VALUE') AS column_name,
  ...
FROM table1
UNION ALL BY NAME
SELECT * FROM table2;

Cortex AISQL Performance

Problem: AI functions running slowly

Solution: Filter data before AI processing:

sql

-- Reduce data volume first
WITH filtered AS (
  SELECT * FROM large_table
  WHERE conditions_to_reduce_rows
)
SELECT AI_CLASSIFY(text, categories)
FROM filtered;

---

Future SQL Improvements on Snowflake Roadmap

Based on community feedback and Snowflake’s direction, expect these future enhancements:

2026 Predicted Features:

• More AI functions in Cortex AISQL
• Enhanced MERGE with more flexible conditions
• Additional higher-order functions
• Improved query optimization for new syntax
• Extended lambda capabilities

Community Requests:

• MERGE NOT MATCHED BY SOURCE (like SQL Server)
• More flexible PIVOT syntax
• Additional string manipulation functions
• Graph query capabilities

---

Conclusion: Embracing Modern SQL in Snowflake

This Snowflake SQL tutorial has covered the revolutionary 2025 improvements represent a significant leap forward in data engineering productivity. MERGE ALL BY NAME alone can save data engineers hours per week by eliminating tedious column mapping.

The key benefits:

• Less boilerplate code
• Fewer errors from typos
• Easier maintenance as schemas evolve
• More time for valuable work

For data engineers, these features mean spending less time fighting SQL syntax and more time solving business problems. The tools are more intelligent, the syntax more intuitive, and the results more reliable.

Start today by identifying one MERGE statement you can simplify with ALL BY NAME. Experience the difference these modern SQL features make in your daily work.

The future of SQL is here—and it’s dramatically simpler.

---

Key Takeaways

• MERGE ALL BY NAME automatically matches columns by name, eliminating manual mapping
• Announced September 29, 2025, this feature reduces MERGE statements from 50+ lines to 5 lines
• UNION BY NAME combines data from sources with different column orders and schemas
• Cortex AISQL brings AI