data.ml
dbt Saas legislation

Account Facts

Account Facts model for saas legislation.

inferred

Blocks

account_facts.sql · 30 lines
inferred 
-- models/account_facts.sql
{{ config(materialized='view') }}

SELECT
  a.id,
  MIN(o.close_date) as customer_created_date,
  current_date - MIN(o.close_date) as days_as_customer,
  SUM(o.amount) total_value
FROM
  salesforce.opportunity o
  LEFT JOIN salesforce.account a ON (a.id = o.account_id)
WHERE
  1=1
  AND o.stage_name = 'Closed Won'
  AND a.is_deleted = 'false'
  AND o.is_deleted = 'false'
GROUP BY
  1
ORDER BY
  1

# models/schema.yml
version: 2

models:
  - name: account_facts
    columns:
      - name: id
      - name: days_as_customer
      - name: total_value

Assumptions

  • materialized='view' is the default; no persist hint found.
  • schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.
account_facts.sql · 17 lines
source-preserved validation: parses 
SELECT
  a.id,
  MIN(o.close_date) as customer_created_date,
  current_date - MIN(o.close_date) as days_as_customer,
  SUM(o.amount) total_value
FROM
  salesforce.opportunity o
  LEFT JOIN salesforce.account a ON (a.id = o.account_id)
WHERE
  1=1
  AND o.stage_name = 'Closed Won'
  AND a.is_deleted = 'false'
  AND o.is_deleted = 'false'
GROUP BY
  1
ORDER BY
  1
account_facts.view.lkml · 46 lines
inferred 
view: account_facts {
  derived_table: {
    sql: SELECT
  a.id,
  MIN(o.close_date) as customer_created_date,
  current_date - MIN(o.close_date) as days_as_customer,
  SUM(o.amount) total_value
FROM
  salesforce.opportunity o
  LEFT JOIN salesforce.account a ON (a.id = o.account_id)
WHERE
  1=1
  AND o.stage_name = 'Closed Won'
  AND a.is_deleted = 'false'
  AND o.is_deleted = 'false'
GROUP BY
  1
ORDER BY
  1 ;;
  }

  dimension: id {
    sql: ${TABLE}.id ;;
  }

  dimension: days_as_customer {
    type: number
    sql: ${TABLE}.days_as_customer ;;
  }

  dimension: total_value {
    type: number
    sql: ${TABLE}.total_value ;;
  }

  dimension_group: customer_created {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.customer_created_date ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}
account_facts.view.lookml · 39 lines
source-preserved 
- view: account_facts
  derived_table:
    sql: SELECT
  a.id,
  MIN(o.close_date) as customer_created_date,
  current_date - MIN(o.close_date) as days_as_customer,
  SUM(o.amount) total_value
FROM
  salesforce.opportunity o
  LEFT JOIN salesforce.account a ON (a.id = o.account_id)
WHERE
  1=1
  AND o.stage_name = 'Closed Won'
  AND a.is_deleted = 'false'
  AND o.is_deleted = 'false'
GROUP BY
  1
ORDER BY
  1
  fields:
  - dimension: id
    sql: ${TABLE}.id
  - dimension: days_as_customer
    type: number
    sql: ${TABLE}.days_as_customer
  - dimension: total_value
    type: number
    sql: ${TABLE}.total_value
  - dimension_group: customer_created
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.customer_created_date
  - measure: count
    type: count
    drill_fields:
    - detail*

Contents

  • account_facts.sql
  • account_facts.sql
  • account_facts.view.lkml
  • account_facts.view.lookml