Sample queries for all databaseslink
Aggregating event data to dailylink
create or replace table transforms.all_events_daily_rollup as
select
product_id,
account_id,
lineitem_id,
date(event_date) as date,
sum(quantity) as total_quantity_succeeded
from webhooks.all_events_audit
where status <> 'Failed'
group by 1, 2, 3, 4
Eliminating duplicate records by choosing the most recentlink
create or replace table transforms.enrichment_accounts_deduped as
with accounts_with_duplicates as (
select
*,
/*
same salesforce account id (enrich_account_c) is linked to multiple records
create a ranking methodology of which records should be kept
*/
row_number() over (partition by enrich_account_c order by created_date desc) as duplicate_rank
from salesforce.enrich_enrichment_c
where not is_deleted
)
select
*
from accounts_with_duplicates
where duplicate_rank = 1
or enrich_account_c is null
Sample queries for Snowflakelink
-- -- Run on the warehouse ONCE
-- /* Create [aggregate table], [cursor], [buffer] */
-- create or replace table orders_aggregated (date1 date, value1 int)
-- cluster by (date1)
-- ;
-- create or replace table cursor as
-- select null as value
-- ;
begin;
set cursor = (select value from cursor);
set buffer = -30;
set current_date = current_date();
/* [INCREMENTAL] - remove records in buffer period */
delete from orders_aggregated
where IFF($cursor is null, true, date1 >= dateadd(day, $buffer, $cursor))
;
/* [INCREMENTAL] - insert incremental aggregate */
insert into orders_aggregated
select
to_date(date1),
sum(value1)
from order_dates_clustered
where IFF($cursor is null, true, date1 >= dateadd(day, $buffer, $cursor))
group by 1
;
/* [INCREMENTAL] - set cursor */
create or replace table cursor as
select $current_date as value
;
/* END TRANSACTION*/
commit;
