Cloud Data Warehouse Architecture Questions

-- Deduplicate staging by event_id keeping latest event_ts
WITH staged_dedup AS (
  SELECT
    ARRAY_AGG(s ORDER BY event_ts DESC, ingestion_id DESC LIMIT 1)[OFFSET(0)] AS row
  FROM staging.events s
  GROUP BY s.event_id
),
src AS (
  SELECT
    row.event_id,
    row.event_ts,
    row.user_id,
    row.event_type,
    row.payload
  FROM staged_dedup
)

MERGE INTO `analytics.events` T
USING src S
ON T.event_id = S.event_id
WHEN MATCHED AND S.event_ts > T.event_ts THEN
  UPDATE SET
    event_ts = S.event_ts,
    user_id = S.user_id,
    event_type = S.event_type,
    payload = S.payload,
    updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED BY TARGET THEN
  INSERT(event_id, event_ts, user_id, event_type, payload, created_at, updated_at)
  VALUES(S.event_id, S.event_ts, S.user_id, S.event_type, S.payload, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP());