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.

UNION BY NAME combining tables with different schemas and column orders flexibly

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
Snowflake Scripting UDF showing procedural logic with conditionals and loops

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:

Productivity comparison showing OLD manual MERGE versus NEW automatic MERGE ALL BY NAME

sql

SQL 22 lines

SQL — OLD WAY: Manual column mapping (tedious and error-prone).

-- OLD WAY: Manual column mapping (tedious and error-prone)MERGE INTO customer_target tUSING customer_updates sON t.customer_id = s.customer_idWHEN 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_dateWHEN 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

SQL 8 lines

SQL — NEW WAY: Automatic column matching (clean and reliable).

-- NEW WAY: Automatic column matching (clean and reliable)MERGE INTO customer_targetUSING customer_updatesON customer_target.customer_id = customer_updates.customer_idWHEN MATCHED THEN  UPDATE ALL BY NAMEWHEN 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

Snowflake MERGE ALL BY NAME automatically matching columns by name regardless of position

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

SQL 20 lines

SQL — This works perfectly!.

-- 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 targetUSING sourceON target.id = source.idWHEN MATCHED THEN UPDATE ALL BY NAMEWHEN 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

SQL 30 lines

SQL — Product dimension table.

-- Product dimension tableCREATE 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 systemCREATE 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 NAMEMERGE INTO dim_productUSING product_updatesON dim_product.product_id = product_updates.product_idWHEN MATCHED THEN  UPDATE ALL BY NAMEWHEN 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

SQL 6 lines

SQL — TRADITIONAL UNION ALL: Requires exact column matching.

-- TRADITIONAL UNION ALL: Requires exact column matchingSELECT id, name, departmentFROM employeesUNION ALLSELECT 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

SQL 20 lines

SQL — NEW: UNION BY NAME matches columns by name.

-- NEW: UNION BY NAME matches columns by nameCREATE 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 employeesUNION ALL BY NAMESELECT * FROM contingent_workers; -- Result: Combines by name, fills missing 'role' with NULL

Output:

Snippet 6 lines

Config snippet — ID | NAME | DEPARTMENT | ROLE

ID | NAME    | DEPARTMENT | ROLE---+---------+------------+--------1  | Alice   | Sales      | Manager2  | Bob     | IT         | Developer3  | Charlie | Sales      | NULL4  | 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

SQL 16 lines

SQL — Legacy system with old column names.

-- Legacy system with old column namesSELECT   cust_id AS customer_id,  cust_name AS name,  phone_num AS phoneFROM legacy_customers UNION ALL BY NAME -- Modern system with new column namesSELECT  customer_id,  name,  phone,  email  -- New column not in legacyFROM modern_customers;

Combining Data from Multiple Regions:

sql

SQL 6 lines

SQL — Different regions have different optional fields.

-- Different regions have different optional fieldsSELECT * FROM us_sales        -- Has 'state' columnUNION ALL BY NAMESELECT * FROM eu_sales        -- Has 'country' columnUNION ALL BY NAMESELECT * FROM asia_sales;     -- Has 'region' column

Incremental Schema Evolution:

sql

SQL 11 lines

SQL — Historical data without new fields.

-- Historical data without new fieldsSELECT * FROM sales_2023 UNION ALL BY NAME -- Current data with additional trackingSELECT * 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

SQL 16 lines

SQL — Traditional approach: Complex WHERE clause.

