dbt Social shopping

Order Delivery Details

Order Delivery Details model for social shopping.

inferred

Blocks

dbt SQL LKML LookML

order_delivery_details.sql · 34 lines

inferred

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

SELECT
  orders.id as order_id,
  fulfillments.state as fulfillment_status,
  shipment_trackings.status as shipment_trackings_status,

  CASE WHEN shipment_trackings.id IS NULL THEN false ELSE true END as has_shipment_tracking,
  CASE WHEN (fulfillments.tracking_number is NULL OR fulfillments.tracking_number = '') THEN false ELSE true END as has_tracking_number,
  fulfillments.tracking_number,
  {{ this }}.tracking_provider,
  fulfillments.tracking_url,
  shipment_trackings.carrier,
  CASE WHEN shipment_tracking_details.external_timestamarketplace IS NOT NULL THEN datediff('day',orders.placed_at,shipment_tracking_details.external_timestamarketplace) ELSE NULL END as days_to_deliver
FROM orders
JOIN sub_orders ON sub_orders.order_id = orders.id
LEFT OUTER JOIN fulfillments ON fulfillments.sub_order_id = sub_orders.id
LEFT OUTER JOIN fulfillment_shipment_trackings ON fulfillment_shipment_trackings.fulfillment_id = fulfillments.id
LEFT OUTER JOIN shipment_trackings ON fulfillment_shipment_trackings.shipment_tracking_id = shipment_trackings.id
LEFT OUTER JOIN shipment_tracking_details ON shipment_tracking_details.shipment_tracking_id = shipment_trackings.id AND shipment_tracking_details.status = <% shipment_status_delivered %>

# models/schema.yml
version: 2

models:
  - name: order_delivery_details
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: has_shipment_tracking
      - name: has_tracking_number

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_delivery_details.sql · 18 lines

source-preserved validation: not checked

SELECT
  orders.id as order_id,
  fulfillments.state as fulfillment_status,
  shipment_trackings.status as shipment_trackings_status,

  CASE WHEN shipment_trackings.id IS NULL THEN false ELSE true END as has_shipment_tracking,
  CASE WHEN (fulfillments.tracking_number is NULL OR fulfillments.tracking_number = '') THEN false ELSE true END as has_tracking_number,
  fulfillments.tracking_number,
  ${TABLE}.tracking_provider,
  fulfillments.tracking_url,
  shipment_trackings.carrier,
  CASE WHEN shipment_tracking_details.external_timestamarketplace IS NOT NULL THEN datediff('day',orders.placed_at,shipment_tracking_details.external_timestamarketplace) ELSE NULL END as days_to_deliver
FROM orders
JOIN sub_orders ON sub_orders.order_id = orders.id
LEFT OUTER JOIN fulfillments ON fulfillments.sub_order_id = sub_orders.id
LEFT OUTER JOIN fulfillment_shipment_trackings ON fulfillment_shipment_trackings.fulfillment_id = fulfillments.id
LEFT OUTER JOIN shipment_trackings ON fulfillment_shipment_trackings.shipment_tracking_id = shipment_trackings.id
LEFT OUTER JOIN shipment_tracking_details ON shipment_tracking_details.shipment_tracking_id = shipment_trackings.id AND shipment_tracking_details.status = <% shipment_status_delivered %>

order_delivery_details.view.lkml · 83 lines

inferred

