dbt Retail gastronomy

Payroll Yoy

Payroll Yoy model for retail gastronomy.

inferred

Blocks

dbt SQL LKML LookML

payroll_yoy.sql · 75 lines

inferred

-- models/payroll_yoy.sql
{{ config(materialized='table') }}

SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours,
    SUM(0) AS total_amount_ly,
    SUM(0) AS ot_amount_ly,
    SUM(0) AS vaca_amount_ly,
    SUM(0) AS hours_ly,
    SUM(0) AS ot_hours_ly,
    NULL AS vaca_hours_ly

FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey

UNION
      SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear+1 AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(0) AS total_amount,
    SUM(0) AS ot_amount,
    SUM(0) AS vaca_amount,
    SUM(0) AS hours,
    SUM(0) AS ot_hours,
    SUM(0) AS vaca_amount,

    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours


FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey

# models/schema.yml
version: 2

models:
  - name: payroll_yoy
    columns:
      - name: date_key
      - name: date_payroll_key
        tests:
          - unique
          - not_null
      - name: earnings_code_key
      - name: payroll_dept_key
      - name: iso_week
      - name: iso_year

Assumptions

materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

payroll_yoy.sql · 56 lines

source-preserved validation: parses

SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours,
    SUM(0) AS total_amount_ly,
    SUM(0) AS ot_amount_ly,
    SUM(0) AS vaca_amount_ly,
    SUM(0) AS hours_ly,
    SUM(0) AS ot_hours_ly,
    NULL AS vaca_hours_ly

FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey

UNION
      SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear+1 AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(0) AS total_amount,
    SUM(0) AS ot_amount,
    SUM(0) AS vaca_amount,
    SUM(0) AS hours,
    SUM(0) AS ot_hours,
    SUM(0) AS vaca_amount,

    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours


FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey

payroll_yoy.view.lkml · 243 lines

inferred

