Metadata-Version: 2.5
Name: dataplat
Version: 0.11.0
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'
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 | datasets | duplicate | usage       [3]
│       └── 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.
`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 |
| `cloud` | `dp cloud` | boto3, plotext |
| `all` | everything | all of the above |

`duckdb` is the one extra that is an *engine* rather than an area, so it is
deliberately **not** part of `db`: the extension module alone is three times
psycopg's size, and 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 that call. Default `180`. |
| `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 borg
```

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 the instance this was built against, `borg`
has 20 datasets whose `schema` field says so — and **111 more that reference it
only in SQL**. Airbyte destinations landing in the schema are reported with the
connections that write through them (`raw`: one destination, 37 connections).

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: 34 Superset dataset(s), 1 Airbyte destination(s), 37 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.

`--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.

### 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 port 42 --from-database "Postgres Prod" --to-database "Redshift Prod" --suggest
dp bi superset dataset ports
```

`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`. 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`).

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.

Measured on a real dashboard, its five
datasets carried 137 flagged expressions and its charts used 28 of them — 24
were a single transform, the `FILTER` rule above, and the other 4 were the
silent `avg()` truncation, which needed the model.

`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. `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 `180`).

**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` refuses them 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. The dataset's own SQL ports normally.
- **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.raw_superset__logs
export DP_SUPERSET_USAGE_USERS_TABLE=_raw.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 — on one real instance, 90 days held 271,922
`ChartRestApi.warm_up_cache` events next to the `dashboard` ones. 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 (13 of 252 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)
