SQL Electronics cloud storage

Mesh Stats

Mesh Stats model for electronics cloud storage.

source-preserved

Blocks

dbt SQL LKML LookML

mesh_stats.sql · 69 lines

inferred

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

SELECT *
FROM (
      SELECT
        'All Originals' AS classification_string,
        'ORIGINAL' AS representation_type,
        'All' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
    )
    UNION
    (
      SELECT
        'Original Images' AS classification_string,
        'ORIGINAL' AS representation_type,
        'IMAGE' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'IMAGE'
    )
    UNION
    (
      SELECT
        'Original Videos' AS classification_string,
        'ORIGINAL' AS representation_type,
        'VIDEO' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'VIDEO'
    )
    UNION
    (
      SELECT
        'Thumbnails' AS classification_string,
        'ALTERNATIVE' AS representation_type,
        'IMAGE' AS identifier,
        COUNT(mesh_id) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(blocks) * 32768 AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      from analytics.mesh_derived_mobject_count
    )

# models/schema.yml
version: 2

models:
  - name: mesh_stats
    columns:
      - name: classification_string
      - name: representation_type
      - name: identifier
      - name: mesh_id_count
      - name: item_count_mm
      - name: size_tb
      - name: avg_size_per_mesh_gb
      - name: avg_size_per_item_mb
      - name: max_batch_ts_uspac

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.

mesh_stats.sql · 50 lines

source-preserved validation: parses

SELECT *
FROM (
      SELECT
        'All Originals' AS classification_string,
        'ORIGINAL' AS representation_type,
        'All' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
    )
    UNION
    (
      SELECT
        'Original Images' AS classification_string,
        'ORIGINAL' AS representation_type,
        'IMAGE' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'IMAGE'
    )
    UNION
    (
      SELECT
        'Original Videos' AS classification_string,
        'ORIGINAL' AS representation_type,
        'VIDEO' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'VIDEO'
    )
    UNION
    (
      SELECT
        'Thumbnails' AS classification_string,
        'ALTERNATIVE' AS representation_type,
        'IMAGE' AS identifier,
        COUNT(mesh_id) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(blocks) * 32768 AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      from analytics.mesh_derived_mobject_count
    )

mesh_stats.view.lkml · 110 lines

inferred

view: mesh_stats {
  derived_table: {
    sql: SELECT *
FROM (
      SELECT
        'All Originals' AS classification_string,
        'ORIGINAL' AS representation_type,
        'All' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
    )
    UNION
    (
      SELECT
        'Original Images' AS classification_string,
        'ORIGINAL' AS representation_type,
        'IMAGE' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'IMAGE'
    )
    UNION
    (
      SELECT
        'Original Videos' AS classification_string,
        'ORIGINAL' AS representation_type,
        'VIDEO' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'VIDEO'
    )
    UNION
    (
      SELECT
        'Thumbnails' AS classification_string,
        'ALTERNATIVE' AS representation_type,
        'IMAGE' AS identifier,
        COUNT(mesh_id) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(blocks) * 32768 AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      from analytics.mesh_derived_mobject_count
    ) ;;
  }

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

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

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

  dimension: mesh_id_count {
    label: "Mesh Count"
    type: int
    value_format: "#,###"
    sql: ${TABLE}.mesh_id_count ;;
  }

  dimension: item_count_mm {
    label: "Item Count (MM)"
    type: int
    value_format: "0.0,,\" M\""
    sql: ${TABLE}.item_count_sum ;;
  }

  dimension: size_tb {
    label: "Size (TB)"
    type: number
    value_format: "#,##0.00"
    sql: 1.000 * ${TABLE}.bytes_sum / 1024 / 1024 / 1024 / 1024 ;;
  }

  dimension: avg_size_per_mesh_gb {
    label: "Avg Size per Mesh (GB)"
    type: number
    value_format: "#,##0.000"
    sql: 1.000 * ${TABLE}.bytes_sum / ${TABLE}.mesh_id_count / 1024 / 1024 / 1024 ;;
  }

  dimension: avg_size_per_item_mb {
    label: "Avg Size per Item (MB)"
    type: number
    value_format: "#,##0.000"
    sql: 1.000 * ${TABLE}.bytes_sum / ${TABLE}.item_count_sum / 1024 / 1024 ;;
  }

  dimension: max_batch_ts_uspac {
    label: "Data as of"
    type: time
    sql: ${TABLE}.max_batch_ts_uspac ;;
  }
}

mesh_stats.view.lookml · 91 lines

source-preserved

- view: mesh_stats
  derived_table:
    sql: SELECT *
FROM (
      SELECT
        'All Originals' AS classification_string,
        'ORIGINAL' AS representation_type,
        'All' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
    )
    UNION
    (
      SELECT
        'Original Images' AS classification_string,
        'ORIGINAL' AS representation_type,
        'IMAGE' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'IMAGE'
    )
    UNION
    (
      SELECT
        'Original Videos' AS classification_string,
        'ORIGINAL' AS representation_type,
        'VIDEO' AS identifier,
        COUNT( DISTINCT( mesh_id ) ) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(bytes) AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      FROM analytics.mesh_mobject_category_byte_count
      WHERE identifier = 'VIDEO'
    )
    UNION
    (
      SELECT
        'Thumbnails' AS classification_string,
        'ALTERNATIVE' AS representation_type,
        'IMAGE' AS identifier,
        COUNT(mesh_id) AS mesh_id_count,
        SUM(item_count) AS item_count_sum,
        SUM(blocks) * 32768 AS bytes_sum,
        convert_timezone ('US/Pacific', max( batch_ts ) ) AS max_batch_ts_uspac
      from analytics.mesh_derived_mobject_count
    )
  fields:
  - dimension: classification_string
    type: string
    sql: ${TABLE}.classification_string
  - dimension: representation_type
    type: string
    sql: ${TABLE}.representation_type
  - dimension: identifier
    type: string
    sql: ${TABLE}.identifier
  - dimension: mesh_id_count
    type: int
    label: Mesh Count
    value_format: '#,###'
    sql: ${TABLE}.mesh_id_count
  - dimension: item_count_mm
    type: int
    label: Item Count (MM)
    value_format: '0.0,," M"'
    sql: ${TABLE}.item_count_sum
  - dimension: size_tb
    type: number
    label: Size (TB)
    value_format: '#,##0.00'
    sql: 1.000 * ${TABLE}.bytes_sum / 1024 / 1024 / 1024 / 1024
  - dimension: avg_size_per_mesh_gb
    type: number
    label: Avg Size per Mesh (GB)
    value_format: '#,##0.000'
    sql: 1.000 * ${TABLE}.bytes_sum / ${TABLE}.mesh_id_count / 1024 / 1024 / 1024
  - dimension: avg_size_per_item_mb
    type: number
    label: Avg Size per Item (MB)
    value_format: '#,##0.000'
    sql: 1.000 * ${TABLE}.bytes_sum / ${TABLE}.item_count_sum / 1024 / 1024
  - dimension: max_batch_ts_uspac
    type: time
    label: Data as of
    sql: ${TABLE}.max_batch_ts_uspac

mesh_stats.sql
mesh_stats.sql
mesh_stats.view.lkml
mesh_stats.view.lookml

Mesh Stats

Blocks

Assumptions

Contents