dbt Fintech api

Growth Usage

Growth Usage model for fintech api.

inferred

Blocks

dbt SQL LKML LookML

growth_usage.sql · 26 lines

inferred

-- models/growth_usage.sql
{{ config(materialized='table') }}

SELECT
  DATE_TRUNC('day',current_time_ms) AS date,
  utilization_metric AS product_detail,
  CASE WHEN (product_detail LIKE 'auth%') THEN 'auth' WHEN (product_detail LIKE 'connect%') THEN 'connect' ELSE product_detail END AS product,
  COUNT(*) AS count_today,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 6 PRECEDING)) AS tr_7_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 29 PRECEDING)) AS tr_30_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 89 PRECEDING)) AS tr_90_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 364 PRECEDING)) AS tr_365_avg
FROM
  api_production
GROUP BY
  date,
  product_detail

# models/schema.yml
version: 2

models:
  - name: growth_usage
    columns:
      - name: product
      - name: product_detail

Assumptions

materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

growth_usage.sql · 14 lines

source-preserved validation: parses

SELECT
  DATE_TRUNC('day',current_time_ms) AS date,
  utilization_metric AS product_detail,
  CASE WHEN (product_detail LIKE 'auth%') THEN 'auth' WHEN (product_detail LIKE 'connect%') THEN 'connect' ELSE product_detail END AS product,
  COUNT(*) AS count_today,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 6 PRECEDING)) AS tr_7_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 29 PRECEDING)) AS tr_30_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 89 PRECEDING)) AS tr_90_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 364 PRECEDING)) AS tr_365_avg
FROM
  api_production
GROUP BY
  date,
  product_detail

growth_usage.view.lkml · 53 lines

inferred

view: growth_usage {
  derived_table: {
    sql: SELECT
  DATE_TRUNC('day',current_time_ms) AS date,
  utilization_metric AS product_detail,
  CASE WHEN (product_detail LIKE 'auth%') THEN 'auth' WHEN (product_detail LIKE 'connect%') THEN 'connect' ELSE product_detail END AS product,
  COUNT(*) AS count_today,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 6 PRECEDING)) AS tr_7_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 29 PRECEDING)) AS tr_30_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 89 PRECEDING)) AS tr_90_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 364 PRECEDING)) AS tr_365_avg
FROM
  api_production
GROUP BY
  date,
  product_detail ;;
    persist_for: "24 hours"
  }

  dimension: product {
  }

  dimension: product_detail {
  }

  dimension_group: date {
    type: date
  }

  measure: count_today {
    type: sum
  }

  measure: trailing_7_day_average {
    type: sum
    sql: ${TABLE}.tr_7_avg ;;
  }

  measure: trailing_30_day_average {
    type: sum
    sql: ${TABLE}.tr_30_avg ;;
  }

  measure: trailing_90_day_average {
    type: sum
    sql: ${TABLE}.tr_90_avg ;;
  }

  measure: trailing_365_day_average {
    type: sum
    sql: ${TABLE}.tr_365_avg ;;
  }
}

growth_usage.view.lookml · 36 lines

source-preserved

- view: growth_usage
  derived_table:
    sql: SELECT
  DATE_TRUNC('day',current_time_ms) AS date,
  utilization_metric AS product_detail,
  CASE WHEN (product_detail LIKE 'auth%') THEN 'auth' WHEN (product_detail LIKE 'connect%') THEN 'connect' ELSE product_detail END AS product,
  COUNT(*) AS count_today,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 6 PRECEDING)) AS tr_7_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 29 PRECEDING)) AS tr_30_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 89 PRECEDING)) AS tr_90_avg,
  ROUND(AVG(COUNT(*) * 1.0) OVER (PARTITION BY product ORDER BY date ROWS 364 PRECEDING)) AS tr_365_avg
FROM
  api_production
GROUP BY
  date,
  product_detail
    persist_for: 24 hours
  fields:
  - dimension: product
  - dimension: product_detail
  - dimension_group: date
    type: date
  - measure: count_today
    type: sum
  - measure: trailing_7_day_average
    type: sum
    sql: ${TABLE}.tr_7_avg
  - measure: trailing_30_day_average
    type: sum
    sql: ${TABLE}.tr_30_avg
  - measure: trailing_90_day_average
    type: sum
    sql: ${TABLE}.tr_90_avg
  - measure: trailing_365_day_average
    type: sum
    sql: ${TABLE}.tr_365_avg

growth_usage.sql
growth_usage.sql
growth_usage.view.lkml
growth_usage.view.lookml

Growth Usage

Blocks

Assumptions

Contents