dbt Freelance marketplace

${table}.auth Facts

${table}.auth Facts model for freelance marketplace.

inferred

Blocks

dbt SQL LKML LookML

${TABLE}.auth_facts.sql · 40 lines

inferred

-- models/${TABLE}.auth_facts.sql
{{ config(materialized='table') }}

SELECT
  user_id
, COUNT(*) AS auth_count
, MIN(authz_on) AS first_authz_on
, MIN(CASE WHEN source = 'signup' THEN authz_on ELSE NULL END) AS first_signup_authz_on
, SUM(CASE WHEN esn = 'facebook' THEN 1 ELSE 0 END) AS has_facebook
, SUM(CASE WHEN esn = 'linkedin' THEN 1 ELSE 0 END) AS has_linkedin
, SUM(CASE WHEN esn = 'deviantart' THEN 1 ELSE 0 END) AS has_deviantart
, SUM(CASE WHEN esn = 'google+' THEN 1 ELSE 0 END) AS has_google
, SUM(CASE WHEN esn = 'behance' THEN 1 ELSE 0 END) AS has_behance
, SUM(CASE WHEN esn = 'dribbble' THEN 1 ELSE 0 END) AS has_dribbble
, SUM(CASE WHEN esn = 'github' THEN 1 ELSE 0 END) AS has_github
, SUM(CASE WHEN esn = 'twitter' THEN 1 ELSE 0 END) AS has_twitter
, SUM(CASE WHEN esn = 'stackoverflow' THEN 1 ELSE 0 END) AS has_stackoverflow FROM esn_authz
GROUP BY 1
ORDER BY 2 DESC

# models/schema.yml
version: 2

models:
  - name: ${TABLE}.auth_facts
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: auth_count
      - name: has_facebook
      - name: has_linkedin
      - name: has_deviantart
      - name: has_google
      - name: has_behance
      - name: has_dribbble
      - name: has_github
      - name: has_twitter
      - name: has_stackoverflow

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.

${TABLE}.auth_facts.sql · 16 lines

source-preserved validation: parses

SELECT
  user_id
, COUNT(*) AS auth_count
, MIN(authz_on) AS first_authz_on
, MIN(CASE WHEN source = 'signup' THEN authz_on ELSE NULL END) AS first_signup_authz_on
, SUM(CASE WHEN esn = 'facebook' THEN 1 ELSE 0 END) AS has_facebook
, SUM(CASE WHEN esn = 'linkedin' THEN 1 ELSE 0 END) AS has_linkedin
, SUM(CASE WHEN esn = 'deviantart' THEN 1 ELSE 0 END) AS has_deviantart
, SUM(CASE WHEN esn = 'google+' THEN 1 ELSE 0 END) AS has_google
, SUM(CASE WHEN esn = 'behance' THEN 1 ELSE 0 END) AS has_behance
, SUM(CASE WHEN esn = 'dribbble' THEN 1 ELSE 0 END) AS has_dribbble
, SUM(CASE WHEN esn = 'github' THEN 1 ELSE 0 END) AS has_github
, SUM(CASE WHEN esn = 'twitter' THEN 1 ELSE 0 END) AS has_twitter
, SUM(CASE WHEN esn = 'stackoverflow' THEN 1 ELSE 0 END) AS has_stackoverflow FROM esn_authz
GROUP BY 1
ORDER BY 2 DESC

${TABLE}.auth_facts.view.lkml · 93 lines

inferred

