data.ml
SQL Religious org

Snapshot Table

Snapshot Table model for religious org.

source-preserved

Blocks

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

SELECT results.resultid
, results.resulttypeid
, 'snapshot' as table_category
, country_name
, resultdate
, resulttypename
, resultvalue
, LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate) AS lastvalue
, COALESCE(resultvalue - LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate), resultvalue) AS netresultvalue
FROM results
LEFT JOIN resulttypes ON results.resulttypeid = resulttypes.resulttypeid
LEFT JOIN projects ON results.projectid = projects.projectid
LEFT JOIN countries ON projects.countryid = countries.countryid
WHERE resulttypename IN ('Attendees Meeting','Fellowship Locations')
AND {% condition results.resultdate_date %} resultdate {% endcondition %}
ORDER BY resultdate

# models/schema.yml
version: 2

models:
  - name: snapshot_table
    columns:
      - name: resultid
        tests:
          - unique
          - not_null
      - name: country_name
      - name: resulttypeid
      - name: lastvalue
      - name: resulttypename
      - name: resultvalue
      - name: netresultvalue

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.
snapshot_table.sql · 16 lines
source-preserved validation: not checked 
SELECT results.resultid
, results.resulttypeid
, 'snapshot' as table_category
, country_name
, resultdate
, resulttypename
, resultvalue
, LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate) AS lastvalue
, COALESCE(resultvalue - LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate), resultvalue) AS netresultvalue
FROM results
LEFT JOIN resulttypes ON results.resulttypeid = resulttypes.resulttypeid
LEFT JOIN projects ON results.projectid = projects.projectid
LEFT JOIN countries ON projects.countryid = countries.countryid
WHERE resulttypename IN ('Attendees Meeting','Fellowship Locations')
AND {% condition results.resultdate_date %} resultdate {% endcondition %}
ORDER BY resultdate
snapshot_table.view.lkml · 68 lines
inferred 
view: snapshot_table {
  derived_table: {
    sql: SELECT results.resultid
, results.resulttypeid
, 'snapshot' as table_category
, country_name
, resultdate
, resulttypename
, resultvalue
, LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate) AS lastvalue
, COALESCE(resultvalue - LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate), resultvalue) AS netresultvalue
FROM results
LEFT JOIN resulttypes ON results.resulttypeid = resulttypes.resulttypeid
LEFT JOIN projects ON results.projectid = projects.projectid
LEFT JOIN countries ON projects.countryid = countries.countryid
WHERE resulttypename IN ('Attendees Meeting','Fellowship Locations')
AND {% condition results.resultdate_date %} resultdate {% endcondition %}
ORDER BY resultdate ;;
  }

  dimension: resultid {
    primary_key: yes
    sql: ${TABLE}.resultid ;;
  }

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

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

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: total_result_value {
    type: sum
    sql: ${netresultvalue} ;;
  }
}
snapshot_table.view.lookml · 51 lines
source-preserved 
- view: snapshot_table
  derived_table:
    sql: SELECT results.resultid
, results.resulttypeid
, 'snapshot' as table_category
, country_name
, resultdate
, resulttypename
, resultvalue
, LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate) AS lastvalue
, COALESCE(resultvalue - LAG(resultvalue, 1) OVER (PARTITION BY country_name, resulttypename ORDER BY resultdate), resultvalue) AS netresultvalue
FROM results
LEFT JOIN resulttypes ON results.resulttypeid = resulttypes.resulttypeid
LEFT JOIN projects ON results.projectid = projects.projectid
LEFT JOIN countries ON projects.countryid = countries.countryid
WHERE resulttypename IN ('Attendees Meeting','Fellowship Locations')
AND {% condition results.resultdate_date %} resultdate {% endcondition %}
ORDER BY resultdate
  fields:
  - dimension: resultid
    primary_key: true
    sql: ${TABLE}.resultid
  - dimension: country_name
    sql: ${TABLE}.country_name
  - dimension: resulttypeid
    sql: ${TABLE}.resulttypeid
  - dimension: lastvalue
    type: number
    sql: ${TABLE}.lastvalue
  - dimension: resulttypename
    sql: ${TABLE}.resulttypename
  - dimension: resultvalue
    type: number
    sql: ${TABLE}.resultvalue
  - dimension: netresultvalue
    type: number
    sql: ${TABLE}.netresultvalue
  - dimension_group: resultdate
    type: time
    timeframes:
    - date
    - month
    - year
    sql: ${TABLE}.resultdate
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_result_value
    type: sum
    sql: ${netresultvalue}

Contents

  • snapshot_table.sql
  • snapshot_table.sql
  • snapshot_table.view.lkml
  • snapshot_table.view.lookml