# Dr Anomalies

> Detect data anomalies in one Datarails Finance OS table and answer IN CHAT — severity-ranked outliers, duplicates, missing/null rates, rare values — scoped to the latest complete fiscal year. Writes no file (use the anomalies-report skill for an Excel workbook; use the profile skill for unscoped whole-history statistics). The MCP profiling and aggregation tools return baseline aggregates only (raw rows come from the separate get_data_by_* calls); this skill computes the findings client-side.

- Skill: `datarails/dr-anomalies` (Agent Skill)
- Install (CLI): `npx skillmds@latest add datarails/dr-anomalies`
- Raw SKILL.md: https://api.skillmd.com/api/skills/datarails/dr-anomalies/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Datarails (https://skillmd.com/u/datarails)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/datarails/dr-anomalies

---


# Datarails Anomaly Detection

Find data quality issues in Finance OS tables. The MCP server's
profile tools are deliberately thin — they return baseline
aggregates and nothing more. **This skill computes the actual findings
(outliers, severity, duplicate counts, null rates) by post-processing
those aggregates.** Be explicit about which numbers came from the tool
versus which the skill derived.

## Tool reality check

Before designing the analysis, read what each tool actually returns:

| Tool | What it returns | What it does NOT compute |
|---|---|---|
| `profile_numeric_fields` | `SUM, AVG, MIN, MAX, COUNT` per numeric field — in the backend-native `DR_Values`/`col_keys`/`row_keys` layout, no per-value aggregator labels | median, std dev, percentiles, outlier flags, null counts |
| `profile_categorical_fields` | distinct-count + first 10 sample values per field (capped at 5 fields; **bare calls default to upload/mapping metadata columns — always pass `fields`**) | per-value frequency, null counts, uniqueness ratio |
| `start_aggregation_by_id` / `…_by_alias` → poll `get_aggregation_result_by_id` / `…_by_alias` | grouped totals with no row limit | anything not expressible as GROUP BY + aggregation |
| `get_data_by_id` / `…_by_alias` | raw rows (≤500/page) with value-list **and** advanced filters | — |

For severity, percentiles, std dev, z-scores, and most named anomaly
categories below — the skill computes them, not the tool. There is no
server-side anomaly tool; everything is derived here.

## Workflow

### Step 1: Verify Authentication

If any tool call fails with an authentication or connection error, guide
the user to connect via the Connectors UI ("+" → Connectors → Datarails →
Connect).

### Step 2: Gather baseline aggregates

1. `get_fields_by_id(table_id)` — field ids, names, types. (If you only have
   a name/alias, resolve the table via `list_data_models` first.)

> **Async fetch — aggregations and distinct values run as start → poll.** `start_aggregation_by_id`/`_by_alias` and `start_distinct_values_by_id`/`_by_alias` take the same arguments as the retired blocking calls (dimensions/metrics/filters; table id + field id, or alias + field alias) and return immediately with `{"status": "pending", "handle": {...}}`. Echo that `handle` back verbatim to the matching `get_aggregation_result_by_*` / `get_distinct_values_result_by_*` tool: a `{"status": "running", "retry_after_seconds": N}` response means poll again with the same handle after ~N seconds (≈5s) — it is not an error, and large jobs may take several polls; when ready, the result arrives in the familiar shape (for distinct values, pass `limit` to the result tool). An expired/unknown-handle error means restart with the `start_*` tool. *Transitional fallback:* if the `start_*` tools aren't available on the connector (older server), the blocking twins `get_aggregated_data_by_*` / `get_distinct_values_by_*` still work with the same arguments.

> **Data-scope discovery — run before any aggregate (reuse anything already discovered this conversation).**
> 1. **Scenario domain.** Pull distinct values of the scenario field (`start_distinct_values_by_alias`/`_by_id` → poll the matching result tool) — never assume a scenario name exists (`Budget` frequently doesn't; many orgs carry only `{Actuals, Forecast}`). For budget/plan questions, if no budget-like scenario exists, look for a planning-version-like field (alias/name matching `/plan|version|cycle|budget/i`) and use its versions as the plan side; if neither exists, say so and offer a comparison across the scenarios that do exist.
> 2. **Account grain.** Pull distinct values of each account-hierarchy level field (L0/L1/L2-like). Use the level whose values partition P&L flows into revenue/COGS/opex-like buckets — on many orgs the top level is the balance-sheet equation (ASSET/LIABILITY/EQUITY/INCOME) and P&L line items live one level deeper. For P&L work, scope to P&L flows and exclude balance-sheet buckets; never present asset/liability/equity totals as revenue or expenses.
> 3. **Period scope.** Discover the date field's range (distinct values of the reporting-month field, or MIN and MAX in two separate calls — one aggregation per field per call). Default every P&L question to the latest complete fiscal year (or trailing 12 closed months) — never an unscoped all-time total: financials tables are multi-year cumulative and mix balance-sheet stock with P&L flow. **Label every output with the period + scenario it covers.**
> 4. **Reading GROUP BY responses.** Each response returns **exactly one row per requested group** — no subtotal rows and no grand-total row mixed into the `data` list; grand totals arrive in a separate top-level `totals` field beside the rows (`{"data": [...], "totals": {...}}`), computed across **all** groups, not just the returned prefix. **For a grand total, read `totals` — never sum the rows when the response carries `truncated: true`** (summing the returned prefix silently under-counts; dev repro: 474 of 31,455 rows summed to 21% of the true total). **`totals` combines the per-group results rather than re-scanning the rows**, so it is exact exactly when the aggregation is decomposable: SUM (sum of the group sums), COUNT (sum of the group counts), MIN, and MAX. It is **WRONG for AVG** (unweighted mean of the group averages) and **COUNT_UNIQUE** (sum of the per-group distinct counts, so a value recurring across groups is counted once per group) — true average = SUM total ÷ COUNT total (two calls: a field may be aggregated at most once per request); true distinct count = the distinct-values tools. Treat every aggregation type not named exact above — **`UNIQUE_VALUES` included**, whose cross-group de-duplication is unverified (the `COUNT_UNIQUE` behaviour above is evidence the engine may not de-duplicate across groups at all) — as not decomposable: derive it from complete rows or the distinct-values tools, never from `totals`. `totals` is absent on dimension-less aggregations (the single returned row IS the total) and may be absent on responses cached before the rollout (cache TTL ≤ 7 days) — only in those two cases is a total obtained by summing complete (untruncated) rows. Null groups arrive explicitly labeled `[null]` and are real groups; read null counts from that bucket. **Defensive filter:** keep only rows in which **every requested dimension key is present** — a roll-up row *omits* one or more keys entirely, whereas a genuine null is *present* with the value `[null]`. On a correct response this is a no-op; it guards against a stale cached response still carrying legacy subtotal and grand-total rows, each of which equals the whole total and would inflate any sum. When COUNT-ing rows per group, aggregate a different field than the GROUP BY dimension itself — a same-field COUNT of the grouped dimension can 500.
> 5. **Truncated results.** Any data tool may return `{"data": [...], "truncated": true, "total_rows": N, "returned_rows": M, "guidance": "..."}` when the result exceeds the response size limit (~50 KB). The `data` prefix is **incomplete** — never compute totals, shares, or trends from it, and never present it as the full result. On aggregations the top-level `totals` field is **unaffected by truncation** (computed across all groups, not just the returned prefix) — read grand totals from it instead of re-fetching. Narrow the query (fewer dimensions, more filters, fewer selected columns — or a business metric for a named KPI) and re-fetch **only when the rows themselves are needed** beyond the cap; with `totals` present, a SUM/COUNT/MIN/MAX grand total never requires a re-fetch or chunking by dimension (AVG, COUNT_UNIQUE and UNIQUE_VALUES never read `totals` — true average = SUM total ÷ COUNT total from two calls; true distinct count = the distinct-values tools). A truncated response **without** `totals` (pre-rollout cache) cannot answer a grand-total question from its prefix. Re-run the aggregation **once** — a fresh run may miss the stale entry and return `totals`. If the re-run still carries no `totals`, stop re-running and fall back to narrowing or chunking by dimension until the responses are complete, then sum those rows. Never total the prefix.

2. `profile_numeric_fields(table_id)` — SUM/AVG/MIN/MAX/COUNT per
   numeric field. **Reading the response:** it arrives in the
   backend-native `DR_Values`/`col_keys`/`row_keys` layout — values can
   appear duplicated per stat and carry no aggregator label. Map each
   value to its statistic via the keys before labeling it; never present
   a number as MIN/MAX/AVG/COUNT without confirming its key. If the
   mapping is ambiguous, anchor it by cross-checking ONE field with
   `start_aggregation_by_id` (MIN and MAX in two separate calls — one
   aggregation per field per call) → poll `get_aggregation_result_by_id(handle)`
   until ready (async-fetch pattern) before deriving outlier bands.
3. `profile_categorical_fields(table_id, fields=[...])` — pass an
   **explicit `fields` list of business dimensions from the discovered
   schema** (account levels, scenario, entity/department-like, date).
   Omitting `fields` profiles the table's upload/mapping metadata
   columns (tab/label/user/mapper-style bookkeeping fields) instead of
   business data — never call it bare. The tool also silently caps at 5
   fields. Use this to plan rare-value checks; the actual per-value
   counts come from step 4.
4. For each candidate-key or category field: `start_aggregation_by_id`
   with that field id as the dimension and `COUNT` of a **different**
   dense field — e.g. the discovered amount field — as the metric
   (`metrics=[{"field_id": <amount_field_id>, "agg": "COUNT"}]`) →
   poll `get_aggregation_result_by_id(handle)` until ready (async-fetch
   pattern). Never
   COUNT the same field you group by — that pattern can return a 500.
   This is the only way to get per-value frequencies and null counts.

### Step 3: Compute findings client-side

**First, normalize each GROUP BY response once.** Build a `valid_rows`
collection by keeping only rows in which **every requested dimension key is
present** (data-scope preamble, item 4), preserving genuine `[null]` values —
those are real groups. On a correct response this keeps everything; during the
stale-cache window it drops legacy roll-up rows that would otherwise inflate
the missing-value denominator, always satisfy `COUNT > 1` for duplicate
detection, and shift rare-category thresholds. **Every recipe below runs on
`valid_rows`,** and the total-rows denominator reads from the response's
top-level `totals` COUNT when present (exact even under truncation); when
`totals` is absent (stale pre-rollout cache — the same window that can carry
roll-up rows), it is the sum of `valid_rows` group counts from a complete
response.

For each anomaly category, apply the recipe below to the aggregates from
step 2. Scope every aggregate to the period from the data-scope preamble
(latest complete fiscal year or trailing 12 closed months by default) —
an all-time baseline mixes years and balance-sheet stock with P&L flow,
which pollutes AVG bands and frequency thresholds. Label each finding
with the period + scenario it covers.

**Range outliers (numeric fields)**
- From `profile_numeric_fields`: per field you have `MIN, MAX, AVG, COUNT` —
  key-mapped per the reading rule in step 2; never derive a band from a
  value whose statistic you haven't confirmed.
- Approximate spread = `(MAX - MIN)`; flag values outside
  `[AVG - k*spread/2, AVG + k*spread/2]` for `k ≈ 2`. This is a
  coarser substitute for the `|z| > 3` rule (true std dev isn't
  available).
- To surface specific rows you can now filter directly:
  `get_data_by_id(table_id, select=[...], filters=[{"field_id": <amount_id>,
  "values": {"type": "advanced", "val": [{"condition": "gt", "value":
  "<upper_band>"}]}}])` (or an `or`-chained `lt` for the low tail). Advanced
  comparison filters are supported — no need for bucketed aggregation just to
  find the rows.
- Severity heuristic: ≥100 rows or ≥1% of total outside the band →
  HIGH; ≥1000 rows or ≥10% → CRITICAL; otherwise MEDIUM.

**Missing values**
- From the `get_aggregation_result_by_id` GROUP BY result, the null group
  arrives explicitly labeled `[null]` — read the null count from that
  bucket only. The response carries **one row per group and no total
  row in the data** — the total-rows denominator is the top-level `totals`
  COUNT when present (exact even under truncation), else the **sum of all
  group counts** (including `[null]`) from a complete response — counting a
  legacy total row as one inflates null rates toward 100% and fakes a giant
  duplicate.
  Null rate = `[null]` bucket count ÷ that denominator. (Or filter
  directly with an advanced `is null` condition.)
- Severity heuristic: ≥10% null on a non-nullable field → CRITICAL;
  ≥1% → HIGH; <1% → LOW.

**Duplicates**
- Pick a candidate composite key (e.g. `[transaction_id]` or
  `[amount, vendor, posting_date]`). Call `start_aggregation_by_id`
  grouping by those field ids, with `COUNT` of a **different** dense field
  (e.g. the discovered amount field) as the metric, then poll
  `get_aggregation_result_by_id(handle)` until ready (async-fetch
  pattern) — never COUNT a field
  that is also a GROUP BY dimension. Client-side, filter groups where
  `COUNT > 1`.
- Severity heuristic: any duplicate of a primary-key field →
  CRITICAL; duplicates on a composite key → HIGH; near-duplicates
  (suspicious but plausible) → MEDIUM.

**Rare categorical values**
- From the per-field GROUP BY result, flag values whose frequency is
  below a small absolute threshold (e.g. `< 10` rows) or below
  `0.01%` of total rows (denominator = the top-level `totals` COUNT when
  present, else the **sum of all group counts** from a complete response,
  computed client-side). These are often typos, test data, or
  stale enums.
- Severity heuristic: usually LOW or MEDIUM unless the field is a
  required dimension.

**Future-dated or implausible dates**
- Filter the date field directly with an advanced range condition
  (`total_range` with epoch-second strings), or add the date field to
  `dimensions` in `start_aggregation_by_id` (poll
  `get_aggregation_result_by_id(handle)` until ready) and inspect the
  buckets for values beyond today or before a plausible earliest date.

**Out of scope for this skill**
- Referential integrity (would require joining tables, which the
  Finance OS API doesn't expose).
- Per-character data hygiene (trailing whitespace, casing) —
  requires raw-row scanning, which is capped at 500 rows/page.

### Step 4: Present findings

Organize the computed findings by severity:
- 🔴 **CRITICAL** — Requires immediate attention
- 🟠 **HIGH** — Should be addressed soon
- 🟡 **MEDIUM** — Worth investigating
- 🟢 **LOW** — Minor issues or informational

Always state which aggregates the finding was derived from so the user
can re-derive it manually if they want.

## Arguments

| Argument | Description |
|----------|-------------|
| `<table_id>` | Required — the table to analyze |
| `--severity <level>` | Filter results to a specific severity bucket |
| `--type <type>` | Filter to a specific anomaly category (see list below) |

## Anomaly categories handled

| Type | How the skill computes it |
|------|---------------------------|
| `outliers` | Range heuristic on `profile_numeric_fields` MIN/MAX/AVG (key-mapped from the `DR_Values` layout) |
| `missing` | `[null]` bucket from `start_aggregation_by_id` → `get_aggregation_result_by_id` GROUP BY ÷ total-rows denominator (`totals` COUNT when present, else summed group counts) |
| `duplicates` | `start_aggregation_by_id` → `get_aggregation_result_by_id` GROUP BY candidate key + COUNT of a different dense field, filter COUNT > 1 |
| `rare-category` | `start_aggregation_by_id` → `get_aggregation_result_by_id` GROUP BY field + filter COUNT < threshold |
| `temporal` | Aggregate by date dimension (or advanced date filter) + inspect for future/past-bound values |

## Example Interaction

**User: "/dr-anomalies 999999"**

The skill's response should look like this (illustrative — the table
name, id, fields, and figures below are invented; your org's will
differ) — but every number in it is something **the skill computed**
from the baseline aggregates, not a server-returned finding:

```
🔍 Anomaly Detection: GL Transactions (ID: 999999)
═══════════════════════════════════════════════════════════

Scope: FY2024 (latest complete year) | Scenario: Actuals
Scanned 125,000 records | Computed 47 findings

🔴 CRITICAL (3 findings)
───────────────────────────────────────────────────────────

1. DUPLICATE TRANSACTIONS
   • Derived from: aggregate(group_by=[transaction_id], COUNT(amount))
   • 23 transaction_id values appear ≥2 times
   • Examples: [45231×2, 67892×2, 89234×2, ...]

   💡 Recommendation: Review for accidental double-entry.

2. FUTURE-DATED TRANSACTIONS
   • Derived from: aggregate(group_by=[posting_date], COUNT(amount))
   • 5 buckets fall after today (max: 2024-12-31)
   • Fetch the affected rows with an advanced date filter on
     posting_date if needed.

3. HIGH NULL RATE: vendor_name
   • Derived from: aggregate(group_by=[vendor_name], COUNT(amount)) →
     [null] bucket ÷ total-rows denominator (totals COUNT)
   • 2,500 records (2.0%) have a null vendor_name while vendor_id
     is populated.

🟠 HIGH (12 findings)
───────────────────────────────────────────────────────────

4. AMOUNT RANGE OUTLIERS
   • Derived from: profile_numeric_fields(amount), stats key-mapped
     from the DR_Values/col_keys/row_keys response:
     MIN=-1,250,000  AVG=40,000  MAX=8,750,000  COUNT=125,000
   • Coarse band [AVG - (MAX-MIN), AVG + (MAX-MIN)]:
     115 rows above the band, 12 below.
   • Note: this is a range heuristic — true z-scores are not
     available because std dev isn't returned by the tool.

...

═══════════════════════════════════════════════════════════
📊 SUMMARY
═══════════════════════════════════════════════════════════

| Severity | Count | Action                    |
|----------|-------|---------------------------|
| Critical | 3     | Investigate immediately   |
| High     | 12    | Address this week         |
| Medium   | 18    | Plan for remediation      |
| Low      | 14    | Fix during maintenance    |

Data Quality Score: derived (computed from finding counts, not from
the MCP server).
```

## Investigation Workflow

1. **Review the computed findings** — every number traces back to a
   specific tool call; show that call when asked.
2. **Surface specific rows** — for outliers/duplicates, fetch them
   directly with `get_data_by_id` advanced filters, or aggregate with
   bucketed dimensions to get IDs and pass them to `/dr-query`.
3. **Verify business rules** — some "anomalies" are valid by
   policy.
4. **Document decisions** — note which findings are false positives.
5. **Create a remediation plan** — prioritize by severity.

## Related Skills

- `/dr-profile` — field statistics (same client-side computation pattern)
- `/dr-query` — fetch specific rows once you know the IDs
- `/dr-tables` — schema discovery + `start_aggregation_by_id` source