Skip to main content

Debugging All Contacts with SQL

Reconciling Marketing Cloud's All Contacts view against your Data Extensions — why a subscriber appears in one and not the other, status mismatches across channels, deletion-in-progress states, and the queries that surface each.

How-to·Last updated 2026-05-07·Drafted by Lira · Edited by German Medina

A contact appears in your Data Extension but not in All Contacts. Or the reverse. Or the same contact shows Active in your subscribers DE and Held in All Contacts. Or marketing thinks they unsubscribed someone and the DE still says active. The All Contacts view in Marketing Cloud is the canonical record of every contact across channels (email, SMS, push, web), and when it disagrees with a Data Extension you own, the DE is wrong — but figuring out how it diverged is the part that takes time.

This is the reconciliation playbook. Five queries that compare your DE against the canonical contact stores and surface where each contact's state actually lives.

What "All Contacts" actually is

It's a virtual aggregation across channel-specific contact tables. The relevant SDVs:

| SDV | What it holds | |---|---| | _Subscribers | The legacy email-only subscriber model. Status: Active / Bounced / Held / Unsubscribed. | | _ListSubscribers | The mapping between subscribers and Lists (legacy). | | _Bounce / _Unsubscribe | Channel-event records that drove the status changes. | | _EnterpriseAttribute (MC Connect) | Cross-BU shared attributes. |

The "All Contacts" UI joins these plus the Contact Builder model (which uses different underlying tables not always queryable via SQL Activity). For most reconciliation purposes, _Subscribers is the SDV you query — it's the email-channel canonical record.

Step 1 — Cross-reference your DE against _Subscribers

Find contacts in your DE that don't exist in _Subscribers (or vice versa).

-- In your DE but NOT in _Subscribers (the canonical email contact)
SELECT
  d.SubscriberKey,
  d.EmailAddress,
  d.Status AS YourDEStatus
FROM your_master_de d
LEFT JOIN _Subscribers s
  ON LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
WHERE s.SubscriberKey IS NULL;

-- In _Subscribers but NOT in your DE (typically the bigger problem)
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.Status AS CanonicalStatus
FROM _Subscribers s
LEFT JOIN your_master_de d
  ON LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
WHERE d.SubscriberKey IS NULL;

The LTRIM(RTRIM(CAST(...))) is mandatory — SubscriberKey is a string and the type-coercion + whitespace trap silently breaks the join otherwise (see JOIN, gotchas — #9).

The first query usually returns zero or a small set: contacts you added to your DE that haven't been processed by a Send yet (so they're not in _Subscribers). The second query returns the recovery list — contacts that exist in MC but your audience-build SQL is missing.

Step 2 — Status mismatch audit

For contacts that appear in both, find where the statuses don't agree.

SELECT
  d.SubscriberKey,
  d.EmailAddress,
  d.Status      AS DEStatus,
  s.Status      AS CanonicalStatus
FROM your_master_de d
INNER JOIN _Subscribers s
  ON LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
WHERE d.Status <> s.Status
   OR (d.Status IS NULL AND s.Status IS NOT NULL)
   OR (d.Status IS NOT NULL AND s.Status IS NULL);

Common patterns in the result:

| Your DE | _Subscribers | What likely happened | |---|---|---| | Active | Unsubscribed | The subscriber unsubscribed via a Send footer link; your DE wasn't refreshed | | Active | Held | Salesforce held the subscriber after multiple bounces; your DE missed the event | | Active | Bounced | Hard bounce on the canonical record; your DE has stale active status | | Inactive | Active | Marketing marked them inactive in your DE for business reasons; canonical record unchanged | | Active | NULL | Subscriber created in your DE but never sent to (so no _Subscribers row exists) |

The Held status is the one that surprises teams — it's MC's automatic suppression after the bounce-out threshold. If your audience DE doesn't filter out Held, you're sending to addresses MC won't actually deliver to.

Step 3 — Recent unsubscribes / bounces snapshot

Pull the events that drove status changes in the last 30 days, so you can correlate against your DE refresh cadence.

INSERT INTO de_log_contact_events
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  u.EventDate         AS UnsubscribedAt,
  NULL                AS BouncedAt,
  'unsubscribe'       AS EventType,
  GETDATE()           AS SnapshotAt
FROM _Unsubscribe u
INNER JOIN _Subscribers s
  ON u.SubscriberID = s.SubscriberID
WHERE u.EventDate >= DATEADD(day, -30, GETDATE());

INSERT INTO de_log_contact_events
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  NULL                AS UnsubscribedAt,
  b.EventDate         AS BouncedAt,
  CASE WHEN b.IsUnique = 1 THEN 'hard-bounce' ELSE 'soft-bounce' END AS EventType,
  GETDATE()           AS SnapshotAt
FROM _Bounce b
INNER JOIN _Subscribers s
  ON b.SubscriberID = s.SubscriberID
WHERE b.EventDate >= DATEADD(day, -30, GETDATE());

