Articles

SAP BODS Interview Questions: Table Comparison, Change Operations, and History Preservation

Practice SAP BODS interview questions with row-by-row Table Comparison, operation-code traces, history-preserving changes, safe keys, and restart checks.

SAP BODS interview questions become clearer when you trace one row through the data flow. Table_Comparison detects differences and assigns operation codes; History_Preserving decides which changes create another version; generated keys and target loading options determine which stored rows are affected. A correct comparison is only one part of a correct historical load.

Explain the input contract, the row operation, and the target key before discussing performance. This guide uses an original four-key fixture to separate complete snapshots from incremental feeds, then follows a history-preserving change through the transforms.

Official SAP documentation supports the transform behavior. Our worked ledgers, failure scenarios and offline Python checks are interview exercises, not candidate reports or results from a running SAP environment.

What is the Table Comparison transform responsible for?

Official behavior: SAP's Table_Comparison data outputs reference describes a difference dataset with INSERT and UPDATE operation codes, plus DELETE when deletion detection is enabled. Unchanged input does not become an output row merely because it was read.

Do not confuse the comparison table with the final write. The transform establishes what differs from a selected comparison image. Subsequent transforms can change the operation or values, and the target configuration controls persistence.

For an interview, name the input business key, comparison columns, comparison table and any generated key. “Compare the source and target” leaves all four ambiguous. A business key identifies an entity; a generated key can identify one stored version of it.

The cited technical pages currently show a 2025 documentation version; the history walkthrough also has an explicit 4.3 version link. Check the documentation for the installed release when discussing an actual job. Do not substitute SAP HANA Smart Data Integration node settings for similarly named Data Services transforms.

Which rows change in a four-key example?

Our original fixture compares business key, region and contact code. Contact codes such as a1 are synthetic values, not real personal data. Assume one comparison row per business key for this first trace.

Business key	Comparison row	Incoming row	Expected comparison result
A	East, a1	East, a1	No output: compared values match
B	East, b1	West, b2	UPDATE: an existing key changed
C	Absent	South, c1	INSERT: a new business key
D	North, d1	Absent	Depends on input completeness and deletion detection

B is a change even if only the contact code changes, provided that column belongs to the Table Comparison comparison list. If you exclude it there, a downstream history transform cannot recover a change that never reached it.

Ask what constitutes a meaningful difference. An extraction timestamp that changes every run can manufacture updates if it is compared. Conversely, excluding a business-relevant field can hide a required update. Type conversion and normalization should follow a declared business rule; trimming every string without checking the meaning can erase real distinctions.

The output count in this fixture is two without deletion detection: B and C. It becomes three when D is a legitimate detected deletion. Four source/target states do not imply four output records, and a later history step may increase the write count again.

When does an absent row mean DELETE?

Official constraint: the Table_Comparison editor options say that deletion detection assumes the input represents the complete dataset. The option is off by default. With it enabled, comparison rows missing from that input can be flagged DELETE.

Engineering inference: absence in a partial extract is not deletion evidence. In the fixture, if A, B and C are merely today's changed records, D may still exist unchanged in the source. Treating D as deleted would create a false retirement.

Clarify completeness for the comparison scope, not just for the job name. A snapshot of one region is incomplete relative to a comparison table containing every region. A successfully downloaded file may still be incomplete if upstream extraction stopped early. An empty complete snapshot and an empty incremental batch therefore require different interpretations.

Before enabling deletion detection, describe the evidence for completeness: extract completion, expected partitions, snapshot boundary and reconciliation checks. If the feed carries explicit tombstones, discuss how those events become operations instead of inferring deletion from absence.

A useful follow-up is: “What happens when the source file is empty?” A cautious design validates its declared scope before retiring every comparison row. This is an input-contract decision, not a reason to disable all deletion processing permanently.

Why are there two comparison-column decisions?

Table Comparison asks whether an incoming row differs. History Preserving asks whether the relevant difference deserves another version. These decisions can use different column sets.

Official example: SAP's history-preservation walkthrough distinguishes a history-preserving field from a field updated in place. It also warns that the key used for non-history updates can affect either all versions or only the latest version.

For our fixture, suppose region changes must retain history, while contact corrections should overwrite the intended current version. Compare region and contact in Table Comparison, but use region in History Preserving's history comparison list.

