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
*,
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
begin;
set cursor = (select value from cursor);
set buffer = -30;
set current_date = current_date();
delete from orders_aggregated
where IFF($cursor is null, true, date1 >= dateadd(day, $buffer, $cursor))
;
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
;
create or replace table cursor as
select $current_date as value
;
commit;