How to Query ServiceNow Data with AI When the Join Isn't a Foreign Key
DRAFT. Incident extends task, so the join between them is not a foreign key. Introspection has nothing to infer and the governance analyzer never sees the join.
Ask replicated ServiceNow for open Sev-1 incidents by customer and you get an answer. It's even the right answer. It just arrives with a warning attached:
This result wasn't produced entirely from your organization's approved definitions, so it may differ from your official reports. Use with care.
That warning is the interesting part. The SQL is correct, the numbers are correct, and the system is still telling you it can't vouch for them. Working out why leads somewhere specific about how ServiceNow stores data, and it isn't a quirk of one table. It's true of nine of them.
The question
"Which customers have the most open critical incidents?" It's the first thing a service owner asks once ServiceNow lands in a warehouse, and it's checkable: one row per company, one count, ordered.
Three tables answer it. incident.incident holds severity. task.task holds whether the record is still active. core.core_company holds the customer name.
What breaks
Here's the query. It runs against a ServiceNow estate replicated into Redshift. The numbers below come from a synthetic demo org we maintain for exactly this, so the company names are invented, but the schema, the join, and the governance output are the real thing:
SELECT cc.name AS company,
COUNT(DISTINCT CASE WHEN i.severity = 1 THEN t.sys_id END) AS open_sev1
FROM incident.incident i
JOIN task.task t ON i.sys_id = t.sys_id
JOIN core.core_company cc ON cc.sys_id = t.company
WHERE t.active = true
GROUP BY cc.name
ORDER BY open_sev1 DESC;It runs, and it returns exactly what you'd want:
| Company | Open Sev-1 |
|---|---|
| Helios Aerospace | 7 |
| Pinecrest Avionics | 3 |
| Maplewood Diagnostics | 2 |
| Norwood Machinery | 2 |
| Yellowstone Devices | 2 |
Look at the join on the second line, though. i.sys_id = t.sys_id. Not a foreign key pointing at a parent. The two tables are joined on the same primary key value, because an incident and its task are the same record.
That's the shape that breaks things, and it broke this exact query before anyone declared it. The live logs show this chain coming back flagged:
ungoverned_joins: [
{ from: "incident.incident", to: "task.task", reason: "undefined_relationship" },
{ from: "task.task", to: "core.core_company", reason: "undefined_relationship" }
]Two flags, two different causes, which is what makes this worth an article rather than a bug report.
Why it breaks
Inheritance isn't a relationship anything can read
ServiceNow's data model is built on table inheritance. incident extends task, and so do eight other things:
Nine children, one shared primary key, zero declared relationships.
Every one of those children shares its parent's sys_id. The parent carries the fields they all have (state, priority, active, assigned_to, opened_at, and about twenty more) and each child carries only what's specific to it. On incident, that's severity, incident_state, category, caller_id, resolved_at and a dozen others.
It's a good design. It's why you can report across every work item in one query without a union.
It also means there is no foreign key between incident and task. Point introspection at the replicated schema and it finds two tables, each with a primary key, and nothing declaring that they're related. There's no constraint to read, no column named task_id, nothing to infer from. The relationship exists in ServiceNow's application metadata, and replication doesn't bring that along.
So the governance analyzer never sees the join. Not because it's wrong, but because nothing told it the join exists.
The second flag is the ordinary kind
task.company points at core.core_company through a real foreign key. That one introspection can see. It was simply missing from the model's relationships: block.
Worth separating, because the fixes are different. One is a declaration nobody could have inferred. The other is a declaration somebody skipped.
The failure mode is a warning, not a wrong number
This is where ServiceNow differs from a fan-out problem. When a Salesforce query joins across a one-to-many and sums the parent's amount, you get a confidently wrong number and nothing tells you. Here the number is right, and the system says it can't stand behind it.
That sounds milder. It's arguably worse in practice, because an ungoverned answer that happens to be correct today teaches the team to ignore the warning. The next one won't be correct.
The fix, named
The declaration that closes the first gap is not a special inheritance feature. It's an ordinary relationship, saying out loud what no constraint implies:
- from_table: incident
from_column: sys_id
to_table: task
to_column: sys_id
relationship: one_to_one
description: incident extends task; the two rows share a sys_idOnce that's in the model, the analyzer knows i.sys_id = t.sys_id is a sanctioned path rather than an unexplained equality between two primary keys. Adding the task.company foreign key closes the second.
Run the same SQL against the model with both declared, and the governance block comes back like this:
governed_joins: [
{ from: "incident.incident", to: "task.task", on: "i.sys_id = t.sys_id" },
{ from: "core.core_company", to: "task.task", on: "cc.sys_id = t.company" }
]
ungoverned_joins: []That's the state the model is in today, and it's locked in by a regression test: if either relationship is removed, the test fails with the join chain named. Building it, and the choices inside it, is the next post in this series.
One thing that fix does not cover, and the reason the warning at the top of this article is still there: the metric itself. COUNT(DISTINCT CASE WHEN i.severity = 1 THEN t.sys_id END) is still hand-rolled SQL rather than a definition anyone signed off. The joins are governed; the number isn't. Those are separate declarations and they fail separately.
Reproduce it yourself
You don't need a warehouse to see the shape.
- Get a Personal Developer Instance. Free, full admin, pre-loaded with demo data. It's reclaimed after ten days of inactivity, so do this when you have an hour rather than bookmarking it.
- Open any incident record, then look at the same record through the
tasktable. Samesys_id, different visible fields. That's the inheritance, in the UI. - List what extends task. In the platform's table configuration, filter tables by their parent. You'll get the nine above.
- Check for a foreign key between them. There isn't one. That's the whole article in one observation.
If you've already replicated ServiceNow into a warehouse, the faster version: introspect it and read what came back. Count how many relationships were inferred between incident and task. The answer is zero, and now you know why.
What carries over
The specifics here are ServiceNow. The shape isn't.
Every enterprise application encodes relationships somewhere its schema can't express. Salesforce hides meaning in __c custom fields whose names say nothing. NetSuite uses a polymorphic transactions table where the row's type decides what the row means. ServiceNow puts the relationship in application metadata that replication leaves behind.
In each case introspection does what it can, then stops at the boundary of what the schema actually states. The rest is a declaration somebody has to write down once.
Bring us the join your schema doesn't declare.
agami-core is source-available. Point it at your replicated ServiceNow, and see which relationships come back inferred and which come back empty.
Get agami-core or tell us which report nobody trusts →
Frequently asked questions
Why can't introspection just detect the inheritance? Because the schema doesn't record it. In a replicated warehouse, incident and task are two ordinary tables that happen to share primary key values. The parent-child relationship lives in ServiceNow's application metadata, which replication tools don't carry across. There's nothing in the target database for introspection to read.
Does a database view fix this? It fixes the query, not the governance. A view can pre-join incident and task so analysts don't have to, and plenty of teams do exactly that. But the view is another object that has to be described before anything can reason about what it means, and it multiplies once you want the same treatment for problem, change, and the request tables.
Is this specific to Redshift? No. The example here runs on Redshift because that's where the replica we tested against lives, but the cause is upstream of the warehouse. Snowflake, BigQuery and Postgres all receive the same two tables with the same missing relationship.
How many of these declarations will I need? Nine, for the task hierarchy, plus one more if you use hardware assets, which extend alm_asset the same way. It's a bounded list you write once, not per-query work.
