data.ml
dbt Clothing app

Derived Revenue

Derived Revenue model for clothing app.

inferred

Blocks

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

SELECT
  account_id,
  revenue_type,
  revenue
FROM
(
  (
    SELECT
      account_id,
      'typical_monthly_recurring' as revenue_type,
      typical_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'most_recent_monthly_recurring' as revenue_type,
      most_recent_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'first_monthly_recurring' as revenue_type,
      revenue
    FROM
    (
      SELECT
        account_id,
        count_toward_mrr * invoice_total as revenue,
        row_number() OVER (PARTITION BY account_id ORDER BY invoice_date ASC)
        as row_num
      FROM
        central_data.account_financial_mrr
    ) as mrr
    WHERE row_num= 1
  )
  UNION
  (
    SELECT account_id,
        'total' as revenue_type,
        SUM(count_toward_mrr * invoice_total) as revenue
    FROM
        central_data.account_financial_mrr
    WHERE invoice_date < current_date
    GROUP BY account_id

  )

)

# models/schema.yml
version: 2

models:
  - name: derived_revenue
    columns:
      - name: account_id
      - name: revenue_type
      - name: revenue

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.
derived_revenue.sql · 58 lines
source-preserved validation: parses 
SELECT
  account_id,
  revenue_type,
  revenue
FROM
(
  (
    SELECT
      account_id,
      'typical_monthly_recurring' as revenue_type,
      typical_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'most_recent_monthly_recurring' as revenue_type,
      most_recent_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'first_monthly_recurring' as revenue_type,
      revenue
    FROM
    (
      SELECT
        account_id,
        count_toward_mrr * invoice_total as revenue,
        row_number() OVER (PARTITION BY account_id ORDER BY invoice_date ASC)
        as row_num
      FROM
        central_data.account_financial_mrr
    ) as mrr
    WHERE row_num= 1
  )
  UNION
  (
    SELECT account_id,
        'total' as revenue_type,
        SUM(count_toward_mrr * invoice_total) as revenue
    FROM
        central_data.account_financial_mrr
    WHERE invoice_date < current_date
    GROUP BY account_id

  )

)
derived_revenue.view.lkml · 81 lines
inferred 
view: derived_revenue {
  derived_table: {
    sql: SELECT
  account_id,
  revenue_type,
  revenue
FROM
(
  (
    SELECT
      account_id,
      'typical_monthly_recurring' as revenue_type,
      typical_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'most_recent_monthly_recurring' as revenue_type,
      most_recent_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'first_monthly_recurring' as revenue_type,
      revenue
    FROM
    (
      SELECT
        account_id,
        count_toward_mrr * invoice_total as revenue,
        row_number() OVER (PARTITION BY account_id ORDER BY invoice_date ASC)
        as row_num
      FROM
        central_data.account_financial_mrr
    ) as mrr
    WHERE row_num= 1
  )
  UNION
  (
    SELECT account_id,
        'total' as revenue_type,
        SUM(count_toward_mrr * invoice_total) as revenue
    FROM
        central_data.account_financial_mrr
    WHERE invoice_date < current_date
    GROUP BY account_id

  )

) ;;
  }

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

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

  dimension: revenue {
    type: number
    sql: ${TABLE}.revenue ;;
  }

  measure: revenue_total {
    type: sum_distinct
    sql: ${revenue} ;;
  }
}
derived_revenue.view.lookml · 72 lines
source-preserved 
- view: derived_revenue
  derived_table:
    sql: SELECT
  account_id,
  revenue_type,
  revenue
FROM
(
  (
    SELECT
      account_id,
      'typical_monthly_recurring' as revenue_type,
      typical_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'most_recent_monthly_recurring' as revenue_type,
      most_recent_monthly_mrr as revenue
    FROM
      central_data.external_data_transfer
    WHERE cc_date_added is not null
    AND ACCOUNT_STATUS = 'Customer'
  )
  UNION
  (
    SELECT
      account_id,
      'first_monthly_recurring' as revenue_type,
      revenue
    FROM
    (
      SELECT
        account_id,
        count_toward_mrr * invoice_total as revenue,
        row_number() OVER (PARTITION BY account_id ORDER BY invoice_date ASC)
        as row_num
      FROM
        central_data.account_financial_mrr
    ) as mrr
    WHERE row_num= 1
  )
  UNION
  (
    SELECT account_id,
        'total' as revenue_type,
        SUM(count_toward_mrr * invoice_total) as revenue
    FROM
        central_data.account_financial_mrr
    WHERE invoice_date < current_date
    GROUP BY account_id

  )

)
  fields:
  - dimension: account_id
    type: int
    sql: ${TABLE}.account_id
  - dimension: revenue_type
    sql: ${TABLE}.revenue_type
  - dimension: revenue
    type: number
    sql: ${TABLE}.revenue
  - measure: revenue_total
    type: sum_distinct
    sql: ${revenue}

Contents

  • derived_revenue.sql
  • derived_revenue.sql
  • derived_revenue.view.lkml
  • derived_revenue.view.lookml