view: payroll_yoy {
  derived_table: {
    sql: SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours,
    SUM(0) AS total_amount_ly,
    SUM(0) AS ot_amount_ly,
    SUM(0) AS vaca_amount_ly,
    SUM(0) AS hours_ly,
    SUM(0) AS ot_hours_ly,
    NULL AS vaca_hours_ly

FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey

UNION
      SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear+1 AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(0) AS total_amount,
    SUM(0) AS ot_amount,
    SUM(0) AS vaca_amount,
    SUM(0) AS hours,
    SUM(0) AS ot_hours,
    SUM(0) AS vaca_amount,

    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours


FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey ;;
    persist_for: "6 hours"
  }

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

  dimension: date_payroll_key {
    type: number
    primary_key: yes
    hidden: yes
    sql: CONCAT(${TABLE}.date_key,  ${TABLE}.payroll_dept_key, ${TABLE}.iso_year, ${TABLE}.iso_week, ${TABLE}.earnings_code_key ) ;;
  }

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

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

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

  dimension: iso_year {
    type: int
    value_format: "0000"
    sql: ${TABLE}.iso_year ;;
  }

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

  measure: total_amount {
    type: sum
    value_format: "$#,###"
    drill_fields: [regular*]
    sql: ${TABLE}.total_amount ;;
  }

  measure: running_total_amount {
    type: running_total
    value_format: "$#,###"
    drill_fields: [regular*]
    sql: ${total_amount} ;;
  }

  measure: total_ot_amount {
    type: sum
    value_format: "$#,###"
    drill_fields: [ot*]
    sql: ${TABLE}.ot_amount ;;
  }

  measure: total_vaca_amount {
    type: sum
    value_format: "$#,###"
    drill_fields: [vacation*]
    sql: ${TABLE}.vaca_amount ;;
  }

  measure: total_hours {
    type: sum
    drill_fields: [date_key, payroll_dept_key, total_hours, total_hours_ly, percent_hours_yoy]
    sql: ${TABLE}.hours ;;
  }

  measure: total_ot_hours {
    type: sum
    drill_fields: [date_key, payroll_dept_key, total_hours, total_ot_hours, total_ot_hours_ly, percent_ot_hours_yoy]
    sql: ${TABLE}.ot_hours ;;
  }

  measure: total_vaca_hours {
    type: sum
    drill_fields: [vacation*]
    sql: ${TABLE}.vaca_hours ;;
  }

  measure: total_vaca_hours_ly {
    type: sum
    drill_fields: [date_key, payroll_dept_key, total_hours, total_vaca_hours, total_vaca_hours_ly, percent_vaca_hours_yoy]
    sql: ${TABLE}.vaca_hours_ly ;;
  }

  measure: total_amount_ly {
    type: sum
    value_format: "$#,###"
    drill_fields: [regular*]
    sql: ${TABLE}.total_amount_ly ;;
  }

  measure: running_total_amount_ly {
    type: running_total
    value_format: "$#,###"
    drill_fields: [regular*]
    sql: ${total_amount_ly} ;;
  }

  measure: running_total_amount_yoy_perc {
    type: number
    value_format: "#.00%"
    drill_fields: [regular*]
    sql: (${running_total_amount}-${running_total_amount_ly})/NULLIF(${running_total_amount_ly},0) ;;
  }

  measure: total_ot_amount_ly {
    type: sum
    value_format: "$#,###"
    drill_fields: [ot*]
    sql: ${TABLE}.ot_amount_ly ;;
  }

  measure: total_vaca_amount_ly {
    value_format: "$#,###"
    drill_fields: [vacation*]
    sql: ${TABLE}.vaca_amount_ly ;;
  }

  measure: total_hours_ly {
    type: sum
    drill_fields: [date_key, payroll_dept_key, total_hours, total_hours_ly, percent_hours_yoy]
    sql: ${TABLE}.hours_ly ;;
  }

  measure: total_ot_hours_ly {
    type: sum
    drill_fields: [date_key, payroll_dept_key, total_hours, total_ot_hours, total_ot_hours_ly, percent_ot_hours_yoy]
    sql: ${TABLE}.ot_hours_ly ;;
  }

  measure: percent_total_amount_yoy {
    type: number
    value_format: "#.00%"
    sql: ${total_amount}/NULLIF(${total_amount_ly},0)-1 ;;
  }

  measure: percent_hours_yoy {
    type: number
    value_format: "#.00%"
    drill_fields: [date_key, payroll_dept_key, total_hours, total_hours_ly, percent_hours_yoy]
    sql: ${total_hours}/NULLIF(${total_hours_ly},0)-1 ;;
  }

  measure: percent_ot_hours_yoy {
    type: number
    value_format: "#.00%"
    drill_fields: [date_key, payroll_dept_key, total_hours, total_ot_hours, total_ot_hours_ly, percent_ot_hours_yoy]
    sql: ${total_ot_hours} / NULLIF(${total_ot_hours_ly},0) -1 ;;
  }

  measure: percent_vaca_hours_yoy {
    type: number
    value_format: "#.00%"
    drill_fields: [date_key, payroll_dept_key, total_hours, total_vaca_hours, total_vaca_hours_ly, percent_vaca_hours_yoy]
    sql: ${total_vaca_hours} / NULLIF(${total_vaca_hours_ly},0) -1 ;;
  }

  measure: vaca_hours_percent_of_total {
    type: number
    value_format: "#.00%"
    drill_fields: [date_key, payroll_dept_key, total_hours, total_vaca_hours, total_vaca_hours_ly, percent_vaca_hours_yoy]
    sql: ${total_vaca_hours} / NULLIF(${total_hours},0) ;;
  }

  measure: ot_hours_percent_of_total {
    type: number
    value_format: "#.00%"
    drill_fields: [ot*]
    sql: ${total_ot_hours} / NULLIF(${total_hours},0) ;;
  }

  measure: labor_percent_of_sales {
    type: number
    value_format: "#.00%"
    sql: ${total_amount}/NULLIF(${sales_weekly_yoy.total_net_sales},0) ;;
  }
}

payroll_yoy.view.lookml · 247 lines

source-preserved

- view: payroll_yoy
  derived_table:
    sql: SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours,
    SUM(0) AS total_amount_ly,
    SUM(0) AS ot_amount_ly,
    SUM(0) AS vaca_amount_ly,
    SUM(0) AS hours_ly,
    SUM(0) AS ot_hours_ly,
    NULL AS vaca_hours_ly

FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey

UNION
      SELECT
    d.datekey as date_key,
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear+1 AS iso_year,
    f.earningscodekey as earnings_code_key,
    SUM(0) AS total_amount,
    SUM(0) AS ot_amount,
    SUM(0) AS vaca_amount,
    SUM(0) AS hours,
    SUM(0) AS ot_hours,
    SUM(0) AS vaca_amount,

    SUM(f.lineamount) AS total_amount,
    SUM(CASE WHEN isOT = 'Y' THEN lineamount ELSE 0.0 END) AS ot_amount,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN linehours ELSE 0.0 END) AS vaca_hours


FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY d.datekey, f.department_key, d.IsoWeek, d.IsoYear, f.earningscodekey
    persist_for: 6 hours
  fields:
  - dimension: date_key
    type: int
    sql: ${TABLE}.date_key
  - dimension: date_payroll_key
    type: number
    primary_key: true
    hidden: true
    sql: CONCAT(${TABLE}.date_key,  ${TABLE}.payroll_dept_key, ${TABLE}.iso_year, ${TABLE}.iso_week, ${TABLE}.earnings_code_key )
  - dimension: earnings_code_key
    sql: ${TABLE}.earnings_code_key
  - dimension: payroll_dept_key
    type: number
    sql: ${TABLE}.payroll_dept_key
  - dimension: iso_week
    type: number
    sql: ${TABLE}.iso_week
  - dimension: iso_year
    type: int
    value_format: 0000
    sql: ${TABLE}.iso_year
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_amount
    type: sum
    value_format: '$#,###'
    drill_fields:
    - regular*
    sql: ${TABLE}.total_amount
  - measure: running_total_amount
    type: running_total
    value_format: '$#,###'
    drill_fields:
    - regular*
    sql: ${total_amount}
  - measure: total_ot_amount
    type: sum
    value_format: '$#,###'
    drill_fields:
    - ot*
    sql: ${TABLE}.ot_amount
  - measure: total_vaca_amount
    type: sum
    value_format: '$#,###'
    drill_fields:
    - vacation*
    sql: ${TABLE}.vaca_amount
  - measure: total_hours
    type: sum
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_hours_ly
    - percent_hours_yoy
    sql: ${TABLE}.hours
  - measure: total_ot_hours
    type: sum
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_ot_hours
    - total_ot_hours_ly
    - percent_ot_hours_yoy
    sql: ${TABLE}.ot_hours
  - measure: total_vaca_hours
    type: sum
    drill_fields:
    - vacation*
    sql: ${TABLE}.vaca_hours
  - measure: total_vaca_hours_ly
    type: sum
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_vaca_hours
    - total_vaca_hours_ly
    - percent_vaca_hours_yoy
    sql: ${TABLE}.vaca_hours_ly
  - measure: total_amount_ly
    type: sum
    value_format: '$#,###'
    drill_fields:
    - regular*
    sql: ${TABLE}.total_amount_ly
  - measure: running_total_amount_ly
    type: running_total
    value_format: '$#,###'
    drill_fields:
    - regular*
    sql: ${total_amount_ly}
  - measure: running_total_amount_yoy_perc
    type: number
    value_format: '#.00%'
    drill_fields:
    - regular*
    sql: (${running_total_amount}-${running_total_amount_ly})/NULLIF(${running_total_amount_ly},0)
  - measure: total_ot_amount_ly
    type: sum
    value_format: '$#,###'
    drill_fields:
    - ot*
    sql: ${TABLE}.ot_amount_ly
  - measure: total_vaca_amount_ly
    value_format: '$#,###'
    drill_fields:
    - vacation*
    sql: ${TABLE}.vaca_amount_ly
  - measure: total_hours_ly
    type: sum
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_hours_ly
    - percent_hours_yoy
    sql: ${TABLE}.hours_ly
  - measure: total_ot_hours_ly
    type: sum
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_ot_hours
    - total_ot_hours_ly
    - percent_ot_hours_yoy
    sql: ${TABLE}.ot_hours_ly
  - measure: percent_total_amount_yoy
    type: number
    value_format: '#.00%'
    sql: ${total_amount}/NULLIF(${total_amount_ly},0)-1
  - measure: percent_hours_yoy
    type: number
    value_format: '#.00%'
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_hours_ly
    - percent_hours_yoy
    sql: ${total_hours}/NULLIF(${total_hours_ly},0)-1
  - measure: percent_ot_hours_yoy
    type: number
    value_format: '#.00%'
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_ot_hours
    - total_ot_hours_ly
    - percent_ot_hours_yoy
    sql: ${total_ot_hours} / NULLIF(${total_ot_hours_ly},0) -1
  - measure: percent_vaca_hours_yoy
    type: number
    value_format: '#.00%'
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_vaca_hours
    - total_vaca_hours_ly
    - percent_vaca_hours_yoy
    sql: ${total_vaca_hours} / NULLIF(${total_vaca_hours_ly},0) -1
  - measure: vaca_hours_percent_of_total
    type: number
    value_format: '#.00%'
    drill_fields:
    - date_key
    - payroll_dept_key
    - total_hours
    - total_vaca_hours
    - total_vaca_hours_ly
    - percent_vaca_hours_yoy
    sql: ${total_vaca_hours} / NULLIF(${total_hours},0)
  - measure: ot_hours_percent_of_total
    type: number
    value_format: '#.00%'
    drill_fields:
    - ot*
    sql: ${total_ot_hours} / NULLIF(${total_hours},0)
  - measure: labor_percent_of_sales
    type: number
    value_format: '#.00%'
    sql: ${total_amount}/NULLIF(${sales_weekly_yoy.total_net_sales},0)

payroll_yoy.sql
payroll_yoy.sql
payroll_yoy.view.lkml
payroll_yoy.view.lookml

Payroll Yoy

Blocks

Assumptions

Contents