Redshift Cluster Disk Utilization

db_space.yml

- explore: db_space
  label: 'DB Space'
  hidden: true
  
- view: db_space
  derived_table:
    sql: |
      SELECT name AS table
        , trim(pgn.nspname) AS schema
        , SUM(b.mbytes) AS megabytes
        , SUM(a.rows) AS rows
      FROM (SELECT db_id
              , id
              , name
              , SUM(rows) AS rows
            FROM stv_tbl_perm a
            GROUP BY 1,2,3) AS a
      INNER JOIN pg_class AS pgc 
      ON pgc.oid = a.id
      INNER JOIN pg_namespace AS pgn 
      ON pgn.oid = pgc.relnamespace
      INNER JOIN pg_database AS pgdb
      ON pgdb.oid = a.db_id
      INNER JOIN (SELECT tbl
                    , COUNT(*) AS mbytes
                  FROM stv_blocklist
                  GROUP BY 1) AS b 
      ON a.id = b.tbl
      GROUP BY 1,2
  fields:
      
    - dimension: table
      sql: ${TABLE}.table
  
    - dimension: schema
      sql: ${TABLE}.schema
  
    - dimension: megabytes
      type: number
      sql: ${TABLE}.megabytes
  
    - dimension: rows
      type: number
      sql: ${TABLE}.rows    

    - dimension: table_stem
      sql: |
        CASE 
          WHEN (${table} ~ '(lr|lc)\\$[a-zA-Z0-9]+_.*')
          THEN LTRIM(REGEXP_SUBSTR(${table}, '_.*'), '_') || ' - Looker PDT'
          ELSE ${table}
        END    
        
    - measure: total_megabytes
      type: sum
      sql: ${megabytes}
      
    - measure: total_rows
      type: sum
      sql: ${rows}      
      
    - measure: total_tables
      type: count_distinct
      sql: ${table}