dbt Saas infrastructure

Org Facts

Org Facts model for saas infrastructure.

inferred

Blocks

dbt SQL LKML LookML

org_facts.sql · 41 lines

inferred

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

SELECT
  runtime_states.organization_id AS organizations_id
  ,MIN(runtime_state_versions.created_at) AS first_date
  ,'Terraform' AS type
FROM runtime_state_versions
JOIN runtime_states
ON runtime_states.id=runtime_state_versions.state_id
GROUP BY 1
UNION
SELECT
  runtime_infrastructures.organization_id AS organizations_id
  ,MIN(runtime_infrastructures.created_at) AS first_date
  ,'Consul' AS type
FROM runtime_infrastructures
GROUP BY 1
UNION
SELECT
  runtime_artifacts.organization_id AS organizations_id
  ,MIN(runtime_artifact_versions.created_at) AS first_date
  ,'Artifact' AS type
FROM runtime_artifact_versions
JOIN runtime_artifact_types
ON runtime_artifact_versions.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
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: org_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.

org_facts.sql · 27 lines

source-preserved validation: not checked

SELECT
  runtime_states.organization_id AS organizations_id
  ,MIN(runtime_state_versions.created_at) AS first_date
  ,'Terraform' AS type
FROM runtime_state_versions
JOIN runtime_states
ON runtime_states.id=runtime_state_versions.state_id
GROUP BY 1
UNION
SELECT
  runtime_infrastructures.organization_id AS organizations_id
  ,MIN(runtime_infrastructures.created_at) AS first_date
  ,'Consul' AS type
FROM runtime_infrastructures
GROUP BY 1
UNION
SELECT
  runtime_artifacts.organization_id AS organizations_id
  ,MIN(runtime_artifact_versions.created_at) AS first_date
  ,'Artifact' AS type
FROM runtime_artifact_versions
JOIN runtime_artifact_types
ON runtime_artifact_versions.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
WHERE runtime_artifact_types.type NOT LIKE '<% product_placeholder %>'
GROUP BY 1

org_facts.view.lkml · 51 lines

inferred

view: org_facts {
  derived_table: {
    sql: SELECT
  runtime_states.organization_id AS organizations_id
  ,MIN(runtime_state_versions.created_at) AS first_date
  ,'Terraform' AS type
FROM runtime_state_versions
JOIN runtime_states
ON runtime_states.id=runtime_state_versions.state_id
GROUP BY 1
UNION
SELECT
  runtime_infrastructures.organization_id AS organizations_id
  ,MIN(runtime_infrastructures.created_at) AS first_date
  ,'Consul' AS type
FROM runtime_infrastructures
GROUP BY 1
UNION
SELECT
  runtime_artifacts.organization_id AS organizations_id
  ,MIN(runtime_artifact_versions.created_at) AS first_date
  ,'Artifact' AS type
FROM runtime_artifact_versions
JOIN runtime_artifact_types
ON runtime_artifact_versions.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
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: P10 {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_date ;;
  }

  dimension_group: type {
    type: time
    sql: ${TABLE}.type ;;
  }

  measure: count {
    type: count
  }
}

org_facts.view.lookml · 46 lines

source-preserved

- view: org_facts
  derived_table:
    sql: SELECT
  runtime_states.organization_id AS organizations_id
  ,MIN(runtime_state_versions.created_at) AS first_date
  ,'Terraform' AS type
FROM runtime_state_versions
JOIN runtime_states
ON runtime_states.id=runtime_state_versions.state_id
GROUP BY 1
UNION
SELECT
  runtime_infrastructures.organization_id AS organizations_id
  ,MIN(runtime_infrastructures.created_at) AS first_date
  ,'Consul' AS type
FROM runtime_infrastructures
GROUP BY 1
UNION
SELECT
  runtime_artifacts.organization_id AS organizations_id
  ,MIN(runtime_artifact_versions.created_at) AS first_date
  ,'Artifact' AS type
FROM runtime_artifact_versions
JOIN runtime_artifact_types
ON runtime_artifact_versions.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
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: P10
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_date
  - dimension_group: type
    type: time
    sql: ${TABLE}.type
  - measure: count
    type: count

org_facts.sql
org_facts.sql
org_facts.view.lkml
org_facts.view.lookml

Org Facts

Blocks

Assumptions

Contents