view: order_delivery_details {
  derived_table: {
    sql: SELECT
  orders.id as order_id,
  fulfillments.state as fulfillment_status,
  shipment_trackings.status as shipment_trackings_status,

  CASE WHEN shipment_trackings.id IS NULL THEN false ELSE true END as has_shipment_tracking,
  CASE WHEN (fulfillments.tracking_number is NULL OR fulfillments.tracking_number = '') THEN false ELSE true END as has_tracking_number,
  fulfillments.tracking_number,
  ${TABLE}.tracking_provider,
  fulfillments.tracking_url,
  shipment_trackings.carrier,
  CASE WHEN shipment_tracking_details.external_timestamarketplace IS NOT NULL THEN datediff('day',orders.placed_at,shipment_tracking_details.external_timestamarketplace) ELSE NULL END as days_to_deliver
FROM orders
JOIN sub_orders ON sub_orders.order_id = orders.id
LEFT OUTER JOIN fulfillments ON fulfillments.sub_order_id = sub_orders.id
LEFT OUTER JOIN fulfillment_shipment_trackings ON fulfillment_shipment_trackings.fulfillment_id = fulfillments.id
LEFT OUTER JOIN shipment_trackings ON fulfillment_shipment_trackings.shipment_tracking_id = shipment_trackings.id
LEFT OUTER JOIN shipment_tracking_details ON shipment_tracking_details.shipment_tracking_id = shipment_trackings.id AND shipment_tracking_details.status = <% shipment_status_delivered %> ;;
    persist_for: "6 hours"
  }

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

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

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

  dimension_group: fulfillment_status {
    type: string
    sql: ${TABLE}.fulfillment_status ;;
  }

  dimension_group: shipment_trackings_status {
    type: string
    sql: ${TABLE}.shipment_trackings_status ;;
  }

  dimension_group: tracking_number {
    type: string
    sql: ${TABLE}.tracking_number ;;
  }

  dimension_group: tracking_comarketplaceany {
    type: string
    sql: ${TABLE}.tracking_comarketplaceany ;;
  }

  dimension_group: tracking_url {
    type: string
    sql: ${TABLE}.tracking_url ;;
  }

  dimension_group: carrier {
    type: string
    sql: ${TABLE}.carrier ;;
  }

  dimension_group: days_to_deliver {
    type: int
    sql: ${TABLE}.days_to_deliver ;;
  }

  measure: average_days_to_deliver {
    type: avg
    sql: ${TABLE}.days_to_deliver ;;
  }

  measure: count {
    type: count
  }
}

order_delivery_details.view.lookml · 58 lines

source-preserved

- view: order_delivery_details
  derived_table:
    sql: SELECT
  orders.id as order_id,
  fulfillments.state as fulfillment_status,
  shipment_trackings.status as shipment_trackings_status,

  CASE WHEN shipment_trackings.id IS NULL THEN false ELSE true END as has_shipment_tracking,
  CASE WHEN (fulfillments.tracking_number is NULL OR fulfillments.tracking_number = '') THEN false ELSE true END as has_tracking_number,
  fulfillments.tracking_number,
  ${TABLE}.tracking_provider,
  fulfillments.tracking_url,
  shipment_trackings.carrier,
  CASE WHEN shipment_tracking_details.external_timestamarketplace IS NOT NULL THEN datediff('day',orders.placed_at,shipment_tracking_details.external_timestamarketplace) ELSE NULL END as days_to_deliver
FROM orders
JOIN sub_orders ON sub_orders.order_id = orders.id
LEFT OUTER JOIN fulfillments ON fulfillments.sub_order_id = sub_orders.id
LEFT OUTER JOIN fulfillment_shipment_trackings ON fulfillment_shipment_trackings.fulfillment_id = fulfillments.id
LEFT OUTER JOIN shipment_trackings ON fulfillment_shipment_trackings.shipment_tracking_id = shipment_trackings.id
LEFT OUTER JOIN shipment_tracking_details ON shipment_tracking_details.shipment_tracking_id = shipment_trackings.id AND shipment_tracking_details.status = <% shipment_status_delivered %>
    persist_for: 6 hours
  fields:
  - dimension: order_id
    type: int
    primary_key: true
    sql: ${TABLE}.order_id
  - dimension: has_shipment_tracking
    type: yesno
    sql: ${TABLE}.has_shipment_tracking
  - dimension: has_tracking_number
    type: yesno
    sql: ${TABLE}.has_tracking_number
  - dimension_group: fulfillment_status
    type: string
    sql: ${TABLE}.fulfillment_status
  - dimension_group: shipment_trackings_status
    type: string
    sql: ${TABLE}.shipment_trackings_status
  - dimension_group: tracking_number
    type: string
    sql: ${TABLE}.tracking_number
  - dimension_group: tracking_comarketplaceany
    type: string
    sql: ${TABLE}.tracking_comarketplaceany
  - dimension_group: tracking_url
    type: string
    sql: ${TABLE}.tracking_url
  - dimension_group: carrier
    type: string
    sql: ${TABLE}.carrier
  - dimension_group: days_to_deliver
    type: int
    sql: ${TABLE}.days_to_deliver
  - measure: average_days_to_deliver
    type: avg
    sql: ${TABLE}.days_to_deliver
  - measure: count
    type: count

order_delivery_details.sql
order_delivery_details.sql
order_delivery_details.view.lkml
order_delivery_details.view.lookml

Order Delivery Details

Blocks

Assumptions

Contents