Announcing New SQL Features in Public Preview to Optimize Snowflake Workloads
We're excited to announce the public preview of three new features designed to give you more control, flexibility and power in Snowflake: Directed Join, Snowflake Scripting UDFs and gap-filling/interpolation. These features help optimize query performance, extend custom logic and simplify time-series analysis.
Take control of your query plans with Directed Join
Sometimes, you know more about your data than the query optimizer does, allowing you to make better decisions about JOIN ordering. This is especially true in complex queries where cardinality estimation can be tricky. Directed Join is a new feature that gives you direct control over the query execution plan for join operations.
Let's look at a three-table example. Imagine you have a massive sales table with billions of rows, a large products table with millions of items and a small product_categories table. Without DIRECTED JOIN, the optimizer might join the two largest tables first (sales and products), creating a huge intermediate result set before joining it with the small product_categories table. This can be highly inefficient. While the Snowflake optimizer would likely find the correct join order in this simple example, Directed Join becomes invaluable in more complex cases with numerous joins where the optimal path isn't as clear.
Here's how you can use it to force a more efficient plan by joining from the smallest table to the largest:
SELECT
s.sale_id,
pc.category_name
FROM product_categories pc
INNER DIRECTED JOIN
products p ON pc.category_id = p.category_id
INNER DIRECTED JOIN
sales s ON p.product_id = s.product_id;
WHERE pc.category = 'furniture'By specifying the join order, you can guide the optimizer to build a more resource-efficient plan, leading to faster and more cost-effective queries.
Learn more: Directed Join documentation
Extend your SQL with Snowflake Scripting UDFs
You can now write SQL user-defined functions (UDFs) with the power and flexibility of Snowflake Scripting. This brings the familiar procedural logic of stored procedures — including variable declaration, branching (IF/ELSE) and looping (FOR) — directly into UDFs. This allows you to build more complex and powerful reusable functions.
Let's look at an example. Suppose you want to calculate a sales commission that varies based on the sale amount. With Snowflake Scripting UDFs, you can encapsulate this logic into a reusable function. You can then use this UDF in any query, just like a built-in function.
CREATE OR REPLACE FUNCTION calculate_commission(sale_amount NUMBER(10, 2))
RETURNS NUMBER(10, 2)
LANGUAGE SQL
AS
DECLARE
commission_rate NUMBER(3, 2);
BEGIN
IF (sale_amount > 1000) THEN
commission_rate := 0.15;
ELSEIF (sale_amount > 500) THEN
commission_rate := 0.10;
ELSE
commission_rate := 0.05;
END IF;
RETURN sale_amount * commission_rate;
END;SELECT
sale_id,
amount,
calculate_commission(amount) AS commission
FROM sales;This makes your SQL cleaner, more modular and easier to maintain; here’s how some of our early adopters are using it:
A banking company is performing advanced statistical analysis by creating UDFs to compute p-values and z-scores.
A large telecom company implemented custom UDFs for IMEI string standardization using this feature.
Learn more: Snowflake Scripting UDFs documentation
Simplify time-series analysis with gap-filling and interpolation
Time-series analysis often requires data with consistent granularity, but real-world data frequently contains gaps. With our new RESAMPLE clause and INTERPOLATE_* window functions, you can now create uniform time-series data and fill gaps directly in a single query.
Consider this simplified scenario, where you have a table, daily_store_metrics, that records daily revenue and the active promotion ID for a store. The daily_revenue value is missing on some days, and the active_promotion_id column is "stateful," meaning it's only recorded when a new promotion starts. The goal is to create a complete daily report with estimated revenue and the correct promotion ID for every day.
First, let's create the sparse data:
CREATE OR REPLACE TABLE daily_store_metrics (
metric_date DATE,
store_id INT,
daily_revenue INT,
active_promotion_id INT
);
INSERT INTO daily_store_metrics VALUES
('2025-09-01', 1, 10000, 101), -- Day 1: Revenue & new promotion
('2025-09-03', 1, 11000, NULL),-- Day 3: Revenue, no promo change
('2025-09-06', 1, 12500, 202); -- Day 6: Revenue & new promotionNow, you can use RESAMPLE to create a uniform daily time grid. Then, apply INTERPOLATE_LINEAR to estimate the fluctuating daily_revenue and INTERPOLATE_FFILL (forward fill) to carry over the stateful active_promotion_id.
SELECT
metric_date,
store_id,
-- Linearly interpolate to estimate revenue on missing days
INTERPOLATE_LINEAR(daily_revenue) OVER (PARTITION BY store_id ORDER BY metric_date) AS estimated_revenue,
-- Forward-fill to find the active promotion for each day
INTERPOLATE_FFILL(active_promotion_id) OVER (PARTITION BY store_id ORDER BY metric_date) AS promotion_in_effect
FROM daily_store_metrics
RESAMPLE (
USING metric_date
INCREMENT BY INTERVAL '1 day'
PARTITION BY store_id
);
These new functions dramatically simplify time-series preparation, handling tasks in a single SQL query that previously required complex logic.
Learn more: Gap-filling/interpolation documentation
We're thrilled to bring these new features to you and can't wait to see what you'll build. Get started today by exploring the documentation and trying them out in your own Snowflake environment.
Author
