data.ml
SQL Freelance marketplace

User Facts

User Facts model for freelance marketplace.

source-preserved

Blocks

user_facts.sql · 27 lines
inferred 
-- models/user_facts.sql
{{ config(materialized='table') }}

SELECT
    mstr_clients."Team Rollup" AS team_id
  , MIN(to_timestamp(mstr_clients."Date Created"/1000)::date) AS first_reg_date
  , NULL AS freelancer_id
FROM "ExternalDB"."Companies" AS mstr_clients
GROUP BY 1

UNION ALL

SELECT
  NULL AS team_id
, to_timestamp(mstr_freelancers."Date Created"/1000)::date AS first_reg_date
, mstr_freelancers."Record ID#" AS freelancer_id
FROM "ExternalDB"."Developers" mstr_freelancers
WHERE mstr_freelancers."IsProvider" = 't'

# models/schema.yml
version: 2

models:
  - name: user_facts
    columns:
      - name: team_id
      - name: freelancer_id

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.
user_facts.sql · 15 lines
source-preserved validation: parses 
SELECT
    mstr_clients."Team Rollup" AS team_id
  , MIN(to_timestamp(mstr_clients."Date Created"/1000)::date) AS first_reg_date
  , NULL AS freelancer_id
FROM "ExternalDB"."Companies" AS mstr_clients
GROUP BY 1

UNION ALL

SELECT
  NULL AS team_id
, to_timestamp(mstr_freelancers."Date Created"/1000)::date AS first_reg_date
, mstr_freelancers."Record ID#" AS freelancer_id
FROM "ExternalDB"."Developers" mstr_freelancers
WHERE mstr_freelancers."IsProvider" = 't'
user_facts.view.lkml · 58 lines
inferred 
view: user_facts {
  derived_table: {
    sql: SELECT
    mstr_clients."Team Rollup" AS team_id
  , MIN(to_timestamp(mstr_clients."Date Created"/1000)::date) AS first_reg_date
  , NULL AS freelancer_id
FROM "ExternalDB"."Companies" AS mstr_clients
GROUP BY 1

UNION ALL

SELECT
  NULL AS team_id
, to_timestamp(mstr_freelancers."Date Created"/1000)::date AS first_reg_date
, mstr_freelancers."Record ID#" AS freelancer_id
FROM "ExternalDB"."Developers" mstr_freelancers
WHERE mstr_freelancers."IsProvider" = 't' ;;
    persist_for: "12 hours"
  }

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

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

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

  measure: client_count {
    type: count_distinct
    sql: ${team_id} ;;
  }

  measure: freelancer_count {
    type: count_distinct
    sql: ${freelancer_id} ;;
  }

  measure: client_count_cumulative {
    label: "Clients (Cumulative)"
    type: running_total
    sql: ${client_count} ;;
  }

  measure: freelancer_count_cumulative {
    label: "Freelancers (Cumulative)"
    type: running_total
    sql: ${freelancer_count} ;;
  }
}
user_facts.view.lookml · 47 lines
source-preserved 
- view: user_facts
  derived_table:
    sql: SELECT
    mstr_clients."Team Rollup" AS team_id
  , MIN(to_timestamp(mstr_clients."Date Created"/1000)::date) AS first_reg_date
  , NULL AS freelancer_id
FROM "ExternalDB"."Companies" AS mstr_clients
GROUP BY 1

UNION ALL

SELECT
  NULL AS team_id
, to_timestamp(mstr_freelancers."Date Created"/1000)::date AS first_reg_date
, mstr_freelancers."Record ID#" AS freelancer_id
FROM "ExternalDB"."Developers" mstr_freelancers
WHERE mstr_freelancers."IsProvider" = 't'
    persist_for: 12 hours
  fields:
  - dimension: team_id
    type: int
    sql: ${TABLE}.team_id
  - dimension: freelancer_id
    type: int
    sql: ${TABLE}.freelancer_id
  - dimension_group: first_reg_date
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.first_reg_date
  - measure: client_count
    type: count_distinct
    sql: ${team_id}
  - measure: freelancer_count
    type: count_distinct
    sql: ${freelancer_id}
  - measure: client_count_cumulative
    type: running_total
    label: Clients (Cumulative)
    sql: ${client_count}
  - measure: freelancer_count_cumulative
    type: running_total
    label: Freelancers (Cumulative)
    sql: ${freelancer_count}

Contents

  • user_facts.sql
  • user_facts.sql
  • user_facts.view.lkml
  • user_facts.view.lookml