-- Traditional approach: Complex WHERE clauseSELECT *FROM customer_reviewsWHERE (  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 languageSELECT *FROM customer_reviewsWHERE 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

SQL 20 lines

SQL — Classify customer support tickets automatically.

-- Classify customer support tickets automaticallySELECT   ticket_id,  subject,  AI_CLASSIFY(    description,    ['Technical Issue', 'Billing Question', 'Feature Request',      'Bug Report', 'Account Access']  ) AS ticket_categoryFROM support_tickets; -- Multi-label classificationSELECT  product_id,  AI_CLASSIFY(    product_description,    ['Electronics', 'Clothing', 'Home & Garden', 'Sports'],    'multi_label'  ) AS categoriesFROM 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

SQL 16 lines

SQL — Traditional: Difficult to get insights from text.

-- Traditional: Difficult to get insights from textSELECT   product_id,  STRING_AGG(review_text, ' | ')  -- Just concatenatesFROM reviewsGROUP BY product_id; -- AI_AGG: Extract meaningful insightsSELECT  product_id,  AI_AGG(    review_text,    'Summarize the common themes in these reviews, highlighting both positive and negative feedback'  ) AS review_summaryFROM reviewsGROUP 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:

Real-world Cortex AISQL pipeline filtering, classifying, and aggregating customer feedback

sql

SQL 29 lines

SQL — Step 1: Filter relevant feedback.

-- Step 1: Filter relevant feedbackCREATE OR REPLACE TABLE relevant_feedback ASSELECT *FROM customer_feedbackWHERE AI_FILTER(feedback_text, 'Is this feedback about product quality or features?'); -- Step 2: Classify feedback by categoryCREATE OR REPLACE TABLE categorized_feedback ASSELECT  feedback_id,  customer_id,  AI_CLASSIFY(    feedback_text,    ['Product Quality', 'Feature Request', 'User Experience',      'Performance', 'Pricing']  ) AS feedback_category,  feedback_textFROM relevant_feedback; -- Step 3: Aggregate insights by categorySELECT  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_insightsFROM categorized_feedbackGROUP 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

SQL 27 lines

SQL — Sample data: Monthly sales by product.

-- Sample data: Monthly sales by productCREATE 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 namesSELECT *FROM monthly_salesPIVOT (  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:

Snippet 4 lines

Config snippet — PRODUCT | JANUARY_SALES | FEBRUARY_SALES | M…

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

Benefits:

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

UNPIVOT with Aliases

Similarly, UNPIVOT now supports aliases:

sql

SQL 11 lines

SQL — Unpivot with custom column names.

-- Unpivot with custom column namesSELECT *FROM pivot_sales_dataUNPIVOT (  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

shell 7 lines

CLI — Simple UDF: No procedural logic allowed.

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

New: Snowflake Scripting UDFs

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

sql

shell 29 lines

CLI command — executes CREATE OR REPLACE FUNCTION calculate_tiered_comm….

CREATE OR REPLACE FUNCTION calculate_tiered_commission(  sales_amount FLOAT)RETURNS FLOATLANGUAGE SQLAS$$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 statementSELECT  salesperson,  sales_amount,  calculate_tiered_commission(sales_amount) AS commissionFROM 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

shell 62 lines

CLI command — executes CREATE OR REPLACE FUNCTION calculate_dynamic_pri….

CREATE OR REPLACE FUNCTION calculate_dynamic_price(  base_price FLOAT,  inventory_level INT,  demand_score FLOAT,  competitor_price FLOAT)RETURNS FLOATLANGUAGE SQLAS$$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 catalogSELECT  product_id,  product_name,  base_price,  calculate_dynamic_price(    base_price,    inventory_level,    demand_score,    competitor_price  ) AS optimized_priceFROM products;

Lambda Expressions with Table Column References

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

Lambda expressions in Snowflake referencing both array elements and table columns

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

SQL 6 lines

SQL — OLD: Limited to array elements only.

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

Now you can reference table columns:

sql

SQL 17 lines

SQL — NEW: Reference table columns in lambda.

-- NEW: Reference table columns in lambdaCREATE TABLE products (  product_id INT,  product_name VARCHAR,  prices ARRAY,  discount_threshold FLOAT); -- Use table column 'discount_threshold' in lambdaSELECT  product_id,  product_name,  FILTER(    prices,    p -> p > discount_threshold  -- References table column!  ) AS prices_above_thresholdFROM products;

Real-World Use Case: Dynamic Filtering

sql

SQL 20 lines

SQL — Inventory table with multiple warehouse locations.

-- Inventory table with multiple warehouse locationsCREATE TABLE inventory (  product_id INT,  warehouse_locations ARRAY,  min_stock_level INT,  stock_levels ARRAY); -- Filter warehouses where stock is below minimumSELECT  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_amountsFROM inventory;

Complex Example: Price Optimization

sql

SQL 25 lines

SQL — Apply dynamic discounts based on product-specific rules.

-- Apply dynamic discounts based on product-specific rulesCREATE 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_pricesFROM 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

SQL 3 lines

SQL query on documents — filters rows (use Run SQL to test in the Playground).

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

AND Mode: All tokens must be present

sql

SQL 3 lines

SQL query on articles — filters rows (use Run SQL to test in the Playground).

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

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

sql

SQL 3 lines

SQL query on blogs — filters rows (use Run SQL to test in the Playground).

SELECT *FROM blogsWHERE 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

SQL 8 lines

SQL — Replicate only specific schemas.

-- Replicate only specific schemasALTER DATABASE production_dbSET REPLICABLE_WITH_FAILOVER_GROUPS = TRUE; ALTER SCHEMA production_db.critical_schemaSET REPLICABLE_WITH_FAILOVER_GROUPS = TRUE; -- Other schemas not replicated, reducing costs

XML Format Support (General Availability)

Native XML support for semi-structured data:

sql

SQL 10 lines

SQL — Load XML files.

-- Load XML filesCOPY INTO xml_dataFROM @my_stage/data.xmlFILE_FORMAT = (TYPE = 'XML'); -- Query XML with familiar functionsSELECT  xml_data:customer:@id::STRING AS customer_id,  xml_data:customer:name::STRING AS customer_nameFROM 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

SQL 9 lines

SQL — POOR: AI function on entire table.

-- POOR: AI function on entire tableSELECT AI_CLASSIFY(text, categories) FROM large_table; -- BETTER: Filter first, then classifySELECT AI_CLASSIFY(text, categories)FROM large_tableWHERE date >= CURRENT_DATE - 7  -- Only recent dataAND text IS NOT NULLAND 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:

Migration roadmap for adopting Snowflake SQL 2025 improvements in four phases

Phase 1: Assess Current Code

Identify candidates for improvement:

sql

SQL 7 lines

SQL — Find MERGE statements that could use ALL BY NAME.

-- Find MERGE statements that could use ALL BY NAMESELECT query_textFROM snowflake.account_usage.query_historyWHERE query_text ILIKE '%MERGE INTO%'AND query_text ILIKE '%UPDATE SET%'AND query_text LIKE '%=%'  -- Has manual mappingAND 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

SQL 8 lines

SQL — Check column names match.

-- Check column names matchSELECT column_name FROM information_schema.columns WHERE table_name = 'TARGET_TABLE'MINUSSELECT 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

SQL 7 lines

SQL — Make NULLs explicit if needed.

-- Make NULLs explicit if neededSELECT  COALESCE(column_name, 'DEFAULT_VALUE') AS column_name,  ...FROM table1UNION ALL BY NAMESELECT * FROM table2;

Cortex AISQL Performance

Problem: AI functions running slowly

Solution: Filter data before AI processing:

sql

SQL 7 lines

SQL — Reduce data volume first.

-- Reduce data volume firstWITH 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
Snowflake SQL 2025 improvements overview showing all major features and enhancements

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.

Question this article answers

The short answer first. Open it to read the working note.

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