dbt Electronics cloud storage

Sisyphus Mesh Mobject Category Byte Count Combined

Sisyphus Mesh Mobject Category Byte Count Combined model for electronics cloud storage.

inferred

Blocks

dbt SQL LKML LookML

sisyphus_mesh_mobject_category_byte_count_combined.sql · 29 lines

inferred

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

SELECT mesh_id,
       bytes,
       item_count,
       identifier,
       representation_type,
       batch_ts
FROM ( (
    SELECT mesh_id, bytes, item_count, identifier, 'ORIGINAL' AS representation_type, batch_ts
    FROM analytics.mesh_mobject_category_byte_count
    WHERE identifier NOT LIKE 'MOVIE'
  ) UNION (
    SELECT mesh_id, blocks * 32768 AS bytes, item_count, 'IMAGE' AS identifier, 'ALTERNATIVE' AS representation_type, batch_ts
    FROM analytics.mesh_derived_mobject_count
  ) ) AS p

# models/schema.yml
version: 2

models:
  - name: sisyphus_mesh_mobject_category_byte_count_combined
    columns:
      - name: mesh_id
      - name: batch_ts
      - name: identifier
      - name: representation_type
      - name: the_type

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.

sisyphus_mesh_mobject_category_byte_count_combined.sql · 14 lines

source-preserved validation: parses

SELECT mesh_id,
       bytes,
       item_count,
       identifier,
       representation_type,
       batch_ts
FROM ( (
    SELECT mesh_id, bytes, item_count, identifier, 'ORIGINAL' AS representation_type, batch_ts
    FROM analytics.mesh_mobject_category_byte_count
    WHERE identifier NOT LIKE 'MOVIE'
  ) UNION (
    SELECT mesh_id, blocks * 32768 AS bytes, item_count, 'IMAGE' AS identifier, 'ALTERNATIVE' AS representation_type, batch_ts
    FROM analytics.mesh_derived_mobject_count
  ) ) AS p

sisyphus_mesh_mobject_category_byte_count_combined.view.lkml · 106 lines

inferred

view: sisyphus_mesh_mobject_category_byte_count_combined {
  derived_table: {
    sql: SELECT mesh_id,
       bytes,
       item_count,
       identifier,
       representation_type,
       batch_ts
FROM ( (
    SELECT mesh_id, bytes, item_count, identifier, 'ORIGINAL' AS representation_type, batch_ts
    FROM analytics.mesh_mobject_category_byte_count
    WHERE identifier NOT LIKE 'MOVIE'
  ) UNION (
    SELECT mesh_id, blocks * 32768 AS bytes, item_count, 'IMAGE' AS identifier, 'ALTERNATIVE' AS representation_type, batch_ts
    FROM analytics.mesh_derived_mobject_count
  ) ) AS p ;;
  }

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

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

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

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

  dimension: the_type {
    sql: case when ${TABLE}.identifier = 'IMAGE' and ${TABLE}.representation_type = 'ORIGINAL' then 'image'
     when ${TABLE}.identifier = 'IMAGE' and ${TABLE}.representation_type = 'ALTERNATIVE' then 'thumbnail'
     when ${TABLE}.identifier = 'VIDEO' then 'video'
     when ${TABLE}.identifier = 'DOCUMENT' then 'document'
     when ${TABLE}.identifier is null then 'unknown_category'
     else 'other'
end ;;
  }

  measure: count {
    type: count
  }

  measure: count_distinct {
    type: count_distinct
    sql: ${TABLE}.mesh_id ;;
  }

  measure: item_count {
    type: sum
    sql: ${TABLE}.item_count ;;
  }

  measure: avg_item_count {
    type: average
    sql: ${TABLE}.item_count ;;
  }

  measure: size_bytes {
    type: sum
    sql: ${TABLE}.bytes ;;
  }

  measure: avg_size_bytes {
    type: average
    sql: ${TABLE}.bytes ;;
  }

  measure: size_terabytes {
    type: number
    value_format: "#,##0.000"
    drill_fields: [detail*]
    sql: ${size_bytes} / power(1024,4) ;;
  }

  measure: avg_item_size_mb {
    type: number
    value_format: "#,##0.0000"
    drill_fields: [detail*]
    sql: 1.0 * ${size_bytes} / ${item_count} / 1024 / 1024 ;;
  }

  measure: avg_mesh_objects {
    type: number
    value_format: "#,##0.0"
    drill_fields: [detail*]
    sql: 1.0 * ${item_count} / ${count_distinct} ;;
  }

  measure: avg_mesh_size_gb {
    type: number
    value_format: "#,##0.000"
    drill_fields: [detail*]
    sql: 1.0 * ${size_bytes} / ${count_distinct} / 1024 / 1024 / 1024 ;;
  }

  measure: max_batch_ts_uspac {
    type: max
    sql: ${TABLE}.batch_ts ;;
  }
}