view: ${TABLE}.auth_facts {
  derived_table: {
    sql: SELECT
  user_id
, COUNT(*) AS auth_count
, MIN(authz_on) AS first_authz_on
, MIN(CASE WHEN source = 'signup' THEN authz_on ELSE NULL END) AS first_signup_authz_on
, SUM(CASE WHEN esn = 'facebook' THEN 1 ELSE 0 END) AS has_facebook
, SUM(CASE WHEN esn = 'linkedin' THEN 1 ELSE 0 END) AS has_linkedin
, SUM(CASE WHEN esn = 'deviantart' THEN 1 ELSE 0 END) AS has_deviantart
, SUM(CASE WHEN esn = 'google+' THEN 1 ELSE 0 END) AS has_google
, SUM(CASE WHEN esn = 'behance' THEN 1 ELSE 0 END) AS has_behance
, SUM(CASE WHEN esn = 'dribbble' THEN 1 ELSE 0 END) AS has_dribbble
, SUM(CASE WHEN esn = 'github' THEN 1 ELSE 0 END) AS has_github
, SUM(CASE WHEN esn = 'twitter' THEN 1 ELSE 0 END) AS has_twitter
, SUM(CASE WHEN esn = 'stackoverflow' THEN 1 ELSE 0 END) AS has_stackoverflow FROM esn_authz
GROUP BY 1
ORDER BY 2 DESC ;;
    persist_for: "12 hours"
  }

  dimension: user_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.user_id ;;
  }

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

  dimension: has_facebook {
    type: yesno
    sql: ${TABLE}.has_facebook ;;
  }

  dimension: has_linkedin {
    type: yesno
    sql: ${TABLE}.has_linkedin ;;
  }

  dimension: has_deviantart {
    type: yesno
    sql: ${TABLE}.has_deviantart ;;
  }

  dimension: has_google {
    type: yesno
    sql: ${TABLE}.has_google ;;
  }

  dimension: has_behance {
    type: yesno
    sql: ${TABLE}.has_behance ;;
  }

  dimension: has_dribbble {
    type: yesno
    sql: ${TABLE}.has_dribbble ;;
  }

  dimension: has_github {
    type: yesno
    sql: ${TABLE}.has_github ;;
  }

  dimension: has_twitter {
    type: yesno
    sql: ${TABLE}.has_twitter ;;
  }

  dimension: has_stackoverflow {
    type: yesno
    sql: ${TABLE}.has_stackoverflow ;;
  }

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

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

  measure: count {
    type: count
  }
}

${TABLE}.auth_facts.view.lookml · 72 lines

source-preserved

- view: ${TABLE}.auth_facts
  derived_table:
    sql: SELECT
  user_id
, COUNT(*) AS auth_count
, MIN(authz_on) AS first_authz_on
, MIN(CASE WHEN source = 'signup' THEN authz_on ELSE NULL END) AS first_signup_authz_on
, SUM(CASE WHEN esn = 'facebook' THEN 1 ELSE 0 END) AS has_facebook
, SUM(CASE WHEN esn = 'linkedin' THEN 1 ELSE 0 END) AS has_linkedin
, SUM(CASE WHEN esn = 'deviantart' THEN 1 ELSE 0 END) AS has_deviantart
, SUM(CASE WHEN esn = 'google+' THEN 1 ELSE 0 END) AS has_google
, SUM(CASE WHEN esn = 'behance' THEN 1 ELSE 0 END) AS has_behance
, SUM(CASE WHEN esn = 'dribbble' THEN 1 ELSE 0 END) AS has_dribbble
, SUM(CASE WHEN esn = 'github' THEN 1 ELSE 0 END) AS has_github
, SUM(CASE WHEN esn = 'twitter' THEN 1 ELSE 0 END) AS has_twitter
, SUM(CASE WHEN esn = 'stackoverflow' THEN 1 ELSE 0 END) AS has_stackoverflow FROM esn_authz
GROUP BY 1
ORDER BY 2 DESC
    persist_for: 12 hours
  fields:
  - dimension: user_id
    type: number
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: auth_count
    type: number
    sql: ${TABLE}.auth_count
  - dimension: has_facebook
    type: yesno
    sql: ${TABLE}.has_facebook
  - dimension: has_linkedin
    type: yesno
    sql: ${TABLE}.has_linkedin
  - dimension: has_deviantart
    type: yesno
    sql: ${TABLE}.has_deviantart
  - dimension: has_google
    type: yesno
    sql: ${TABLE}.has_google
  - dimension: has_behance
    type: yesno
    sql: ${TABLE}.has_behance
  - dimension: has_dribbble
    type: yesno
    sql: ${TABLE}.has_dribbble
  - dimension: has_github
    type: yesno
    sql: ${TABLE}.has_github
  - dimension: has_twitter
    type: yesno
    sql: ${TABLE}.has_twitter
  - dimension: has_stackoverflow
    type: yesno
    sql: ${TABLE}.has_stackoverflow
  - dimension_group: first_authz_on
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_authz_on
  - dimension_group: first_signup_authz_on
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_signup_authz_on
  - measure: count
    type: count

${TABLE}.auth_facts.sql
${TABLE}.auth_facts.sql
${TABLE}.auth_facts.view.lkml
${TABLE}.auth_facts.view.lookml

${table}.auth Facts

Blocks

Assumptions

Contents