Skip to main content

Debugging query results — Data 360 how-to

A number came back wrong, blank, or stale from a Data 360 query or Calculated Insight. The diagnostic is always the same — walk down the layers from the model to the query and find the first one that's broken. The query-results debugging playbook.

How-to·Last updated 2026-06-01·Drafted by Lira · Edited by German Medina

A number came back and it's wrong. Or blank. Or it's a clean number that happens to be yesterday's. A dashboard tile, a segment count, a Calculated Insight a segment reads, an export the Query API produced — the value is off, and nothing errored to tell you where. Query-layer bugs are silent the same way mapping bugs are: the query runs, the number returns, and it's just wrong. So the diagnostic is the same shape — walk down the layers from the model to the query itself, and the first layer that's broken owns the bug.

The layers

[ The MODEL — is the object/relationship even there? ]
        ↓
[ The MAPPING — did the value land in the DMO at all? ]
        ↓
[ The CI DEFINITION — right grain, right measure? ]
        ↓
[ The REFRESH — is the CI serving last run's number? ]
        ↓
[ The QUERY — right object, right count, all the pages? ]

Start at the top. Each layer assumes the one above it is sound, so a wrong answer at layer 4 is meaningless if layer 1 is broken. Walk down in order; stop at the first layer that's wrong, fix it there, and re-check below it.

Layer 1 — The model: is the object or relationship even there?

Start here, because nothing below matters if the path your query needs doesn't exist. The symptom is the sharpest of the five: not a wrong number but a query you cannot write, or a join that returns nothing because the traversal isn't modeled.

  • The check — does every object your number depends on exist as a DMO, and does every traversal between them correspond to a relationship someone actually modeled? An order-count-per-person needs the order object and a modeled path from the order to the resolved individual. A source object never joins straight to UnifiedIndividual__dlm; that path runs through the IndividualIdentityLink__dlm bridge identity resolution maintains.
  • The symptom — a JOIN that produces zero rows with no error, or a question you're told to answer that simply can't be expressed in SQL. An unmodeled join doesn't fail loudly; the path just isn't available. (See Query & Insights gotchas, gotcha 8.)
  • The fix — model the relationship, then the query can traverse it. This is a data-architecture change, not a query tweak — which is exactly why it's layer 1: discovering it last means everything downstream was built on a path that was never there.

If the model has the objects and the relationships, the path exists. Go down.

Layer 2 — The mapping: did the value land in the DMO at all?

If you can write the query but an attribute comes back blank or wrong for every row, the value may never have reached the DMO. This is the mapping layer, and it has its own full playbook — this step is the hand-off to it.

  • The check — read the attribute straight off the DMO for a record you know the answer for. Is it blank, or wrong, at rest — before any aggregation? If the raw DMO attribute is already wrong, the query is innocent; the value broke upstream.
  • The symptom — one attribute blank for every record (an unmapped field), or wrong for every record (mapped to the wrong attribute), or wrong for some records (a type mismatch on ingest). None of these is a query bug, and no amount of rewriting the SELECT fixes them.
  • The fix — drop into the mapping diagnostic and walk from the DLO up: debugging mapping failures is the layer-by-layer version, and mapping DLOs to DMOs is the surface it debugs.

If the DMO attribute is correct at rest, the data is there and harmonized. The bug is in how you're computing or reading it. Go down.

Layer 3 — The CI definition: right grain, right measure?

If the wrong number comes from a Calculated Insight, and it's consistently wrong — the same wrong value everywhere the CI is read — suspect the definition before anything else. A CI is dimensions and measures, not an arbitrary SELECT, and the two ways to get it wrong both produce a number that's wrong by design.

  • The check — read the CI's definition, not its output. What is it grouped by (the dimensions), and what does it compute (the measures)? Does the grain match the number you expected — one row per buyer, or per buyer per month? Is the measure the right aggregation — COUNT(DISTINCT …) where you meant distinct things, not SUM or a plain COUNT that double-counts?
  • The symptom — a number that's off by a consistent, structural amount: inflated because the grain is too fine and rows fan out, or wrong because SUM counted what should have been counted once. It's wrong identically for every consumer, which is the tell that it's the definition and not the read.
  • The fix — correct the dimensions or the measure in the CI definition. Because every segment, activation, and agent retrieves the same CI, one corrected definition propagates the fix everywhere — just as the wrong one propagated the error. (See Calculated Insights on grain as the decision.)

A consistently wrong CI is almost always layer 3. If the definition is right but the number is still off, the definition isn't the problem — the timing is. Go down.

Layer 4 — The refresh: is the CI serving last run's number?

If the CI's definition is correct but the number is stale — right for a moment in the past, wrong for now — you're reading a cached result. A CI is exactly as fresh as its last run, and between runs it serves the last value it computed, with nothing in the consumer to say so.

  • The check — when did this CI last run, and what's its cadence? Compare the last-run timestamp against the data you expected it to reflect. A daily-refreshed CI feeding a decision made hourly is serving a number up to a day old, and it looks current.
  • The symptom — the number was right yesterday, or matches a state the data has since moved past. Re-running the source query live (via the Query API) returns a different, current number than the CI serves — that gap is the staleness.
  • The fix — match the CI's refresh cadence to the freshest decision it feeds, and write the cadence down next to the CI. If the decision genuinely needs sub-hour reaction, that may mean a streaming CI or a live query instead of a batch recompute — but don't raise the cadence past what the decision actually consumes, because cost scales with what you reprocess. (See Calculated Insights on freshness.)

If the CI is fresh and correctly defined — or the number didn't come from a CI at all — the problem is in the query you ran. Go down.

Layer 5 — The query: right object, right count, all the pages?

The last layer is the query itself, and it has three distinct failure modes. By here the model, the mapping, and any CI are sound, so a wrong number is something the query is doing to correct data.

  • Wrong object — querying the DLO instead of the DMO, or a source object instead of the unified one. A DLO carries the source's raw shape and mess; query it and you inherit both. Worse and more common: counting a source-aligned object (ssot__Individual__dlm) when you meant resolved people (UnifiedIndividual__dlm), or vice versa. The check: read your FROM. Does that object hold what you think you're counting?
  • Identity-count mismatch — counting unified individuals when you meant source rows, or the reverse. Identity resolution merges source rows into one unified individual, so a count of UnifiedIndividual__dlm is people and a count of the source object is records — and the two will not reconcile, by design. Two dashboards disagree, both are "right" against different objects, and the afternoon goes to discovering that. The check: for this number, are you counting people or records, and is that the object in your FROM?
  • Pagination truncation — reading page one and stopping. The Query API paginates and goes async for large results. Code that reads the first response and stops is silently analyzing a fraction of the rows, with no error to flag it — the count looks plausible and is just smaller than reality. The check: does the caller loop to the end (v2: until done is true, following nextBatchId; Query Connect: poll the queryId, then page by offset/rowLimit), or does it read the first page and call it the answer? (See the Query API.)

A diagnostic you can run

When you suspect layer 5's identity-count mismatch, the fastest confirmation is to count both objects and see whether the gap is the one identity resolution should produce. Two single-object counts, compared by eye — no direct join:

-- Count resolved people. This is individuals AFTER identity resolution merged
-- duplicate source rows — expect this to be the SMALLER number.
SELECT COUNT(ssot__Id__c) AS unified_individuals
FROM UnifiedIndividual__dlm;
-- Count source-aligned rows, pre-resolution. Expect this to be LARGER than the
-- unified count: several source records collapse into one unified individual.
-- If these two numbers are equal, identity resolution isn't merging anything —
-- that's a Layer 1/2 finding (match rules or the source key), not a query bug.
SELECT COUNT(ssot__Id__c) AS source_rows
FROM ssot__Individual__dlm;

If unified_individuals is sensibly below source_rows, resolution is doing its job and your bug is simply that you counted the wrong one of the two — a one-line FROM fix. If the two are equal when you expected merging, the bug isn't here at all: it's the model or the match rules (layer 1), and you've just walked back up the stack to where it actually broke. The traversal that ties a source row to its unified individual runs through IndividualIdentityLink__dlm, never a direct join — but for a count comparison you don't join at all, you just count each object on its own.

Common symptoms mapped to layers

| Symptom | Likely layer | Where to look | |---|---|---| | JOIN returns nothing, or the question can't be written | Model | Object exists? Relationship modeled? | | One attribute blank or wrong for every row, at rest | Mapping | The DLO to DMO mapping (debug it there) | | Number consistently wrong, identically everywhere | CI definition | Grain (group-by) and measure (SUM vs COUNT(DISTINCT …)) | | Number clean but stale — yesterday's value | Refresh | CI last-run timestamp vs. cadence | | Two counts that won't reconcile | Query — identity | Unified individuals vs. source rows in FROM | | Export or integration missing most of its rows | Query — pagination | Does the caller page to the end, or read page one? |

Related

Reference: