Metadata-Version: 2.5
Name: dbt-dqm
Version: 0.2.0a3
Summary: Persistent dbt test issue tracking and a local review application
Project-URL: Homepage, https://github.com/a-adamovich/dbt-dqm
Project-URL: Repository, https://github.com/a-adamovich/dbt-dqm
Project-URL: Issues, https://github.com/a-adamovich/dbt-dqm/issues
Project-URL: Changelog, https://github.com/a-adamovich/dbt-dqm/blob/main/CHANGELOG.md
Author: Aliaksei Adamovich
License-Expression: Apache-2.0
License-File: LICENSE.md
Keywords: bigquery,data-observability,data-quality,data-testing,dbt,postgres
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Requires-Python: <3.15,>=3.11
Requires-Dist: dbt-core==1.12.5
Requires-Dist: mashumaro<3.18,>=3.17
Requires-Dist: pandas<4,>=2.2
Requires-Dist: platformdirs<5,>=4
Requires-Dist: pyarrow<26,>=25.0.1
Requires-Dist: pyyaml<7,>=6
Requires-Dist: sqlparse<0.7,>=0.6.0
Requires-Dist: streamlit<2,>=1.52
Requires-Dist: urllib3<3,>=2.8.0
Provides-Extra: bigquery
Requires-Dist: dbt-bigquery==1.12.1; extra == 'bigquery'
Requires-Dist: google-cloud-bigquery<4,>=3.25; extra == 'bigquery'
Requires-Dist: oauthlib<5,>=4.0.0; extra == 'bigquery'
Provides-Extra: postgres
Requires-Dist: dbt-postgres<1.12,>=1.11; extra == 'postgres'
Description-Content-Type: text/markdown

# dbt-dqm

dbt-dqm is an open-source dbt package for persistent, row-level tracking of dbt test failures,
plus a single-user local review app. Each failed row becomes an issue with a stable identity, a
lifecycle (Active → Archived when the test passes) and reviewer annotations that survive later
runs.

## Status and supported versions

