patrol_duckdb
Checks date-partitioned DuckDB fact tables for data-quality incidents, tests proposed fixes against source control totals on a copy, and flags issues safe to auto-fix or needing escalation.
Instructions
Check a fact table in a DuckDB database for data-quality incidents and say which are safe to fix. A patrol checks every daily partition of the fact table for duplicate keys, duplicate rows, missing partitions, rows that point to no reference record, null spikes, and row counts or amounts that do not match the source's control totals. For each problem it proposes a fix, tests that fix on a copy against the control totals, and decides: "auto-fixed" (proven and low risk), "no action needed" (checked, the data is right) or "escalate" (a person must decide).
Use this before trusting, querying or building on a date-partitioned table that lives in a DuckDB
database, or after a load or pipeline change to confirm the data is still right. Use patrol_files
instead when the data is in CSV, TSV or Parquet files. Use run_demo to see the output on sample data.
Behavior: The database is opened read-only. Your data is never modified and nothing is written back. The only side effect is
writing four report files to out_dir: dashboard.html, audit.jsonl, incidents.csv, fixed_version.csv
(the proposed corrected table, for review). Runs locally with no network calls;
usually a few seconds, longer for tables with millions of rows. Fails with a readable error if a
file, table or mapped column does not exist; the error lists the columns that are available.
Returns the counts (incidents, auto_fixed, no_action_needed, escalate), needs_a_person (the escalated
incidents), all_incidents (partition, problems, decision, proposed_fix, residual for each) and
outputs (paths to the report files). Pass outputs.folder and a partition to get_incident for the
full evidence behind one decision. Tell the user about every escalated incident.
Tool call template
Replace each placeholder with a value matching the input schema. Only required parameters are included; add optional parameters as needed.
{
"id": 1,
"jsonrpc": "2.0",
"method": "tools/call",
"params": {
"arguments": {
"db": "<db>",
"table": "<table>",
"ref_table": "<ref_table>",
"manifest_table": "<manifest_table>"
},
"name": "patrol_duckdb"
}
}Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| db | Yes | Path to the DuckDB database file. Opened read-only. | |
| key | No | Column in the fact table that uniquely identifies a row (the primary key). | txn_id |
| date | No | Date column the fact table is partitioned by, one partition per calendar day. The control totals must have a column with the same name. | txn_date |
| table | Yes | Fact table to check, optionally schema-qualified, for example gold.payments. | |
| amount | No | Numeric amount column in the fact table. It is compared with the 'amount' control total. | amount |
| out_dir | No | Folder to write the report files into. Created if missing; report files already there are overwritten. Omit to use a new temporary folder; its path comes back in outputs.folder. | |
| ref_key | No | Column in the reference table that the fact table's reference column must match. | customer_id |
| segment | No | Column that splits each day into segments, for example channel or region. The control totals must have a column with the same name, where '*' means the whole day. | channel |
| category | No | Optional categorical column in the fact table. Omit if the table has none. | |
| ref_table | Yes | Reference table that fact rows point to, for example customers. | |
| reference | No | Column in the fact table that points to the reference table (the foreign key). | customer_id |
| threshold | No | Gate threshold: the largest relative gap to the control totals that a verified fix may leave and still be marked auto-fixed (0.01 = 1%). Lower is stricter. Omit for the benchmark-calibrated default, 0.0135. | |
| manifest_table | Yes | Table of the source system's control totals, one row per date and segment, with columns: the date column, the segment column ('*' = whole day), n (row count) and amount (sum). |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| note | Yes | ||
| table | Yes | ||
| outputs | Yes | ||
| escalate | Yes | ||
| incidents | Yes | ||
| auto_fixed | Yes | ||
| rows_checked | Yes | ||
| all_incidents | Yes | ||
| gate_threshold | Yes | ||
| needs_a_person | Yes | ||
| no_action_needed | Yes |