sisyphus_mesh_mobject_category_byte_count_combined.view.lookml · 77 lines

source-preserved

- view: sisyphus_mesh_mobject_category_byte_count_combined
  derived_table:
    sql: SELECT mesh_id,
       bytes,
       item_count,
       identifier,
       representation_type,
       batch_ts
FROM ( (
    SELECT mesh_id, bytes, item_count, identifier, 'ORIGINAL' AS representation_type, batch_ts
    FROM analytics.mesh_mobject_category_byte_count
    WHERE identifier NOT LIKE 'MOVIE'
  ) UNION (
    SELECT mesh_id, blocks * 32768 AS bytes, item_count, 'IMAGE' AS identifier, 'ALTERNATIVE' AS representation_type, batch_ts
    FROM analytics.mesh_derived_mobject_count
  ) ) AS p
  fields:
  - dimension: mesh_id
    sql: ${TABLE}.mesh_id
  - dimension: batch_ts
    sql: ${TABLE}.batch_ts
  - dimension: identifier
    sql: ${TABLE}.identifier
  - dimension: representation_type
    sql: ${TABLE}.representation_type
  - dimension: the_type
    sql: case when ${TABLE}.identifier = 'IMAGE' and ${TABLE}.representation_type = 'ORIGINAL' then 'image'
     when ${TABLE}.identifier = 'IMAGE' and ${TABLE}.representation_type = 'ALTERNATIVE' then 'thumbnail'
     when ${TABLE}.identifier = 'VIDEO' then 'video'
     when ${TABLE}.identifier = 'DOCUMENT' then 'document'
     when ${TABLE}.identifier is null then 'unknown_category'
     else 'other'
end
  - measure: count
    type: count
  - measure: count_distinct
    type: count_distinct
    sql: ${TABLE}.mesh_id
  - measure: item_count
    type: sum
    sql: ${TABLE}.item_count
  - measure: avg_item_count
    type: average
    sql: ${TABLE}.item_count
  - measure: size_bytes
    type: sum
    sql: ${TABLE}.bytes
  - measure: avg_size_bytes
    type: average
    sql: ${TABLE}.bytes
  - measure: size_terabytes
    type: number
    value_format: '#,##0.000'
    drill_fields:
    - detail*
    sql: ${size_bytes} / power(1024,4)
  - measure: avg_item_size_mb
    type: number
    value_format: '#,##0.0000'
    drill_fields:
    - detail*
    sql: 1.0 * ${size_bytes} / ${item_count} / 1024 / 1024
  - measure: avg_mesh_objects
    type: number
    value_format: '#,##0.0'
    drill_fields:
    - detail*
    sql: 1.0 * ${item_count} / ${count_distinct}
  - measure: avg_mesh_size_gb
    type: number
    value_format: '#,##0.000'
    drill_fields:
    - detail*
    sql: 1.0 * ${size_bytes} / ${count_distinct} / 1024 / 1024 / 1024
  - measure: max_batch_ts_uspac
    type: max
    sql: ${TABLE}.batch_ts

sisyphus_mesh_mobject_category_byte_count_combined.sql
sisyphus_mesh_mobject_category_byte_count_combined.sql
sisyphus_mesh_mobject_category_byte_count_combined.view.lkml
sisyphus_mesh_mobject_category_byte_count_combined.view.lookml

Sisyphus Mesh Mobject Category Byte Count Combined

Blocks

Assumptions

Contents