/syncing-migrated-self-managed-tables

name: syncing-migrated-self-managed-tables
description: >
  Turn on syncing for warehouse tables that PostHog moved from self-managed tables to managed sources (Amazon S3,
  Google Cloud Storage, Azure Blob, Cloudflare R2 or File upload). Use when the user asks "what happened to my
  self-managed tables", "how do I sync my migrated bucket tables", "why does my table say it is waiting for its first
  sync", "will I be billed for these tables", or when a migrated table fails its first sync with access denied, "No
  files are available for table", or "PostHog did not replace the self-managed table".

Syncing migrated self-managed tables

PostHog no longer links tables that read a bucket directly. Eligible existing self-managed tables now have a managed source and a schema that is turned off. The table keeps working and keeps its name. It costs nothing until someone turns syncing on. Turning syncing on starts billing, so the user must agree first.

What happened

• PostHog made one source for each bucket and key prefix. Tables that need different access rules get different

sources.

• Each table got one schema with `should_sync` set to `false`. The schema reads the same files that the table read.
• The table keeps its id and name. Queries, views and insights that use it keep working.
• Until the first sync, ClickHouse still reads the user's bucket on every query. There is no charge for this.
• The first successful sync "adopts" the table. PostHog copies the data into its own storage and points the same table

row at the copy. After that, the table is a normal imported table and is billed like one.

• Source descriptions show the origin: "Self-managed tables in <bucket>" for bucket sources and "Uploaded file for

<name>" for file upload sources.

Available tools

| Tool | Purpose | | ------------------------------------------------- | ------------------------------------------------------------ | | `external-data-sources-list` | Find the migrated sources by description | | `external-data-schemas-list` | Find schemas with `pending_self_managed_table` set to `true` | | `external-data-schemas-retrieve` | Read one schema: sync config, status, `latest_error` | | `external-data-schemas-partial-update` | Turn syncing on, and set `sync_type` and `sync_frequency` | | `external-data-sources-bulk-update-schemas` | Do the same for many schemas of one source in one request | | `external-data-schemas-incremental-fields-create` | See which sync methods the table supports | | `external-data-schemas-reload` | Start a sync now, only if none started on its own | | `external-data-schemas-cancel` | Stop a sync that is running |

Workflow

Step 1 - Find the tables

Call `external-data-schemas-list`. A table that waits for its first sync has `pending_self_managed_table: true`. You can also look for sources with `external-data-sources-list` and read their descriptions.

A table that is still listed as self-managed in the warehouse tables was not migrated. See "Tables that were not migrated".

Step 2 - Ask the user before you change anything

Turning syncing on starts billing. Rows that a sync imports count toward data warehouse usage, like any other import. Tell the user which tables you plan to sync, and how often. Wait for a clear yes. Do not turn syncing on for tables the user did not name.

A table that the user does not want to import can stay as it is. It keeps reading the bucket for free.

Step 3 - Pick the sync method

Call `external-data-schemas-incremental-fields-create` for the schema to see what the table supports.

• `full_refresh` is the default and the safe choice. Each sync reads all files again and replaces the table.
• `incremental` and `append` for file tables use the file modification time (`_file_modified_at`). A sync reads only

files that changed since the last sync.

• `incremental` replaces the rows of a file that was rewritten and drops the rows of a file that was deleted. Use it

when a writer updates existing files.

• `append` only adds rows. A rewritten file is imported again on top of its old rows, so the table keeps stale rows

and duplicates. Use `append` only when every file is written once and never changed.

• An `incremental` Delta table reads only the data files that changed since the last sync. When the writer deletes,

updates or rewrites data, the sync replaces the rows of the removed files and reads the new files. It reads the whole table again only when the table was made again (a new Delta table id), its version went back, or the stored version can no longer be read.

• A table that a writer replaces on each run (for example a dlt `replace` write) is always read in full. Use

`full_refresh` for it.

Do not change `sync_type` on a table that is already running. Wait for the sync to end, or cancel it first.

Step 4 - Turn syncing on

For one schema, call `external-data-schemas-partial-update` with only the fields that change:

{ "should_sync": true, "sync_type": "full_refresh", "sync_frequency": "24hour" }

For `incremental` or `append`, also send the cursor. Migrated schemas have no stored cursor, so the update is rejected without it. Take `field` and `field_type` from the `incremental_fields` entry that `external-data-schemas-incremental-fields-create` returned (`_file_modified_at` for file tables, `_delta_version` for Delta tables):

{
  "should_sync": true,
  "sync_type": "incremental",
  "incremental_field": "<field>",
  "incremental_field_type": "<field_type>",
  "sync_frequency": "24hour"
}

For many schemas of one source, use `external-data-sources-bulk-update-schemas` with one entry per sch