Articles
DataStage Interview Questions: Partitioning, Lookup Failures, and Row Reconciliation
Practice DataStage Lookup interview questions with partition ownership, four failure policies, duplicate keys, and row reconciliation.
A DataStage Lookup interview answer is incomplete if it says only “join customer rows to a reference table.” In a parallel job, the reference row must be visible in the same partition as the source row; a missing match must follow the configured Continue, Drop, Fail, or Reject policy; and duplicate reference keys can multiply output rows when multi-row matching is deliberately enabled. Diagnose those three decisions before you reconcile counts or choose a restart.
This article uses an original nine-row customer-enrichment fixture. Official IBM facts support Lookup-stage behavior, partition placement, and the named failure policies. Editorial inference supplies keys, partitions, the row-count ledger, and an operational checklist. No candidate report proves that an employer asks this exact DataStage exercise.
Start with the job shape and the grain
Assume a Parallel job reads one row per incoming customer_id and adds a segment from a reference data set. It writes enriched rows to a target and can write unmatched rows to a reject link. The source and reference keys are strings after the same normalization rule. The relevant stage is IBM’s Lookup stage, which can use in-memory reference data, a Lookup File Set, or supported database lookups. It is not interchangeable with a Join or Merge stage: memory profile, sorting requirements, and unmatched-row behavior differ. IBM says ordinary Lookup input need not be sorted, while large in-memory references can page and degrade performance.
State the grain first: one source customer row is the unit to reconcile. Reference rows are lookup candidates, not additional source customers. If the business rule requires exactly one enriched output per source customer, a duplicated reference key is a data-quality problem even if the stage can be configured to emit two matches. If the business rule is one output per customer–segment association, row expansion may be intentional. Do not infer that from a green job status; make the cardinality requirement explicit.
The fixture routes nine source rows into three partitions with three rows each. The partition labels P0, P1, and P2 are editorial placeholders, not a promise about DataStage’s hash outputs for these IDs.
| Partition | Incoming customer keys | Reference situation |
|---|---|---|
| P0 | C01, C04, C07 | Three unique matches. |
| P1 | C02, C05, C08 | C05 missing; C08 has two reference rows. |
| P2 | C03, C06, C09 | Three unique matches. |
Assume the reference contains one row for each key except C05, plus two rows for C08, such as conflicting segments Retail and Enterprise. The exact names do not affect the count. This fixture is intentionally small enough to audit by hand and rich enough to expose two different failures: missing reference coverage and a violated uniqueness expectation.
Why a valid key can still look missing
IBM’s Lookup documentation states that the data being looked up must be in the same partition as the source row that references it. One design is Entire partitioning for the reference, making the reference available wherever source rows arrive. Another is compatible keyed partitioning of both sides by the lookup key. IBM’s memory troubleshooting guidance explicitly recommends hashing both source and lookup data on the lookup keys when a large Entire reference creates memory pressure. It also explains that physical memory segments and node placement make the footprint more nuanced than “one full Python dictionary per partition.”
A common wrong diagnosis is “C05 was rejected, so the reference table has no C05.” First inspect whether that key exists anywhere in the reference source, then where the compiled job placed it. If the main stream uses customer_id hash partitioning but the reference is randomly partitioned, the matching reference could be on another partition and the local Lookup instance would not see it. Conversely, if C05 is truly absent from a correctly placed reference, repartitioning cannot make it appear.
For a production-sized job, inspect the stage link partitioning settings, generated OSH or job plan, number of nodes, reference-load messages, key expression, data types, case sensitivity, and normalization. Compare sample keys after transforms such as trim, case fold, and null handling. A partition problem and a key-mismatch problem can produce the same visible “no match.” State the two hypotheses before deciding to repartition.
Entire is simple for a small reference but can become expensive. IBM’s support page discusses memory-map failures and notes that the reference must be prepared before source rows are processed. Hash partitioning reduces reference volume per process when the source side uses the same key scheme, at the cost of reshuffling and configuration complexity. A sparse lookup against a database can avoid loading the whole reference in memory but adds query and database-capacity considerations. Choose based on measured reference size, access pattern, and execution plan; “always use hash” is as weak as “always use Entire.”
Four lookup-failure policies, four different row ledgers
IBM’s Lookup Stage Conditions offers Continue, Drop, Fail, and Reject for a lookup failure. For the single missing C05, Continue sends the source row onward with lookup fields null when nullable or defaulted otherwise, and continues later lookups. Drop emits no output for that source row. Fail stops the job with a fatal error. Reject sends it to a reject link. The same dialog also distinguishes “condition not met” from “lookup failure”; read the column configured for the actual branch.
Do not casually treat Continue as successful enrichment. If segment is null or defaulted and a downstream target silently maps it to Unknown, the job may be technically successful while the data-quality rule is violated. Conversely, Reject can be an intentional quarantine policy that allows valid rows to continue. Agree on an acceptable missing-key count and whether rejected customers must block downstream publication. IBM’s reject-link description does not itself decide the business threshold.
A row ledger for this fixture should distinguish source rows, matched source rows, output records, and reject records. Those numbers need not be equal. If the only anomaly were missing C05 and every other key unique, Reject would mean 9 inputs, 8 enriched outputs, and 1 reject. Once C08 can produce two reference matches, one input can produce two outputs, so the same policy yields 9 enriched output records plus 1 reject record. Reporting “all nine source rows loaded” from the output count alone would be false.
Duplicate reference keys and intentional multiplication
IBM documents that one in-memory reference link may be designated as legitimately returning multiple rows in the Lookup Stage Conditions window. Under this exercise’s explicit configuration—one reference link, multi-row matching enabled, two C08 reference rows—the C08 source row yields two enriched records. Seven other source rows match once, so the matched portion produces 7+2=9 output records. C05 then contributes according to the configured failure action.
Failure action for C05 | Enriched output records under stated duplicate configuration | Reject records | Job outcome |
|---|---|---|---|
| Continue | 10, including C05 with null/default lookup fields | 0 | Runs onward; quality needs review. |
| Drop | 9 | 0 | Missing source row disappears. |
| Reject | 9 | 1 | Quarantines C05; other rows continue. |
| Fail | No stable final count to assert | Depends on work before failure | Fatal stop; inspect committed downstream effects. |
The table is a conditional fixture prediction, not a universal DataStage runtime guarantee. If multi-row matching is not enabled, duplicate reference handling can differ; inspect stage settings and logs rather than promising arbitrary “first match wins.” If the business wants exactly one segment per customer, resolve C08 upstream with a documented key and tie-break rule, or reject conflicting reference rows. Merely selecting one without a deterministic order can make reruns disagree.
For a reference with slowly changing rows, the lookup key may need both customer ID and an effective-date condition, or an upstream filter for the current version. A duplicate can be a modeling error, an intended history, or a pipeline mistake. The DataStage stage option cannot infer which one. In an interview, ask for the required target grain and show how that answer changes the configuration.
Reconcile by key and branch, not just totals
Under the chosen Reject policy and enabled multi-row reference match, the source-side accounting is 9 = 8 source rows with at least one match + 1 unmatched source row. The output-record accounting is 9 enriched records = 7 single-match records + 2 C08 records. The reject side contains one C05 record. You cannot add output and reject counts and expect 9, because expansion made 10 emitted records from nine source rows. Instead, reconcile distinct source-row identifiers and separately reconcile produced records.
Keep a per-key diagnostic sample: source key and original row ID; normalized lookup key; source partition; reference partition or Entire placement; reference-match count; selected failure action; output-row IDs; reject reason. For C08, expect two output IDs tied to the same source row ID under the stated policy. For C05, expect none on the enriched link and one on the reject link. This is a usable test oracle. It helps distinguish a true missing key, a partition mismatch, a duplicate reference, and a dropped row that nobody noticed.
A target load adds another boundary. The Lookup output link count is not the same as target committed count. A downstream constraint, connector rejection, transaction rollback, or deduplication step can change it. Record link-level counts and target before/after counts, then reconcile by stable batch and row identity. On failure, ask whether the target already contains some rows and whether replay is safe. “The job failed, rerun it” is not a recovery plan when accepted rows may already be visible.
A concise diagnosis sequence for a failed job
First, locate the exact stage and partition reporting the problem. If the error is an allocation or mapping failure before source processing, investigate reference size, memory-map limits, and partition strategy. If rows flow but expected keys fail to match, check key normalization and co-partitioning. If counts rise unexpectedly, inspect duplicate reference keys and the multiple-row setting. If target counts differ from Lookup-link counts, inspect the downstream writer separately.
Then isolate a tiny fixture in a test job: one definitely matching key, one intentionally absent key, and one duplicate reference key. Run each failure policy and inspect link counts and row payloads. This verifies the configured behavior for the specific engine, stage type, and version. The IBM documentation establishes options; your run establishes that this job is wired the way you think it is. Label observations from a test job as observations, and do not generalize a local run into a universal rule.
Finally, state the production decision: small reference with predictable memory may justify Entire; a large reference may need keyed distribution or a different lookup approach; quality-critical missing keys may need Reject or Fail; duplicate keys need an explicit grain rule. Back the decision with key-level and partition-level evidence.
Fast answers to DataStage follow-ups
Does Lookup require sorted input? IBM says the ordinary Lookup stage does not require source or reference input sorted. Other stages and particular partition designs have different requirements.
Why can an existing reference key fail to match? A different local partition, a transformed key mismatch, a null/case/type issue, or a stale/filtered reference can each do it.
What does Continue do on lookup failure? It sends the row onward, with missing lookup fields null or defaulted as defined, and continues processing.
Can duplicate reference rows expand output? Yes when a supported in-memory reference link is configured to return multiple rows. Verify whether that is intended for the target grain.
Why do output plus reject counts exceed source count here? C08 expands into two outputs. Reconcile source-row identities separately from emitted records.