Kater CLI Make and compare sync

kater sync

Updates a connection's dataset files from the tables and columns in your warehouse.

First it writes one DB dataset file for each table, then it updates the Kater dataset over each DB dataset. Run it when tables or columns change in the warehouse. See DB datasets and Kater datasets.

Commands

Command	What it does
`kater sync status`	Show when each dataset sync last succeeded.

Examples

# Go to a connection's folder in your local copy of Kater content
cd connections/snowflake_prod_connection
# See what a sync would change, and save nothing
kater sync --scope modeling --dry-run
# Update the DB datasets, then the Kater datasets
kater sync --scope modeling
# Update only the google_ads__campaign_report DB dataset and the Kater dataset over it
kater sync --scope modeling --select source:google_ads__campaign_report
# Update every table but that one
kater sync --scope modeling --exclude source:google_ads__campaign_report
# Update only the DB dataset files
kater sync --scope modeling --resource-type db_dataset
# Update only the Kater datasets, from the DB dataset files already saved
kater sync --scope modeling --resource-type kater_dataset
# Update only the Kater dataset google_ads__campaign_report
kater sync --scope modeling --resource-type kater_dataset --select google_ads__campaign_report
# Add a table that no DB dataset declares yet
kater sync --scope modeling --sources DEMO_DB.ANALYTICS_AD_REPORTING.TIKTOK_ADS__CAMPAIGN_REPORT
# Delete the files of tables that are gone from the warehouse
kater sync --scope modeling --prune
# Let sync keep google_ads__campaign_report up to date
kater sync --scope modeling --adopt google_ads__campaign_report
# See when each step last synced on this computer
kater sync status --scope modeling

Options

Option	Description
`-s`, `--select <selection>`	The datasets to sync, as dbt selects them. Give a DB dataset by its name, `source:NAME`, a path, a tag, or a Kater dataset by its name, which syncs the DB datasets that it reads. Without it, sync reads every table that `connection.yaml` selects. See Selecting models. You can give it more than one time.
`--exclude <selection>`	The datasets to leave out, in the same words as `--select`. You can give it more than one time.
`--state <state>`	What `state:modified` and `state:new` compare with: a folder that holds an earlier `manifest.json`, or `git:REF`, such as `git:main`.
`--resource-type <type>`	Runs only one step. `db_dataset` updates the DB dataset files from the warehouse. `kater_dataset` updates the Kater datasets over them from the saved files, and does not read the warehouse. Without it, sync runs both steps, in that order. You can give it more than one time.
`--request-file <file>`	A JSON file that holds the whole request. Give `-` to read it from standard input. The command then ignores its other sync options.
`--dry-run`	Shows what a sync would change, and saves nothing. With `--json`, it also gives each file’s new SQL, and the revisions that `--revisions-file` takes.
`--check`	Checks whether anything is out of date, and saves nothing. It exits `1` when a sync would change a file, or cannot sync. It cannot go with `--dry-run`.
`--sources <database.schema.table>`	A warehouse table that no DB dataset declares yet, such as `DEMO_DB.ANALYTICS_AD_REPORTING.TIKTOK_ADS__CAMPAIGN_REPORT`. It must be a table that `connection.yaml` selects. Kater reads the name as your warehouse does. Put a name with SQL quotes in single quotes for your shell. For a table that has a DB dataset, use `--select`. You can give it more than one time.
`--revisions-file <file>`	A JSON file with the `revisions` object of a `--dry-run --json` that you reviewed. It holds `db_datasets` and `kater_datasets`. Sync then saves only when no file changed after that dry run.
`--rename-map <file>`	A JSON file that lists the tables or columns that the warehouse renamed, for the Kater datasets step. The datasets that read them then follow the new names. See Renamed tables and columns.
`--adopt <dataset>`	A Kater dataset that sync did not make, for sync to keep up to date from now on. Give its file’s name in `modeling/kater_objects/datasets/`, such as `google_ads__campaign_report`, or its path. Everything written in it stays. You can give it more than one time.
`--incomplete-metadata`	Tells the Kater datasets step that the DB dataset files may be incomplete. That step then adds and removes no fields, and saves no dataset. Sync the DB datasets, then run it again without this option.
`--prune`	Deletes the DB dataset files of tables that the warehouse no longer has, or that `connection.yaml` no longer selects. It deletes only the files that sync wrote or took over. The Kater datasets that sync made from tables `connection.yaml` no longer selects go too, when nobody changed them and nothing reads them. Without it, the first step stops at those files. It cannot go with `--check`.
`--no-profile`	Skips profiling new columns, so sync reads no rows. Those columns stay unmarked, and a later sync profiles them. It cannot go with `--reprofile`, `--dry-run` or `--check`.
`--reprofile`	Profiles every column again, not only the new ones. It cannot go with `--no-profile`, `--dry-run` or `--check`.

