Tag: sql

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