reviewing-warehouse-suggestions

Reviewing warehouse suggestions

PostHog computes warehouse suggestions every day from how the project reads its views and tables. Each suggestion is about one view or table. Its payload holds everything that accepting it would create or change.

1. Find the suggestions

Call warehouse-suggestions-list with status=proposed. The results come in priority order, so start at the top. If the list is empty, call warehouse-suggestions-status: it says whether suggestions are turned off, still collecting read history, or not eligible.

Call warehouse-suggestions-get for a suggestion the user wants to discuss. Its evidence has the read counts behind it.

2. Explain it with its own numbers

Tell the user what accepting does and what it costs, using the payload fields. Do not invent a score or a confidence level.

• materialize: the view is read often enough that storing its result saves compute.
  • saves_seconds_per_month and saves_bytes_per_month are what the reads stop costing.
  • Materializing adds a recurring refresh cost, which these numbers already subtract.
  • freshness_today_seconds and freshness_after_seconds say how old the data can be now and after.
  • refresh_interval_seconds is the proposed interval. refresh_interval_basis says what kept it from being shorter: source_sync (the sources sync no faster), fastest_schedule (PostHog refreshes views no more often), read_rate (people read the view no more often) or savings (a shorter interval costs more than it saves).
  • refresh_mode is incremental when each refresh can read only new rows, by incremental_key, and update rows by unique_key. Otherwise it is full, and each refresh rebuilds the whole table.
• unmaterialize: this materialized view is read so rarely that refreshing it costs more than computing it on each read.
  • refresh_seconds_per_month and refresh_bytes_per_month are what its refreshes cost now.
  • live_seconds_per_month and live_bytes_per_month estimate what its reads would cost if each one computed the view. The estimate uses a full refresh per read, so it leans high.
  • read_seconds is how long one read would take afterwards. Nine in ten of its refreshes finished within this time. Tell the user that reads get slower by about this much, and that each read sees current data instead of the last refresh.
  • Accepting stops the refreshes and deletes the stored table. The view definition stays, so it can be materialized again later.
  • A 409 on accept means the view changed since the suggestion was made, for example another view now reads from it. Explain the reason to the user and do not retry.
• certify: many people read this view or table, from several places. Use the read counts in evidence.
• deprecate: nobody reads this materialized view, but it still refreshes. refresh_seconds_per_month and refresh_bytes_per_month are that cost. Accepting marks it deprecated in the data catalog, stops its refreshes and deletes its stored table. The view definition stays, so it can be materialized again later. To keep it refreshing, pass keep_materialized: true. After accepting, created_asset.stopped_materialization says whether the refreshes stopped: they keep running when other views read from it or its refreshes feed person properties, a destination or a workflow.

3. Change a materialize suggestion before accepting

The payload is a starting point. To change it, pass the payload field with the same name to the accept tool. An omitted field keeps the suggested value.

• Another interval: pass refresh_interval_seconds.
• Full instead of incremental, or the reverse: pass refresh_mode.
• Other keys:
  1. Read the view's query with view-get.
  2. Call view-check-incremental with that query, plus the incremental_key and unique_key the user wants. Without keys it lists key_candidates and unique_key_candidates.
  3. Accept with those keys only when the check returns eligible: true. Otherwise explain the blockers to the user.
• Late rows: pass lookback_seconds so each refresh reads that far back again.

PostHog proposes incremental only when it is certain of the keys. A full suggestion can still refresh incrementally if the user chooses keys that pass the check. The unique key must include every column the query groups by.

4. Accept or dismiss

Accept with warehouse-suggestions-accept-prepare, then warehouse-suggestions-accept-execute after the user types confirm. The server checks every choice against the view's current query:

• A 400 names the field it refused and why. Show the reason to the user and ask what to change.
• A 409 means the view changed after the suggestion was made, so the suggested keys no longer apply. Offer a full refresh, or keys checked with view-check-incremental.

Dismiss with warehouse-suggestions-dismiss and a reason (not_useful, not_now or other). warehouse-suggestions-resume brings a dismissed suggestion back.