data.ml
dbt Design marketplace

Maker Policies

Maker Policies model for design marketplace.

inferred

Blocks

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

SELECT
  subscriber_id
  , SUBSTRING_INDEX(
    GROUP_CONCAT(text ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS current_text
  , SUBSTRING_INDEX(
    GROUP_CONCAT(updated_at ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS last_updated
  , MIN(created_at) AS first_created
FROM
  member_shoppolicy
GROUP BY
  subscriber_id

# models/schema.yml
version: 2

models:
  - name: maker_policies
    columns:
      - name: subscriber_id
        tests:
          - unique
          - not_null
      - name: current_text
      - name: last_updated
      - name: first_created

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.
maker_policies.sql · 17 lines
source-preserved validation: not checked 
SELECT
  subscriber_id
  , SUBSTRING_INDEX(
    GROUP_CONCAT(text ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS current_text
  , SUBSTRING_INDEX(
    GROUP_CONCAT(updated_at ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS last_updated
  , MIN(created_at) AS first_created
FROM
  member_shoppolicy
GROUP BY
  subscriber_id
maker_policies.view.lkml · 48 lines
inferred 
view: maker_policies {
  derived_table: {
    sql: SELECT
  subscriber_id
  , SUBSTRING_INDEX(
    GROUP_CONCAT(text ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS current_text
  , SUBSTRING_INDEX(
    GROUP_CONCAT(updated_at ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS last_updated
  , MIN(created_at) AS first_created
FROM
  member_shoppolicy
GROUP BY
  subscriber_id ;;
  }

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

  dimension: current_text {
    type: string
    sql: ${TABLE}.current_text ;;
  }

  dimension: last_updated {
    type: time
    sql: ${TABLE}.last_updated ;;
  }

  dimension: first_created {
    type: time
    sql: ${TABLE}.first_created ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}
maker_policies.view.lookml · 38 lines
source-preserved 
- view: maker_policies
  derived_table:
    sql: SELECT
  subscriber_id
  , SUBSTRING_INDEX(
    GROUP_CONCAT(text ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS current_text
  , SUBSTRING_INDEX(
    GROUP_CONCAT(updated_at ORDER BY updated_at DESC SEPARATOR '|')
    , '|'
    , 1
  ) AS last_updated
  , MIN(created_at) AS first_created
FROM
  member_shoppolicy
GROUP BY
  subscriber_id
  fields:
  - dimension: subscriber_id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.subscriber_id
  - dimension: current_text
    type: string
    sql: ${TABLE}.current_text
  - dimension: last_updated
    type: time
    sql: ${TABLE}.last_updated
  - dimension: first_created
    type: time
    sql: ${TABLE}.first_created
  - measure: count
    type: count
    drill_fields:
    - detail*

Contents

  • maker_policies.sql
  • maker_policies.sql
  • maker_policies.view.lkml
  • maker_policies.view.lookml