data.ml
LKML Fintech lending

Fact Redshift Etl

Fact Redshift Etl model for fintech lending.

inferred

Blocks

fact_redshift_etl.view.lkml · 239 lines
inferred 
view: fact_redshift_etl {
  sql_table_name: reporting.fact_redshift_etl ;;

  dimension: fact_redshift_etl_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.fact_redshift_etl_id ;;
  }

  dimension: dehydrate_records_duration_ms {
    type: int
    sql: ${TABLE}.dehydrate_records_duration_ms ;;
  }

  dimension: enforce_daily_grain_duration_ms {
    type: int
    sql: ${TABLE}.enforce_daily_grain_duration_ms ;;
  }

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

  dimension: execute_copy_command_duration_ms {
    type: int
    sql: ${TABLE}.execute_copy_command_duration_ms ;;
  }

  dimension: execute_sync_script_duration_ms {
    type: int
    sql: ${TABLE}.execute_sync_script_duration_ms ;;
  }

  dimension: extract_to_compressed_file_duration_ms {
    type: int
    sql: ${TABLE}.extract_to_compressed_file_duration_ms ;;
  }

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

  dimension: flag_job_failed {
    type: yesno
    sql: ${TABLE}.flag_job_failed ;;
  }

  dimension: flag_job_last_executed {
    type: int
    sql: ${TABLE}.flag_job_last_executed ;;
  }

  dimension: job_duration_ms {
    type: int
    sql: ${TABLE}.job_duration_ms ;;
  }

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

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

  dimension: job_name_no_schema {
    sql: SUBSTRING(${TABLE}.job_name,CHARINDEX('.',Job_Name) + 1,LEN(Job_Name)) ;;
  }

  dimension: job_name_no_schema_time {
    sql: SUBSTRING(${TABLE}.job_name,CHARINDEX('.',Job_Name) + 1,LEN(Job_Name)) + '-' + TO_CHAR(Job_Start_Date_Time,'HH12:MI:SS:PM') ;;
  }

  dimension: records_dehydrated {
    type: int
    sql: ${TABLE}.records_dehydrated ;;
  }

  dimension: records_deleted_to_grain {
    type: int
    sql: ${TABLE}.records_deleted_to_grain ;;
  }

  dimension: records_extracted_uploaded_and_copied {
    type: int
    sql: ${TABLE}.records_extracted_uploaded_and_copied ;;
  }

  dimension: transfer_compressed_file_to_s3_duration_ms {
    type: int
    sql: ${TABLE}.transfer_compressed_file_to_s3_duration_ms ;;
  }

  dimension_group: job_start_date {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.job_start_date_time ;;
  }

  measure: count {
    type: count
    drill_fields: [fact_redshift_etl_id, job_name]
  }

  measure: total_extract_to_compressed_file_duration_ms {
    type: sum
    sql: ${extract_to_compressed_file_duration_ms} ;;
  }

  measure: total_transfer_compressed_file_to_s3_duration_ms {
    type: sum
    sql: ${transfer_compressed_file_to_s3_duration_ms} ;;
  }

  measure: total_execute_copy_command_duration_ms {
    type: sum
    sql: ${execute_copy_command_duration_ms} ;;
  }

  measure: total_dehydrate_records_duration_ms {
    type: sum
    sql: ${dehydrate_records_duration_ms} ;;
  }

  measure: total_enforce_daily_grain_duration_ms {
    type: sum
    sql: ${enforce_daily_grain_duration_ms} ;;
  }

  measure: total_execute_sync_script_duration_ms {
    type: sum
    sql: ${execute_sync_script_duration_ms} ;;
  }

  measure: total_extract_to_compressed_file_duration_seconds {
    type: int
    sql: ${total_extract_to_compressed_file_duration_ms} / 1000 ;;
  }

  measure: total_transfer_compressed_file_to_s3_duration_seconds {
    type: int
    sql: ${total_transfer_compressed_file_to_s3_duration_ms} / 1000 ;;
  }

  measure: total_execute_copy_command_duration_seconds {
    type: int
    sql: ${total_execute_copy_command_duration_ms} / 1000 ;;
  }

  measure: total_dehyrdrate_records_duration_seconds {
    type: int
    sql: ${total_dehydrate_records_duration_ms} / 1000 ;;
  }

  measure: total_enforce_daily_grain_duration_seconds {
    type: int
    sql: ${total_enforce_daily_grain_duration_ms} / 1000 ;;
  }

  measure: total_execute_sync_script_duration_seconds {
    type: int
    sql: ${total_execute_sync_script_duration_ms} / 1000 ;;
  }

  measure: total_job_duration_seconds {
    type: sum
    sql: ${job_duration_ms} / 1000 ;;
  }

  measure: total_job_fail {
    type: sum
    sql: CAST(${flag_job_failed} AS INT) ;;
  }

  measure: total_job_success {
    type: sum
    sql: CASE WHEN CAST(${flag_job_failed} AS INT) = 0 THEN 1 ELSE 0 END ;;
  }

  measure: avg_extract_to_compressed_file_duration_ms {
    type: avg
    sql: ${extract_to_compressed_file_duration_ms} ;;
  }

  measure: avg_transfer_compressed_file_to_s3_duration_ms {
    type: avg
    sql: ${transfer_compressed_file_to_s3_duration_ms} ;;
  }

  measure: avg_execute_copy_command_duration_ms {
    type: avg
    sql: ${execute_copy_command_duration_ms} ;;
  }

  measure: avg_dehydrate_records_duration_ms {
    type: avg
    sql: ${dehydrate_records_duration_ms} ;;
  }

  measure: avg_enforce_daily_grain_duration_ms {
    type: avg
    sql: ${enforce_daily_grain_duration_ms} ;;
  }

  measure: avg_execute_sync_script_duration_ms {
    type: avg
    sql: ${execute_sync_script_duration_ms} ;;
  }

  measure: avg_extract_to_compressed_file_duration_seconds {
    type: int
    sql: ${avg_extract_to_compressed_file_duration_ms} / 1000 ;;
  }

  measure: avg_transfer_compressed_file_to_s3_duration_seconds {
    type: int
    sql: ${avg_transfer_compressed_file_to_s3_duration_ms} / 1000 ;;
  }

  measure: avg_execute_copy_command_duration_seconds {
    type: int
    sql: ${avg_execute_copy_command_duration_ms} / 1000 ;;
  }

  measure: avg_dehyrdrate_records_duration_seconds {
    type: int
    sql: ${avg_dehydrate_records_duration_ms} / 1000 ;;
  }

  measure: avg_enforce_daily_grain_duration_seconds {
    type: int
    sql: ${avg_enforce_daily_grain_duration_ms} / 1000 ;;
  }

  measure: avg_execute_sync_script_duration_seconds {
    type: int
    sql: ${avg_execute_sync_script_duration_ms} / 1000 ;;
  }
}
fact_redshift_etl.view.lookml · 149 lines
source-preserved 
- view: fact_redshift_etl
  sql_table_name: reporting.fact_redshift_etl
  fields:
  - dimension: fact_redshift_etl_id
    type: int
    primary_key: true
    sql: ${TABLE}.fact_redshift_etl_id
  - dimension: dehydrate_records_duration_ms
    type: int
    sql: ${TABLE}.dehydrate_records_duration_ms
  - dimension: enforce_daily_grain_duration_ms
    type: int
    sql: ${TABLE}.enforce_daily_grain_duration_ms
  - dimension: error_message
    sql: ${TABLE}.error_message
  - dimension: execute_copy_command_duration_ms
    type: int
    sql: ${TABLE}.execute_copy_command_duration_ms
  - dimension: execute_sync_script_duration_ms
    type: int
    sql: ${TABLE}.execute_sync_script_duration_ms
  - dimension: extract_to_compressed_file_duration_ms
    type: int
    sql: ${TABLE}.extract_to_compressed_file_duration_ms
  - dimension: failed_step
    sql: ${TABLE}.failed_step
  - dimension: flag_job_failed
    type: yesno
    sql: ${TABLE}.flag_job_failed
  - dimension: flag_job_last_executed
    type: int
    sql: ${TABLE}.flag_job_last_executed
  - dimension: job_duration_ms
    type: int
    sql: ${TABLE}.job_duration_ms
  - dimension: job_id
    sql: ${TABLE}.job_id
  - dimension: job_name
    sql: ${TABLE}.job_name
  - dimension: job_name_no_schema
    sql: SUBSTRING(${TABLE}.job_name,CHARINDEX('.',Job_Name) + 1,LEN(Job_Name))
  - dimension: job_name_no_schema_time
    sql: SUBSTRING(${TABLE}.job_name,CHARINDEX('.',Job_Name) + 1,LEN(Job_Name)) + '-' + TO_CHAR(Job_Start_Date_Time,'HH12:MI:SS:PM')
  - dimension: records_dehydrated
    type: int
    sql: ${TABLE}.records_dehydrated
  - dimension: records_deleted_to_grain
    type: int
    sql: ${TABLE}.records_deleted_to_grain
  - dimension: records_extracted_uploaded_and_copied
    type: int
    sql: ${TABLE}.records_extracted_uploaded_and_copied
  - dimension: transfer_compressed_file_to_s3_duration_ms
    type: int
    sql: ${TABLE}.transfer_compressed_file_to_s3_duration_ms
  - dimension_group: job_start_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.job_start_date_time
  - measure: count
    type: count
    drill_fields:
    - fact_redshift_etl_id
    - job_name
  - measure: total_extract_to_compressed_file_duration_ms
    type: sum
    sql: ${extract_to_compressed_file_duration_ms}
  - measure: total_transfer_compressed_file_to_s3_duration_ms
    type: sum
    sql: ${transfer_compressed_file_to_s3_duration_ms}
  - measure: total_execute_copy_command_duration_ms
    type: sum
    sql: ${execute_copy_command_duration_ms}
  - measure: total_dehydrate_records_duration_ms
    type: sum
    sql: ${dehydrate_records_duration_ms}
  - measure: total_enforce_daily_grain_duration_ms
    type: sum
    sql: ${enforce_daily_grain_duration_ms}
  - measure: total_execute_sync_script_duration_ms
    type: sum
    sql: ${execute_sync_script_duration_ms}
  - measure: total_extract_to_compressed_file_duration_seconds
    type: int
    sql: ${total_extract_to_compressed_file_duration_ms} / 1000
  - measure: total_transfer_compressed_file_to_s3_duration_seconds
    type: int
    sql: ${total_transfer_compressed_file_to_s3_duration_ms} / 1000
  - measure: total_execute_copy_command_duration_seconds
    type: int
    sql: ${total_execute_copy_command_duration_ms} / 1000
  - measure: total_dehyrdrate_records_duration_seconds
    type: int
    sql: ${total_dehydrate_records_duration_ms} / 1000
  - measure: total_enforce_daily_grain_duration_seconds
    type: int
    sql: ${total_enforce_daily_grain_duration_ms} / 1000
  - measure: total_execute_sync_script_duration_seconds
    type: int
    sql: ${total_execute_sync_script_duration_ms} / 1000
  - measure: total_job_duration_seconds
    type: sum
    sql: ${job_duration_ms} / 1000
  - measure: total_job_fail
    type: sum
    sql: CAST(${flag_job_failed} AS INT)
  - measure: total_job_success
    type: sum
    sql: CASE WHEN CAST(${flag_job_failed} AS INT) = 0 THEN 1 ELSE 0 END
  - measure: avg_extract_to_compressed_file_duration_ms
    type: avg
    sql: ${extract_to_compressed_file_duration_ms}
  - measure: avg_transfer_compressed_file_to_s3_duration_ms
    type: avg
    sql: ${transfer_compressed_file_to_s3_duration_ms}
  - measure: avg_execute_copy_command_duration_ms
    type: avg
    sql: ${execute_copy_command_duration_ms}
  - measure: avg_dehydrate_records_duration_ms
    type: avg
    sql: ${dehydrate_records_duration_ms}
  - measure: avg_enforce_daily_grain_duration_ms
    type: avg
    sql: ${enforce_daily_grain_duration_ms}
  - measure: avg_execute_sync_script_duration_ms
    type: avg
    sql: ${execute_sync_script_duration_ms}
  - measure: avg_extract_to_compressed_file_duration_seconds
    type: int
    sql: ${avg_extract_to_compressed_file_duration_ms} / 1000
  - measure: avg_transfer_compressed_file_to_s3_duration_seconds
    type: int
    sql: ${avg_transfer_compressed_file_to_s3_duration_ms} / 1000
  - measure: avg_execute_copy_command_duration_seconds
    type: int
    sql: ${avg_execute_copy_command_duration_ms} / 1000
  - measure: avg_dehyrdrate_records_duration_seconds
    type: int
    sql: ${avg_dehydrate_records_duration_ms} / 1000
  - measure: avg_enforce_daily_grain_duration_seconds
    type: int
    sql: ${avg_enforce_daily_grain_duration_ms} / 1000
  - measure: avg_execute_sync_script_duration_seconds
    type: int
    sql: ${avg_execute_sync_script_duration_ms} / 1000

Contents

  • fact_redshift_etl.view.lkml
  • fact_redshift_etl.view.lookml