dbt Saas infrastructure

Organization Facts

Organization Facts model for saas infrastructure.

inferred

Blocks

dbt SQL LKML LookML

organization_facts.sql · 31 lines

inferred

-- models/organization_facts.sql
{{ config(materialized='view') }}

SELECT
  organizations.id AS organizations_id
  ,MIN(runtime_states.created_at) AS first_terraform_date
  ,MIN(runtime_infrastructures.created_at) AS first_product_date
  ,MIN(runtime_artifacts.created_at) AS first_prod_artifact_date
FROM organizations
FULL OUTER JOIN runtime_states
ON runtime_states.organization_id=organizations.id
FULL OUTER JOIN runtime_infrastructures
ON runtime_infrastructures.organization_id=organizations.id
FULL OUTER JOIN runtime_artifacts
ON runtime_artifacts.organization_id=organizations.id
JOIN runtime_artifact_types
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
WHERE runtime_artifact_types.type NOT LIKE '<% product_placeholder %>'

GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: organization_facts
    columns:
      - name: organization_id
        tests:
          - unique
          - not_null

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.

organization_facts.sql · 17 lines

source-preserved validation: not checked

SELECT
  organizations.id AS organizations_id
  ,MIN(runtime_states.created_at) AS first_terraform_date
  ,MIN(runtime_infrastructures.created_at) AS first_product_date
  ,MIN(runtime_artifacts.created_at) AS first_prod_artifact_date
FROM organizations
FULL OUTER JOIN runtime_states
ON runtime_states.organization_id=organizations.id
FULL OUTER JOIN runtime_infrastructures
ON runtime_infrastructures.organization_id=organizations.id
FULL OUTER JOIN runtime_artifacts
ON runtime_artifacts.organization_id=organizations.id
JOIN runtime_artifact_types
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
WHERE runtime_artifact_types.type NOT LIKE '<% product_placeholder %>'

GROUP BY 1

organization_facts.view.lkml · 48 lines

inferred

view: organization_facts {
  derived_table: {
    sql: SELECT
  organizations.id AS organizations_id
  ,MIN(runtime_states.created_at) AS first_terraform_date
  ,MIN(runtime_infrastructures.created_at) AS first_product_date
  ,MIN(runtime_artifacts.created_at) AS first_prod_artifact_date
FROM organizations
FULL OUTER JOIN runtime_states
ON runtime_states.organization_id=organizations.id
FULL OUTER JOIN runtime_infrastructures
ON runtime_infrastructures.organization_id=organizations.id
FULL OUTER JOIN runtime_artifacts
ON runtime_artifacts.organization_id=organizations.id
JOIN runtime_artifact_types
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
WHERE runtime_artifact_types.type NOT LIKE '<% product_placeholder %>'

GROUP BY 1 ;;
  }

  dimension: organization_id {
    primary_key: yes
    sql: ${TABLE}.organizations_id ;;
  }

  dimension_group: first_terraform {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_terraform_date ;;
  }

  dimension_group: first_product {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_product_date ;;
  }

  dimension_group: first_prod_artifact {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_prod_artifact_date ;;
  }

  measure: count {
    type: count
  }
}

organization_facts.view.lookml · 49 lines

source-preserved

- view: organization_facts
  derived_table:
    sql: SELECT
  organizations.id AS organizations_id
  ,MIN(runtime_states.created_at) AS first_terraform_date
  ,MIN(runtime_infrastructures.created_at) AS first_product_date
  ,MIN(runtime_artifacts.created_at) AS first_prod_artifact_date
FROM organizations
FULL OUTER JOIN runtime_states
ON runtime_states.organization_id=organizations.id
FULL OUTER JOIN runtime_infrastructures
ON runtime_infrastructures.organization_id=organizations.id
FULL OUTER JOIN runtime_artifacts
ON runtime_artifacts.organization_id=organizations.id
JOIN runtime_artifact_types
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
WHERE runtime_artifact_types.type NOT LIKE '<% product_placeholder %>'

GROUP BY 1
  fields:
  - dimension: organization_id
    primary_key: true
    sql: ${TABLE}.organizations_id
  - dimension_group: first_terraform
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_terraform_date
  - dimension_group: first_product
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_product_date
  - dimension_group: first_prod_artifact
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_prod_artifact_date
  - measure: count
    type: count

organization_facts.sql
organization_facts.sql
organization_facts.view.lkml
organization_facts.view.lookml

Organization Facts

Blocks

Assumptions

Contents