Snowflake SQL Tutorial: MERGE ALL BY NAME & More

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,
…

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

1-- NEW WAY: Automatic column matching (clean and reliable)2MERGE INTO customer_target3USING customer_updates4ON customer_target.customer_id = customer_updates.customer_id5WHEN MATCHED THEN6  UPDATE ALL BY NAME7WHEN NOT MATCHED THEN8  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
);

…

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,
…

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

1-- TRADITIONAL UNION ALL: Requires exact column matching2SELECT id, name, department3FROM employees4UNION ALL5SELECT emp_id, emp_name, dept  -- Different names: FAILS!6FROM 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
);

…

Output:

1ID | NAME    | DEPARTMENT | ROLE2---+---------+------------+--------31  | Alice   | Sales      | Manager42  | Bob     | IT         | Developer53  | Charlie | Sales      | NULL64  | 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
…

Combining Data from Multiple Regions:

sql

1-- Different regions have different optional fields2SELECT * FROM us_sales        -- Has 'state' column3UNION ALL BY NAME4SELECT * FROM eu_sales        -- Has 'country' column5UNION ALL BY NAME6SELECT * FROM asia_sales;     -- Has 'region' column

Incremental Schema Evolution:

sql

1-- Historical data without new fields2SELECT * FROM sales_20233 4UNION ALL BY NAME5 6-- Current data with additional tracking7SELECT * FROM sales_2024      -- Added 'source_channel' column8 9UNION ALL BY NAME10 11SELECT * 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%'
…

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']
…

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
…

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
…

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
…

Output:

1PRODUCT | JANUARY_SALES | FEBRUARY_SALES | MARCH_SALES2--------+---------------+----------------+-------------3Laptop  | 50000         | 55000          | 600004Phone   | 30000         | 35000          | 40000

Benefits:

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

UNPIVOT with Aliases

Similarly, UNPIVOT now supports aliases:

sql

1-- Unpivot with custom column names2SELECT *3FROM pivot_sales_data4UNPIVOT (5  monthly_amount6  FOR sales_month IN (q1_sales, q2_sales, q3_sales, q4_sales)7) AS unpivot_alias (8  product_name,9  quarter,10  amount11);

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

1-- Simple UDF: No procedural logic allowed2CREATE FUNCTION calculate_discount(price FLOAT, discount_pct FLOAT)3RETURNS FLOAT4AS5$$6  price * (1 - discount_pct / 100)7$$;

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
…

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
…

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

1-- OLD: Limited to array elements only2SELECT FILTER(3  price_array,4  x -> x > 100  -- Can only use array elements5)6FROM 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
);

…

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
);

…

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
);
…

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

1SELECT *2FROM documents3WHERE SEARCH(content, 'data engineering best practices', 'PHRASE');

AND Mode: All tokens must be present

sql

1SELECT *2FROM articles3WHERE SEARCH(title, 'snowflake performance optimization', 'AND');

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

sql

1SELECT *2FROM blogs3WHERE 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

1-- Replicate only specific schemas2ALTER DATABASE production_db3SET REPLICABLE_WITH_FAILOVER_GROUPS = TRUE;4 5ALTER SCHEMA production_db.critical_schema6SET REPLICABLE_WITH_FAILOVER_GROUPS = TRUE;7 8-- Other schemas not replicated, reducing costs

XML Format Support (General Availability)

Native XML support for semi-structured data:

sql

1-- Load XML files2COPY INTO xml_data3FROM @my_stage/data.xml4FILE_FORMAT = (TYPE = 'XML');5 6-- Query XML with familiar functions7SELECT8  xml_data:customer:@id::STRING AS customer_id,9  xml_data:customer:name::STRING AS customer_name10FROM 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

1-- POOR: AI function on entire table2SELECT AI_CLASSIFY(text, categories) FROM large_table;3 4-- BETTER: Filter first, then classify5SELECT AI_CLASSIFY(text, categories)6FROM large_table7WHERE date >= CURRENT_DATE - 7  -- Only recent data8AND text IS NOT NULL9AND 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

1-- Find MERGE statements that could use ALL BY NAME2SELECT query_text3FROM snowflake.account_usage.query_history4WHERE query_text ILIKE '%MERGE INTO%'5AND query_text ILIKE '%UPDATE SET%'6AND query_text LIKE '%=%'  -- Has manual mapping7AND 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

1-- Check column names match2SELECT column_name 3FROM information_schema.columns 4WHERE table_name = 'TARGET_TABLE'5MINUS6SELECT column_name 7FROM information_schema.columns 8WHERE table_name = 'SOURCE_TABLE';

UNION BY NAME NULL Handling

Problem: Unexpected NULLs in results

Solution: Remember missing columns become NULL:

sql

1-- Make NULLs explicit if needed2SELECT3  COALESCE(column_name, 'DEFAULT_VALUE') AS column_name,4  ...5FROM table16UNION ALL BY NAME7SELECT * FROM table2;

Cortex AISQL Performance

Problem: AI functions running slowly

Solution: Filter data before AI processing:

sql

1-- Reduce data volume first2WITH filtered AS (3  SELECT * FROM large_table4  WHERE conditions_to_reduce_rows5)6SELECT AI_CLASSIFY(text, categories)7FROM 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.