data.ml
SQL Fintech payments

Utilization

Utilization model for fintech payments.

source-preserved

Blocks

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

select day, m."deviceId", m."OrgUnitId",
  (pd."pingDay" is not null) as ping
from (
  select jd.day, jd."deviceId", oj.id as "OrgUnitId"
  from (
    select day, j."deviceId", j."OrgUnitId"
      from
        "Units" j,
        (select (generate_series(now() - interval '1 year', now(), '1 day'::interval))::date as day) days) jd,
        "OrgUnits" oj
      where jd."OrgUnitId" = oj.id and jd.day >= oj."createdAt"::date and oj."deletedAt" is null
        ) m
      left outer join "PingDaysMaterialized" pd on (m.day = pd."pingDay" and pd."deviceId" = m."deviceId"
      )

# models/schema.yml
version: 2

models:
  - name: utilization
    columns:
      - name: composite_key
        tests:
          - unique
          - not_null
      - name: day
      - name: device_id
      - name: org_unit_id
      - name: ping
      - name: ping_num

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.
utilization.sql · 14 lines
source-preserved validation: parses 
select day, m."deviceId", m."OrgUnitId",
  (pd."pingDay" is not null) as ping
from (
  select jd.day, jd."deviceId", oj.id as "OrgUnitId"
  from (
    select day, j."deviceId", j."OrgUnitId"
      from
        "Units" j,
        (select (generate_series(now() - interval '1 year', now(), '1 day'::interval))::date as day) days) jd,
        "OrgUnits" oj
      where jd."OrgUnitId" = oj.id and jd.day >= oj."createdAt"::date and oj."deletedAt" is null
        ) m
      left outer join "PingDaysMaterialized" pd on (m.day = pd."pingDay" and pd."deviceId" = m."deviceId"
      )
utilization.view.lkml · 62 lines
inferred 
view: utilization {
  derived_table: {
    sql: select day, m."deviceId", m."OrgUnitId",
  (pd."pingDay" is not null) as ping
from (
  select jd.day, jd."deviceId", oj.id as "OrgUnitId"
  from (
    select day, j."deviceId", j."OrgUnitId"
      from
        "Units" j,
        (select (generate_series(now() - interval '1 year', now(), '1 day'::interval))::date as day) days) jd,
        "OrgUnits" oj
      where jd."OrgUnitId" = oj.id and jd.day >= oj."createdAt"::date and oj."deletedAt" is null
        ) m
      left outer join "PingDaysMaterialized" pd on (m.day = pd."pingDay" and pd."deviceId" = m."deviceId"
      ) ;;
  }

  dimension: composite_key {
    primary_key: yes
    sql: ${TABLE}."deviceId" || ${TABLE}."day"::text ;;
  }

  dimension: day {
    type: date
    sql: ${TABLE}.day ;;
  }

  dimension: device_id {
    sql: ${TABLE}."deviceId" ;;
  }

  dimension: org_unit_id {
    type: int
    sql: ${TABLE}."OrgUnitId" ;;
  }

  dimension: ping {
    type: yesno
    sql: ${TABLE}."ping" ;;
  }

  dimension: ping_num {
    type: number
    sql: case when ${TABLE}.ping then 1.0 else 0.0 end ;;
  }

  measure: ping_count {
    type: sum
    sql: ${ping_num} ;;
  }

  measure: utilization {
    type: avg
    value_format: "0.00\%"
    sql: 100 * ${ping_num} ;;
  }

  measure: days {
    type: count
  }
}
utilization.view.lookml · 43 lines
source-preserved 
- view: utilization
  derived_table:
    sql: select day, m."deviceId", m."OrgUnitId",
  (pd."pingDay" is not null) as ping
from (
  select jd.day, jd."deviceId", oj.id as "OrgUnitId"
  from (
    select day, j."deviceId", j."OrgUnitId"
      from
        "Units" j,
        (select (generate_series(now() - interval '1 year', now(), '1 day'::interval))::date as day) days) jd,
        "OrgUnits" oj
      where jd."OrgUnitId" = oj.id and jd.day >= oj."createdAt"::date and oj."deletedAt" is null
        ) m
      left outer join "PingDaysMaterialized" pd on (m.day = pd."pingDay" and pd."deviceId" = m."deviceId"
      )
  fields:
  - dimension: composite_key
    primary_key: true
    sql: ${TABLE}."deviceId" || ${TABLE}."day"::text
  - dimension: day
    type: date
    sql: ${TABLE}.day
  - dimension: device_id
    sql: ${TABLE}."deviceId"
  - dimension: org_unit_id
    type: int
    sql: ${TABLE}."OrgUnitId"
  - dimension: ping
    type: yesno
    sql: ${TABLE}."ping"
  - dimension: ping_num
    type: number
    sql: case when ${TABLE}.ping then 1.0 else 0.0 end
  - measure: ping_count
    type: sum
    sql: ${ping_num}
  - measure: utilization
    type: avg
    value_format: '0.00\%'
    sql: 100 * ${ping_num}
  - measure: days
    type: count

Contents

  • utilization.sql
  • utilization.sql
  • utilization.view.lkml
  • utilization.view.lookml