dbt Ecommerce

Order Facts

Order Facts model for ecommerce.

inferred

Blocks

dbt SQL LKML LookML

order_facts.sql · 31 lines

inferred

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

SELECT o.id as order_id
  , SUM(CASE WHEN rar.id not in (9,12,13) OR rar.id IS NULL
    THEN (li.price)
    ELSE NULL
    END ) AS "order_total_price_local_authorized"
  , SUM(li.price) AS price_total

FROM storefront.spree_orders o
LEFT JOIN storefront.spree_line_items li ON li.order_id = o.id
LEFT JOIN storefront.spree_inventory_units iu ON iu.line_item_id = li.id
LEFT JOIN storefront.spree_return_items ri ON ri.inventory_unit_id = iu.id
LEFT JOIN storefront.spree_return_authorizations ra ON ra.id = ri.return_authorization_id
LEFT JOIN storefront.spree_return_authorization_reasons rar ON rar.id = ra.return_authorization_reason_id
GROUP BY o.id

# models/schema.yml
version: 2

models:
  - name: order_facts
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: order_total_price_local_authorized
      - name: order_total_price_usd_authorized
      - name: net_authorized_usd

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.

order_facts.sql · 14 lines

source-preserved validation: parses

SELECT o.id as order_id
  , SUM(CASE WHEN rar.id not in (9,12,13) OR rar.id IS NULL
    THEN (li.price)
    ELSE NULL
    END ) AS "order_total_price_local_authorized"
  , SUM(li.price) AS price_total

FROM storefront.spree_orders o
LEFT JOIN storefront.spree_line_items li ON li.order_id = o.id
LEFT JOIN storefront.spree_inventory_units iu ON iu.line_item_id = li.id
LEFT JOIN storefront.spree_return_items ri ON ri.inventory_unit_id = iu.id
LEFT JOIN storefront.spree_return_authorizations ra ON ra.id = ri.return_authorization_id
LEFT JOIN storefront.spree_return_authorization_reasons rar ON rar.id = ra.return_authorization_reason_id
GROUP BY o.id

order_facts.view.lkml · 61 lines

inferred

view: order_facts {
  derived_table: {
    sql: SELECT o.id as order_id
  , SUM(CASE WHEN rar.id not in (9,12,13) OR rar.id IS NULL
    THEN (li.price)
    ELSE NULL
    END ) AS "order_total_price_local_authorized"
  , SUM(li.price) AS price_total

FROM storefront.spree_orders o
LEFT JOIN storefront.spree_line_items li ON li.order_id = o.id
LEFT JOIN storefront.spree_inventory_units iu ON iu.line_item_id = li.id
LEFT JOIN storefront.spree_return_items ri ON ri.inventory_unit_id = iu.id
LEFT JOIN storefront.spree_return_authorizations ra ON ra.id = ri.return_authorization_id
LEFT JOIN storefront.spree_return_authorization_reasons rar ON rar.id = ra.return_authorization_reason_id
GROUP BY o.id ;;
    persist_for: "1 hour"
  }

  dimension: order_id {
    type: number
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension: order_total_price_local_authorized {
    type: number
    value_format: "$#,##0.00"
    sql: ${TABLE}.order_total_price_local_authorized ;;
  }

  dimension: order_total_price_usd_authorized {
    type: number
    value_format: "$#,##0.00"
    sql: ${order_total_price_local_authorized} * ${exchange_rates.usd_value} ;;
  }

  dimension: net_authorized_usd {
    type: number
    value_format: "$#,##0.00"
    sql: ${orders.promo_total_usd} + ${order_total_price_usd_authorized} ;;
  }

  measure: total_authorized_usd {
    type: sum
    value_format: "$#,##0.00"
    sql: ${order_total_price_usd_authorized} ;;
  }

  measure: total_net_authorized_usd {
    type: sum
    value_format: "$#,##0.00"
    sql: ${net_authorized_usd} ;;
  }

  measure: total_test {
    type: sum
    sql: ${TABLE}.price_total ;;
  }
}

order_facts.view.lookml · 46 lines

source-preserved

- view: order_facts
  derived_table:
    sql: SELECT o.id as order_id
  , SUM(CASE WHEN rar.id not in (9,12,13) OR rar.id IS NULL
    THEN (li.price)
    ELSE NULL
    END ) AS "order_total_price_local_authorized"
  , SUM(li.price) AS price_total

FROM storefront.spree_orders o
LEFT JOIN storefront.spree_line_items li ON li.order_id = o.id
LEFT JOIN storefront.spree_inventory_units iu ON iu.line_item_id = li.id
LEFT JOIN storefront.spree_return_items ri ON ri.inventory_unit_id = iu.id
LEFT JOIN storefront.spree_return_authorizations ra ON ra.id = ri.return_authorization_id
LEFT JOIN storefront.spree_return_authorization_reasons rar ON rar.id = ra.return_authorization_reason_id
GROUP BY o.id
    persist_for: 1 hour
  fields:
  - dimension: order_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: order_total_price_local_authorized
    type: number
    value_format: '$#,##0.00'
    sql: ${TABLE}.order_total_price_local_authorized
  - dimension: order_total_price_usd_authorized
    type: number
    value_format: '$#,##0.00'
    sql: ${order_total_price_local_authorized} * ${exchange_rates.usd_value}
  - dimension: net_authorized_usd
    type: number
    value_format: '$#,##0.00'
    sql: ${orders.promo_total_usd} + ${order_total_price_usd_authorized}
  - measure: total_authorized_usd
    type: sum
    value_format: '$#,##0.00'
    sql: ${order_total_price_usd_authorized}
  - measure: total_net_authorized_usd
    type: sum
    value_format: '$#,##0.00'
    sql: ${net_authorized_usd}
  - measure: total_test
    type: sum
    sql: ${TABLE}.price_total

order_facts.sql
order_facts.sql
order_facts.view.lkml
order_facts.view.lookml

Order Facts

Blocks

Assumptions

Contents