Cloud Data Architecture and Tradeoffs Questions

-- 1) Build deduplicated staging: for each event_id keep the row with max event_ts
WITH dedup AS (
  SELECT DISTINCT ON (event_id)
    event_id,
    user_id,
    event_ts,
    payload
  FROM staging_events
  ORDER BY event_id, event_ts DESC  -- newest per event_id first
)

-- 2) Upsert into fact_events: insert new rows; update only when incoming event_ts is newer
INSERT INTO fact_events (event_id, user_id, event_ts, payload, updated_at)
SELECT event_id, user_id, event_ts, payload, now() AT TIME ZONE 'UTC'
FROM dedup
ON CONFLICT (event_id) DO UPDATE
SET
  user_id    = EXCLUDED.user_id,
  event_ts   = EXCLUDED.event_ts,
  payload    = EXCLUDED.payload,
  updated_at = now() AT TIME ZONE 'UTC'
-- only apply update if incoming event_ts is strictly newer
WHERE EXCLUDED.event_ts > fact_events.event_ts;