New Lambda Function in Snowflake: ACCUMULATE

• From: Saqib | Date: Sun, 03 May 2026 15:06:59 +0000 #
  Subject: New Lambda Function in Snowflake: ACCUMULATE

  Snowflake has introduce a new Lambda Function: ACCUMULATE ACCUMULATE let's you roll-your-own aggregate functions. Here is an example of how to use ACCUMULATE to build Geometric Mean (not Arithmetic Mean) function: with orders_fact as ( select 10 as order_amount union all select 20 as order_amount union all select 30 as order_amount union all select 40 as order_amount union all select 50000000 as order_amount union all select 60 as order_amount ) SELECT ACCUMULATE( order_amount, -- input column (v) -> [v, 1], -- init (state, v) -> [state[0] * v, state[1] + 1], -- fold (state1, state2) -> [state1[0] + state2[0], state1[1] + state2[1]], -- merge (state) -> POWER(state[0], 1/state[1]) -- terminate ) AS geometric_mean , EXP(SUM(LN(orderamount))/COUNT(orderamount)) as geometricmeanalt -- alternate way of calculating the geometric mean FROM orders_fact;

  [ Reply ]
  • |-- Reply
    From: Saqib | Date: Mon, 04 May 2026 16:31:57 +0000 #

    some more advance examples from Pascal: https://medium.com/@pascalpfffle/accumulate-building-custom-aggregations-without-writing-a-single-udf-72cd3167064b

    [ Reply ]
  • |-- Reply
    From: Saqib | Date: Mon, 04 May 2026 16:39:17 +0000 #

    More examples from Satish Kumar: https://medium.com/towards-data-engineering/snowflakes-accumulate-build-custom-aggregates-with-lambda-power-d1675248267f

    [ Reply ]
  • |-- Reply
    From: Saqib | Date: Fri, 08 May 2026 05:02:12 +0000 #

    Davide Mauri has posted a nice example of how to use the ACCUMULATE function to calculate the compound interest with varying interest rates: CREATE OR REPLACE TEMPORARY TABLE InterestRates ( RowId INT NOT NULL AUTOINCREMENT PRIMARY KEY, "Month" DATE NOT NULL, AnnualInterestRate DECIMAL(7,4) NOT NULL ); -- 12 months: mostly 5.00%, with April/October at 5.25% and July at 4.75% INSERT INTO InterestRates ("Month", AnnualInterestRate) VALUES ('2025-01-01', 0.0500), ('2025-02-01', 0.0500), ('2025-03-01', 0.0500), ('2025-04-01', 0.0525), -- slightly higher ('2025-05-01', 0.0500), ('2025-06-01', 0.0500), ('2025-07-01', 0.0475), -- slightly lower ('2025-08-01', 0.0500), ('2025-09-01', 0.0500), ('2025-10-01', 0.0525), -- slightly higher ('2025-11-01', 0.0500), ('2025-12-01', 0.0500); SELECT 10000.0 AS InitialBalance, (InitialBalance * ACCUMULATE( 1.0 + AnnualInterestRate / 12.0, (val) -> val, (state, val) -> state * val, (state1, state2) -> state1 * state2, (state) -> state ))::NUMERIC(18,4) AS FinalBalance FROM InterestRates WHERE "Month" <= '2025-08-01'; Read more: https://medium.com/snowflake/accumulate-any-aggregate-you-can-imagine-in-pure-sql-f5edbd36d7a1

    [ Reply ]