Geo

-- models/geo.sql
{{ config(materialized='view') }}

SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  geo_country,
  geo_region,
  geo_city,
  geo_zipcode,
  geo_latitude,
  geo_longitude
FROM (
  SELECT
    domain_userid,
    domain_sessionidx,
    geo_country,
    geo_region,
    geo_city,
    geo_zipcode,
    geo_latitude,
    geo_longitude,
    RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS rn
    -- Oddly, we have users who show multiple locations per session. Maybe they're working behind proxy IPs?
  FROM {{ this }}.events
  WHERE geo_country IS NOT NULL
  --AND geo_country != ''
  ) AS t
WHERE t.rn = 1;

# models/schema.yml
version: 2

models:
  - name: geo
    columns:
      - name: visit_id
        tests:
          - unique
          - not_null
      - name: domain_user_id
      - name: domain_session_index
      - name: country
      - name: region
      - name: city
      - name: zipcode
      - name: latitude
      - name: longitude

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.

SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  geo_country,
  geo_region,
  geo_city,
  geo_zipcode,
  geo_latitude,
  geo_longitude
FROM (
  SELECT
    domain_userid,
    domain_sessionidx,
    geo_country,
    geo_region,
    geo_city,
    geo_zipcode,
    geo_latitude,
    geo_longitude,
    RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS rn
    -- Oddly, we have users who show multiple locations per session. Maybe they're working behind proxy IPs?
  FROM ${TABLE}.events
  WHERE geo_country IS NOT NULL
  --AND geo_country != ''
  ) AS t
WHERE t.rn = 1;

view: geo {
  derived_table: {
    sql: SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  geo_country,
  geo_region,
  geo_city,
  geo_zipcode,
  geo_latitude,
  geo_longitude
FROM (
  SELECT
    domain_userid,
    domain_sessionidx,
    geo_country,
    geo_region,
    geo_city,
    geo_zipcode,
    geo_latitude,
    geo_longitude,
    RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS rn
    -- Oddly, we have users who show multiple locations per session. Maybe they're working behind proxy IPs?
  FROM ${TABLE}.events
  WHERE geo_country IS NOT NULL
  --AND geo_country != ''
  ) AS t
WHERE t.rn = 1; ;;
  }

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

  dimension: domain_user_id {
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: domain_session_index {
    type: int
    sql: ${TABLE}.domain_sessionidx ;;
  }

  dimension: country {
    sql: ${TABLE}.geo_country ;;
  }

  dimension: region {
    sql: ${TABLE}.geo_region ;;
  }

  dimension: city {
    sql: ${TABLE}.geo_city ;;
  }

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

  dimension: latitude {
    sql: ${TABLE}.geo_latitude ;;
  }

  dimension: longitude {
    sql: ${TABLE}.geo_longitude ;;
  }

  measure: country_count {
    type: count_distinct
    sql: ${country} ;;
  }

  measure: region_count {
    type: count_distinct
    sql: ${region} ;;
  }

  measure: city_count {
    type: count_distinct
    sql: ${city} ;;
  }
}

- view: geo
  derived_table:
    sql: SELECT
  domain_userid,
  domain_sessionidx,
  domain_userid || '-' || domain_sessionidx AS visit_id,
  geo_country,
  geo_region,
  geo_city,
  geo_zipcode,
  geo_latitude,
  geo_longitude
FROM (
  SELECT
    domain_userid,
    domain_sessionidx,
    geo_country,
    geo_region,
    geo_city,
    geo_zipcode,
    geo_latitude,
    geo_longitude,
    RANK() OVER (PARTITION BY domain_userid, domain_sessionidx ORDER BY dvce_tstamp) AS rn
    -- Oddly, we have users who show multiple locations per session. Maybe they're working behind proxy IPs?
  FROM ${TABLE}.events
  WHERE geo_country IS NOT NULL
  --AND geo_country != ''
  ) AS t
WHERE t.rn = 1;
  fields:
  - dimension: visit_id
    primary_key: true
    sql: ${TABLE}.visit_id
  - dimension: domain_user_id
    sql: ${TABLE}.domain_userid
  - dimension: domain_session_index
    type: int
    sql: ${TABLE}.domain_sessionidx
  - dimension: country
    sql: ${TABLE}.geo_country
  - dimension: region
    sql: ${TABLE}.geo_region
  - dimension: city
    sql: ${TABLE}.geo_city
  - dimension: zipcode
    sql: ${TABLE}.zipcode
  - dimension: latitude
    sql: ${TABLE}.geo_latitude
  - dimension: longitude
    sql: ${TABLE}.geo_longitude
  - measure: country_count
    type: count_distinct
    sql: ${country}
  - measure: region_count
    type: count_distinct
    sql: ${region}
  - measure: city_count
    type: count_distinct
    sql: ${city}