Redshift Recent Data Loads

recent_data_load.yml

- explore: data_loads
  hidden: true
- view: data_loads
  derived_table:
    sql: |
      select replace(regexp_substr(filename, '//[a-zA-Z0-9\-]+/'), '/', '') as root_bucket
        , case
            when replace(regexp_substr(filename, '//[a-zA-Z0-9\-]+/'), '/', '') = 'snowplow-looker-emr-out'
            then 'snowplow'
            else regexp_replace(regexp_substr(filename, '/[a-zA-Z0-9\\-\\_]+\\.[a-zA-Z]{3,4}'), '(/|[0-9]{2,})', '')
          end as file_name
        , max(curtime) as most_recent_load
      from stl_load_commits
      group by 1,2

  fields:

  - dimension: root_bucket
    type: string
    sql: ${TABLE}.root_bucket

  - dimension: file_name
    type: string
    sql: ${TABLE}.file_name

  - dimension_group: most_recent_load
    type: time
    timeframes: [time, date]
    sql: ${TABLE}.most_recent_load
    
  - dimension: hours_since_last_load
    type: int
    sql: datediff('hour', ${TABLE}.most_recent_load, getdate())
    html: |
        {% if value < 24 %}
        <div style="color:#B40404; background-color:#22CE7E; font-size:100%; text-align:center">{{ rendered_value }}</div>
        {% elsif value >= 24 and value < 48 %}
        <div style="color:#868A08; background-color:#95F047; font-size:100%; text-align:center">{{ rendered_value }}</div>
        {% elsif value >= 48 %}
        <div style="color:#868A08; background-color:#C64646; font-size:100%; text-align:center">{{ rendered_value }}</div>        
        {% endif %}