data.ml
dbt Saas cicd

Test For Green Builds

Test For Green Builds model for saas cicd.

inferred

Blocks

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

SELECT DISTINCT ON (name)
      name, build_num, start_time
FROM
(SELECT
  builds.project_id AS "project_id",
  organizations.name AS "name",
  builds.build_num AS "build_num",
  builds.start_time AS "start_time"
FROM organizations
LEFT JOIN organization_projects ON organizations.id = organization_projects.organization_id
LEFT JOIN projects ON projects.id = organization_projects.project_id
LEFT JOIN builds ON projects.id = builds.project_id
WHERE
  builds.is_first_green_build
ORDER BY builds.start_time ASC) AS "TABLE"
LIMIT 100

# models/schema.yml
version: 2

models:
  - name: test_for_green_builds
    columns:
      - name: name
      - name: build_num
      - name: start_time

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.
test_for_green_builds.sql · 16 lines
source-preserved validation: parses 
SELECT DISTINCT ON (name)
      name, build_num, start_time
FROM
(SELECT
  builds.project_id AS "project_id",
  organizations.name AS "name",
  builds.build_num AS "build_num",
  builds.start_time AS "start_time"
FROM organizations
LEFT JOIN organization_projects ON organizations.id = organization_projects.organization_id
LEFT JOIN projects ON projects.id = organization_projects.project_id
LEFT JOIN builds ON projects.id = builds.project_id
WHERE
  builds.is_first_green_build
ORDER BY builds.start_time ASC) AS "TABLE"
LIMIT 100
test_for_green_builds.view.lkml · 45 lines
inferred 
view: test_for_green_builds {
  derived_table: {
    sql: SELECT DISTINCT ON (name)
      name, build_num, start_time
FROM
(SELECT
  builds.project_id AS "project_id",
  organizations.name AS "name",
  builds.build_num AS "build_num",
  builds.start_time AS "start_time"
FROM organizations
LEFT JOIN organization_projects ON organizations.id = organization_projects.organization_id
LEFT JOIN projects ON projects.id = organization_projects.project_id
LEFT JOIN builds ON projects.id = builds.project_id
WHERE
  builds.is_first_green_build
ORDER BY builds.start_time ASC) AS "TABLE"
LIMIT 100 ;;
  }

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

  dimension: build_num {
    type: int
    sql: ${TABLE}.build_num ;;
  }

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: build_num_avg {
    type: avg
    sql: ${TABLE}.build_num ;;
  }
}
test_for_green_builds.view.lookml · 35 lines
source-preserved 
- view: test_for_green_builds
  derived_table:
    sql: SELECT DISTINCT ON (name)
      name, build_num, start_time
FROM
(SELECT
  builds.project_id AS "project_id",
  organizations.name AS "name",
  builds.build_num AS "build_num",
  builds.start_time AS "start_time"
FROM organizations
LEFT JOIN organization_projects ON organizations.id = organization_projects.organization_id
LEFT JOIN projects ON projects.id = organization_projects.project_id
LEFT JOIN builds ON projects.id = builds.project_id
WHERE
  builds.is_first_green_build
ORDER BY builds.start_time ASC) AS "TABLE"
LIMIT 100
  fields:
  - dimension: name
    type: string
    sql: ${TABLE}.name
  - dimension: build_num
    type: int
    sql: ${TABLE}.build_num
  - dimension: start_time
    type: time
    sql: ${TABLE}.start_time
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: build_num_avg
    type: avg
    sql: ${TABLE}.build_num

Contents

  • test_for_green_builds.sql
  • test_for_green_builds.sql
  • test_for_green_builds.view.lkml
  • test_for_green_builds.view.lookml