data.ml
dbt Service reviews marketplace

Settlement Detail

Settlement Detail model for service reviews marketplace.

inferred

Blocks

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

SELECT
srm_issue.redeem_location_id AS location_id
,srm_issue.issue_location_id AS interaction_location_id
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * -1
      ELSE 0
    END AS issuances
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * srm_issue.redeem_conversion_rate * -1
      ELSE 0
    END AS converted_issuances
, 0 AS redemptions
, 0 AS converted_redemptions
, srm_issue.redeem_amount AS issuance_amount
, srm_issue.redeem_date AS redeem_date
, srm_issue.settlement_header_id AS settlement_header_id
, srm_issue.currency_id as currency_id
, srm_issue.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_issue.issue_location_id = srm_issue.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_issue
     inner join rpt_settlement_header sh on srm_issue.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id
UNION
SELECT
srm_redemp.issue_location_id AS location_id
,srm_redemp.redeem_location_id AS interaction_location_id
, 0 AS issuances
, 0 AS converted_issuances
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount
      ELSE 0
    END AS redemptions
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount * srm_redemp.redeem_conversion_rate
      ELSE 0
    END AS converted_redemptions
, srm_redemp.redeem_amount AS issuance_amount
, srm_redemp.redeem_date AS redeem_date
, srm_redemp.settlement_header_id AS settlement_header_id
, srm_redemp.currency_id as currency_id
, srm_redemp.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_redemp.issue_location_id = srm_redemp.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_redemp
     inner join rpt_settlement_header sh on srm_redemp.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id

# models/schema.yml
version: 2

models:
  - name: settlement_detail
    columns:
      - name: location_id
        tests:
          - unique
          - not_null
      - name: interaction_location_id
      - name: same_location
      - name: issuances
      - name: redemptions
      - name: converted_redemptions
      - name: settlement_header_id
      - name: currency_id
      - name: conversion_rate
      - name: primary_currency_id

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.
settlement_detail.sql · 55 lines
source-preserved validation: parses 
SELECT
srm_issue.redeem_location_id AS location_id
,srm_issue.issue_location_id AS interaction_location_id
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * -1
      ELSE 0
    END AS issuances
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * srm_issue.redeem_conversion_rate * -1
      ELSE 0
    END AS converted_issuances
, 0 AS redemptions
, 0 AS converted_redemptions
, srm_issue.redeem_amount AS issuance_amount
, srm_issue.redeem_date AS redeem_date
, srm_issue.settlement_header_id AS settlement_header_id
, srm_issue.currency_id as currency_id
, srm_issue.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_issue.issue_location_id = srm_issue.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_issue
     inner join rpt_settlement_header sh on srm_issue.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id
UNION
SELECT
srm_redemp.issue_location_id AS location_id
,srm_redemp.redeem_location_id AS interaction_location_id
, 0 AS issuances
, 0 AS converted_issuances
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount
      ELSE 0
    END AS redemptions
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount * srm_redemp.redeem_conversion_rate
      ELSE 0
    END AS converted_redemptions
, srm_redemp.redeem_amount AS issuance_amount
, srm_redemp.redeem_date AS redeem_date
, srm_redemp.settlement_header_id AS settlement_header_id
, srm_redemp.currency_id as currency_id
, srm_redemp.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_redemp.issue_location_id = srm_redemp.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_redemp
     inner join rpt_settlement_header sh on srm_redemp.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id
