Lookups in a Data Load: Cached, Uncached and Wrong

The lookup data load step translates a source key into the reference value your target needs. A fast lookup is useful only when its matches, misses, and cache rules preserve the intended rows.

Define What a Match Means

A lookup often maps a customer code to a warehouse surrogate key. The business key should identify the intended reference row under a clear rule. Historical dimensions may also require an effective date.

Do not begin with cache settings before checking reference uniqueness. If several reference rows qualify, different implementations can multiply rows or choose an unintended match. That is a modeling problem before it is a performance problem.

CREATE TABLE #Dimension
(
    CustomerKey int PRIMARY KEY,
    CustomerCode nvarchar(20) NOT NULL
);
CREATE TABLE #Incoming
(
    SourceRowId int PRIMARY KEY,
    CustomerCode nvarchar(20) NULL
);
INSERT #Dimension VALUES (10, N'A'), (20, N'B');
INSERT #Incoming VALUES (1, N'A'), (2, N'C'), (3, NULL);
SELECT CustomerCode, COUNT_BIG(*) AS matches
FROM #Dimension
GROUP BY CustomerCode
HAVING COUNT_BIG(*) > 1;

The example uses a deliberately small reference set. Add the actual uniqueness constraint when the business rule permits it. Do not rely only on a validation query that may run long before the lookup.

Understand the Full-Cache Snapshot

In SSIS full-cache mode, the reference set is loaded before the lookup processes input rows. This avoids a separate database lookup for every incoming row. It also requires enough memory for the selected reference data.

That cache represents what was loaded at its initialization point. A new dimension member inserted afterward does not automatically appear in the existing full cache. Decide whether the package should see a fixed snapshot or refreshed reference data.

Select only the columns and reference rows the lookup needs. A large unused payload consumes memory without improving matching. Include cache-loading time when measuring the overall package.

Know the Partial and Uncached Tradeoffs

Partial caching retrieves reference results as needed and keeps selected results in memory. SSIS can also cache misses when configured. That can reduce repeated queries but preserve a missed result after the reference data changes.

No-cache mode queries the reference source without retaining a lookup cache. That can add database calls and latency. It still does not guarantee a business-consistent view across the whole load without an appropriate source and transaction design.

Matching semantics also matter. SSIS full-cache comparisons can differ from database comparisons in case, trailing spaces, and numeric precision. Test representative keys when changing cache modes rather than assuming only speed will change.

Keep Unmatched Rows Visible

An inner join keeps matched rows and removes unmatched rows. That can make a load appear clean while losing incoming facts. Start with an explicit view of both outcomes.

SELECT i.SourceRowId, i.CustomerCode, d.CustomerKey,
       CASE WHEN d.CustomerKey IS NULL THEN N'No match'
            ELSE N'Matched' END AS lookup_status
FROM #Incoming AS i
LEFT JOIN #Dimension AS d ON d.CustomerCode = i.CustomerCode;

In SSIS, configure no-match handling explicitly and connect the chosen output to a meaningful destination. Do not assume the default silently discards rows, because behavior depends on the selected handling option. An ignored or unconnected route can still lose evidence.

SELECT i.SourceRowId, i.CustomerCode
FROM #Incoming AS i
WHERE NOT EXISTS
 (SELECT 1 FROM #Dimension AS d
  WHERE d.CustomerCode = i.CustomerCode);

Choose whether a miss fails the batch, enters quarantine, or maps to an approved unknown member. Record the reason and retain the source key. The right choice depends on whether the missing reference is invalid or merely late.

Normalize Only With an Agreed Rule

Trimming or changing case can help when the source contract says those differences are meaningless. It can also merge distinct identifiers when they are meaningful. Apply the same reviewed normalization to both sides.

Check type lengths and collations before blaming the cache. A truncated source key can match the wrong reference row perfectly. The lookup cannot recover information removed earlier in the pipeline.

SELECT SourceRowId, CustomerCode,
       LEN(CustomerCode) AS character_length_without_trailing_spaces,
       DATALENGTH(CustomerCode) AS storage_bytes
FROM #Incoming;

These two length measures answer different questions. Use them to investigate unexpected spaces and storage representation. Neither replaces a documented key format.

Reconcile the Whole Flow

Count incoming, matched, rejected, and deliberately deferred rows for the same batch. Check for multiplication as well as loss. A lookup with duplicate reference matches can increase totals instead of reducing them.

Measure cache initialization, database calls, and end-to-end duration in your environment. Keep correctness checks unchanged while comparing modes. The fastest configuration is useful only after every input row has an explained outcome.

A lookup is not just a quick match, it is a decision about every incoming row.

This post was rewritten from scratch in September 2026. The original, published on 2011-09-14, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.