data.ml
dbt Fintech api

Item Facts

Item Facts model for fintech api.

inferred

Blocks

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

SELECT
  production.item_id AS item_id
  , COUNT(DISTINCT production_audit.client_id) AS "production_audit.clients"
  , COUNT(DISTINCT CONCAT(production_audit.client_id, (TO_CHAR(production_audit.current_time_ms, 'YYYY-MM-DD HH24:MI:SS')))) AS "production_audit.count"
FROM public.api_production AS production
LEFT JOIN public.production_audit AS production_audit ON production_audit.client_id = production.client_id
AND
DATE(production_audit.current_time_ms) = DATE(production.current_time_ms)


WHERE
  ((production.current_time_ms) >= (DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) )) AND (production.current_time_ms) < (DATEADD(day,7, DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) ) )))
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: item_facts
    columns:
      - name: item_id
        tests:
          - unique
          - not_null
      - name: production_auditclients
      - name: production_auditcount

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.
item_facts.sql · 13 lines
source-preserved validation: parses 
SELECT
  production.item_id AS item_id
  , COUNT(DISTINCT production_audit.client_id) AS "production_audit.clients"
  , COUNT(DISTINCT CONCAT(production_audit.client_id, (TO_CHAR(production_audit.current_time_ms, 'YYYY-MM-DD HH24:MI:SS')))) AS "production_audit.count"
FROM public.api_production AS production
LEFT JOIN public.production_audit AS production_audit ON production_audit.client_id = production.client_id
AND
DATE(production_audit.current_time_ms) = DATE(production.current_time_ms)


WHERE
  ((production.current_time_ms) >= (DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) )) AND (production.current_time_ms) < (DATEADD(day,7, DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) ) )))
GROUP BY 1
item_facts.view.lkml · 37 lines
inferred 
view: item_facts {
  derived_table: {
    sql: SELECT
  production.item_id AS item_id
  , COUNT(DISTINCT production_audit.client_id) AS "production_audit.clients"
  , COUNT(DISTINCT CONCAT(production_audit.client_id, (TO_CHAR(production_audit.current_time_ms, 'YYYY-MM-DD HH24:MI:SS')))) AS "production_audit.count"
FROM public.api_production AS production
LEFT JOIN public.production_audit AS production_audit ON production_audit.client_id = production.client_id
AND
DATE(production_audit.current_time_ms) = DATE(production.current_time_ms)


WHERE
  ((production.current_time_ms) >= (DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) )) AND (production.current_time_ms) < (DATEADD(day,7, DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) ) )))
GROUP BY 1 ;;
  }

  dimension: item_id {
    primary_key: yes
    sql: ${TABLE}.item_id ;;
  }

  dimension: production_auditclients {
    type: number
    sql: ${TABLE}."production_audit.clients" ;;
  }

  dimension: production_auditcount {
    type: number
    sql: ${TABLE}."production_audit.count" ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}
item_facts.view.lookml · 29 lines
source-preserved 
- view: item_facts
  derived_table:
    sql: SELECT
  production.item_id AS item_id
  , COUNT(DISTINCT production_audit.client_id) AS "production_audit.clients"
  , COUNT(DISTINCT CONCAT(production_audit.client_id, (TO_CHAR(production_audit.current_time_ms, 'YYYY-MM-DD HH24:MI:SS')))) AS "production_audit.count"
FROM public.api_production AS production
LEFT JOIN public.production_audit AS production_audit ON production_audit.client_id = production.client_id
AND
DATE(production_audit.current_time_ms) = DATE(production.current_time_ms)


WHERE
  ((production.current_time_ms) >= (DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) )) AND (production.current_time_ms) < (DATEADD(day,7, DATEADD(day,-6, DATE_TRUNC('day',GETDATE()) ) )))
GROUP BY 1
  fields:
  - dimension: item_id
    primary_key: true
    sql: ${TABLE}.item_id
  - dimension: production_auditclients
    type: number
    sql: ${TABLE}."production_audit.clients"
  - dimension: production_auditcount
    type: number
    sql: ${TABLE}."production_audit.count"
  - measure: count
    type: count
    drill_fields:
    - detail*

Contents

  • item_facts.sql
  • item_facts.sql
  • item_facts.view.lkml
  • item_facts.view.lookml