Then query the snapshot to find contacts whose status in your DE doesn't reflect a recent event:

-- Contacts who unsubscribed or bounced in the last 30 days but
-- your DE still has them as Active
SELECT
  e.SubscriberKey,
  e.EmailAddress,
  e.EventType,
  COALESCE(e.UnsubscribedAt, e.BouncedAt) AS EventAt,
  d.Status AS YourDEStatus
FROM de_log_contact_events e
INNER JOIN your_master_de d
  ON LTRIM(RTRIM(CAST(e.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
WHERE d.Status = 'Active';

Each row is a contact you're still trying to send to that MC won't actually deliver to. Update your DE's audience-build to honor these events.

Step 4 — BU and All Subscribers list checks

If you're in a multi-BU tenant, a contact may appear in the parent BU's _Subscribers but not in the child BU's view. Run the diagnostic from each BU context to compare.

The All Subscribers list status (Active / Bounced / Held / Unsubscribed) is the one that gates Send eligibility — if a contact is Held or Unsubscribed at the All Subscribers level, no Send will reach them regardless of what your DE says.

-- All Subscribers status for a specific list of SubscriberKeys
SELECT
  s.SubscriberKey,
  s.EmailAddress,
  s.Status         AS AllSubscribersStatus,
  s.DateUndeliverable AS BounceOutDate
FROM _Subscribers s
WHERE s.SubscriberKey IN (
  SELECT SubscriberKey FROM your_master_de
);

The DateUndeliverable column is non-NULL when MC has hard-bounced or unsubscribed the contact. Filter your audience by DateUndeliverable IS NULL if you want only deliverable contacts.

Step 5 — Reconciliation snapshot

Once you've audited, snapshot the diagnostic counts so the next time something looks off, you have a baseline.

INSERT INTO de_log_contact_reconciliation
SELECT
  GETDATE()                                AS SnapshotAt,
  'your_master_de'                         AS DEName,
  (SELECT COUNT(*) FROM your_master_de)    AS DERowCount,
  (SELECT COUNT(*) FROM _Subscribers)      AS CanonicalRowCount,
  (SELECT COUNT(*)
   FROM your_master_de d
   LEFT JOIN _Subscribers s
     ON LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
      = LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
   WHERE s.SubscriberKey IS NULL)         AS InDeNotInCanonical,
  (SELECT COUNT(*)
   FROM _Subscribers s
   LEFT JOIN your_master_de d
     ON LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
      = LTRIM(RTRIM(CAST(d.SubscriberKey AS NVARCHAR(255))))
   WHERE d.SubscriberKey IS NULL)         AS InCanonicalNotInDe;

Run this weekly (or daily for high-cadence sending). Big jumps week-over-week mean something changed in either the audience-build or the canonical record.

Common causes ranked by frequency

| Cause | Where to look | |---|---| | Audience-build SQL doesn't filter by Status from _Subscribers | Step 2 — large mismatch count | | Audience-build doesn't honor recent unsubscribe / bounce events | Step 3 — events newer than DE rebuild cadence | | Multi-BU: contact exists in parent BU but not child | Step 4 — query from parent BU context | | SubscriberKey cast / trim missing in the join (silent miscount) | Step 1 returns suspiciously many rows | | Audience DE rebuild cadence is slower than the canonical events | Compare DE last-rebuild timestamp vs Step 3 EventAt |

Remediation playbook

  1. Filter audience by canonical status. Add the join from your audience-build SQL to _Subscribers (with the trim/cast pattern), filter WHERE s.Status = 'Active' AND s.DateUndeliverable IS NULL.
  2. Snapshot recent events daily. Run Step 3 as a scheduled Automation that writes to de_log_contact_events, then your audience-build joins against the snapshot rather than _Unsubscribe / _Bounce directly (see FROM).
  3. Add reconciliation alerts. If InCanonicalNotInDe jumps more than X% week-over-week, fire a notification.
  4. For multi-BU: build the master subscriber DE at the parent BU level and propagate down via Shared Data Extensions.
  5. Document the contact lifecycle in your team's runbook — who marks contacts inactive in your DE, what triggers a status sync, when reconciliation runs.

Related

  • FROM — _Subscribers and other SDVs are unstable for production reads; snapshot first
  • JOIN — LTRIM(RTRIM(CAST())) for the SubscriberKey join
  • WHERE — filter by Status + DateUndeliverable IS NULL
  • INSERT INTO — writing the diagnostic snapshots
  • MC SQL gotchas — see #6 (SDV rotation), #9 (SubscriberKey casting)
  • Style Guide — the discipline that prevents repeat reconciliation drift
  • Debugging email sends + Debugging value length — the other two debugging snippets

Catalog complete: with this snippet, all 19 pages of the SQL section are shipped. The next layer of work is the SSJS, AMPscript, and Config subcategories — each of which will follow the same template (gotchas + reference catalog + debugging snippets) established here.