SQL Saas translation

Accounts

Accounts model for saas translation.

source-preserved

Blocks

dbt SQL LKML LookML

accounts.sql · 48 lines

inferred

-- models/accounts.sql
{{ config(materialized='view') }}

SELECT accounts.*
    , CASE
        WHEN account_billing_types.name IN ('invoiceable','chargeable')
        THEN true
        ELSE false
      END AS live
    , account_billing_types.name as account_billing_types_name
    , CASE WHEN ec.entity_id IS NULL THEN 0 ELSE 1 END as enterprise_client
FROM {{ this }}.accounts
LEFT JOIN {{ this }}.account_billings
ON account_billings.account_id = accounts.account_id
LEFT JOIN {{ this }}.account_billing_types
ON account_billing_types.account_billing_type_id = account_billings.account_billing_type_id
LEFT JOIN
  (
    SELECT DISTINCT it.entity_id
    FROM {{ this }}.invoice_tracking it
    WHERE it.invoice_type_id = 1 -- chargeable
      AND it.entity_type_id = 1  -- account entities
      AND it.invoice_status_id = 2 -- success;
      AND it.active = 1 -- active invoice
  ) as ec
ON accounts.account_id = ec.entity_id

# models/schema.yml
version: 2

models:
  - name: accounts
    columns:
      - name: account_id
        tests:
          - unique
          - not_null
      - name: uid
      - name: account_name
      - name: account_name_id_combo
      - name: enabled
      - name: live
      - name: product_id
      - name: show_fuzzy_match_enabled
      - name: exclude_test_account
      - name: account_billing_types_name
      - name: is_enterprise_client
        description: "An Enterprise Client is any account that has had (at any point in time) an invoice generated for that account"

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.

accounts.sql · 23 lines

source-preserved validation: not checked

SELECT accounts.*
    , CASE
        WHEN account_billing_types.name IN ('invoiceable','chargeable')
        THEN true
        ELSE false
      END AS live
    , account_billing_types.name as account_billing_types_name
    , CASE WHEN ec.entity_id IS NULL THEN 0 ELSE 1 END as enterprise_client
FROM ${TABLE}.accounts
LEFT JOIN ${TABLE}.account_billings
ON account_billings.account_id = accounts.account_id
LEFT JOIN ${TABLE}.account_billing_types
ON account_billing_types.account_billing_type_id = account_billings.account_billing_type_id
LEFT JOIN
  (
    SELECT DISTINCT it.entity_id
    FROM ${TABLE}.invoice_tracking it
    WHERE it.invoice_type_id = 1 -- chargeable
      AND it.entity_type_id = 1  -- account entities
      AND it.invoice_status_id = 2 -- success;
      AND it.active = 1 -- active invoice
  ) as ec
ON accounts.account_id = ec.entity_id

accounts.view.lkml · 100 lines

inferred

view: accounts {
  derived_table: {
    sql: SELECT accounts.*
    , CASE
        WHEN account_billing_types.name IN ('invoiceable','chargeable')
        THEN true
        ELSE false
      END AS live
    , account_billing_types.name as account_billing_types_name
    , CASE WHEN ec.entity_id IS NULL THEN 0 ELSE 1 END as enterprise_client
FROM ${TABLE}.accounts
LEFT JOIN ${TABLE}.account_billings
ON account_billings.account_id = accounts.account_id
LEFT JOIN ${TABLE}.account_billing_types
ON account_billing_types.account_billing_type_id = account_billings.account_billing_type_id
LEFT JOIN
  (
    SELECT DISTINCT it.entity_id
    FROM ${TABLE}.invoice_tracking it
    WHERE it.invoice_type_id = 1 -- chargeable
      AND it.entity_type_id = 1  -- account entities
      AND it.invoice_status_id = 2 -- success;
      AND it.active = 1 -- active invoice
  ) as ec
ON accounts.account_id = ec.entity_id ;;
  }

  dimension: account_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.account_id ;;
  }

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

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

  dimension: account_name_id_combo {
    sql: ${account_id} || '-' || ${account_name} ;;
  }

  dimension: enabled {
    type: yesno
    sql: ${TABLE}.enabled ;;
  }

  dimension: live {
    type: yesno
    sql: ${TABLE}.live ;;
  }

  dimension: product_id {
    type: int
    sql: ${TABLE}.product_id ;;
  }

  dimension: show_fuzzy_match_enabled {
    type: yesno
    sql: ${TABLE}.show_fuzzy_match_enabled ;;
  }

  dimension: exclude_test_account {
    type: yesno
    sql: ${TABLE}.test = 0 ;;
  }

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

  dimension: is_enterprise_client {
    description: "An Enterprise Client is any account that has had (at any point in time) an invoice generated for that account"
    type: yesno
    sql: ${TABLE}.enterprise_client = 1 ;;
  }

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

  measure: count {
    type: count
  }

  measure: count_live {
    type: count
    filters: [live: "True"]
  }

  measure: count_enabled {
    type: count
    filters: [enabled: "True"]
  }
}

accounts.view.lookml · 75 lines

source-preserved

- view: accounts
  derived_table:
    sql: SELECT accounts.*
    , CASE
        WHEN account_billing_types.name IN ('invoiceable','chargeable')
        THEN true
        ELSE false
      END AS live
    , account_billing_types.name as account_billing_types_name
    , CASE WHEN ec.entity_id IS NULL THEN 0 ELSE 1 END as enterprise_client
FROM ${TABLE}.accounts
LEFT JOIN ${TABLE}.account_billings
ON account_billings.account_id = accounts.account_id
LEFT JOIN ${TABLE}.account_billing_types
ON account_billing_types.account_billing_type_id = account_billings.account_billing_type_id
LEFT JOIN
  (
    SELECT DISTINCT it.entity_id
    FROM ${TABLE}.invoice_tracking it
    WHERE it.invoice_type_id = 1 -- chargeable
      AND it.entity_type_id = 1  -- account entities
      AND it.invoice_status_id = 2 -- success;
      AND it.active = 1 -- active invoice
  ) as ec
ON accounts.account_id = ec.entity_id
  fields:
  - dimension: account_id
    type: int
    primary_key: true
    sql: ${TABLE}.account_id
  - dimension: uid
    sql: ${TABLE}.uid
  - dimension: account_name
    sql: ${TABLE}.account_name
  - dimension: account_name_id_combo
    sql: ${account_id} || '-' || ${account_name}
  - dimension: enabled
    type: yesno
    sql: ${TABLE}.enabled
  - dimension: live
    type: yesno
    sql: ${TABLE}.live
  - dimension: product_id
    type: int
    sql: ${TABLE}.product_id
  - dimension: show_fuzzy_match_enabled
    type: yesno
    sql: ${TABLE}.show_fuzzy_match_enabled
  - dimension: exclude_test_account
    type: yesno
    sql: ${TABLE}.test = 0
  - dimension: account_billing_types_name
    sql: ${TABLE}.account_billing_types_name
  - dimension: is_enterprise_client
    type: yesno
    description: An Enterprise Client is any account that has had (at any point in time) an invoice generated for that account
    sql: ${TABLE}.enterprise_client = 1
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created
  - measure: count
    type: count
  - measure: count_live
    type: count
    filters:
    - live: True
  - measure: count_enabled
    type: count
    filters:
    - enabled: True

accounts.sql
accounts.sql
accounts.view.lkml
accounts.view.lookml

Accounts

Blocks

Assumptions

Contents