sfdc.fact_contract_events.yaml

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