Metadata-Version: 2.5
Name: dataplat
Version: 0.18.8
Summary: One command to manage any shape of data platform: databases, ingestion, BI, cloud, and CI.
Project-URL: Homepage, https://github.com/hanslemm/dataplat
Project-URL: Repository, https://github.com/hanslemm/dataplat
Project-URL: Issues, https://github.com/hanslemm/dataplat/issues
Project-URL: Changelog, https://github.com/hanslemm/dataplat/blob/main/CHANGELOG.md
Author: Hans Lemm
License-Expression: MIT
License-File: LICENSE
Keywords: airbyte,aws,cli,data-platform,dbt,duckdb,postgres,redshift,superset
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: Operating System :: MacOS :: MacOS X
Classifier: Operating System :: POSIX :: Linux
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: System :: Systems Administration
Classifier: Topic :: Utilities
Requires-Python: >=3.12
Requires-Dist: rich>=14.2.0
Requires-Dist: typer>=0.21.1
Provides-Extra: all
Requires-Dist: boto3>=1.35.0; extra == 'all'
Requires-Dist: croniter>=2.0.0; extra == 'all'
Requires-Dist: duckdb>=1.5.5; extra == 'all'
Requires-Dist: httpx>=0.25.0; extra == 'all'
Requires-Dist: plotext>=5.3.2; extra == 'all'
Requires-Dist: psycopg[binary]>=3.2.9; extra == 'all'
Requires-Dist: pyyaml>=6.0.1; extra == 'all'
Requires-Dist: textual>=0.64.0; extra == 'all'
Provides-Extra: bi
Requires-Dist: httpx>=0.25.0; extra == 'bi'
Requires-Dist: psycopg[binary]>=3.2.9; extra == 'bi'
Provides-Extra: cloud
Requires-Dist: boto3>=1.35.0; extra == 'cloud'
Requires-Dist: plotext>=5.3.2; extra == 'cloud'
Provides-Extra: db
Requires-Dist: psycopg[binary]>=3.2.9; extra == 'db'
Provides-Extra: dbt
Requires-Dist: psycopg[binary]>=3.2.9; extra == 'dbt'
Requires-Dist: pyyaml>=6.0.1; extra == 'dbt'
Provides-Extra: duckdb
Requires-Dist: duckdb>=1.5.5; extra == 'duckdb'
Provides-Extra: ingest
Requires-Dist: croniter>=2.0.0; extra == 'ingest'
Requires-Dist: httpx>=0.25.0; extra == 'ingest'
Requires-Dist: textual>=0.64.0; extra == 'ingest'
Provides-Extra: people
Requires-Dist: httpx>=0.25.0; extra == 'people'
Requires-Dist: psycopg[binary]>=3.2.9; extra == 'people'
Description-Content-Type: text/markdown

# dataplat

**One command to manage any shape of data platform.**

`dataplat` ships a single CLI, `dp`, with an area for each type of component
in a data platform: warehouses/databases, ingestion, BI, cloud, and CI. Point
it at *your* stack through environment variables — nothing about your
infrastructure is hardcoded.

```text
dp
├── status                 # one-shot health overview (--json, --no-aws)
├── open                   # airbyte | superset | rds [id] | redshift | secrets [name]
├── config                 # init | show | doctor [--connect]
├── db                     # warehouses & databases (Postgres, Redshift, DuckDB)
│   ├── query              # ad-hoc SQL (--format table|csv|json, --write guard)
│   ├── describe           # schema/table/view report (--json)
│   ├── long-queries       # triage per target (--history, --json)      [1]
│   ├── kill               # cancel/terminate queries by PID            [1]
│   ├── role               # list | show | create | grant | drop        [1]
│   ├── schema             # list | create | drop | grant | revoke | alter [2]
│   │                      # impact — what outside the DB depends on it
│   ├── top-tables         # rank big tables (--drop-sql, --drop)
│   └── dbt-orphans        # deprecated — moved to `dp dbt orphans`     [1]
├── dbt                    # named dbt projects (DP_DBT_PROJECTS)
│   └── orphans            # scan/rename | revert | purge (--older-than) [1]
│                          # -p/--project, -t/--target — see "dbt projects"
├── ingest                 # data ingestion
│   └── airbyte
│       ├── connections    # list | get | create | update | set-cursor
│       │                  # sync | refresh | reset | delete
│       ├── jobs           # list | get | cancel
│       ├── sources        # list | get | create | update | delete
│       ├── destinations   # list | get | create | update | delete
│       ├── definitions    # list-sources | list-destinations
│       ├── workspaces     # list | get
│       ├── tags           # list | create
│       └── templates      # source | destination | connection
├── bi                     # business intelligence
│   └── superset
│       ├── users          # list | create | update | delete | set-password
│       ├── roles          # list
│       ├── groups         # list
│       ├── dashboards     # list | colors | compare | datasets            [3]
│       │                  # duplicate | usage
│       └── dataset        # port | ports — verified expression ports       [4]
├── people                 # access across every area at once
│   ├── onboard            # create accounts everywhere, copying a colleague
│   └── offboard           # disable everywhere; drops nothing
├── cloud                  # cloud providers
│   └── aws
│       ├── secrets        # list | get | compare | set | edit | rename-key
│       │                  # describe | versions | rollback | delete | restore
│       ├── rds            # metrics | plot | list
│       └── redshift       # metrics
└── ci                     # build infrastructure
    └── github
        └── runner         # start | stop | status
```