It also takes the global options --json, --kater-root, --scope and --help.

The two steps

DB datasets. Sync reads the tables and columns of the databases and schemas that connection.yaml selects. It writes one DB dataset file for each table, and profiles each new column one time.
Kater datasets. Sync reads those saved files, not the warehouse. It creates or updates the standard Kater dataset over each DB dataset, and keeps what you wrote on it. It leaves custom SQL datasets as they are.

--resource-type db_dataset or --resource-type kater_dataset runs one step alone. Run the first step before the second.

How the two steps run

One lock. Both steps run under one lock on the connection, so no other sync of it runs between them.
The second step waits. The Kater datasets step runs only when the DB datasets step saved its changes, or had none to save.
Saved changes stay. When the DB datasets step saves and the Kater datasets step cannot sync, the DB dataset changes stay saved.
A dry run plans both. A dry run plans the second step against the files that the first step would write.
A removed schema’s Kater datasets. With --prune, the second step deletes each Kater dataset that sync made from a table connection.yaml no longer selects. It keeps one that a person changed, or that a semantic model or a workspace reads, and the sync stops on it with the file to change or delete.

Which tables it syncs

Every table. Without --select, sync reads every table that connection.yaml selects.
Some tables. --select and --exclude take the words that Selecting models lists. A DB dataset by its name, source:NAME, a path or a tag selects those DB datasets. A Kater dataset by its name selects the DB datasets that it reads.
A new table. A table that no DB dataset declares yet has nothing for --select to name. Give it with --sources, as DATABASE.SCHEMA.TABLE. The next sync can select it by name.

Which connection it syncs

One connection’s modeling folder. Name it with --scope, such as --scope connections/snowflake_prod_connection/modeling.
Without --scope. Sync uses your saved scope, then the folder you are in. That must be a connection’s modeling folder, or a folder in it.
In the Files terminal. A sync that names no --scope works on the connection that the terminal’s folder is in. Any folder in that connection works.

Review, check or save

Save. With neither --dry-run nor --check, sync saves the changes.
Review. --dry-run shows the changes, and saves nothing.
Check. --check exits 1 when something is out of date, and saves nothing.
All or nothing. Each step saves all of its changes together, or none of them.
What it says. Each step says what changed, or would change, in each file. When something stops a step, it says why, and which options to run it again with.
For agents. --json gives the whole answer as JSON.

Review before you save

Run the sync with --dry-run --json, and review the changes.
Save the answer’s revisions object in a file.
Run the sync with --revisions-file and that file. When any file changed after the dry run, sync saves nothing and asks for a new plan.

What sync reads and writes

Only the list of tables and columns. Sync never calls GET_DDL, and it never changes connection.yaml.
Only sampled rows. The only rows that it reads are samples of new columns, to profile them. --no-profile skips them.
Its own record. It keeps a record of the files that it writes in the connection’s .kater/dataset-sync/ folder. Keep that folder with the connection. Never delete it to force a sync.
In a local copy. Sync changes the files on your computer. Commit them and run kater git push to publish them.

What you need

A sign-in. Sync reads your warehouse through Kater, so run kater login first.
The permission. Sync needs the settings:manage permission. Without it, ask an admin to run sync, or to give your role that permission.
One sync at a time. When another sync of the same connection runs, sync stops. Try again when it finishes.
A clean start. When an earlier sync stopped partway, sync stops and names the record that it left. Check the files that it lists, delete the record, then sync again.

The DB dataset files

