data.ml
dbt Medical device

Overread Report Json

Overread Report Json model for medical device.

inferred

Blocks

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

WITH tokenized_table AS
(SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[1], '_', ' ')) AS "tokenized_analysis"
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[2], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[3], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[4], '_', ' '))
  FROM overread_reports)
SELECT
ROW_NUMBER() OVER (ORDER BY id, tokenized_analysis)
, *
FROM tokenized_table

# models/schema.yml
version: 2

models:
  - name: overread_report_json
    columns:
      - name: row_number
        tests:
          - unique
          - not_null
      - name: id
      - name: tokenized_analysis

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.
overread_report_json.sql · 24 lines
source-preserved validation: parses 
WITH tokenized_table AS
(SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[1], '_', ' ')) AS "tokenized_analysis"
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[2], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[3], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[4], '_', ' '))
  FROM overread_reports)
SELECT
ROW_NUMBER() OVER (ORDER BY id, tokenized_analysis)
, *
FROM tokenized_table
overread_report_json.view.lkml · 50 lines
inferred 
view: overread_report_json {
  derived_table: {
    sql: WITH tokenized_table AS
(SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[1], '_', ' ')) AS "tokenized_analysis"
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[2], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[3], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[4], '_', ' '))
  FROM overread_reports)
SELECT
ROW_NUMBER() OVER (ORDER BY id, tokenized_analysis)
, *
FROM tokenized_table ;;
  }

  dimension: row_number {
    primary_key: yes
    sql: ${TABLE}.row_number ;;
  }

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

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

  measure: count {
    type: count
  }

  measure: percentage {
    type: percent_of_total
    sql: ${count} ;;
  }
}
overread_report_json.view.lookml · 39 lines
source-preserved 
- view: overread_report_json
  derived_table:
    sql: WITH tokenized_table AS
(SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[1], '_', ' ')) AS "tokenized_analysis"
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[2], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[3], '_', ' '))
  FROM overread_reports
  UNION
  SELECT
    id
    , UPPER(replace((string_to_array(replace(replace(replace(tokenized_analysis_json::varchar, '"', ''), '[',''), ']', ''),','))[4], '_', ' '))
  FROM overread_reports)
SELECT
ROW_NUMBER() OVER (ORDER BY id, tokenized_analysis)
, *
FROM tokenized_table
  fields:
  - dimension: row_number
    primary_key: true
    sql: ${TABLE}.row_number
  - dimension: id
    sql: ${TABLE}.id
  - dimension: tokenized_analysis
    sql: ${TABLE}.tokenized_analysis
  - measure: count
    type: count
  - measure: percentage
    type: percent_of_total
    sql: ${count}

Contents

  • overread_report_json.sql
  • overread_report_json.sql
  • overread_report_json.view.lkml
  • overread_report_json.view.lookml