[1] Cannot apply to a `duckdb` target: an in-process, single-user database has no
roles, no other sessions, and no rename that survives a dependent view. See
[Engines](#engines) for the matrix and the reason per command.

[2] `list`, `create` and `drop` work on every engine; `grant`, `revoke` and
`alter` need a server — DuckDB has no `GRANT` statement and does not implement
`ALTER SCHEMA`.

[3] `duplicate` copies a dashboard and repoints the copy's charts onto another
database's datasets, creating what is missing. It never deletes anything.
`compare` asks of two dashboards that already exist whether the copy still says
what the original says, chart by chart; it writes nothing and repairs nothing.
`colors` carries across the series colours the original can be *shown* to use,
and names every one it will not pin rather than guessing. `usage` ranks
dashboards by how many people open them, read from Superset's own log tables —
`--unused` for the ones nobody does.

[4] A port records what a dataset's SQL, metrics and calculated columns become
on another engine, and the measurement that justified it. `duplicate` replays
recorded ports and refuses if one is missing or stale.

## Installation

Requires Python 3.12 or newer, on Linux or macOS. Windows is untested and parts
of it will not work: `dp config init` creates a symlink, the dependency
self-install re-execs the process, and `dp ci github runner` drives `docker`.

```bash
uv tool install "dataplat[all]"     # recommended: everything
# or
pipx install "dataplat[all]"
# or
pip install "dataplat[all]"
```

Each area's dependencies are an optional extra, so you can also install
only what your platform uses:

| Extra | Enables | Pulls in |
| --- | --- | --- |
| `db` | `dp db` | psycopg |
| `duckdb` | `duckdb` targets inside `dp db` | duckdb |
| `ingest` | `dp ingest` | httpx, textual, croniter |
| `bi` | `dp bi` | httpx, psycopg (via `db`) |
| `cloud` | `dp cloud` | boto3, plotext |
| `all` | everything | all of the above |

`bi` includes `db` because Superset is a front-end for a database and this area
manages both ends of that. `dp bi superset dashboards usage` ranks dashboards by
who opens them, which it reads from Superset's own log table — a table in a
warehouse, not something the API exposes; `dashboards list --usage` joins that
same table; and `dataset port` grades a rewrite by running it against the source
and target warehouses. Installing `bi` alone and discovering that halfway
through a migration is worse than carrying a driver you might not use.

`duckdb` is the one extra that is an *engine* rather than an area, so it is
deliberately **not** part of `db` — and that is the opposite call from the one
above, for a reason. DuckDB is an *alternative* to PostgreSQL, never needed
alongside it by the same target, and the extension module alone is three times
psycopg's size: someone whose warehouse is PostgreSQL should not carry an
embedded database engine to run `dp db describe`. `dataplat[db,duckdb]` is what
a DuckDB target needs; `dataplat[all]` includes both.

A bare `pip install dataplat` gives you the core (`status`, `open`,
`config`) with every other area stubbed. You don't have to plan this in
advance: `dp` knows which areas your configuration enables and installs
what's missing on demand — see below.

If your warehouse *is* a DuckDB file, read [Engines](#engines) before you
install: `dp db query`, `dp db describe` and `dp db top-tables` work against it,
and the other four `dp db` commands cannot — DuckDB is in-process and
single-user, so there is nothing for them to act on.

## Auto-installing dependencies

Two ways, both of which detect whether `dp` runs from a uv tool, pipx, or
plain-venv install and use the matching installer:

```bash
dp config sync            # detect enabled areas, install missing deps (confirms)
dp config sync --check    # report only; exit 1 if something is missing (CI-friendly)
```

Or just use a command: if your config enables an area whose extra is
missing, `dp db query ...` shows exactly what it will run, asks, installs,
and re-runs your original command. Non-interactive sessions never install
silently — they print the command and exit instead. `dp config doctor`
also reports per-area dependency status.

Either path only ever *adds* to your install: the command is pinned to the
`dataplat` version you already run, so installing an extra never upgrades the
tool underneath you, and it carries your existing extras along — `duckdb`
included — so adding `db` cannot drop an `ingest` you had, and no self-install
can drop the DuckDB driver.

Carrying `duckdb` along matters more than the rest, because nothing would ever
put it back. It is the one dependency outside this machinery: it belongs to an
*engine*, and both paths above plan per *area*. So it is never installed for you
and never offered mid-command — you are already talking to a database by then. A
DuckDB target whose driver is missing stops at exit 3 with the extra named:

```text
Error: A duckdb target needs the duckdb package, which is not installed: it is
the 'duckdb' extra (dataplat[duckdb]). Run: …
```

The `Run:` tail is the command for the environment `dp` runs from — uv tool,
pipx, or the venv's pip — pinned to the version you already have, so it adds the
driver without upgrading the tool underneath you.

## Shell completion

```bash
dp --install-completion    # detect the shell, write the script, hook it up
dp --show-completion       # print it instead, and install it yourself
```

Completion takes effect in the next shell. bash, zsh and fish are supported;
`--show-completion` is the escape hatch when your rc file is managed by
something else (Nix, chezmoi, a dotfiles repo) or when shell detection fails.

`dp <TAB>` is answered from the area names alone and imports nothing.
Completing *inside* an area has to import it, because the subcommands it owes
the shell are that area's own — so the first `dp db <TAB>` pays for psycopg, and
an area whose extra is not installed completes to nothing rather than offering
to install it mid-keystroke.

## Quick start

1. Declare your database targets — any names you like:

   ```bash
   # ~/.envrc (or any env mechanism you prefer)
   export DP_TARGETS="warehouse,lake,local"
   export DP_DEFAULT_TARGET="warehouse"

   export WAREHOUSE_ENGINE=postgresql
   export WAREHOUSE_HOST=db.example.com
   export WAREHOUSE_DATABASE=analytics
   export WAREHOUSE_USER=me
   export WAREHOUSE_PASSWORD=…

   export LAKE_ENGINE=redshift
   export LAKE_HOST=lake.abc123.eu-central-1.redshift-serverless.amazonaws.com
   export LAKE_DATABASE=dev
   export LAKE_USER=me
   export LAKE_PASSWORD=…
   export LAKE_REASSIGN_OWNER=admin      # role drop reassigns owned objects here

   export LOCAL_ENGINE=duckdb
   export LOCAL_PATH=~/data/warehouse.duckdb   # a file, not a server; or :memory:
   ```

2. Optionally link that file globally and check your setup:

   ```bash
   dp config init --envrc ~/.envrc   # set the global link
   dp config show                    # which .envrc is active, what's set
   dp config doctor --connect        # validate config; probe live systems
   ```

3. Go:

   ```bash
   dp status
   dp db query 'SELECT 1'
   dp db query -t lake 'SELECT 1'
   dp db query -t local 'SELECT 1'
   ```

## Environment loading

`dp` loads variables from `.envrc` on startup without overriding values
already set in your shell. Lookup order:

1. `DP_ENVRC_PATH`
2. `~/.config/dataplat/.envrc` (the global link — set it with `dp config init`)
3. `.envrc` in the current directory
4. the `.envrc` beside a development checkout of dataplat itself

Candidate 3 makes every command sensitive to where you run it: standing in a
cloned repo points `dp` at whatever host and credentials that repo's `.envrc`
exports. `dp config show` and `dp config doctor` always name the active file
*and* which candidate produced it, and warn when it came from the current
directory. Set `DP_ENVRC_ALLOW_CWD=0` to drop that candidate entirely and
rely only on the global link you chose.

## Engines

`dp db` speaks three engines, and they are not three sizes of the same database.
PostgreSQL and Redshift are servers reached over the PostgreSQL wire protocol,
with users, sessions and an ACL system. DuckDB is a database **file**, opened
inside the `dp` process: no host, no port, no password, no TLS, and no users at
all — every connection is the same implicit user, `duckdb`.

| Engine | `<NAME>_ENGINE` | Reached through | Needs |
| --- | --- | --- | --- |
| PostgreSQL | `postgresql` (the default) | host/port/user/password over libpq | `dataplat[db]` |
| Redshift | `redshift` | the same, port 5439 by default | `dataplat[db]` |
| DuckDB | `duckdb` | a database file, in this process | `dataplat[db,duckdb]` |

### What each command can do

| `dp db` command | PostgreSQL | Redshift | DuckDB | Why not, on DuckDB |
| --- | :-: | :-: | :-: | --- |
| `query` | ✓ | ✓ | ✓ | — |
| `describe` | ✓ | ✓ | ✓ | — |
| `top-tables` | ✓ | ✓ | ✓ | works, but ranks by estimated rows and shows no sizes — [see below](#duckdb-top-tables-sizes-are-estimates) |
| `schema list` / `create` / `drop` | ✓ | ✓ ³ | ✓ ⁴ | — |
| `schema grant` / `revoke` | ✓ | ✓ | ✗ | it has no `GRANT` statement at all — the keyword does not parse, because there are no users or roles to grant anything to |
| `schema impact` | ✓ | ✓ | ✓ | it opens no database connection: the answer comes from Superset and Airbyte |
| `schema alter` | ✓ | ✓ ⁵ | ✗ | it does not implement `ALTER SCHEMA` ("Altering schemas is not yet supported"), and has neither owners nor quotas to alter |
| `role list` / `show` / `create` / `grant` / `drop` | ✓ | ✓ ¹ | ✗ | it has no users or roles at all — `pg_roles`, `pg_authid` and `pg_user` do not exist, and every connection is the same implicit user, `duckdb` |
| `long-queries` | ✓ | ✓ | ✗ | it runs inside this process and has no `pg_stat_activity`: there are no other sessions to inspect |
| `kill` | ✓ | ✓ | ✗ | the same — there is no other session to cancel |
| `dbt orphans` ⁶ | ✓ | ✓ ² | ✗ | it quarantines an orphan by renaming it, and `ALTER TABLE … RENAME TO` fails with a `DependencyException` whenever a view depends on the table, which in a dbt project is the normal case. DuckDB has no `CASCADE` |

¹ `dp db role show` reports `Password set: unknown` on Redshift: there is no
`pg_authid`, and `pg_user.passwd` is masked to `'********'` for every row, so
the question cannot be answered rather than answered wrongly.
² `dp dbt orphans` does not consider materialized views on Redshift: there is
no `pg_matviews` catalog listing them.
³ `dp db schema list` adds Used/Quota columns on Redshift, the only engine with
schema quotas. `svv_schema_quota_state` is version-dependent, so an unavailable
view renders every quota as `?` rather than failing the listing.
⁴ `dp db schema list` reports `duckdb` as every schema's owner — there is no
`pg_roles` and every connection is the same implicit user — and
`--include-system` reveals nothing extra, because DuckDB keeps its catalog
schemas out of `pg_namespace` entirely. `dp db schema create` works but rejects
`--owner`: `CREATE SCHEMA ... AUTHORIZATION` does not parse there.
⁵ `dp db schema alter --quota` is Redshift-only, and so is `create --quota`. Off
Redshift the flag warns and is skipped when there is other work to do, and is an
error when it is the only change requested — a silent skip there would report
success having done nothing.
⁶ Moved out of `dp db` into its own area — see [dbt projects](#dbt-projects).
`dp db dbt-orphans` still works exactly as before (same subcommands, same
flags); both `dp db --help` and its own `--help` mark it **(deprecated)** with
a pointer to `dp dbt orphans`, and running it prints a `DeprecationWarning` to
stderr, where it cannot corrupt `--json` or other scripted output.

A refused command **exits 2** — "a combination of arguments that cannot work",
the same code as an unknown flag or an unknown target — and says which engine
and why:

```console
$ dp db role list -t local
Error: dp db role list cannot run against DuckDB: it has no users or roles at
all — pg_roles, pg_authid and pg_user do not exist, and every connection is the
same implicit user, 'duckdb'. That is what DuckDB is, not a missing dataplat
feature.
```

It never says "not implemented", because none of these is a gap waiting for a
release. A single-user in-process database has no roles to grant, no concurrent
sessions to triage and nobody else's query to cancel; `dbt-orphans` is refused
because its one mechanism is a rename DuckDB rejects, and a destructive command
that half works is worse than none. If you need role management or query
triage, that is a reason to put a server behind the data, not to wait for a
flag.

A mixed configuration keeps working. `dp db long-queries` runs across every
target by default, so there the refusal is *per target*: the servers still
report, each DuckDB target says why it cannot on **stderr** — where it cannot
corrupt `--json` — and the run is not counted as a failure, because nothing
failed. Only when every target in scope is a DuckDB one is the refusal the whole
answer, and then it exits 2.

Where a command *does* run but cannot answer every part of its report, it names
what it left out. `dp db describe` against a DuckDB target ends with a
**Not applicable on DuckDB** section listing privileges, default privileges,
size and materialized views, each with the reason — because a section that is
simply missing reads as "nothing is configured" when what it means is "this
engine has no such concept".

### DuckDB configuration

```bash
export DP_TARGETS="warehouse,local"
export LOCAL_ENGINE=duckdb
export LOCAL_PATH=~/data/warehouse.duckdb   # or :memory:
export LOCAL_READ_ONLY=1                    # optional
```

| Variable | Purpose |
| --- | --- |
| `<NAME>_ENGINE=duckdb` | Makes this target a database file instead of a server. |
| `<NAME>_PATH` | The database file. `~` is expanded; a relative path is resolved against the directory you run `dp` in, exactly as DuckDB would. `:memory:` opens an ephemeral in-memory database instead. |
| `<NAME>_DATABASE` | Accepted as a fallback for `_PATH`, because every other engine takes a database *name* from it and that is what you will reach for first. `_PATH` wins if both are set. |
| `<NAME>_READ_ONLY` | Truthy ⇒ open the file read-only. DuckDB enforces it itself, so it is a guard and not a hint: a write fails with `Cannot execute statement of type "CREATE" on database … attached in read-only mode!`, and — like any statement the engine rejects — exits 1 rather than 5, because retrying it cannot help. |

`--database`/`-d` is the flag spelling of the path and beats both variables;
there is no `--path`, because every db command already has `-d`.

Five behaviours worth knowing before you configure one:

- **Server settings are refused, not ignored.** `<NAME>_HOST`, `_PORT`,
  `_USER`, `_PASSWORD` and `_SSLMODE` — and the matching flags — stop a DuckDB
  target at exit 3 naming the offender, because a target carrying both a host
  and a path is a configuration that is wrong in one of two ways, and guessing
  which half you meant is how a query ends up running against the wrong
  database. The mirror is refused too: `<NAME>_PATH` on a `postgresql` target.
  Only this target's own `<NAME>_*` variables count, so a stray `PGHOST` left
  in your shell does not break a DuckDB target.
- **dataplat never creates the database file.** `duckdb.connect()` would, and
  for a read-mostly tool that is the wrong default: a mistyped path would become
  an empty database, and `dp db describe` would then report — truthfully — that
  your warehouse contains nothing. A missing path is exit 3, naming the path.
- **`--verbose` names the file, and whether it was opened read-only:**
  `[dp:sql] connect /data/warehouse.duckdb engine=duckdb read-only`.
- **`dp status` includes DuckDB targets**, and says what it could not check
  rather than leaving a blank: `✓ local — reachable; long-running queries not
  applicable on DuckDB — it runs inside this process and has no
  pg_stat_activity …`.
- **`dp config show` and `dp config doctor` are not DuckDB-aware yet.** They
  list and check the libpq variables for every target, so a correct DuckDB
  target shows `_HOST`/`_USER`/`_PASSWORD` as `unset`, `doctor` reports them as
  missing, and neither mentions `_PATH` or `_READ_ONLY`. Ignore that for DuckDB
  targets — and do not set `_HOST` to silence it, since that is exactly what a
  DuckDB target refuses.

### DuckDB top-tables sizes are estimates

`dp db top-tables` works against DuckDB, but its numbers do not mean what the
same columns mean on a server. PostgreSQL ranks by bytes from
`pg_total_relation_size()` (heap + indexes + toast); Redshift by
`svv_table_info.size`. DuckDB has neither `pg_total_relation_size()` nor
`pg_database_size()`, and no catalog column carrying per-table bytes at all. So
on a DuckDB target the report is a different report:

- rows are ranked by `duckdb_tables().estimated_size` — DuckDB's row-count
  **estimate**, not bytes — and the section header says so: `ranked by estimated
  rows`, with the column headed `Rows (est.)`;
- the `Size` and `% of disk` columns are **not shown**, rather than shown full of
  `—`: a Size column of dashes under a `0 B (0.0% of disk)` footer would read as
  a dataplat defect instead of as the engine's answer. The keys stay in `--json`
  with `size_bytes: null` and `matched_bytes: null`, so a script sees the gap
  explicitly rather than a field that vanished;
- the one real byte figure is the whole database file, from
  `pragma_database_size()`. It is printed as `Database file:` and deliberately
  not used as the denominator of a percentage: it covers every schema in the
  file, including free blocks, so nothing above it divides into it;
- the section prints, in one line, exactly where its numbers came from, and
  `--json` carries the same thing per target as `ranked_by` and `size_basis` —
  so two targets on different engines in one report cannot be misread as
  comparable.

Use it to find the big tables inside one DuckDB file. Do not compare a DuckDB
ranking with a PostgreSQL one, and do not add the two engines' figures together.

One thing to know before `--drop`/`--drop-sql` there, which the emitted script
also says: DuckDB does **not** block `DROP TABLE` on a dependent view — it
leaves the view broken — while a foreign-key child does block it, and there is no
`CASCADE`.

## dbt projects

`dp dbt` is the area for dbt-aware commands. Today it has one: `orphans`
(scan/rename, `revert`, `purge --older-than`) — the same command that used to
live at `dp db dbt-orphans`, moved here so it can be scoped to a *named*
project instead of the single project a whole installation used to share.

```bash
export DP_DBT_PROJECTS="acme,acme_qa"
export DP_DBT_DEFAULT_PROJECT="acme"       # used when --project is omitted

export ACME_DBT_PATH=~/repos/acme/dbt      # dir with dbt_project.yml
export ACME_DBT_TARGETS="warehouse,lake"   # DP_TARGETS names it builds into
# export ACME_DBT_PROFILES_DIR=...         # optional, defaults to _DBT_PATH
# export ACME_DBT_NAME=...                 # optional, defaults to dbt_project.yml's name:

export ACME_QA_DBT_PATH=~/repos/acme/dbt
export ACME_QA_DBT_TARGETS="qa"

dp dbt orphans                     # scans ACME (the default project)
dp dbt orphans -p acme_qa          # scans the named project instead
dp dbt orphans -p all              # every configured project, each with its own settings
dp dbt orphans -p acme -t lake     # narrow to one of the project's own targets
```

`<NAME>` above is the **project** name, not a target name — `DP_DBT_PROJECTS`
is its own registry, parallel to `DP_TARGETS` but one level up: a project
declares *which* targets (already-configured `DP_TARGETS` names) it builds
into, and `-p`/`--project` picks the project the way `-t`/`--target` picks a
target within it. `-t all` is accepted under a configured project exactly as
it always was on the legacy path below — identical to omitting `-t`.

**No `DP_DBT_PROJECTS` at all** falls back to the pre-project shape this
command has always had: `DP_DBT_PROJECT` (a bare dbt project name, not a
path) plus `DP_TARGETS`/`--target` directly. An installation that has not
adopted named projects keeps working unchanged. `DP_DBT_INVOCATION_COMMAND`
and `DP_DBT_ORPHANS_EXCLUDE_SCHEMAS` are read on this path; once a project is
configured, `<NAME>_DBT_INVOCATION_COMMAND` / `<NAME>_DBT_ORPHANS_EXCLUDE_SCHEMAS`
take precedence per project, falling back to the same two legacy variables
when a project does not set its own.

Three things this command refuses to guess at, because it renames and later
drops what it finds:

- **Two projects sharing a warehouse in one invocation.** Each project's live
  dbt-model set is scoped to itself, so scanning the same target once per
  project would see the *other* project's live tables as its own orphans —
  under `-p all --no-dry-run` that is one project's production tables
  quarantined as another's garbage. Caught by target name, and (best-effort)
  by matching host/port/database across differently-named targets on one
  cluster. Run the overlapping projects one at a time (`--project <name>`)
  instead.
- **An old-format audit log or rename-age record that cannot be attributed to
  one target.** A log written before named projects existed recorded only the
  engine family (`postgres`/`redshift`), not which target produced it;
  `revert` and `purge --older-than` still read it, but refuse when more than
  one configured target shares that engine — the log cannot tell them apart.
  Rerun scoped to one target with `--target` to make it unambiguous.
- **`revert` with no `--log` picks the newest log on disk**, and if none of
  its entries belong to any target in this invocation, it refuses rather than
  reporting a hollow "Reverted 0 object(s)" success — the log is very likely
  the wrong one (a different project's run), not proof there was nothing to
  revert. The same reasoning covers a log with no renames recorded at all: an
  auto-picked empty log could just as easily be the wrong log as a clean
  scan, so `revert` run unconditionally straight after a scan — a runbook, a
  CI step — now exits non-zero on a run that found nothing, where it used to
  exit 0. Pass `--log` explicitly to confirm which log you mean, which is
  also what keeps that kind of run non-interactive; an explicitly passed log
  that matches nothing, or has nothing recorded, is your own call and is
  left alone.

A project with a compiled manifest (`target/manifest.json`, from `dbt compile`
or `dbt docs generate`) gets one more thing: a relation the manifest still
claims to produce is spared even if it has not rebuilt inside `--window-days`,
and so is a partition child of a produced parent. A manifest that cannot be
read at all, or that reports zero produced relations, refuses outright —
diffing against nothing would flag everything already in scope as an orphan.
This changes what `--window-days` means, but does not remove it from the
picture: a relation built inside the window is still always spared, and the
window still decides which schemas get scanned and which builds count as
live — so a smaller window still means more rename candidates. What changes
is that the window stops being the *only* way to be spared: a relation the
manifest still claims to produce survives even if it has not rebuilt inside
the window. On the legacy no-project path there is no manifest, so the
window stays the sole criterion, exactly as before.

The scan assumes it is the only dbt project writing into the schemas it
scans — it has no way to attribute an existing table to a *different* dbt
project that also happens to write into scope, so two dbt projects sharing
schemas outside of a declared `DP_DBT_PROJECTS` overlap (see above) is a
known, undetected hazard, not a case that raises.

## CI: GitHub runners

`dp ci github runner` runs a self-hosted GitHub Actions runner in Docker,
authenticated with a GitHub App (`GHA_APP_ID`, `GHA_APP_PRIVATE_KEY`). It can
register against one repository or a whole organization:

```bash
# repository-level: serves this repository only
dp ci github runner start -n my-runner -r https://github.com/my-org/repo
# organization-level: serves every repository its runner group allows
dp ci github runner start -n munin -o my-org -g my-group
dp ci github runner status -n munin
dp ci github runner stop -n munin
```

The scope is inferred from `--repo-url` / `--org`. Repository runners need the
App's repository permission "Administration: Read and write" on that
repository; organization runners need the organization permission "Self-hosted
runners: Read and write", accepted on the installation by an org owner. The
private key reaches docker through the process environment, never argv.

## Configuration reference

| Variable | Purpose |
| --- | --- |
| `DP_ENVRC_PATH` | Explicit `.envrc` to load, ahead of every other candidate. |
| `DP_ENVRC_ALLOW_CWD` | Set to `0` to stop picking up `.envrc` from the current directory. |
| `DP_VERBOSE` | Set to `1` to trace every statement and request to stderr, for a whole session — same switch as `--verbose`. |
| `DP_TARGETS` | Comma-separated DB target names (e.g. `warehouse,lake`). |
| `DP_DEFAULT_TARGET` | Target used when `--target` is omitted (default: first of `DP_TARGETS`). |
| `<NAME>_ENGINE` | `postgresql` (default), `redshift` or `duckdb`, per target. |
| `<NAME>_HOST/_PORT/_USER/_PASSWORD/_DATABASE/_SSLMODE` | Connection settings, per target. Server-only: all but `_DATABASE` are refused on a `duckdb` target — see [Engines](#engines). |
| `<NAME>_PATH` | DuckDB only: the database file, or `:memory:`. `<NAME>_DATABASE` is accepted as a fallback. |
| `<NAME>_READ_ONLY` | DuckDB only: truthy ⇒ open the database file read-only. |
| `<NAME>_REASSIGN_OWNER` | Default owner for `dp db role drop` ownership transfer. |
| `AIRBYTE_BASE_URL` + `AIRBYTE_CLIENT_ID`/`AIRBYTE_CLIENT_SECRET` (cloud) or `AIRBYTE_EMAIL`/`AIRBYTE_PASSWORD` (OSS) | Airbyte API access. |
| `SUPERSET_BASE_URL`, `SUPERSET_ADMIN_USERNAME`, `SUPERSET_ADMIN_PASSWORD` | Superset API access. |
| `<NAME>_USERNAME_TEMPLATE` | Username convention for `dp people` on this target, e.g. `ex_{first_initial}{last}`. **A target without one gets no accounts** — which is how an SSO-managed warehouse opts out. |
| `SUPERSET_USERNAME_TEMPLATE` | The same for Superset. Defaults to `{local}` (the email name), since that is what Superset usernames almost always are. |
| `DP_SUPERSET_USAGE_TARGET` | Name of the `DP_TARGETS` entry holding Superset's `logs` table — its metadata database, or a replica. Required by `dashboards usage` and `dashboards list --by-usage`. |
| `DP_SUPERSET_USAGE_LOGS_TABLE` | Where that target keeps Superset's action log. Default `public.logs`, which is Superset's own name for it. |
| `DP_SUPERSET_USAGE_USERS_TABLE` | The same for the user table. Default `public.ab_user`. |
| `DP_SUPERSET_USAGE_EXCLUDE_USERS` | Comma-separated usernames never counted as viewers. Usually unnecessary — service traffic is excluded by its log *action* — but useful where automation runs as a real account. |
| `DP_SUPERSET_PORTS_DIR` | Version-controlled directory holding port files for `dataset port`, `dataset ports` and `duplicate`. Required by all three; unset or unreadable is a configuration error. |
| `DP_SUPERSET_PORT_MODEL_CMD` | Argument vector `dataset port --suggest` runs for an expression no rule covers, split with `shlex` and executed directly (never a shell). Unset means `claude -p`; set but empty means there is no model to ask, and `--suggest` refuses. |
| `DP_SUPERSET_PORT_MODEL_TIMEOUT` | Seconds allowed for **one** such call — `--max-attempts` multiplies it rather than dividing it. Default `300`. A call that hits the bound is refused, not retried. |
| `DP_AWS_PROFILE` | Default AWS profile for `dp cloud aws` commands. |
| `DP_AWS_PROFILE_ALIASES` | Short aliases, e.g. `prod=AdminAccess-Prod,qa=AdminAccess-QA`. |
| `DP_AWS_REGION` | Default AWS region (falls back to `AWS_REGION`, then the profile). |
| `DP_RDS_INSTANCE` | Default RDS instance for `dp cloud aws rds` / `dp status`. |
| `DP_DBT_PROJECTS` | Comma-separated named dbt project names for `dp dbt`, e.g. `acme,acme_qa`. Unset ⇒ the legacy single-project shape below. |
| `DP_DBT_DEFAULT_PROJECT` | Project used when `--project` is omitted (default: first of `DP_DBT_PROJECTS`). |
| `<NAME>_DBT_PATH` | Per **project**: the dbt project directory (must contain `dbt_project.yml`). Required for a project to exist. |
| `<NAME>_DBT_PROFILES_DIR` | Per project: profiles directory. Defaults to `<NAME>_DBT_PATH`. |
| `<NAME>_DBT_NAME` | Per project: dbt project name. Defaults to the `name:` in its `dbt_project.yml`. |
| `<NAME>_DBT_TARGETS` | Per project: comma-separated `DP_TARGETS` names it builds into. |
| `DP_DBT_PROJECT` | **Legacy**, no-project-configured fallback only: a bare dbt project name (not a path) for `dp dbt orphans`. Ignored once `DP_DBT_PROJECTS` is set. |
| `DP_DBT_INVOCATION_COMMAND` | Optional filter on dbt_artifacts invocations. Per-project override: `<NAME>_DBT_INVOCATION_COMMAND`. |
| `DP_DBT_ORPHANS_EXCLUDE_SCHEMAS` | Comma-separated schemas to skip (default `raw,_raw,dbt_artifacts`). Per-project override: `<NAME>_DBT_ORPHANS_EXCLUDE_SCHEMAS`. |
| `GHA_APP_ID`, `GHA_APP_PRIVATE_KEY` | GitHub App creds for `dp ci github runner`. |
| `DP_CI_RUNNER_DNS` | Comma-separated DNS servers for the runner container. |

## Conventions

- **`-t/--target`** — named DB target from `DP_TARGETS`. Multi-target
  commands accept `all`. Sets the engine and env prefix in one flag;
  `--engine` / `--env-prefix` remain as overrides.
- **`--json`** — every read command can emit machine-readable output.
- **`--yes/-y`** — every destructive or bulk-mutating command confirms first;
  pass `--yes` in scripts. Bulk mutators also support `--dry-run`.
- **`--limit/-n`** — row caps share one spelling everywhere.
- **Secrets stay off argv** — prefer `--value-stdin` / hidden prompts; values
  are never echoed back.
- **`--verbose`** — a root flag: show what the tool actually sent, on stderr.

### Exit codes

Exit codes are a contract, not an implementation detail — a wrapper script
branches on them long after it has stopped reading our output:

| Code | Meaning | Retry? |
| --- | --- | --- |
| `0` | Success. | — |
| `1` | Unexpected or not-yet-classified failure. Also a declined confirmation: "no" is not an error, but it is not "done" either. | No — you don't know what happened. |
| `2` | Invalid input: an unknown flag or target, a value that cannot be parsed, a combination of arguments that cannot work. | No — the command itself is wrong. |
| `3` | Configuration problem: missing connection settings, an unknown engine, an unset `AIRBYTE_BASE_URL` or `DP_DBT_PROJECT`. | No — a human has to fix the config. |
| `4` | Authentication failure: credentials rejected, a login endpoint that would not authenticate, `aws sso login` failed. | No — a new credential is needed. |
| `5` | External service failure: a call to Airbyte, Superset or AWS failed, timed out or returned something unusable; a warehouse that refused the operation. | **Yes** — the only class where a retry can help. |

`0`, `1` and `2` keep their conventional meanings. `2` is Click's own code for a
usage error, which is why invalid input shares it: `dp db query --format nope`
(Click's complaint) and `-t nosuchtarget` (ours) are one condition to the
caller — "you passed something I cannot use" — and splitting them by who noticed
would be a distinction with no use.

The point of the codes above `2` is that `5` is the one worth retrying, and `3`
and `4` are the ones you must never retry: no amount of sleeping and trying
again creates a missing config file or repairs a rejected password. Cap the
retries anyway — `5` means "the other end failed", which covers a warehouse
that was restarting *and* a `DROP` the server refused because something still
depends on it, and only the first of those gets better on its own.

```bash
dp db long-queries -t warehouse --json > queries.json
case $? in
  0) ;;
  5) echo "service unavailable; will retry" >&2; exit 75 ;;   # EX_TEMPFAIL
  *) echo "not retryable; fix and re-run" >&2; exit 1 ;;
esac
```

Treat `1` as "unknown", never as "retryable": it is the code for a failure
dataplat has not classified, so retrying it is a guess.

### Verbose tracing

`--verbose` (or `DP_VERBOSE=1`) answers the one question logs cannot: what did
`dp` actually send?

```bash
dp --verbose db query 'SELECT 1'              # root flag, before the subcommand
DP_VERBOSE=1 dp db long-queries 2> trace.log  # or for a whole session
dp --verbose db describe public 2>&1 >/dev/null | grep '\[dp:sql\]'
```

Every line is prefixed with its category — `[dp:sql]` or `[dp:http]` — and
collapsed onto one line, so the output greps cleanly:

```text
[dp:sql] connect me@db.example.com:5432/analytics engine=postgresql
[dp:sql] SELECT 1 FROM pg_namespace WHERE nspname = %s | 1 params bound
[dp:http] GET https://api.airbyte.com/v1/jobs?limit=20
[dp:http] GET https://api.airbyte.com/v1/jobs?limit=20 -> 200 143.8ms
```

SQL is traced *before* the statement runs, which is the point: the trace you
need is the one for the query that never came back, and a line written afterwards
would never be written at all. That is also why there is no duration on it — use
`dp db long-queries` for how long. HTTP gets two lines for the same reason, one
on the way out and one on the response; a line with no `-> status` partner *is*
the signal that a request hung, was refused, or never connected.

**It writes to stderr and never to stdout**, so `--json` and `--format csv` stay
machine-readable with tracing on. Piping into `jq` is still valid, and
`2>/dev/null` drops the trace without touching the data:

```bash
dp --verbose db query --format json 'SELECT 1' 2>/dev/null | jq
```

**Secrets are never traced.** Every message is redacted on the way out —
passwords (including the SQL `PASSWORD '…'` literal that role creation sends),
tokens, API keys, `Authorization` headers and credentials embedded in a URL all
become `***`. Parameter values, result rows and response bodies are not traced
at all: they are your warehouse's data, and a trace that scrolls the answer past
you has hidden the request it exists to show.

## Examples

### Daily overview

```bash
dp status                  # DBs, Airbyte jobs (24h), runners, RDS at a glance
dp open superset           # jump to a web UI
```

### DB query

```bash
dp db query 'SELECT 1'                         # default target
dp db query -t lake 'SELECT 1'                 # named target
dp db query --format csv -n 0 'SELECT ...' > out.csv
echo 'SELECT 1' | dp db query
dp db query --write 'UPDATE t SET x = 1'       # writes need --write or a confirm
dp db query -t local 'SELECT * FROM duckdb_tables()'   # DuckDB target
```

Your SQL is sent as you wrote it, so write it in the target's own dialect —
DuckDB's catalogs (`duckdb_tables()`, `pragma_database_size()`) on a `duckdb`
target, `pg_*` on a server. DuckDB does provide `pg_catalog` compatibility
views, so simple `pg_class` / `information_schema` queries work on all three.

### Long queries and kill

```bash
dp db long-queries                       # all targets: running + recent failures
dp db long-queries -t warehouse --history    # pg_stat_statements aggregate
dp db kill 12345 -t warehouse            # terminate a backend (confirms first)
```

### DB roles

```bash
dp db role list --users-only
dp db role show alice -t lake --json
dp db role create svc_reporting --table-select reporting --databases analytics
dp db role create readers --no-login --table-select reporting   # passwordless group role
dp db role create readers --no-login --grant-to alice,bob
dp db role grant --roles readers,analyst --to alice,bob --dry-run
dp db role grant --roles analyst --to newhire --create-missing-users
dp db role drop old_user --all-databases --dry-run
```

`list`, `create`, `grant`, and `drop` work against both Postgres and Redshift
targets — and against no DuckDB target, which has no users to manage at all (see
[Engines](#engines)). `create` makes login roles with generated passwords by
default; `--no-login` creates a passwordless group-style role instead. `drop`
transfers owned objects to the target's `<NAME>_REASSIGN_OWNER` before
`DROP USER`.

`grant` is for the day after `create`: the role already exists and someone new
needs it. It takes the cross product of `--roles` and `--to`, so two roles and
three people is one invocation. It validates the whole plan before executing any
of it, reports grants already in effect instead of re-issuing them, and refuses
combinations the engine cannot express — a Redshift group holds login users
only, and there is no `GRANT ROLE ... TO GROUP` form — rather than letting those
surface as a raw SQL error partway through. On Redshift a name can be a user
*and* a group *and* a role at once; `--kind` / `--to-kind` disambiguate, and an
ambiguous name is refused rather than guessed.

`--create-missing-users` creates any `--to` name that does not exist yet as a
login user, writing generated passwords to the same CSV `create` uses
(`~/.config/dataplat/credentials/`, mode `0600`). Creates and grants share one
transaction, so a failed grant leaves no half-onboarded user behind and the
command is safe to re-run.

### Schemas

```bash
dp db schema list                        # schemas with owner and object counts
dp db schema list --like 'dev_*'         # glob `*` or SQL `%`; `_` is literal
dp db schema list --include-system       # add pg_catalog, information_schema, …
dp db schema create analytics --owner svc_etl --quota 50GB
dp db schema drop dev_old --cascade --dry-run
dp db schema grant --schemas analytics --to readers --privileges read
dp db schema grant --grant readers:read --grant etl:readwrite --schemas analytics
dp db schema revoke --schemas analytics --from contractor --privileges all --cascade
dp db schema alter analytics --owner svc_new --quota UNLIMITED
dp db schema alter dev_a --rename-to dev_b
```

`list`, `create` and `drop` work on all three engines. `grant`, `revoke` and
`alter` need a server: DuckDB has no `GRANT` statement at all and does not
implement `ALTER SCHEMA` (see [Engines](#engines)). What differs on `list` is
where the answer comes from: PostgreSQL resolves the owner through `pg_roles`,
Redshift through `pg_user`, and DuckDB has no `pg_roles` at all, so it reports its
single implicit user, `duckdb`.

`--like` treats `*` and `%` as wildcards and `_` as a literal character. That
last part matters: in SQL `LIKE`, `_` matches *any* single character, so an
unescaped `dev_*` also selects `devops_prod`. Harmless on a listing, not harmless
on `schema drop --like`, so the underscore is escaped and the statement declares
`ESCAPE '#'`.

**Privileges.** `--privileges` takes any of `usage`, `create`, `all`, `select`,
`insert`, `update`, `delete`, `table-all`, `sequence-usage`, `function-execute`,
`default-select`, `default-all`, or the presets `read` and `readwrite`. Use
`--grant grantee:privileges` when two grantees need different things in one
invocation. `PUBLIC` is a valid grantee here — unlike `dp db role grant`, where
role membership cannot be granted to it.

Three behaviours worth knowing:

- **Any table-level privilege implies `usage`** on the containing schema, because
  an object cannot be reached without it.
- **`default-*` privileges require a grantor.** `ALTER DEFAULT PRIVILEGES`
  without `FOR ROLE`/`FOR USER` binds to whoever is connected, so tables later
  created by dbt or the schema owner inherit nothing — the most common way
  default privileges silently fail. Each schema's own owner is used by default;
  `--default-for` overrides it.
- **Grants already in effect are reported, not re-issued**, so re-running
  converges and the plan shows only what changes.

**Destructive paths.** `drop` prints owner and object counts *before* the
confirmation, so `--cascade`'s blast radius is visible rather than implied, and
`RESTRICT` is emitted explicitly rather than left to the server default. `drop`
and `alter` both refuse `public`, `main`, `information_schema`, `catalog_history`
and anything `pg_*` — including via `--like`, which is re-checked after matching.

Redshift adds schema quotas, shown as two extra columns when the cluster reports
them. An unknown quota renders as `?`, never `0` — `svv_schema_quota_state` is
version-dependent, and "nobody could tell" is not "no limit".

Two engine differences worth knowing:

- DuckDB's `main` is its *default* schema, the analogue of PostgreSQL's `public`,
  so it is listed rather than hidden. A database whose tables all live in `main`
  would otherwise list as empty.
- DuckDB never exposes `information_schema` or `pg_catalog` through
  `pg_namespace` — it flags them `internal` — so `--include-system` has nothing
  extra to show there.

### Cleanup

```bash
dp db top-tables --schema-prefix dev_ -n 30
dp db top-tables --drop-sql > review.sql       # emit a script
dp dbt orphans                                 # dry-run scan (default)
dp dbt orphans --no-dry-run                    # apply renames (confirms)
dp dbt orphans purge --older-than 7 --no-dry-run
dp dbt orphans revert                          # undo from the audit log
```

### AWS secrets

```bash
dp cloud aws secrets list --prefix /kubernetes
dp cloud aws secrets get /my/secret --key password
dp cloud aws secrets compare /my/secret -p prod -p qa
echo -n "hunter2" | dp cloud aws secrets set my/secret --value-stdin -p qa
dp cloud aws secrets versions my/secret
dp cloud aws secrets rollback my/secret        # AWSCURRENT -> AWSPREVIOUS
```

All writes show their targets and confirm (or `--yes`).

### AWS monitoring

```bash
dp cloud aws rds metrics --json
dp cloud aws rds plot -m cpu -m connections --hours 12
dp cloud aws redshift metrics -w my-workgroup
```

### Before dropping a schema

`schema drop` shows what a schema contains. It cannot show what *elsewhere*
depends on it, which is the half that surprises people:

```bash
dp db schema impact legacy
```

Superset datasets are matched two ways, and the second is the one that matters:
a virtual dataset can name a schema only inside its SQL, where no schema filter
in Superset will ever show it. On a mature instance the datasets that name a
schema only in their SQL **outnumber the ones whose `schema` field says so,
several times over** — so the field alone can call a schema unused while most
of what would break is invisible to it. Airbyte destinations landing in the
schema are reported with the connections that write through them.

Add `--json` to feed it to something else. Both systems are optional: with only
one configured, you still get that half of the answer.

`dp db schema drop` runs the same check itself, before the confirmation and
before a `--dry-run` exits, so the answer arrives at the moment it is needed
rather than only when someone thinks to ask:

```text
Depends on this:
  raw: 12 Superset dataset(s), 1 Airbyte destination(s), 4 connection(s)
       writing into it — `dp db schema impact raw`
```

It is advisory and never fatal — a Superset outage produces a note, not a
blocked drop — and `--no-impact` skips it.

### Moving a dashboard to another warehouse

Retiring a warehouse means moving what reads from it. `dp` can find that:

```bash
dp bi superset dashboards list --database "Postgres Prod"
```

and, per dashboard, say how much of it can move at all:

```bash
dp bi superset dashboards datasets 42 --to-database "Redshift Prod"
```

`duplicate` then copies the dashboard and points the copy's charts at the other
database's datasets:

```bash
dp bi superset dashboards duplicate 42 \
    --from-database "Postgres Prod" --to-database "Redshift Prod"
```

Nothing is written until every dataset resolves — one run reports every
problem rather than one problem per run — and `--dry-run` writes nothing at
all. A dataset with no counterpart is created, with its metrics and calculated
columns copied across: a chart names its metrics as strings, so a dataset
without them is one the chart cannot render even though its id is correct.

A dataset on neither the source nor the target connection is reported and left
alone. A dashboard can read from several databases, and repointing the ones
nobody was migrating would be a change nobody asked for.

Where the two sides disagree on a name, `--map` is repeatable and takes
`schema.table=schema.table`:

```bash
dp bi superset dashboards duplicate 42 \
    --from-database "Postgres Prod" --to-database "Redshift Prod" \
    --map public.orders=analytics.orders
```

An entry naming a dataset this dashboard does not read is an error, not a
no-op. A typo that is quietly ignored gives you a clean exit and a migration
that did not do what you asked, which is the worst of both.

`datasets` takes the same `--from-database` on its own, which answers the
question a retirement actually starts with — which of this dashboard's
datasets still live on the warehouse you are trying to switch off:

```bash
dp bi superset dashboards datasets 42 --from-database "Postgres Prod"
```

**Virtual datasets carry SQL, and Redshift is not Postgres.** Before creating
one, `dp` runs its SQL against the target and reports the engine's own error if
it will not run. It also scans for constructs known to behave *differently
without erroring* — a bare `::numeric` that truncates on Redshift, and a
`concat()` that propagates NULL where Postgres ignores it. That list is
vendored from the dbt migration's own evidence and is advisory: it reports,
it never blocks.

That check fetches **no rows**, and it says so with a predicate — `WHERE 1=0`
— rather than a `LIMIT`. The difference is not stylistic. SQL Lab substitutes
its own row limit for whatever the statement carries, so `LIMIT 0` meant `dp`
asked for nothing and Superset asked for up to `DEFAULT_SQLLAB_LIMIT` rows on
its behalf; on one wide dataset that was enough to exhaust the web pod's
memory and take both replicas down while the statement itself cost 0.42s on
the warehouse. A false predicate survives anything the far side does to the
clauses around it. The same shape is what grading's probes use.

**A chart can also carry its own SQL**, in an ad-hoc metric or an ad-hoc
filter written into the chart rather than into any dataset. Those travel with
the chart, so copying a dataset's SQL, metrics and calculated columns does
nothing for them — while a Postgres `FILTER (WHERE ...)` in one fails on
Redshift exactly as it would in a dataset metric. `duplicate` runs the same
deterministic rules over them. What a rule ports is rewritten in the copy's
charts and the rule names itself; what no rule covers is reported as **carried
across unverified**, with any construct-scan findings beside it, and copied
exactly as it stands. Those are the only two outcomes: an expression this
cannot port is never copied silently.

A rule is only applied here if what it *produces* is legal here. Superset
allows a sub-query in a dataset's SQL and refuses one in a chart's own
(`ADHOC_SUBQUERY_NOT_ALLOWED_ERROR`, issue 1037, measured live), so a rewrite
that introduced one would replace a chart that fails with the engine's own
message — naming the operator it does not have — with one that fails about the
shape of the SQL and sends the reader looking in the wrong place. Such a
rewrite is declined and the expression joins the carried-across-unverified
list with that reason.

It is never a refusal either. A chart's ad-hoc expression has no stable
identity to key a port file on, so there is nothing an operator could go and
author to unblock the run — a refusal would be unanswerable, which is worse
than a copy that names what went across unmeasured. Both lists print before
the confirmation prompt and under `--dry-run`, so the decision to proceed is
made knowing which charts carry SQL that will not survive the move, and
`--json` carries the same split under `chart_adhoc`.

Only the charts actually being repointed are read this way — a chart left
pointing at the source database goes on running against the engine its SQL was
written for — and only an entry whose own `expressionType` is `SQL` counts as
an expression. A saved ad-hoc filter records a `filterOptionName` containing
the literal word "filter" on very nearly every chart there is, so searching a
chart's text for `FILTER` matches almost all of them and means nothing.

`--compare` is the check that settles it. Because `duplicate` copies rather
than moves, both dashboards are live at once, so each chart's query can be run
on both and the results diffed:

```bash
dp bi superset dashboards duplicate 42 \
    --from-database "Postgres Prod" --to-database "Redshift Prod" --compare
```

Rows are ordered in Python, never with an `ORDER BY`: the two engines disagree
on text collation, and Redshift ignores trailing blanks where Postgres does
not, so sorting on the engines would invent differences. A disagreement exits
`1`, not `5` — `5` is the retryable code, and a dashboard whose numbers differ
will not agree because something tried again.

The report names *what* disagrees, not just that something does — a differing
cell count and any columns present on one side only. That is the case worth
designing for: a cast that silently rounds returns the same number of rows as
before, so a row count alone would call it agreement.

`--compare` needs `--verify`, which is on by default; asking for `--compare
--no-verify` says so rather than quietly doing nothing. Charts it cannot pair
— two charts sharing a name *and* a dataset are indistinguishable once cloned
— are reported as not compared rather than compared against the wrong
original, and the summary line says how many of the charts were actually
checked. Being unable to check something is not the same as checking it and
finding it sound, and the report keeps those apart.

The original dashboard, its charts and its datasets are never modified, and
nothing is ever deleted. If a later step fails, the copy and any created
datasets stay, and their ids are printed.

**A creation that goes unanswered is settled by looking, not by guessing.** A
read timeout on a POST is not a failed POST: the request was accepted, and
what ran out was `dp`'s patience for the reply rather than the work behind it.
Measured — the POST that ended a live migration had in fact succeeded. So
`Failed to reach … the request was accepted but no response arrived` is
exactly true and no use at all. Everything that identifies the dataset — the
target database, the schema, the table name — is settled before that POST is
sent and none of it is lost with the answer, so `dp` goes and looks.

What it can conclude is deliberately lopsided, and the asymmetry is measured
too. Checked a minute or two after that timeout, the dataset **listing** did
not show the dataset; a read by id minutes later found it. Superset was still
finishing the work, and the catalogue lagged the object it had already begun.

- **Present is confirmable.** The dataset is there and carries the statement
  this run sent, so that call created it. The run says so, names the id it
  learned from the catalogue rather than from the answer, and *carries on* —
  which is the point, because the two calls after a creation are what give a
  dataset its columns and metrics.
- **Absent is not.** "Not in the listing" is evidence of nothing, so `dp`
  looks again — seven times over three minutes — and if it still cannot see
  it, reports `UNKNOWN`. It never says the call created nothing, and it never
  tells you to re-run on the strength of an empty listing.
- **Nor is a name held by something else.** A dataset found at the right name
  carrying a statement this run never sent is never adopted as its own: the
  call after a creation replaces a dataset's columns and metrics wholesale, so
  adopting a stranger's dataset would overwrite it.

The unknown case is named on the `Left in place:` line as well — what this run
created, and beside it what it could not account for, in the one place the
operator already reads. Nothing is ever re-sent: a POST that may already have
been acted on is how one dataset becomes two.

Why "delete it first, then re-run" rather than just "re-run": a run that dies
between creating a dataset and populating it leaves a **shell**. The measured
one carried 204 of its source's 211 columns and 1 of its 102 metrics. A re-run
*matches* a dataset that already exists instead of creating one, so it would
adopt that shell and ship a copy whose charts name a hundred metrics the
target does not define.

**What a run adopts, it says out loud.** A dataset the plan matches is used as
it stands — nothing is copied into it, which is what matching one means — so
before the confirmation prompt `dp` reads each one and reports what it does
not carry of the source's metrics and columns, by name:

```
Adopted as they stand:
  public.orders → dataset 904 (101 of 102 metric(s) and 7 of 211 column(s) the
                               source carries are not defined there)
    metrics: orders_28d, revenue_eur, …
```

Reported, never refused. A target dataset curated by hand may legitimately
carry a different set, and this command's position is already that a dataset
missing its metrics is copied anyway — but never silently. The half-built
remains of an interrupted run are, by name and schema, indistinguishable from
a dataset somebody built on purpose; this is where that difference shows.

**Creating a virtual dataset is a warehouse call wearing Superset's clothes.**
Superset runs the statement against the target to learn what columns it
returns, so the cost scales with the query and not with the payload. That call
is given 300 seconds — the same budget SQL Lab's own execution gets here,
because it is the same warehouse being waited on — while creating a *physical*
dataset, which costs a table reflection and no query of yours, keeps 120. The
evidence is one creation, bracketed on both sides: it outlasted 120 seconds,
and it had finished within the few minutes before the next check found it. 300
sits inside that bracket. The upper bound is loose — it is when somebody
looked, not when Superset finished — and one call on one instance says nothing
about the next.

### Is the copy still saying what the original said?

`duplicate --compare` answers that once, while the copy is being made, and then
never again. The question outlives the migration: a chart gets fixed, a week of
data arrives, somebody is asked to trust a number. `compare` is the same check
standing on its own, against two dashboards that already exist.

```bash
dp bi superset dashboards compare 42 77
dp bi superset dashboards compare 42 77 --chart 118 --tolerance 0.1
dp bi superset dashboards compare 42 77 --limit 20 --stop-after 3
```

It reads and reports. Nothing is written to either dashboard and nothing it
finds is repaired — a comparison that also fixed things could not be trusted to
report honestly about what it fixed.

**A chart's name is not a key.** Measured on a real migrated dashboard: three
distinct charts share one title, character for character. Charts are paired on
what survives a migration and still tells them apart — title, then
visualisation type, then the `schema.table` the chart reads, which the
migration carries across even though the dataset id does not. The dataset is
only looked up for charts the first two facets could not separate, so an
unambiguous dashboard costs no extra request.

What still cannot be told apart is **not paired**. The group is printed with
the candidate ids on both sides and the flag that settles it:

```
4 chart(s) could not be paired at all — name, visualisation and dataset are identical
  Top regions / table / analytics.regions
    original 5, 6 — copy 95, 96
    settle one by hand with --pair 5=95
```

Guessing instead would produce a confident percentage about a chart compared
against the wrong counterpart, which is worse than no number at all.

**Exact equality is the wrong bar**, for the reason the dataset ports already
have a tolerance: two warehouses fed by the same ingestion differ by minutes of
lag. So a chart is graded by the same `Grade` a port is, against the same
`--tolerance` (0.5% by default), and the report gives a **size** rather than a
flag. Measured on a real pair: one chart out by 0.0013% — a single row in
76,732 — and one out by 2.40%, nearly five times the tolerance. "Differs" says
the same thing about both; the percentage does not.

The battery is the port battery — `rows`, `non_null:`, `distinct:`, `min:`,
`max:`, `avg:` — computed over rows already in hand. Having the rows buys one
probe SQL could not: `worst:<column>`, the row where a measure disagrees most,
named by the dimension values that identify it. Rows are matched on those
dimension values, never by position after a sort, because the two engines
disagree on text collation and one extra row would shift every pair after it.
A column present on one side only fails whatever the numbers say: a tolerance
is a statement about numbers, and a missing column is a difference in shape.

**Not measured is not agreement.** There are three outcomes, never two:

| | |
|---|---|
| `agrees` | every probe inside the tolerance |
| `differs` | at least one outside it, with the worst percentage |
| `not measured` | nothing was established either way |

A chart lands in the third when neither it nor its counterpart carries a
`query_context` — Superset builds one in the browser, from per-visualisation
code that is not reproducible here — or when a query it did attempt broke, or
when both sides came back empty, or when `--stop-after` ended the run before
it. The tally keeps them apart and says so out loud, because a run that
measured 7 of 110 charts and reported a 71% pass rate is the failure this
command exists to avoid. A run in which *nothing* was measured exits `1`: exit
`0` is how "we could not check" becomes "they match" in somebody's pipeline.

A stored query context is only half the story, and the half that is usually
missing: Superset's copy endpoint drops `query_context` from every cloned
slice, and a dashboard's chart *listing* never carries the field at all.
Reading each chart in full and borrowing the other side's context — repointed
at this chart's own dataset, exactly as the migration's own verification does
it — is what takes coverage from a handful of charts to most of them. A borrow
that cannot resolve the borrowing chart's dataset is refused rather than run,
because the query it would send is the original's own, and comparing a chart
against itself agrees every time.

**It runs every chart twice, and says what that costs before it spends it.**

```
Cost: 110 pair(s) × 2 queries = 220 chart query(ies), run one at a time.
Nothing is written to either dashboard.
```

Sequential, deliberately: a heavy query fanned out a hundred ways is how this
instance has been knocked over before. Three flags bound it — `--chart` for a
subset, `--limit` for the first N pairs, `--stop-after` to stop once N charts
have differed — and the confirmation comes after the cost line, so `--yes` is
the scriptable path and an unattended run cannot spend two hundred queries by
accident. `--json` carries every verdict, every percentage, the unpaired
charts and the cost, for a pipeline that wants to gate on its own terms.

**What it still cannot tell you.** Agreement here is agreement on today's data
through today's filters, and the report says the parts of that it can see: a
column with no NULLs in it had its NULL handling unverified, and rows that
could not be lined up one to one were compared only by their column totals.
Three things it does not see at all. A difference that leaves every probe
unchanged — two dimension labels swapping their measures exactly — is not
caught. A chart is run with no filters or URL parameters active, so a
divergence that only appears once somebody picks a filter is not covered. And
a truncation spread across many rows shows only as its percentage: `avg:` is
asked, and with it the change-in-kind check, only where both sides returned a
single row, because over a handful of rows a mean landing on a whole number is
ordinary arithmetic rather than a defect.

### Why the copy draws that chart in a different colour

Nothing was dropped. Measured on a real migrated pair: the copy's
`label_colors`, `map_label_colors`, `shared_label_colors` and
`color_scheme_domain` are byte-identical to the original's, and every chart's
`color_scheme` matches. And some charts still render in different colours.

The drift is downstream of the data. A series whose label one of those maps
names is drawn in the colour it names, every time. A series they do **not**
name takes a slot out of the palette in the order its chart happens to meet it
— so its colour is a function of the rows, and two warehouses that disagree
about the rows disagree about the colours. Measured across 18 charts: every one
had at least one series no map named, and 46 labels of 82 were in that state.
The charts that kept their colours are the ones whose labels happen to be
named.

```bash
dp bi superset dashboards colors 42 77            # report only
dp bi superset dashboards colors 42 77 --apply    # write what it can prove
dp bi superset dashboards colors 42 77 --chart 118 --json
```

**A colour is carried only where the original can be shown to use it.** That
means two things at once, and both are required:

1. one of the original's own colour maps names the label — those maps are what
   the instance itself recorded, and
2. the label is in the rows the original's chart returns **today** — a map can
   name a label the dashboard stopped drawing a year ago, and a stale entry is
   not evidence of use.

The second condition is the whole reason this costs a query per chart per side.
Establishing what series a chart draws means running it.

**What it refuses to do is work the colour out.** Which palette slot an unnamed
label would have landed in is decided in the browser, by code that differs
between Superset versions, and there is no way to check a reproduction of it
against the running instance. A pin taken from that guess would be stable,
confident and wrong, and nothing downstream would ever question it — whereas a
colour that visibly drifts makes somebody look. So getting it wrong is worse
than doing nothing, and it is not attempted.

Four refusals, each printed with the chart that draws it:

| | |
|---|---|
| `unprovable` | the original draws it and no map of its own names it |
| `only on the copy` | the original does not draw it, so it has no colour to lend |
| `contested` | two of the original's maps name two different colours |
| `conflict` | the copy already pins it to something else — never overwritten |

A NULL dimension value is disclosed separately. Superset draws one as a series
in its own right and names that series itself, so the label is not knowable
from here; it is reported per chart rather than counted among the labels,
because counting it would mean inventing a name for it.

Series labels themselves are **candidates, not an answer**. Superset composes a
label in the browser — joining several dimensions, appending a metric name —
and this reproduces none of that: it offers the distinct values of a result
set's non-numeric columns and lets the original's own map decide which of them
are real. A candidate it got wrong matches no map either, so it lands in the
refusals. The failure mode of a bad guess here is an over-full report, never a
wrong colour. A chart whose series Superset composes from several dimensions
will therefore report those dimensions' values as unprovable; that is
over-reporting, which is the safe direction.

**Where the pin goes.** Into the copy's `label_colors` — the map a dashboard's
properties editor writes, which is what somebody *asked for*. Not into
`map_label_colors` or `shared_label_colors`: those are written by the dashboard
as it renders and are a record of what was assigned, and a colour written into
a record is one the next render throws away. Everything else in the metadata is
passed through exactly as it was read, and a label the copy already pins is
left alone.

**Reporting is the default; `--apply` is opt-in.** The list of charts at risk is
a deliverable in its own right, and a better one than a plausible guess. When
`--apply` is passed the pins are shown first and confirmed separately from the
query cost, only the copy is ever written to, and the copy's metadata is re-read
immediately before the write so the window in which somebody else's edit could
be clobbered is as narrow as it can be made.

**It does not make the symptom go away, on purpose.** The labels it refuses are
in large part the two warehouses disagreeing about the rows — `only on the
copy` most of all. Pinning those would make the two dashboards look alike while
they said different things, which suppresses a signal that something is wrong
rather than explaining it. They are reported, counted apart from what was
carried, and left exactly as they are. Chasing the data difference is
[`compare`](#is-the-copy-still-saying-what-the-original-said)'s job, and it is
the job worth doing.

**What an operator still has to check by eye.** Every refused label, on the
chart named beside it: those still take their colour from the palette by
encounter order and can drift again whenever the data does. And the pins that
*were* written survive a save of the dashboard only if `label_colors` is the
durable map it is placed in as — the one claim here that could not be measured
against a live instance, and one save of the copy confirms it.

### Verified expression ports

`duplicate` copies a dataset's SQL, its metrics and its calculated columns
onto the target database — but not every engine runs the same SQL. Postgres
`FILTER (WHERE ...)` fails outright on Redshift, which is the easy case: an
error, not a wrong number. `avg(integer)` is the hard one — it returns
`numeric` on Postgres and silently **truncates** to `integer` on Redshift, no
error at all. Only comparing the two engines' output catches that, which is
what a port is for.

A **port** is a file recording what one expression — a dataset's whole SQL, a
metric, or a calculated column — becomes on the target engine, and the
measurement that proved it means the same thing. Ports are meant to be
reviewed like any other change, so they live under `DP_SUPERSET_PORTS_DIR`, a
version-controlled directory, one file per dataset and database pair:

```bash
dp bi superset dataset ports init ~/src/superset-ports
dp bi superset dataset port 42 --from-database "Postgres Prod" --to-database "Redshift Prod" --suggest
dp bi superset dataset ports
```

`ports init` creates the directory, starts a repository in it if nothing
already tracks it, leaves a README explaining what the files are, and prints
the `export DP_SUPERSET_PORTS_DIR=...` line to paste into a shell profile. It
is idempotent, and it never overwrites a README you wrote. There is
deliberately no machine-local default under `~/.config`: a port carries
`approved_by` and `verified_at`, so an unreviewed folder would make that name
decorative and make the same migration come out differently for two operators.
Hence the last thing `ports init` prints when the new repository has no remote
— a local repository gives you history and recoverability, not review and not
sharing.

`port` tries a deterministic **rule** first — `FILTER (WHERE cond)` becomes
`agg(CASE WHEN cond THEN x END)` for every aggregate it recognises, however
deeply the real thing is nested inside a `CASE`. `COUNT(*)` is the exception
the shape forces: `*` is not an expression, so `COUNT(*) FILTER (WHERE cond)`
becomes `COUNT(CASE WHEN cond THEN 1 END)` — the same count, since `COUNT`
skips the NULL the other rows get. Argument forms that cannot legally sit in a
`CASE` branch are refused rather than rewritten into SQL that does not parse:
`SUM(*)` (not valid SQL to begin with), `count(o.*)`, a multi-argument
aggregate like `string_agg(a, ',')`, and an aggregate carrying its own
`ORDER BY`. A refused shape counts as one no rule covers, so `--suggest`
sends it to the model like any other. Only what no rule covers goes
to the model named by `DP_SUPERSET_PORT_MODEL_CMD`, and only when `--suggest`
is passed — a rule is reproducible, free, instant and testable, so it always
runs before anything is asked of a model. Whichever produced a candidate,
nothing is recorded until it is graded: the candidate runs on the target, the
original on the source, and their aggregates are compared within
`--tolerance` percent (default `0.5`).

**A port already recorded and still fresh is reused, not derived again.**
Before a model is asked anything or a probe touches either warehouse, `port`
looks up what `DP_SUPERSET_PORTS_DIR` already holds for this dataset and this
direction and reuses every entry whose recorded hash still matches the live
expression — the same comparison `dataset ports` prints as `fresh` and
`duplicate` applies, so the three commands cannot disagree about what is
current. Measured against a production instance before this existed: a dataset
whose Jinja-templated SQL had a verified, fresh port on disk spent a full model
timeout being asked for that SQL again, failed, and took every metric on the
dataset down with it in a cascade of refusals — all from one question that
never needed asking.

Reuse is decided per expression, never per run: a dataset routinely has a fresh
SQL port beside stale metrics, or the reverse. And it is only ever on a hash
match — never on a name, a dataset id or a near miss. A port is evidence a
human measured a rewrite, and carrying it onto an expression nobody measured
would be a far worse failure than any amount of re-deriving. A port graded
anything but `ok` is not reused either, for the same reason `duplicate` refuses
to apply one.

A recorded port that is present but **stale** is not the same as no port at
all. It is derived again, and the run names it and says why — somebody
verified that rewrite, and replacing it silently is how their work disappears
unnoticed. The closing line separates what was reused from what was newly
derived, and `--json` carries the same split as a `reused` flag per entry.

When every flagged expression already has a fresh port, there is nothing to
record and **the file is left exactly as it is**: a run that measured nothing
must not stamp its own `verified_at` and `approved_by` over somebody else's
evidence, nor turn a no-op into a diff in a directory that exists to be
reviewed. `--re-derive` is how you redo a port on purpose — it ignores the
store entirely, so it also still works when another file in that directory has
stopped parsing.

**A timeout is not a retry.** `--max-attempts` (default `3`) is how many times
one expression may be sent to the model, and the retry is informed: a failure
is fed back into the next prompt, so attempt 2 knows what the engine — or the
tool — said about attempt 1 and can fix it. A **timeout** is the one failure
that carries nothing. The model did not answer badly; it did not answer. So a
timeout ends that expression's model loop on the spot, with a refusal saying
the model did not finish and naming `DP_SUPERSET_PORT_MODEL_TIMEOUT` to raise
— which is the opposite next step from "rewrite this by hand", and used to be
indistinguishable from it. Three attempts at the old 180s bound spent nine
minutes on one expression and learned nothing; one attempt at 300s costs less
and says more. A non-zero exit or unusable output still earns every attempt,
and the expressions beside a timed-out one are still ported.

Two things fail a grade whatever the tolerance says, because neither is a
question about magnitude:

- **Nothing measured is not agreement.** An empty table makes every count
  `0 == 0`, and a metric that is NULL on both sides makes its one probe
  agree with itself — both of which used to read `ok (0.0000%)`. Run
  `dataset port` against a staging table during its truncate-and-reload
  window and the first candidate was recorded as proven. It is now graded
  `UNMEASURED` and refused; re-run when the table has data in it.
- **An average that is a whole number on one side and not on the other.**
  That is what `avg(integer)` truncation looks like, and a relative comparison
  hides it as the average grows: `200.4 → 200` is 0.1996% and `2000.4 → 2000`
  is 0.0200%, both well inside the default. Any average above roughly 80
  concealed its own truncation. The tolerance is not tightened to catch it —
  0.5% was measured from real ingestion lag — the change in *kind* is flagged
  instead. Only averages are asked: `MIN` and `MAX` report a single row's
  value, so one new row carries them across the integer boundary, and counts
  are whole numbers on both sides whatever happens.
- **A metric that is not an aggregate is refused, not graded.** Superset's
  metric field is free SQL and nothing makes it aggregate anything, so a
  metric like `amount * 2` made its probe return one row per table row — and
  the grade compared an arbitrary one of them on each warehouse, which agrees
  or differs by luck. The probe is now capped at two rows and a second row is
  reported as what it is. The cap sits *inside* the probe's subquery, because
  an outer `LIMIT` is the one clause SQL Lab substitutes; nested in a
  subquery it is invisible to that machinery, so at most two rows are ever
  fetched however the far side is configured.

**A dataset's SQL may be a Jinja template, and then only Superset can run it.**
Superset renders a dataset's SQL through Jinja before any warehouse sees it, so
a dataset written like this is not runnable as the SQL `dp` holds:

```sql
select region, sum(amount) as open_amount
from analytics.daily_orders
where 1=1
{% if filter_values('region')|length %}
  and region is not null
{% endif %}
group by region
```

Graded through a driver, the *source* warehouse answered `syntax error at or
near "{"` — it had been handed braces it was never meant to see, and the port
was refused for a reason that had nothing to do with the port. So grading routes
by content: a statement carrying `{{`, `{%` or `{#` is executed through
Superset's SQL Lab, against the `--from-database` and `--to-database`
connections themselves, and everything else keeps the direct path, which is
faster, needs no second login and works where SQL Lab is not permitted. The
probes, the tolerance and the verdict are identical either way — this is a
different way to *execute* a probe, not a different way to judge one.

Braces inside a *string literal* route too. `select '{{ region }}' as label`
looks like a quoted label, and to PostgreSQL it is — but Jinja runs first and
has no concept of a quote, so Superset substitutes it before the warehouse sees
anything. Detection therefore reads the raw text rather than lexing it as SQL:
if Superset will do something to the text, routing through Superset is the
faithful path. One consequence worth knowing is that a PostgreSQL 2-D array
literal contains `{{` (`'{{1,2},{3,4}}'`) and counts as templating. It genuinely
is, to Jinja — which cannot parse it — so a Superset that renders SQL cannot run
that statement either, and being told so beats grading text nobody runs.

**A rendered template is graded on one rendering, and says so.** `dp` activates
no filters and no URL parameters, so `filter_values(...)` is empty, every
`{% if %}` is false and the *base* branch is what runs. The bodies those
conditions guard are never measured — which is most of why the template is a
template — so the grade names them:

```
ok (0.0000%, 3 probe(s)) — untested: 1 conditional branch(es) went untaken --
Superset rendered this template with no filters active, so only the base branch
was measured: `filter_values('region')|length`
```

A `{{ ... }}` substitution gets the same treatment: it rendered to one value
here, and two engines agreeing on that value say nothing about another.

**A Superset that will not render is a refusal, never a fallback.** Before the
first templated probe, `dp` sends a one-line template of its own through SQL Lab
to each connection and checks what comes back. A connection that is not exposed
in SQL Lab, or an instance that renders nothing, is reported as exactly that —
naming the connection, and quoting what it said — rather than falling back to a
raw run that produces the confusing brace error again. Stripping the templating
is never an option: that would grade a query nobody runs.

**The single-statement guard still applies, and it had to learn to look past
the text.** Wherever SQL goes through Superset there is no driver to configure,
so the prepared-statement layer that makes a second statement impossible on the
direct path does not exist and the textual check is the whole defence. That
check reads the text as it stands — which is the wrong question when Superset
renders it first:

```sql
SELECT 1 AS a{{ '); SELECT 1/0 --' }}
```

One statement to any server: the separator sits inside a string literal, which
is exactly what the scan calls it. Jinja then deletes the quotes and the
separator is left bare, inside the wrapper, on a warehouse. So a `;` *anywhere*
inside a Jinja tag is refused, on both paths that reach SQL Lab — grading's
probes and `duplicate`'s validation query — because what a tag renders to is
not knowable before Superset renders it. Measured rather than argued: the
payloads live in the shared separator corpus, and the differential suite
executes what each one renders to against a real PostgreSQL and asserts it
really does run the planted statement. Superset's own protections sit behind
that and are additional, never a substitute.

**A refused port hands over the evidence it already has.** "Not comparable"
covers two different things: a probe that ran and came back NULL on one side,
and a probe a warehouse would not run at all. Only the second has a driver
behind it, and that message is the one thing that says what to fix — so a
refusal names the probe, the warehouse as *you* named it on the command line,
and the engine's own words, untruncated:

```
Error: metric `count_amount` could not be ported: UNMEASURED (no probe ran;
every one was refused by a warehouse, 1 probe(s)) — `value` did not run on
Redshift Prod (target): function listagg(text, unknown) does not exist HINT:
No function matches the given name and argument types. You might need to add
explicit type casts.
The rewrite that was graded (deterministic rule filter-to-case): SUM(CASE WHEN
t = 'r' THEN b END)
```

**One expression that cannot be ported does not discard the ones that could.**
A dataset can carry 53 flagged metrics while the dashboard charts three, so a
run that aborted on the first refusal threw away every port it had already
proven and never attempted the rest — often over a metric nobody charts. Each
refusal is now collected, the run continues, and the port file is written with
the entries that graded. The refusals are printed after it, in full, and the
command exits **1**: a file was written, so it cannot claim success, and what
is absent from that file has to be impossible to miss. If *nothing* graded,
no file is written at all — there is no evidence to record.

That is safe because the gate lives downstream: `dashboards duplicate`
resolves each expression separately and, for one a chart needs with no entry,
prints `No port to trust for:` and `Nothing was written.` before creating
anything. So a partial port file cannot produce a partial migration; it only
stops an unportable expression blocking the expressions that are fine.

`UNMEASURED`, not `DIFFERS`, and that is the third thing the verdict has to
get right. `DIFFERS` is a claim that the two sides differ; when every probe
was refused, what is known is that nothing looked. The reason says which kind
of nothing it was, because they ask for opposite next steps — an empty table
says re-run later, a refused battery says fix the rewrite. A probe that
agreed beside one that was refused is still a measurement, of less than the
battery set out to check: that stays `DIFFERS`, and names the probe that did
not run.

A probe that *both* warehouses refused is called out as such, because the two
mean different things to fix: invalid on the target is a bad port, invalid on
both is a bad probe. A statement whose columns don't line up says which side
each extra column is on, rather than naming it and leaving you to work out
whether the rewrite dropped it or invented it. None of this needs `--verbose`
— a refusal that sends you off to run four more commands is a refusal that
withheld what it already knew.

On a dashboard of a few datasets, only a fraction of the flagged expressions
turn out to be used by its charts — and of those, the large majority are a
single transform, the `FILTER` rule above. The remainder are the silent
`avg()` truncation, which needed the model.

Even that fraction is minutes of model calls and probe batteries, and
`duplicate` is no quicker: its first phase alone is a login, two database
lookups, the dashboard, every chart, every dataset, the target's whole dataset
listing and a live validation query per virtual dataset — all before the plan
table prints. So both commands say what they are doing and where they are:
`Grading count_amount on both warehouses (3/28)`, `Verifying cloned charts:
Revenue (7/12)`. The counts are the point — a bare spinner does not answer "is
it stuck". It is written only to an interactive terminal, and never under
`--json`, so a pipeline and a CI log see exactly what they saw before.

Grading a wide statement is itself minutes long, so it says how much work that
is: `Grading this dataset's SQL on both warehouses, probes 1-178 of 439
(1/27)`. What an operator needs there is the scale of the wait and where in it
they are — a bare spinner does not answer "is it stuck". A battery narrow
enough to go in a single query says so instead (`439 probes in one query`),
because a counter that could only tick once would look wedged; one re-asked
probe by probe after a refusal becomes a genuine counter.

**A statement battery is hundreds of measurements, and none of them is the
thing worth cutting.** A dataset's SQL is graded per column: a row count, a
non-null count for every column, `avg`/`min`/`max` for every numeric one and a
distinct count for the rest — 439 probes for a 200-column statement, on each
of two warehouses. Every one of those used to be its own query, and **every
query re-runs the whole statement**: 878 executions of a large join to grade
one candidate, each also a SQL Lab round trip when the statement is a
Jinja template. The measurements all earn their place — `avg` is where silent
truncation hides, and a non-null count is what catches a NULL semantics change
— so what changed is the asking, not what is asked. The probes are aggregates
over the same subquery, so several go in **one query per side**: four
statements and four scans a side for that dataset instead of 878, with every
probe still asked, every label still reported and nothing to disclose.

Several and not all of them, because a query carrying hundreds of aggregates
stops being one cheap scan. Sending all 439 in a single query exceeded a
300-second read timeout on Redshift through SQL Lab, while the same battery
over the PostgreSQL side of the same port — same SQL Lab, same width —
answered. The server was up throughout and kept serving:
nothing was killed, and the client gave up at its own read timeout while the
warehouse was still working. What the batch exhausted was **time**, not memory.
`COUNT(DISTINCT …)` is the expensive half of it — each one maintains a hash of
the values seen so far, so 181 of them in one pass is 181 hashes to probe and
update for every row — so a batch is closed at 200 probes **or** 50 distinct
counts, whichever comes first.

**Neither number has to be right, and that matters more than either of them.**
The client's patience and the batch's width are independent here: a batch too
slow for the one is *discovered* and narrowed rather than needing the other
guessed correctly up front. A refused batch is halved and re-asked, and what
the battery learns — the narrowest width refused, the widest answered — is
carried to the batches after it, so the search costs its timeouts once per
statement instead of once per batch. That property is what a 300-second
timeout affords, because a timeout is a *lower bound*: it says the statement
wanted more than 300 seconds, never whether it wanted 301 or 3000. No width
can be derived by dividing 439 by an assumed speedup, and none was.

The caps are therefore where the search starts, not a promise. Neither is a
ceiling anyone measured *on Redshift* — that run produced one timeout, not a
curve. The shape of the trade was measured on PostgreSQL 16 (the same
200-column battery, 400k rows, median of three runs), and it is U-shaped
rather than monotone:

| Probes per query | Queries | Elapsed |
| --- | --- | --- |
| 439 (all of them) | 1 | 19.1s |
| 200 | 3 | 14.4s |
| 100 | 5 | 12.8s |
| 50 | 9 | 13.0s |
| 25 | 18 | 14.9s |
| 1 (one per probe) | 439 | 65.0s |

One query is not the cheapest shape even where it finishes, and one query per
probe is five times worse than either — which is why the answer is a middle
and not a revert. A battery with no distinct counts in it is flat from width
50 to width 801 on the same engine, so the 200 cap costs nothing there and
exists for the margin.

A batch either warehouse refuses — for being too wide, too slow, or because of
a `json` column it cannot run `COUNT(DISTINCT)` over — is **halved and
re-asked**, down to the single probe, so getting the batch wrong costs time and
never evidence. Halving rather than collapsing straight back to one query per
probe is the point: a refusal over 439 aggregates says something about the
width and nothing about any one probe, and the column an engine genuinely
cannot aggregate is isolated in about two queries per halving instead of 439.
Batching also makes a grade *more* honest, not less — a statement's row count
and the non-null counts compared against it come from a handful of reads
instead of hundreds minutes apart, which is the very inconsistency the
`untested` disclosure had to defend itself against.

**A warehouse that never answers is a refusal, not a traceback.** SQL Lab holds
the connection open while the warehouse works, so a batch that outlives the
300-second read timeout comes back as nothing at all — no status, no message.
That used to end the run as a raw `httpx.ReadTimeout` with no exit code and
nothing written, throwing away every probe already run. Every transport failure
out of the Superset client — a read timeout, a connect timeout, a refused
connection, a far side that hung up — is now a service failure (exit `5`,
the retryable one) naming the phase and the budget that ran out, which is what
lets the batch above be narrowed instead of lost.

The ones that leave the request's *fate* open — a read timeout, a far side
that hung up mid-answer — are additionally distinguishable by the code that
has to care. A caller that only reads never has to: asking again costs a round
trip. A caller that creates something does, because for it "it did not answer"
and "it did not happen" stop being the same sentence, which is how `duplicate`
above knows to go and look instead of guessing.

**A run outlives Superset's access token.** That token is a JWT with a life
measured in minutes, and these are commands measured in tens of them. A
21-minute `dataset port --suggest` run — three model attempts and hundreds of
probes — ended `statement does not run on Postgres Prod (source): ... (401
Unauthorized): {"msg":"Token has expired"}`: the rewrite was fine, the login
had lapsed somewhere in the middle, and the refusal blamed the SQL, which is
the worst possible way to lose the work because the next move it suggests is
rewriting a query that was already correct. `dp` now re-authenticates and
retries the one request that was refused, so the probes already run are kept
and the operation finishes. It is the client that does this, not each command,
so every Superset call is covered — `duplicate`'s chart-by-chart verification
as much as grading's probe batteries.

Only an *expired* token is renewed, and only because Superset says so in as
many words. Credentials that are genuinely wrong still fail immediately with
exit `4`: they arrive as the same `401`, so the status code alone cannot tell
them apart, and retrying them would ask the same rejected password again
forever. A renewal is attempted once per refused request; a second refusal is
a real failure, reported as an authentication one rather than as a warehouse
that would not run your SQL.

`duplicate` completes a migration in one command: it resolves every recorded
port itself, before it writes anything, matching expressions against the
dashboard's charts *and* its `json_metadata` — a native filter or a
cross-filter scope can name a column no chart payload ever mentions, and
missing that would ship an expression unported with nothing to say so. What
nothing on the dashboard references is still copied — a dataset missing a
metric is broken for everyone else who reads it — but reported as carried
across unverified, never silently.

For everything the dashboard does reference, a port resolves to one of four
outcomes:

- **fresh** — the recorded port's expression still matches what's live in
  Superset (compared by hash, so a save that only reformats the SQL is never
  mistaken for an edit), and `duplicate` applies it.
- **stale** — the expression has changed since the port was verified, and
  `duplicate` refuses the whole run, exit code `1` with nothing written,
  rather than apply evidence for SQL that no longer exists.
- **rule** — no port is recorded, but the expression is one `dataset port`
  would rewrite deterministically the moment you run it.
- **suggest** — no rule covers it either, and `dataset port --suggest` is
  what is needed before `duplicate` has anything to apply.

Three environment variables configure it. `DP_SUPERSET_PORTS_DIR` names that
directory — unset or unreadable, `dataset port`, `dataset ports` and
`duplicate` alike refuse with a configuration error, exit code `3`, rather
than silently porting nothing. That refusal shows an example value and names
`dataset ports init`, because a variable name on its own is a dead end for
anyone who has not seen a port file before. `DP_SUPERSET_PORT_MODEL_CMD` is the command
`--suggest` runs, a full argument vector split shell-style with `shlex` and
executed directly — never through a shell — so a shell alias or function on
your `PATH` is invisible to it; point it at a real binary or script instead.
That includes a multi-account Claude setup, where the account lives in an
environment variable rather than a launcher:

```bash
export DP_SUPERSET_PORT_MODEL_CMD="env CLAUDE_CONFIG_DIR=$HOME/.claude-accounts/work claude -p"
```

Only the SQL text goes to that command, on stdin, and only its stdout comes
back as the candidate — no row data is ever sent, and nothing about it passes
through a shell. `DP_SUPERSET_PORT_MODEL_TIMEOUT` bounds how long **one** call
may run, in seconds (default `300`) — `--max-attempts` multiplies that number
rather than dividing it.

**A passing grade is evidence on today's data, not a proof.** Two engines
agreeing on every aggregate they were asked about says nothing about a NULL,
or a value, that isn't in the table yet. `untested` names exactly that gap —
which columns' NULL handling nothing in the current data exercised — printed
in the grade itself rather than left for a bare `ok` to imply. Each
warehouse's non-null counts are compared against **its own** row count: two
warehouses mid-migration hold different numbers of rows, and a disclosure that
only fires when they match is one that never fires.

It is reported for a dataset's SQL and for its calculated columns — the two
kinds that are graded per row, and so have a non-null count to compare against
a row count. A **metric** has neither: it is an aggregate, measured once per
side, and there is no per-row NULL count to be silent about. Read a metric's
`ok` as "these two aggregates agreed on today's data" and nothing more.

A few edges worth knowing:

- **A port file is hand-editable**, so `duplicate` refuses to apply a grade
  that does not start with `ok` — the grade is the evidence a rewrite was
  measured, and a file saying otherwise should not be trusted just because it
  exists.
- **Two port files recording the same dataset and the same database
  direction are refused**, not resolved by filename order — which one applies
  is a question only a reviewer can settle. Two *different* Superset datasets
  over one physical table are not a collision: Superset allows them, each has
  its own metrics and calculated columns, and each gets its own port file,
  resolved by dataset id.
- **A virtual dataset's metrics and calculated columns can't be graded**, and
  `dataset port` reports each by name rather than failing with a driver
  error. A metric is probed as `SELECT <expression> FROM <schema>.<table>`,
  and on a virtual dataset that table name is the dataset's display name, not
  a relation either warehouse has. Each is a per-expression failure like any
  other, so the dataset's own SQL is still ported: it is graded as a
  statement, discovering its own columns, and never names that table. The
  refusal says to rewrite and verify those expressions by hand, and what
  became of the SQL — no run of the command can grade them for you.
- **A source column typed `json`, `xml` or `point` can't be graded.** Grading
  a dataset's whole SQL discovers its own columns and runs `COUNT(DISTINCT
  ...)` on whichever aren't numeric, and Postgres has no equality operator for
  any of those three types. A virtual dataset selecting one can't be ported
  through this command, and has to be rewritten and verified by hand.

### Which dashboards does anybody actually open?

Before migrating a dashboard, it is worth knowing whether anyone reads it:

```bash
dp bi superset dashboards usage
dp bi superset dashboards usage --unused
dp bi superset dashboards list --database "Postgres Prod" --by-usage
```

That last one is the retirement question in a single command — of the
dashboards still reading a warehouse, which does nobody open? Those you delete
rather than migrate.

**This reads Superset's own tables, not a modelled copy of them.** Every
instance records what happens to it in `logs` (Flask-AppBuilder's action log:
`action`, `user_id`, `dashboard_id`, `dttm`) and `ab_user`. Point `dp` at a
database target holding them and there is nothing to map, because the column
names are Superset's on every install:

```bash
export DP_SUPERSET_USAGE_TARGET=metadata          # a DP_TARGETS name
```

One variable is the whole setup when the target *is* your Superset metadata
database — the table names default to `public.logs` and `public.ab_user`. If
you replicate those into a warehouse instead, name the copies:

```bash
export DP_SUPERSET_USAGE_TARGET=warehouse
export DP_SUPERSET_USAGE_LOGS_TABLE=raw.superset_logs
export DP_SUPERSET_USAGE_USERS_TABLE=raw.superset_ab_user
```

**Cache warm-ups and thumbnails are excluded by what they are, not by who runs
them.** Superset logs a human opening a dashboard as `dashboard`, and its own
machinery under separate names — over a few months of history the automated
`ChartRestApi.warm_up_cache` events can outnumber the human `dashboard` ones
by orders of magnitude. Filtering on the action therefore works before you
know a single service-account name.
`--actions` prints your instance's vocabulary so you can check:

```bash
dp bi superset dashboards usage --actions
```

If your Superset spells a dashboard open differently, pass it to
`--view-action` (repeatable). `DP_SUPERSET_USAGE_EXCLUDE_USERS` is a second
net, for an instance whose automation logs under a real account.

Two limits worth knowing before you delete anything:

- **`--unused` only covers dashboards the API account can see.** Superset
  filters its dashboard list by permission, so a non-admin gets a short one.
  When the logs name dashboards the listing does not, the report says so and
  how many — run as an admin for the complete picture.
- **The metadata database must be reachable as a `dp` target.** Superset's
  `/api/v1/log/` endpoint cannot stand in for it: on an instance with real
  history it times out even asking for a single row. MySQL-backed Superset is
  out of scope, since `dp` targets are Postgres, Redshift or DuckDB.

### Onboarding and offboarding

Access spans three systems, and each names the same person differently. The
conventions live in your `.envrc`, one per target, so nothing about them is
baked into the tool:

```bash
POSTGRES_PROD_USERNAME_TEMPLATE="ex_{first_initial}{last}"   # ex_mcurie
REDSHIFT_PROD_USERNAME_TEMPLATE="{first_initial}_{last}"     # m_curie
# SUPERSET_USERNAME_TEMPLATE defaults to {local}              # marie.curie
```

Grants are not configured anywhere: they are copied from a colleague who
already has the right access, per area.

```bash
dp people onboard marie.curie@example.com \
    --like grace.hopper@example.com --dry-run
```

```text
Onboarding marie.curie@example.com copying grace.hopper@example.com

  Area           Username      Copies                                Status
  postgres_prod  ex_mcurie     reporting, restricted_users, …        create
  redshift_prod  m_curie       restricted_users, finance_users, …    create
  superset       marie.curie   Gamma, Viewer                         exists

! superset: marie.curie would receive Admin (9 of 140 accounts) — few
  accounts here have it, so check it is intended.
```

Drop `--dry-run` to apply it. Each area gets its own generated password — three
systems that can be compromised separately should not share a secret — and all
of them are written to one `0600` file rather than printed.

Offboarding is the same shape and **destroys nothing**: the Superset account is
deactivated, warehouse logins are disabled and memberships revoked, and
everything those accounts own keeps its owner. Removal stays with the commands
that already do it properly — `dp db role drop` (which reassigns ownership) and
`dp bi superset users delete`.

```bash
dp people offboard someone.leaving@example.com --dry-run
```

### Airbyte

```bash
dp ingest airbyte connections list -w <workspace-id> --json
dp ingest airbyte connections sync -c <connection-id> --wait
dp ingest airbyte connections reset -c <connection-id>

# Move every date-based cursor to a date, across all connections from one source
dp ingest airbyte connections set-cursor --source-id <id> --to 2024-01-01 --dry-run
dp ingest airbyte connections set-cursor -c <connection-id> --to 2024-01-01 --yes

# xmin (Postgres transaction-id) cursors: set them directly
dp ingest airbyte connections set-cursor -c <connection-id> --xmin 0 --yes
```

## Development

See [CONTRIBUTING.md](CONTRIBUTING.md) for the integration suite and the rules
for changing SQL that runs on Redshift.

```bash
git clone https://github.com/hanslemm/dataplat
cd dataplat
uv sync --group dev --all-extras
uv run pytest
uv run ruff check .
uv run ruff format --check .
uv run mypy dataplat
```

CI runs those four across Python 3.12 and 3.13 — the floor the wheel
advertises as well as the pinned dev version.

The three engines are not equally covered, and the difference is worth knowing
before you trust a number the tool printed:

| Engine | Coverage in CI | Needs |
| --- | --- | --- |
| PostgreSQL | real SQL, really executed | a container (`-m integration`, `DP_TEST_PG_REQUIRED=1`) |
| DuckDB | real SQL, really executed | nothing — it is in-process, so it runs in the default job |
| Redshift | none: generated and asserted, never executed | a cluster you own (`-m redshift`) |

Redshift cannot be containerized, so CI cannot cover it. If you run dataplat
against a Redshift cluster, you can verify your own deployment: point
`DP_TEST_RS_TARGET` at one of your targets and run the read-only tier
(`uv run pytest -m redshift`). It only issues `SELECT`s — a guard refuses
anything else before it reaches the server — and prints what your cluster
answered. See [CONTRIBUTING.md](CONTRIBUTING.md#testing-against-a-real-redshift-cluster).

### Integration tests against a real PostgreSQL

Most of the suite drives a fake database cursor. That proves a code path
*called* `execute`, never that the SQL it built is valid. The tests in
`tests/integration/` close that gap: they run the real statements against a
live PostgreSQL and check the results, so an invalid column reference or a
broken `GRANT` fails here instead of on your warehouse.

`uv run pytest` stays green **without Docker** — the suite skips itself when
no server is reachable. You only need the container to actually exercise it:

```bash
docker run -d --name dp-pg-test -p 55432:5432 \
    -e POSTGRES_PASSWORD=postgres \
    -e POSTGRES_DB=dataplat_test \
    postgres:16 -c shared_preload_libraries=pg_stat_statements

# Once per database: pg_stat_statements is a per-database extension.
docker exec dp-pg-test psql -U postgres -d dataplat_test \
    -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements'

DP_TEST_PG_REQUIRED=1 uv run pytest -m integration
```

The `-c shared_preload_libraries=pg_stat_statements` is not optional for full
coverage: without it the extension installs but every *read* of the view fails
with `pg_stat_statements must be loaded via shared_preload_libraries`, so
`dp db long-queries --history` stays untested.

| Variable | Purpose |
| --- | --- |
| `DP_TEST_PG_DSN` | Connection string for the test server. Default: `postgresql://postgres:postgres@127.0.0.1:55432/dataplat_test`. |
| `DP_TEST_PG_REQUIRED` | Truthy ⇒ an unreachable server is a hard **error**. Unset ⇒ the tests skip. |

Set `DP_TEST_PG_REQUIRED=1` whenever a skip would be a lie — that is, always
in CI. Without it a broken container makes the tests vanish and the run goes
green having validated no SQL at all. CI runs the integration job with it set,
against a pinned PostgreSQL major, and the release workflow runs the same job
as a gate before publishing.

Each test runs in a transaction that is rolled back afterwards, so tests never
see each other's objects and nothing survives a failed run. To select the
fast, database-free subset explicitly, use `-m "not integration"`.

**Known gap: Redshift.** Redshift cannot be containerized, so every
Redshift-specific code path remains fake-tested only — its SQL is generated and
asserted against a fake cursor, never executed. Treat changes to Redshift
paths as unverified by CI and test them against a real cluster.

**Not a gap: DuckDB.** There is nothing to containerize — a DuckDB database is a
file (or `:memory:`), and the driver is installed by `--all-extras` — so the
DuckDB SQL is really executed, with no marker, no container, no env var and no
skip path. A DuckDB change that "could not be tested" is a change that was not
tested.

## Releasing

Bump `[project].version`, then tag `X.Y.Z` (bare semver, no `v` prefix) on
`main`. The release workflow refuses to publish unless the tag matches that
version, and runs the full check matrix *plus* the integration suite against a
real PostgreSQL first; only then does GitHub Actions build and publish to PyPI
via Trusted Publishing. A published version can be yanked but never replaced,
so the extra minutes buy a guarantee that the shipped SQL has at least been
parsed by a server. Commits follow
[Conventional Commits](https://www.conventionalcommits.org/).

## License

[MIT](LICENSE)