One file for each table. The path is modeling/db_objects/datasets/DATABASE/SCHEMA/TABLE.sql, such as modeling/db_objects/datasets/DEMO_DB/ANALYTICS_AD_REPORTING/GOOGLE_ADS__CAMPAIGN_REPORT.sql.
The DDL and a header. Each file holds the table’s CREATE TABLE statement. Its first line says that sync writes the DDL and the column marks.
Your edits stay. The table’s label and description are yours to edit, and sync keeps them. Put field labels and other additions on the Kater dataset.
The name follows the table. The dataset’s name follows the warehouse table, so sync renames it when the table changes. Kater’s rename changes a semantic model or a Kater dataset and every place that names it. A database dataset does not rename that way.

Files that sync did not write

A file can be at a table’s path before sync writes it, such as one that kater new db-dataset made.

The warehouse has the table. Sync takes the file over and lists it under Adopted. It writes the DDL from the warehouse, profiles the columns that have no mark, and keeps the name, label and description.
The warehouse does not have the table, or connection.yaml does not select it. Sync keeps the file and gives a warning. It never deletes the file, with --prune or without.

Sync keeps a record of the files that it wrote or took over in .kater/dataset-sync/db-baseline.json. After sync takes a file over, --prune deletes it when its table is gone.

How it profiles columns

New columns, one time. Sync samples up to 100,000 rows, and marks each new column @categorical or @continuous.
Unique columns. A column with one row for each value, and no empty values, is also marked @unique.
Kept results. Sync keeps each result in .kater/dataset-sync/, so a sync that finds no change writes the same bytes.
Type changes. When a column’s type changes to another kind, such as from text to a number, sync profiles it again.
Only when it saves. A dry run or a check does not profile, so it reads no rows.
When a sample fails. Sync saves the columns unmarked, and the next sync tries again.

What sync keeps on Kater datasets

Only its own datasets. Sync changes only the datasets that it made or that you adopted. It adds and removes their base columns as the table changes.
Your work stays. Sync keeps the fields, labels and measures that you added, and a field name that you changed.
New tables. A new table gets a dataset in modeling/kater_objects/datasets/, named for the table in lower case. When another dataset has that name, sync stops, and asks you to rename one of them.
Custom SQL datasets. Sync skips them, and says so.

When sync asks you to adopt

A dataset written by hand. A dataset that you wrote over a table stops that table’s sync. Adopt it with --adopt, and sync keeps it up to date from then on.
A dataset that sync keeps. Sync also asks you to adopt a dataset again after a column changed type, or after you edited a base field.
What adopting keeps. Everything written in the dataset stays.

When a table goes away

When the DB dataset file is gone, sync stops at its dataset. Sync the DB datasets to bring the file back. When the table is gone for good, delete the dataset yourself.
When the DB dataset file and the dataset’s own file are both gone, sync stops keeping that dataset.

Renamed tables and columns

When the warehouse renames a column, sync sees one column removed and one added. When it finds a possible rename, it stops and prints the map that it needs. Save the map in a file, and give it with --rename-map. This file says that the column CTR of GOOGLE_ADS__CAMPAIGN_REPORT is now CLICK_THROUGH_RATE:

[{"source": {"database": "DEMO_DB", "schema": "ANALYTICS_AD_REPORTING", "table": "GOOGLE_ADS__CAMPAIGN_REPORT"}, "target": {"database": "DEMO_DB", "schema": "ANALYTICS_AD_REPORTING", "table": "GOOGLE_ADS__CAMPAIGN_REPORT"}, "columns": [{"before": "CTR", "after": "CLICK_THROUGH_RATE"}]}]

For a renamed table, target names the new table.
A rename moves only a dataset that sync keeps. Adopt a dataset before you rename its table.
Each table can be renamed one time in a sync.
Sync refuses a table rename while the old table is still in the warehouse.

When sync stops

Sync saves nothing, and says why, when:

a table is gone from the warehouse, or connection.yaml no longer selects it, and you did not give --prune. This applies only to a file that sync wrote or took over.
a file in modeling/db_objects/ is not at the path above. Sync asks you to move it.
a file in modeling/db_objects/ holds anything other than one DB dataset.
a new table’s name is the name of another dataset in the connection. Rename that dataset, then sync again.
a file would not compile after the sync.
--sources names a table that connection.yaml does not select.
a DB dataset file has unsaved changes in your editor.