Basic SQL Transformations Sample Queries

Sample queries for all databases

Aggregating event data to daily

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 recent

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 Snowflake

Incremental transformation for Snowflake

-- -- 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;