user_rep.sql

config {
  type: "operations",
  hasOutput: true,
  schema: "production",
  disabled: false,
  name: "user_reputation",
  dependencies: ["reputation_data", "reputation_data_v"],
  tags: ["user_reputation"],
  description: "user_reputation based on reputation_data from firehose"
}

create table if not exists ${self()} (
   user_id   int64
  ,updated_at timestamp
)
PARTITION BY DATE(updated_at)
;

merge ${self()} t
using (
  select
      user_id
    , latest_updated_at
  from
    (
        select
            user_id
        ,   max(updated_at) as latest_updated_at

        from 
            ${ref("reputation_data_v")}
        group by
            user_id

    ) y

) s
on t.user_id = s.user_id
when matched then
  update set updated_at = s.latest_updated_at, user_id = s.user_id
when not matched then
  insert (updated_at, user_id) values (latest_updated_at, user_id)
;