data.ml
SQL Communications platform

Parent Organization

Parent Organization model for communications platform.

source-preserved

Blocks

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

SELECT
  parent_org.org_id AS org_id,
  parent_org.parent_org_id AS parent_org_id,
  org.name + " (parent)" AS parent_org_name
FROM (
  SELECT
    objectId AS org_id,
    CASE WHEN UPPER(name) CONTAINS 'BERNIE' THEN 'parent_org_placeholder' ELSE objectId END AS parent_org_id,
  FROM
    source_db.Organization) parent_org
INNER JOIN
  source_db.Organization org
ON
  parent_org.parent_org_id = org.objectId

# models/schema.yml
version: 2

models:
  - name: parent_organization
    columns:
      - name: parent_organization_id
      - name: parent_organization_name
      - name: organization_id
      - name: is_in_org_limit
      - name: parent_organization_name_or_other
        description: "Use this to select the top N organizations, and group all other orgs into an \"Other\" group."

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.
parent_organization.sql · 14 lines
source-preserved validation: not checked 
SELECT
  parent_org.org_id AS org_id,
  parent_org.parent_org_id AS parent_org_id,
  org.name + " (parent)" AS parent_org_name
FROM (
  SELECT
    objectId AS org_id,
    CASE WHEN UPPER(name) CONTAINS 'BERNIE' THEN 'parent_org_placeholder' ELSE objectId END AS parent_org_id,
  FROM
    source_db.Organization) parent_org
INNER JOIN
  source_db.Organization org
ON
  parent_org.parent_org_id = org.objectId
parent_organization.view.lkml · 46 lines
inferred 
view: parent_organization {
  derived_table: {
    sql: SELECT
  parent_org.org_id AS org_id,
  parent_org.parent_org_id AS parent_org_id,
  org.name + " (parent)" AS parent_org_name
FROM (
  SELECT
    objectId AS org_id,
    CASE WHEN UPPER(name) CONTAINS 'BERNIE' THEN 'parent_org_placeholder' ELSE objectId END AS parent_org_id,
  FROM
    source_db.Organization) parent_org
INNER JOIN
  source_db.Organization org
ON
  parent_org.parent_org_id = org.objectId ;;
  }

  dimension: parent_organization_id {
    type: string
    sql: ${TABLE}.parent_org_id ;;
  }

  dimension: parent_organization_name {
    type: string
    sql: ${TABLE}.parent_org_name ;;
  }

  dimension: organization_id {
    type: string
    hidden: yes
    sql: ${TABLE}.org_id ;;
  }

  dimension: is_in_org_limit {
    type: yesno
    hidden: yes
    sql: {% condition organization_limit %} ${organization_rank.rank} {% endcondition %} ;;
  }

  dimension: parent_organization_name_or_other {
    description: "Use this to select the top N organizations, and group all other orgs into an \"Other\" group."
    type: string
    sql: CASE WHEN ${is_in_org_limit} THEN ${parent_organization_name} ELSE 'Other' END ;;
  }
}
parent_organization.view.lookml · 35 lines
source-preserved 
- view: parent_organization
  derived_table:
    sql: SELECT
  parent_org.org_id AS org_id,
  parent_org.parent_org_id AS parent_org_id,
  org.name + " (parent)" AS parent_org_name
FROM (
  SELECT
    objectId AS org_id,
    CASE WHEN UPPER(name) CONTAINS 'BERNIE' THEN 'parent_org_placeholder' ELSE objectId END AS parent_org_id,
  FROM
    source_db.Organization) parent_org
INNER JOIN
  source_db.Organization org
ON
  parent_org.parent_org_id = org.objectId
  fields:
  - dimension: parent_organization_id
    type: string
    sql: ${TABLE}.parent_org_id
  - dimension: parent_organization_name
    type: string
    sql: ${TABLE}.parent_org_name
  - dimension: organization_id
    type: string
    hidden: true
    sql: ${TABLE}.org_id
  - dimension: is_in_org_limit
    type: yesno
    hidden: true
    sql: {% condition organization_limit %} ${organization_rank.rank} {% endcondition %}
  - dimension: parent_organization_name_or_other
    type: string
    description: 'Use this to select the top N organizations, and group all other orgs into an "Other" group.'
    sql: CASE WHEN ${is_in_org_limit} THEN ${parent_organization_name} ELSE 'Other' END

Contents

  • parent_organization.sql
  • parent_organization.sql
  • parent_organization.view.lkml
  • parent_organization.view.lookml