Total Artifacts

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

SELECT
  runtime_artifacts.name as artifact_name
  , runtime_artifacts.id as artifact_id
  , rav.created_at as created_at
  , organizations.id as organization_id
  , runtime_artifact_types.type as artifact_type
  , organizations.username as organization_name
  , rav.version as artifact_version
  , rav.metadata as metadata
FROM runtime_artifact_versions rav
JOIN runtime_artifact_types
ON rav.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
JOIN organizations
ON runtime_artifacts.organization_id=organizations.id

# models/schema.yml
version: 2

models:
  - name: total_artifacts
    columns:
      - name: artifact_name
      - name: artifact_version
      - name: artifact_type
      - name: created_at
      - name: organization_name
      - name: metadata
      - name: organization_id
      - name: artifact_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.

SELECT
  runtime_artifacts.name as artifact_name
  , runtime_artifacts.id as artifact_id
  , rav.created_at as created_at
  , organizations.id as organization_id
  , runtime_artifact_types.type as artifact_type
  , organizations.username as organization_name
  , rav.version as artifact_version
  , rav.metadata as metadata
FROM runtime_artifact_versions rav
JOIN runtime_artifact_types
ON rav.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
JOIN organizations
ON runtime_artifacts.organization_id=organizations.id

view: total_artifacts {
  derived_table: {
    sql: SELECT
  runtime_artifacts.name as artifact_name
  , runtime_artifacts.id as artifact_id
  , rav.created_at as created_at
  , organizations.id as organization_id
  , runtime_artifact_types.type as artifact_type
  , organizations.username as organization_name
  , rav.version as artifact_version
  , rav.metadata as metadata
FROM runtime_artifact_versions rav
JOIN runtime_artifact_types
ON rav.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
JOIN organizations
ON runtime_artifacts.organization_id=organizations.id ;;
  }

  dimension: artifact_name {
    sql: ${TABLE}.artifact_name ;;
  }

  dimension: artifact_version {
    sql: ${TABLE}.artifact_version ;;
  }

  dimension: artifact_type {
    sql: ${TABLE}.artifact_type ;;
  }

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

  dimension: organization_name {
  }

  dimension: metadata {
  }

  dimension: organization_id {
  }

  dimension: artifact_id {
    type: int
    primary_key: yes
  }

  measure: count {
    type: count
  }
}

- view: total_artifacts
  derived_table:
    sql: SELECT
  runtime_artifacts.name as artifact_name
  , runtime_artifacts.id as artifact_id
  , rav.created_at as created_at
  , organizations.id as organization_id
  , runtime_artifact_types.type as artifact_type
  , organizations.username as organization_name
  , rav.version as artifact_version
  , rav.metadata as metadata
FROM runtime_artifact_versions rav
JOIN runtime_artifact_types
ON rav.artifact_type_id=runtime_artifact_types.id
JOIN runtime_artifacts
ON runtime_artifacts.id=runtime_artifact_types.artifact_id
JOIN organizations
ON runtime_artifacts.organization_id=organizations.id
  fields:
  - dimension: artifact_name
    sql: ${TABLE}.artifact_name
  - dimension: artifact_version
    sql: ${TABLE}.artifact_version
  - dimension: artifact_type
    sql: ${TABLE}.artifact_type
  - dimension: created_at
    type: time
    sql: ${TABLE}.created_at
  - dimension: organization_name
  - dimension: metadata
  - dimension: organization_id
  - dimension: artifact_id
    type: int
    primary_key: true
  - measure: count
    type: count