New Lambda Function in Snowflake: ACCUMULATE
[ j/n next | k/p prev | g top | r reply | u up ]- From: Saqib
Newsgroups: comp.databases.snowflake
Subject: New Lambda Function in Snowflake: ACCUMULATE
Date: Sun, 03 May 2026 15:06:59 +0000 #
Message-ID: <f30c98daa5452f348996d1ec33d65acb@quettabyte.net>
X-User-Agent: Quettabyte/1.0 (Terminal Edition)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(order_amount))/COUNT(order_amount)) as geometric_mean_alt -- alternate way of calculating the geometric mean FROM orders_fact;- |-- ReplyFrom: Saqib
Newsgroups: comp.databases.snowflake
Subject: Re: New Lambda Function in Snowflake: ACCUMULATE
Date: Mon, 04 May 2026 16:31:57 +0000 #
Message-ID: <34731bb5800f9f8fd812de70a2cf3b7a@quettabyte.net>
X-User-Agent: Quettabyte/1.0 (Terminal Edition)some more advance examples from Pascal: https://medium.com/@pascalpfffle/accumulate-building-custom-aggregations-without-writing-a-single-udf-72cd3167064b - |-- ReplyFrom: Saqib
Newsgroups: comp.databases.snowflake
Subject: Re: New Lambda Function in Snowflake: ACCUMULATE
Date: Mon, 04 May 2026 16:39:17 +0000 #
Message-ID: <65aae0bbade3112885336f8520f2f213@quettabyte.net>
X-User-Agent: Quettabyte/1.0 (Terminal Edition)More examples from Satish Kumar: https://medium.com/towards-data-engineering/snowflakes-accumulate-build-custom-aggregates-with-lambda-power-d1675248267f - |-- ReplyFrom: Saqib
Newsgroups: comp.databases.snowflake
Subject: Re: New Lambda Function in Snowflake: ACCUMULATE
Date: Fri, 08 May 2026 05:02:12 +0000 #
Message-ID: <8c103735f6fffa7d74bc451ceb108f2a@quettabyte.net>
X-User-Agent: Quettabyte/1.0 (Terminal Edition)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