Geo

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

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 "rank" -- 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 "rank" = 1;

# models/schema.yml
version: 2

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

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.

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 "rank" -- 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 "rank" = 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 "rank" -- 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 "rank" = 1; ;;
    persist_for: "6 hour"
  }

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

  dimension: domain_userid {
    hidden: yes
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: domain_sessionidx {
    type: int
    hidden: yes
    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 "rank" -- 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 "rank" = 1;
    persist_for: 6 hour
  fields:
  - dimension: visit_id
    primary_key: true
    sql: ${TABLE}.visit_id
  - dimension: domain_userid
    hidden: true
    sql: ${TABLE}.domain_userid
  - dimension: domain_sessionidx
    type: int
    hidden: true
    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}