Huser

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

select * from (
  select
    huser.id,
    huser.uidpk,
    huser.status,
    huser.first_name,
    huser.middle_name,
    huser.last_name,
    cast(huser.birthday as timestamp) as birthday,
    huser.email,
    huser.mobile_phone_number,
    huser.password_hash,
    huser.created_at,
    huser.updated_at,
    huser.salutation,
    huser.title,
    RANK() over (PARTITION by uidpk order by updated_at) as rank
  from unite_db_stage_user.huser as huser) tmp
where rank=1

# models/schema.yml
version: 2

models:
  - name: huser
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: birthday
      - name: email
      - name: first_name
      - name: last_name
      - name: middle_name
      - name: mobile_phone_number
      - name: password_hash
      - name: salutation
      - name: status
      - name: title
      - name: uidpk
      - name: age

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 * from (
  select
    huser.id,
    huser.uidpk,
    huser.status,
    huser.first_name,
    huser.middle_name,
    huser.last_name,
    cast(huser.birthday as timestamp) as birthday,
    huser.email,
    huser.mobile_phone_number,
    huser.password_hash,
    huser.created_at,
    huser.updated_at,
    huser.salutation,
    huser.title,
    RANK() over (PARTITION by uidpk order by updated_at) as rank
  from unite_db_stage_user.huser as huser) tmp
where rank=1

view: huser {
  derived_table: {
    sql: select * from (
  select
    huser.id,
    huser.uidpk,
    huser.status,
    huser.first_name,
    huser.middle_name,
    huser.last_name,
    cast(huser.birthday as timestamp) as birthday,
    huser.email,
    huser.mobile_phone_number,
    huser.password_hash,
    huser.created_at,
    huser.updated_at,
    huser.salutation,
    huser.title,
    RANK() over (PARTITION by uidpk order by updated_at) as rank
  from unite_db_stage_user.huser as huser) tmp
where rank=1 ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

  dimension: age {
    sql: TIMESTAMPDIFF(SQL_TSI_YEAR, ${birthday}, curdate()) ;;
  }

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

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

  measure: count {
    type: count
    drill_fields: [id, first_name, middle_name, last_name]
  }

  measure: average_age {
    type: avg
    sql: ${age} ;;
  }
}

- view: huser
  derived_table:
    sql: select * from (
  select
    huser.id,
    huser.uidpk,
    huser.status,
    huser.first_name,
    huser.middle_name,
    huser.last_name,
    cast(huser.birthday as timestamp) as birthday,
    huser.email,
    huser.mobile_phone_number,
    huser.password_hash,
    huser.created_at,
    huser.updated_at,
    huser.salutation,
    huser.title,
    RANK() over (PARTITION by uidpk order by updated_at) as rank
  from unite_db_stage_user.huser as huser) tmp
where rank=1
  fields:
  - dimension: id
    primary_key: true
    sql: ${TABLE}.id
  - dimension: birthday
    sql: ${TABLE}.birthday
  - dimension: email
    sql: ${TABLE}.email
  - dimension: first_name
    sql: ${TABLE}.first_name
  - dimension: last_name
    sql: ${TABLE}.last_name
  - dimension: middle_name
    sql: ${TABLE}.middle_name
  - dimension: mobile_phone_number
    sql: ${TABLE}.mobile_phone_number
  - dimension: password_hash
    sql: ${TABLE}.password_hash
  - dimension: salutation
    sql: ${TABLE}.salutation
  - dimension: status
    sql: ${TABLE}.status
  - dimension: title
    sql: ${TABLE}.title
  - dimension: uidpk
    sql: ${TABLE}.uidpk
  - dimension: age
    sql: TIMESTAMPDIFF(SQL_TSI_YEAR, ${birthday}, curdate())
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.created_at
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.updated_at
  - measure: count
    type: count
    drill_fields:
    - id
    - first_name
    - middle_name
    - last_name
  - measure: average_age
    type: avg
    sql: ${age}