Incoming change for B	Table Comparison	History policy in this exercise	Planned stored-row effect
Contact b1→b2 only	UPDATE	No historical region change	Update the intended existing version
Region East→West	UPDATE	Historical change	Close the old version and insert the new one
Region and contact both change	UPDATE	Historical region change	New version has West and b2; retain old version's prior values

This table is a declared modeling policy, not a universal rule that contact information must be Type 1. State which fields the business needs to analyze as of a historical date. Replacing the field names without considering that requirement can change the correct flow.

Trace a historical UPDATE through the data flow

Official transform behavior: the History_Preserving output example describes INSERT output for a history-compared change, with an UPDATE for the previous version when flag/date options are configured. Non-history changes can remain UPDATE operations.

Our original trace gives B's current row generated key 21 and an effective start of September 1. On October 4, the incoming region becomes West. Configure current flags and valid dates consistently with the chosen history policy.

The planned old-version operation targets key 21, retains East and b1, resets its current flag, and closes its interval. The new-version operation inserts West and b2 with a new generated key, a current flag, and the new start date. Closing the old row must not overwrite its historical region with West.

Official key generation: SAP's Key_Generation reference describes adding generated key values for INSERT rows, including new versions created by History Preserving. A new business entity and a new historical version both need a distinct stored-row identity.

Explain the target's update key explicitly. Updating by B's business key when multiple versions exist can affect more rows than intended. Generating a fresh key for the old-version closure would be equally wrong: that operation must identify the existing stored row.

How should valid dates and deleted history behave?

Official settings: the History_Preserving editor options support using the new record's valid-from date for the old record's valid-to, or using the preceding day. They also provide a setting to preserve DELETE rows as UPDATE rows.

Modeling inference: document the interval convention alongside downstream joins. For half-open timestamp intervals [start, end), an October 4 change can close the old version at the same instant the new one begins. For inclusive whole-day intervals, the old record might end on October 3. Subtracting one day is not a general solution for sub-day events.

For a deleted entity, history preservation normally means retiring its current version while retaining past facts, if that is the declared business policy. Do not describe an operation tagged DELETE as proof that physical rows were removed. Trace its mapping and the target behavior.

SAP's additional guidance on deletes highlights a recreation trap: identical business values can conceal that a previously retired entity became current again. Its example includes the current indicator in comparison. Explain how your design distinguishes recreation from an unchanged retired row.

Where can Map Operation and target settings break the plan?

Official mapping behavior: Map_Operation options allow input row types to be mapped to output row types. NORMAL rows loaded to a target create new rows; NORMAL does not mean “unchanged, ignore it.”

Our diagnostic question is: “Which operation arrives at each transform and at the target?” A generic mapping to NORMAL may change the meaning of an intended update. A mapping that drops an operation can make the difference count look correct upstream while leaving the target incomplete.

Do not flatten operation codes merely to make an intermediate transform accept the data. Draw the required behavior first, then check the transform's accepted input and output types for the installed version. Key handling, target loading options and database constraints need review together.

For failure recovery, cut the fictional load between closing key 21 and inserting the new version. The target now has no current B. In the opposite partial state, it may have two current versions. Restart safety requires reconciliation and a defined commit/replay strategy; the presence of History Preserving alone does not make the job atomic or idempotent.

Compare correctness before choosing a faster method

The Table Comparison editor documents row-by-row lookup, cached comparison and sorted-input approaches. Consider the relative sizes of the incoming batch and comparison table, memory capacity and the work required to sort. “Cached is fastest” is not a complete answer.

For a small delta against a large comparison table, per-row access may avoid reading everything. For a broad snapshot, a cache may reduce repeated database access but needs a realistic memory budget. Sorted input depends on compatible key ordering. These are selection considerations, not measured throughput claims for your environment.

Our offline model checks only the fixture's absence rule:

def classify(source, target, full_snapshot=False):
    result = {}
    for key, values in source.items():
        if key not in target:
            result[key] = "INSERT"
        elif values != target[key]:
            result[key] = "UPDATE"
    if full_snapshot:
        for key in target.keys() - source.keys():
            result[key] = "DELETE"
    return result

Five executed assertions cover the complete and incremental fixture, an unchanged replay, and both interpretations of empty input. This is a Python semantic model; it does not implement SAP null comparison, duplicate handling, generated keys or database writes. Validate those separately in a version-matched test job.