settlement_detail.view.lkml · 133 lines
inferred 
view: settlement_detail {
  derived_table: {
    sql: SELECT
srm_issue.redeem_location_id AS location_id
,srm_issue.issue_location_id AS interaction_location_id
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * -1
      ELSE 0
    END AS issuances
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * srm_issue.redeem_conversion_rate * -1
      ELSE 0
    END AS converted_issuances
, 0 AS redemptions
, 0 AS converted_redemptions
, srm_issue.redeem_amount AS issuance_amount
, srm_issue.redeem_date AS redeem_date
, srm_issue.settlement_header_id AS settlement_header_id
, srm_issue.currency_id as currency_id
, srm_issue.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_issue.issue_location_id = srm_issue.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_issue
     inner join rpt_settlement_header sh on srm_issue.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id
UNION
SELECT
srm_redemp.issue_location_id AS location_id
,srm_redemp.redeem_location_id AS interaction_location_id
, 0 AS issuances
, 0 AS converted_issuances
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount
      ELSE 0
    END AS redemptions
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount * srm_redemp.redeem_conversion_rate
      ELSE 0
    END AS converted_redemptions
, srm_redemp.redeem_amount AS issuance_amount
, srm_redemp.redeem_date AS redeem_date
, srm_redemp.settlement_header_id AS settlement_header_id
, srm_redemp.currency_id as currency_id
, srm_redemp.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_redemp.issue_location_id = srm_redemp.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_redemp
     inner join rpt_settlement_header sh on srm_redemp.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id ;;
  }

  dimension: location_id {
    type: int
    primary_key: yes
  }

  dimension: interaction_location_id {
    type: int
    hidden: yes
  }

  dimension: same_location {
  }

  dimension: issuances {
  }

  dimension: redemptions {
  }

  dimension: converted_redemptions {
  }

  dimension: settlement_header_id {
  }

  dimension: currency_id {
  }

  dimension: conversion_rate {
  }

  dimension: primary_currency_id {
  }

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

  measure: count {
    type: count_distinct
  }

  measure: total_issuances {
    type: sum
    sql: issuances ;;
  }

  measure: total_redemptions {
    type: sum
    sql: redemptions ;;
  }

  measure: total_converted_issuances {
    type: sum
    sql: converted_issuances ;;
  }

  measure: total_converted_redemptions {
    type: sum
    sql: converted_redemptions ;;
  }

  measure: total_amount {
    type: number
    sql: ${total_issuances} + ${total_redemptions} ;;
  }

  measure: total_converted_amount {
    type: number
    sql: ${total_converted_issuances} + ${total_converted_redemptions} ;;
  }
}
settlement_detail.view.lookml · 100 lines
source-preserved 
- view: settlement_detail
  derived_table:
    sql: SELECT
srm_issue.redeem_location_id AS location_id
,srm_issue.issue_location_id AS interaction_location_id
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * -1
      ELSE 0
    END AS issuances
,    CASE
      WHEN srm_issue.issue_location_id <> srm_issue.redeem_location_id
      THEN srm_issue.redeem_amount * srm_issue.redeem_conversion_rate * -1
      ELSE 0
    END AS converted_issuances
, 0 AS redemptions
, 0 AS converted_redemptions
, srm_issue.redeem_amount AS issuance_amount
, srm_issue.redeem_date AS redeem_date
, srm_issue.settlement_header_id AS settlement_header_id
, srm_issue.currency_id as currency_id
, srm_issue.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_issue.issue_location_id = srm_issue.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_issue
     inner join rpt_settlement_header sh on srm_issue.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id
UNION
SELECT
srm_redemp.issue_location_id AS location_id
,srm_redemp.redeem_location_id AS interaction_location_id
, 0 AS issuances
, 0 AS converted_issuances
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount
      ELSE 0
    END AS redemptions
,   CASE
      WHEN srm_redemp.issue_location_id <> srm_redemp.redeem_location_id
      THEN srm_redemp.redeem_amount * srm_redemp.redeem_conversion_rate
      ELSE 0
    END AS converted_redemptions
, srm_redemp.redeem_amount AS issuance_amount
, srm_redemp.redeem_date AS redeem_date
, srm_redemp.settlement_header_id AS settlement_header_id
, srm_redemp.currency_id as currency_id
, srm_redemp.redeem_conversion_rate as conversion_rate
, sc.primary_currency_id as primary_currency_id
,  CASE
    WHEN srm_redemp.issue_location_id = srm_redemp.redeem_location_id THEN TRUE
    ELSE FALSE END AS same_location
FROM rpt_settlement_redemption_map AS srm_redemp
     inner join rpt_settlement_header sh on srm_redemp.settlement_header_id = sh.settlement_header_id
     inner join rpt_settlement_config sc on sh.settlement_config_id = sc.settlement_config_id
  fields:
  - dimension: location_id
    type: int
    primary_key: true
  - dimension: interaction_location_id
    type: int
    hidden: true
  - dimension: same_location
  - dimension: issuances
  - dimension: redemptions
  - dimension: converted_redemptions
  - dimension: settlement_header_id
  - dimension: currency_id
  - dimension: conversion_rate
  - dimension: primary_currency_id
  - dimension_group: redeem
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.redeem_date
  - measure: count
    type: count_distinct
  - measure: total_issuances
    type: sum
    sql: issuances
  - measure: total_redemptions
    type: sum
    sql: redemptions
  - measure: total_converted_issuances
    type: sum
    sql: converted_issuances
  - measure: total_converted_redemptions
    type: sum
    sql: converted_redemptions
  - measure: total_amount
    type: number
    sql: ${total_issuances} + ${total_redemptions}
  - measure: total_converted_amount
    type: number
    sql: ${total_converted_issuances} + ${total_converted_redemptions}

Contents

  • settlement_detail.sql
  • settlement_detail.sql
  • settlement_detail.view.lkml
  • settlement_detail.view.lookml