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 computed often enough that storing its result saves compute.
  • A view that is not stored is computed on every read, and again on every refresh of each materialized view that reads it. recomputed_by_views counts those materialized views. When it is above 0, the saving comes mostly from their refreshes, not from people’s reads.
  • saves_seconds_per_month and saves_bytes_per_month are what those computations 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), consumer_refresh (the materialized views that read it refresh 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 read this view in the window.
  • When materialized is true, it still refreshes. refresh_seconds_per_month and refresh_bytes_per_month are that cost. When materialized is false, it is a plain view and costs nothing to keep. It only adds clutter.
  • Accepting marks it deprecated in the data catalog. For a materialized view, it also stops the refreshes and deletes the 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.
  • To delete the view instead, pass delete_view: true. Ask the user first, because a deleted view cannot be restored. A 409 means another view now reads from it, or it now feeds person properties, account properties, a destination or a workflow, or it now reads a direct-connection source. Explain the reason to the user and do not retry. A 400 on delete_view means some days in the read window have no read data. Offer to mark it deprecated instead.

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.

5. Hand it to someone else

A suggestion’s assignee is the person who should decide it. When a view’s suggestion is first shown, PostHog assigns it to the person who created the view, if they can accept it. To see one person’s suggestions, pass assignee_id to warehouse-suggestions-list.

When the user is not the right person to decide, assign it with warehouse-suggestions-assign. The user needs edit access to the view or table, the same as for dismissing it (can_act). The assignee gets a notification. They need edit access to the view or table, and to the data catalog for certify and deprecate suggestions. A 400 means they do not have it. Tell the user and ask who else should get it.