| | |
| --- | --- |
| Version | `0.2.0a3`, **alpha**: [PyPI](https://pypi.org/project/dbt-dqm/0.2.0a3/), Git tag [`v0.2.0a3`](https://github.com/a-adamovich/dbt-dqm/releases/tag/v0.2.0a3). Interfaces may change between alpha releases. dbt only accepts SemVer, so the package's `dbt_project.yml` spells the same version `0.2.0-a3`. |
| Upgrade from 0.1 | Needs a **fresh DQM schema**: set `dbt_dqm_schema` to a new schema. 0.1 tables are preserved and never reused or migrated. |
| dbt | dbt-core 1.11 and 1.12, with dbt-bigquery 1.11 or 1.12 and dbt-postgres 1.11 (there is no dbt-postgres 1.12; 1.11 supports dbt-core 1.12) |
| Warehouses | BigQuery; Postgres 14 and 16 (needs the `pgcrypto` extension) |
| Review app | Python 3.11–3.14, the same two warehouses |

The Python app pins dbt-core 1.12.5; its optional extras use dbt-bigquery 1.12.1 and dbt-postgres 1.11.x;
the dbt package accepts the wider dbt range above. Python support is `>=3.11,<3.15`.
The [release checklist](docs/release-checklist.md) lists what must pass before a release is tagged;
the [0.2.0a3 evidence](docs/release-0.2.0a3.md) records this alpha's results.
Package contracts (relations, migrations, grants, recovery, retention, maintenance) are in
[warehouse interfaces](docs/warehouse-interfaces.md).

## Install the dbt package

Install the alpha through Git by adding it to your consumer project's `packages.yml`:

```yaml
packages:
  - git: "https://github.com/a-adamovich/dbt-dqm.git"
    revision: v0.2.0a3
```

The alpha is not listed on dbt Hub: Hub's SemVer tag parser does not accept `v0.2.0a3`, so
install this alpha through the Git revision above. Hub registration is planned for 0.2.0.

The 0.2 app expects the matching 0.2 package; don't mix it with 0.1.

Then run `dbt deps`. The review app is a separate Python package (`dbt-dqm`); installing it doesn't
install the dbt package, and vice versa.

On Postgres, create the extension once (the package doesn't, because it usually needs elevated
privileges): `create extension if not exists pgcrypto;`

## Install the Python app and start reviewing

In a Python 3.14 environment (recommended; 3.11–3.14 supported), install the app separately:

```bash
python3.14 -m venv .venv
source .venv/bin/activate
python -m pip install 'dbt-dqm[bigquery]==0.2.0a3'
```

Warehouse adapters are optional. In a checkout, install
only the adapter you need with `uv sync --extra bigquery` or `uv sync --extra postgres`;
use `uv sync --all-extras` for both. Select the extra in your installed environment:

```bash
python -m pip install 'dbt-dqm[bigquery]==0.2.0a3'          # BigQuery
python -m pip install 'dbt-dqm[postgres]==0.2.0a3'          # Postgres
python -m pip install 'dbt-dqm[bigquery,postgres]==0.2.0a3' # Both
```

A plain install includes the app and dbt-core but no warehouse adapters.
Shared UI dependencies such as PyArrow remain installed. Existing environments retain adapters
already installed. Earlier 0.2.0a2 installs included both adapters.

On Windows PowerShell, create and activate the environment with:

```powershell
py -3.12 -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install 'dbt-dqm[bigquery]==0.2.0a3'
```

Activation is optional: `uv run --extra bigquery dbt-dqm app` uses the checkout environment.
For an installed environment, run its `dbt-dqm` command directly.

Configure your dbt profile, add the Git package above, and set a fresh schema in your
consumer project's `dbt_project.yml` when upgrading from 0.1:

```yaml
vars:
  dbt_dqm_schema: dqm_alpha
```

After adding a tracked test as shown below, run these steps separately. A test that finds
failures can return a nonzero exit code; reconciliation must still run afterward:

```bash
dbt deps
dbt parse
dbt run --select package:dbt_dqm  # initialize tracking tables and public views
dbt test --select tag:dqm
dbt build --select package:dbt_dqm
dbt-dqm app --project-dir . --profiles-dir ~/.dbt --target dev
```

In the browser, Sync, edit an issue's notes or review verdict, then Apply. Health shows
setup, tests and maintenance status.

## Configuration defaults and troubleshooting

From your dbt project or a subfolder, launch with `dbt-dqm app`; `dbt-dqm workspace info`
and `dbt-dqm workspace purge` use the same defaults. Explicit flags take precedence:

- Project: `--project-dir`, then `DBT_PROJECT_DIR`, then dbt's current-directory/parent lookup.
- Profiles: `--profiles-dir`, then `DBT_PROFILES_DIR`, then `profiles.yml` in the launch directory,
  then your home directory's `.dbt` folder.
- Target: `--target`, then `DBT_TARGET`, then the rendered profile's `target`, or `default`.

`DBT_PROFILE`, `DBT_TARGET_PATH`, profile `env_var()` values, and
`GOOGLE_APPLICATION_CREDENTIALS` are inherited. Only explicit CLI overrides are passed as
flags to dbt. Paths use the native operating system's conventions; quote paths containing spaces.
The app uses dbt beside its own Python interpreter before searching PATH, and logs that
executable and its version. Technical details include failed commands, exit codes and both output
streams. If tracking relations are missing, run `dbt run --select package:dbt_dqm` first.
This package command works at the normal multithreaded setting; its setup models are ordered.

Custom `generate_schema_name` and `generate_alias_name` macros are supported for package models.
The app and SQL helpers use the parsed model relations. Macro-created internal tracking tables,
such as `dqm_issue_occurrences`, keep their fixed identifiers in the reconciliation model's schema.
Changing naming macros does not migrate or delete existing warehouse data.

## BigQuery authentication through WIF

The app and dbt use Application Default Credentials (ADC) for an `oauth` profile. Supply a
trusted external-account configuration from your existing Workload Identity Federation
environment; it exchanges the external identity for short-lived service-account access
without a downloaded service-account key:

```bash
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/trusted-wif-adc.json
```

```yaml
consumer:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: your-project
      dataset: your_dataset
      location: US
      threads: 4
```

Use your consumer project's profile name in place of `consumer`. Keep the configuration
and its external token source available to both commands and the app. WIF configurations
belong to the environment owner; dbt-dqm does not create pools, providers, service accounts
or IAM bindings. Use the normal ADC environment path, not an explicit credentials-file
override, for WIF. Only use configurations from an environment you trust.

The offline WIF smoke test exercises ADC loading, token exchange, service-account
impersonation and refresh through the installed app/dbt authentication libraries, with
simulated responses. It does not verify live WIF or IAM. Live release QA uses existing
local service-account credentials; no new keys are created or downloaded. Optional
[scheduled BigQuery verification](docs/scheduled-bigquery.md) stays inactive until configured.

## Configure a tracked test

```sql
{{
  config(
    tags=['dqm'],
    store_failures=true,
    meta={'dbt_dqm': {
      'granularity': ['customer_id'],
      'capture_mode': 'allowlist',
      'context_columns': ['reason']
    }}
  )
}}

select customer_id, reason from {{ ref('customers') }} where is_invalid
```

- `meta.dbt_dqm.granularity` is mandatory and is the only source of issue identity. Every failed
  row must return those columns; text identity comparisons are case-insensitive.
- Capture modes: `identity_only` (the default), `allowlist` (adds the named `context_columns`), and
  `full` (explicitly stores complete failed rows).

**Row owners and review metadata.** A failed row's `DQM_OWNER` column (or `owner_column`) sets its
owner, falling back to `poc_responsible` when blank or conflicting:

```sql
{{ config(tags=['dqm'], store_failures=true, severity='warn',
    meta={'dbt_dqm': {'granularity': ['campaign_id'], 'owner_column': 'DQM_OWNER',
      'poc_responsible': 'analytics', 'priority': 'high', 'criticality': 'customer reporting'}}) }}
select campaign_id, case when region='EU' then 'eu_team' else 'global_team' end as DQM_OWNER
from {{ ref('campaigns') }} where invalid_flag
```

Ownership is set only for new occurrences, and reviewer edits persist. Priority and tags are
visible and filterable; review verdicts (true/false positive) are separate from workflow status.

## Collect and reconcile

Run two separate dbt steps, so a failing test command never prevents reconciliation:

```bash
dbt test --select tag:dqm             # collection: an on-run-end hook captures tracked results
dbt build --select package:dbt_dqm    # reconciliation, public views and health views
```

Captures sharing stored-failure tables must be serialized: equal row counts do not prove
that the captured rows belong to the invocation. BigQuery supports one reconciliation per
dataset at a time; overlapping reconciliations are detected, not queued.

`dbt run --select dbt_dqm.dqm_reconcile` runs reconciliation alone. The package publishes
`dqm_current_issues`, `dqm_all_issues`, `dqm_issue_timeline`, the health views and the tracking
tables in the DQM schema. `dbt_dqm_test_tag` and `dbt_dqm_schema` override the defaults; with
dbt's standard schema naming, `dbt_dqm_schema: governance` produces `<target_dataset>_governance`.

## Retention and maintenance

- `dbt_dqm_retention_days` (a positive integer) prunes raw executions and observations that
  reconciliation has already processed, and old run-ledger rows.
- `dbt_dqm_event_retention_days` prunes the `dqm_issue_events` history.

These run as optional maintenance after invocations that captured tracked tests or reconciled, or
on demand with `dbt run-operation cleanup_dqm_logs`. A SQL error inside a maintenance step never
fails the dbt run; outcomes are logged and shown in `dqm_maintenance_health` and the app's Health
tab. The exact guarantee and its limits are in
[warehouse interfaces](docs/warehouse-interfaces.md#optional-maintenance).

> **Event values outlive raw logs by default.** With `dbt_dqm_event_payloads: full` (the default)
> and no `dbt_dqm_event_retention_days`, captured before/after values are kept forever. Under
> `allowlist` or `full` capture they can contain personal data. Set event retention, or choose
> `changed_columns` (column names plus hashes, which are not anonymization) or `none`.

## Local review app

```bash
dbt-dqm app \
  --project-dir /path/to/consumer-project \
  --profiles-dir ~/.dbt \
  --target demo
```

- It uses the selected dbt profile (validated with `dbt debug`), including `env_var()`
  authentication and custom `generate_schema_name` behavior, and synchronizes at the start of each
  browser session.
- It caches a project-and-target-specific snapshot plus pending edits in an owner-only SQLite
  workspace under the user-data directory. Credentials are never copied into it.
  `dbt-dqm workspace info` and `dbt-dqm workspace purge` (with `--project-dir` and `--target`)
  inspect or delete it.
- Edits use compare-and-set versions: a concurrent change produces an explicit conflict rather than
  a silent overwrite. On Postgres, a write blocked by a running reconciliation gives up after
  `--lock-timeout` seconds (default 5) and reports the warehouse as busy.
- Each sync is verified before it replaces the cache; an inconsistent read keeps the cache and
  pending edits.
- **Cache limits:** at most 50,000 issues and 128 MiB of serialized issue data
  (`--max-cache-issues` and `--max-cache-mib` can only lower them). The cache holds Active issues
  plus 90 days of Archived history; `--archive-cache-days 0` keeps Active issues only. An
  oversized sync keeps the existing cache and pending edits. These limits bound serialized data,
  not process memory; see [scale](docs/scale.md).
- **Backlogs above the cache limits (`--browse`, default `auto`):** the app syncs a local
  snapshot while the review population fits the limits, and above them reads issues from the
  warehouse page by page instead. Filters, search, counts and the sort then run in the
  warehouse; only recently loaded pages (up to 2,000 issues or 32 MiB) and issues with pending
  edits are kept locally, and edits keep the same compare-and-set versions. When the warehouse
  can't be read, the cached pages are shown offline. `--browse snapshot` or `--browse warehouse`
  forces a mode.
- **Warehouse cost in that mode:** every page, count and search is a query. On BigQuery the app
  reads 2,000 issues at a time (about 1 GB billed at 1M occurrences, serving 80 pages of 25), a
  header summary costs about 0.16 GB, and a search is refused before it runs if a dry run
  estimates more than `--max-search-gib` (default 1). Postgres needs migration 0007's index for
  fast pages. Measured figures: [scale](docs/scale.md#warehouse-browsing-2026-10-11).

## Demo projects

`integration_tests/demo_postgres` and `integration_tests/demo_bigquery` contain the same synthetic
consumer: one seed, three tracked tests of different shapes, and an assertion of the expected
outcome for each scenario. No company data is included.

```bash
cd integration_tests/demo_postgres   # or integration_tests/demo_bigquery
dbt deps
dbt run-operation reset_demo_dqm
dbt seed
dbt run --select demo_records --vars '{demo_scenario: initial}'
dbt test --select tag:dqm --vars '{demo_scenario: initial}'
dbt build --select package:dbt_dqm --vars '{demo_scenario: initial}'
dbt test --select assert_scenario_outcomes --vars '{demo_scenario: initial}'
```

Repeat the last four commands with `demo_scenario: passed` (every issue archives as a genuine
pass), then `demo_scenario: recurrence` (fresh occurrences for selected identities). The scenario
assertion checks the exact active and archived counts, and assumes this reset → initial → passed →
recurrence order.

| Test | Granularity columns | Attribute columns | Initial failed rows |
| --- | ---: | ---: | ---: |
| Customer email | 1 | 2 | 3 |
| Order amount | 2 | 3 | 3 |
| Order line | 3 | 5 | 3 |

Adapter notes:
- **Postgres:** create `pgcrypto` first; start from the demo's `profiles.yml.example`.
- **BigQuery:** keep the service-account key outside the repository (for example
  `~/.credentials/dbt-dqm/service-account.json`). The demo's schema macro stores test failures in
  the same pre-created dataset, so its account needs only BigQuery Job User on the project and
  BigQuery Data Editor on that dataset.

## Roadmap

The package and the local app are, and will stay, Apache-2.0 open source. A hosted offering may
follow, but nothing in the package or app is gated behind it. See the [roadmap](docs/roadmap.md).

## License

Copyright 2026 Aliaksei Adamovich. Licensed under the Apache License, Version 2.0. See
[LICENSE.md](LICENSE.md).
