data.ml
SQL Fintech lending

Report Application

Report Application model for fintech lending.

source-preserved

Blocks

report_application.sql · 32 lines
inferred 
-- models/report_application.sql
{{ config(materialized='view') }}

SELECT
    userUuid,
    quoteDetailsUuid,
    app_date,
    app_date_local,
    acpt_date,
    acpt_date_local,
    fund_date,
    Applied,
    Offered,
    Accepted,
    Funded,
    IF(Funded, 'Funded', IF(Accepted, 'Accepted', IF(Offered, 'Offered', 'Applied'))) as state,
FROM
[Views.ReportApplication]

# models/schema.yml
version: 2

models:
  - name: report_application
    columns:
      - name: quote_details_uuid
      - name: user_uuid
      - name: applied
      - name: offered
      - name: accepted
      - name: funded
      - name: state

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.
report_application.sql · 15 lines
source-preserved validation: not checked 
SELECT
    userUuid,
    quoteDetailsUuid,
    app_date,
    app_date_local,
    acpt_date,
    acpt_date_local,
    fund_date,
    Applied,
    Offered,
    Accepted,
    Funded,
    IF(Funded, 'Funded', IF(Accepted, 'Accepted', IF(Offered, 'Offered', 'Applied'))) as state,
FROM
[Views.ReportApplication]
report_application.view.lkml · 113 lines
inferred 
view: report_application {
  derived_table: {
    sql: SELECT
    userUuid,
    quoteDetailsUuid,
    app_date,
    app_date_local,
    acpt_date,
    acpt_date_local,
    fund_date,
    Applied,
    Offered,
    Accepted,
    Funded,
    IF(Funded, 'Funded', IF(Accepted, 'Accepted', IF(Offered, 'Offered', 'Applied'))) as state,
FROM
[Views.ReportApplication] ;;
  }

  dimension: quote_details_uuid {
    type: string
    sql: ${TABLE}.quoteDetailsUuid ;;
  }

  dimension: user_uuid {
    type: string
    sql: ${TABLE}.userUuid ;;
  }

  dimension: applied {
    type: yesno
    sql: ${TABLE}.Applied ;;
  }

  dimension: offered {
    type: yesno
    sql: ${TABLE}.Offered ;;
  }

  dimension: accepted {
    type: yesno
    sql: ${TABLE}.Accepted ;;
  }

  dimension: funded {
    type: yesno
    sql: ${TABLE}.Funded ;;
  }

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

  dimension_group: application_date {
    type: time
    timeframes: [time, date, week, month, day_of_week, hour]
    sql: ${TABLE}.app_date ;;
  }

  dimension_group: application_date_local {
    type: time
    timeframes: [time, date, week, month, day_of_week, hour]
    sql: ${TABLE}.app_date_local ;;
  }

  dimension_group: accepted_date {
    type: time
    timeframes: [time, date, week, month, day_of_week, hour]
    sql: ${TABLE}.acpt_date ;;
  }

  dimension_group: accepted_date_local {
    type: time
    timeframes: [time, date, week, month, day_of_week, hour]
    sql: ${TABLE}.acpt_date_date ;;
  }

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

  measure: count {
    type: count
    drill_fields: [report_set*]
  }

  measure: applied_sum {
    type: sum
    drill_fields: [report_set*]
    sql: ${applied} ;;
  }

  measure: offered_sum {
    type: sum
    drill_fields: [report_set*]
    sql: ${offered} ;;
  }

  measure: accepted_sum {
    type: sum
    drill_fields: [report_set*]
    sql: ${accepted} ;;
  }

  measure: funded_sum {
    type: sum
    drill_fields: [report_set*]
    sql: ${funded} ;;
  }
}
report_application.view.lookml · 111 lines
source-preserved 
- view: report_application
  derived_table:
    sql: SELECT
    userUuid,
    quoteDetailsUuid,
    app_date,
    app_date_local,
    acpt_date,
    acpt_date_local,
    fund_date,
    Applied,
    Offered,
    Accepted,
    Funded,
    IF(Funded, 'Funded', IF(Accepted, 'Accepted', IF(Offered, 'Offered', 'Applied'))) as state,
FROM
[Views.ReportApplication]
  fields:
  - dimension: quote_details_uuid
    type: string
    sql: ${TABLE}.quoteDetailsUuid
  - dimension: user_uuid
    type: string
    sql: ${TABLE}.userUuid
  - dimension: applied
    type: yesno
    sql: ${TABLE}.Applied
  - dimension: offered
    type: yesno
    sql: ${TABLE}.Offered
  - dimension: accepted
    type: yesno
    sql: ${TABLE}.Accepted
  - dimension: funded
    type: yesno
    sql: ${TABLE}.Funded
  - dimension: state
    type: string
    sql: ${TABLE}.state
  - dimension_group: application_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_week
    - hour
    sql: ${TABLE}.app_date
  - dimension_group: application_date_local
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_week
    - hour
    sql: ${TABLE}.app_date_local
  - dimension_group: accepted_date
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_week
    - hour
    sql: ${TABLE}.acpt_date
  - dimension_group: accepted_date_local
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_week
    - hour
    sql: ${TABLE}.acpt_date_date
  - dimension_group: fund_date
    type: time
    timeframes:
    - date
    - week
    - month
    - day_of_week
    sql: ${TABLE}.fund_date
  - measure: count
    type: count
    drill_fields:
    - report_set*
  - measure: applied_sum
    type: sum
    drill_fields:
    - report_set*
    sql: ${applied}
  - measure: offered_sum
    type: sum
    drill_fields:
    - report_set*
    sql: ${offered}
  - measure: accepted_sum
    type: sum
    drill_fields:
    - report_set*
    sql: ${accepted}
  - measure: funded_sum
    type: sum
    drill_fields:
    - report_set*
    sql: ${funded}

Contents

  • report_application.sql
  • report_application.sql
  • report_application.view.lkml
  • report_application.view.lookml