Skip to content

Instantly share code, notes, and snippets.

@bstancil
Created September 22, 2022 22:40
Show Gist options
  • Save bstancil/ee9ee57743e7423741fef3c0a3cc669d to your computer and use it in GitHub Desktop.
Save bstancil/ee9ee57743e7423741fef3c0a3cc669d to your computer and use it in GitHub Desktop.
target:
type: table
schema: sfdc
name: fact_contract_events
create:
columns: |
event_date timestamp,
event_name varchar(255) not null,
account_id varchar(255) not null,
opportunity_id varchar(255) not null,
opportunity_name varchar(255),
change_in_mrr varchar(255),
change_in_amount varchar(255)
primary_keys:
- account_id
- opportunity_id
- event_name
load:
type: replace
cronline: '0 * * * *'
dependencies:
- sfdc.dimension_contracts
sql: |
SELECT c.close_date AS event_date,
CASE WHEN c.business_type = 'New Business' THEN 'new mrr'
WHEN c.business_type IN ('Upsell Business','Renewal Business') AND c.change_in_mrr < 0 THEN 'contraction mrr'
WHEN c.business_type IN ('Upsell Business','Renewal Business') AND c.change_in_mrr >= 0 THEN 'expansion mrr'
ELSE 'WTF LOL' END AS event_name,
c.account_id,
c.opportunity_id,
c.opportunity_name,
c.change_in_mrr,
c.change_in_amount
FROM sfdc.dimension_contracts c
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment