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:

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

Anomaly categories handled

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