Why are deleted records a problem in a data warehouse?
When a record is deleted in a source system, most sources do not report it. The record simply stops appearing in the next load. Every layer downstream keeps the last version it received, with no signal that it no longer exists.
A delete reaches you as an absence, and an absence looks exactly like a late, filtered or incomplete load. Telling them apart takes knowledge of the source: what it guarantees to deliver on every load.
biGENIUS-X is a data automation platform: you model your data warehouse and other data solutions, and it generates the native code for your target technology. For delete detection, you record what you know about the source once, in the model, and biGENIUS-X generates the detection logic from it.
How does delete detection work in biGENIUS-X?
Delete detection is a property of a store entity: a table in the biGENIUS-X datastore, the layer that keeps source data historized. The property has four values. Set it to anything other than None, and biGENIUS-X adds three objects to the dataflow.
The three generated objects are:
- Delete detection loader: computes, on every run, the set of business keys that count as deleted.
- Delete detection table: holds exactly that set of keys.
- Delete detection view: merges the incoming data with that set and sets the
BG_IsDeletedflag (1 = deleted, 0 = not deleted).
The store entity's normal loader then historizes the view, so a delete arrives as an ordinary change carrying the flag. Nothing is physically removed. On an SCD2 entity (one that keeps every change as a new, dated version), you can answer not only "what did this customer look like on 1 March?" but also "did they still exist?".
The four methods differ in one place only: how the loader computes the set of deleted keys. Everything after that is identical.
Which delete detection method should you use?
Choose the method based on what your source reliably delivers on every load. Each method relies on a different guarantee from the source, a contract. If the contract holds, the method is correct. If it breaks, the method draws the wrong conclusion.
If a source loads incrementally (delivering only new or changed records) and reports nothing about deletes, no method can detect them. biGENIUS-X warns you when you configure delete detection on such an entity, so you find out at modeling time, not at reconciliation time.
None
None is the default: no flag, no extra objects, and deletes stay invisible. It suits sources that never remove records, or where a removal has no business meaning. It still affects everyone downstream, so document the choice.
FullSet
Contract: every load contains all currently valid business keys.
How it works: any business key the store entity already holds, but the current load doesn't contain, is flagged as deleted. biGENIUS-X generates a single set operation:
SELECT [CUSTOMER_ID] FROM [DS_SE_customer_Result]
EXCEPT
SELECT [CUSTOMER_ID] FROM [DS_SE_customer_Source] The result view (_Result) contains the keys the entity already holds. The source view (_Source) contains the keys in the current load. The difference is the set of deleted keys.
Risk: FullSet is only correct while the contract holds. After one incomplete load, such as a filtered extract or a job that fails halfway, the next run flags every missing record as deleted: real history rows for an event that never happened. Delete detection can't tell whether an absence is real, so guard the load with a data quality rule, such as a minimum row count.
Use it when: the source reliably delivers full extracts.
FullSet with a keep list
The list of currently valid keys does not have to arrive inside the data. You can model it as a separate object (a keep list) and set it as the entity's delete detection source. biGENIUS-X then flags any key that is not on the keep list:
SELECT [CUSTOMER_ID] FROM [DS_SE_customer_Result]
UNION
SELECT [CUSTOMER_ID] FROM [DS_SE_customer_Source]
EXCEPT
SELECT [CUSTOMER_ID] FROM [DS_SC_valid_customers_Result] The UNION adds the keys arriving in the current load to the keys already stored. This way, a key that arrives for the first time and is already missing from the keep list is caught in the same run.
This separates two things a single feed normally combines: the data can load incrementally, while a small list carries what still exists. That list is often much cheaper to get than a full extract, so sources that could otherwise only use None can support delete detection.
PartialSet
Contract: each load contains all valid business keys within a slice of the data.
Example: a budget table reloaded one fiscal year and one cost center at a time. FullSet would flag everything outside the loaded slice, which is nearly the whole table. DeletedSet doesn't apply, because the source isn't reporting deletes.
How it works: you select one or more attributes of the store entity (slice terms). biGENIUS-X then only checks stored records whose values for those attributes also appear in the current load.
This configuration generates:
SELECT [BUDGET_LINE_ID] FROM [DS_SE_sales_budget_Result] AS [Loaded]
WHERE ([Loaded].[FISCAL_YEAR] IN (SELECT DISTINCT [FISCAL_YEAR] FROM [DS_SE_sales_budget_Source]))
AND ([Loaded].[COST_CENTER] IN (SELECT DISTINCT [COST_CENTER] FROM [DS_SE_sales_budget_Source]))
EXCEPT
SELECT [BUDGET_LINE_ID] FROM [DS_SE_sales_budget_Source] In effect, this is FullSet restricted to the slice. The slice values come from the load itself, not from the settings. If a delivery covers three fiscal years, the slice covers three fiscal years, with no configuration change.
Operator: with more than one slice term, the operator decides how they combine. "And" narrows the check to records matching every term. "Or" widens it to records matching any term. This choice can change the number of flagged records more than any other setting, so state it in your model documentation.
Before choosing slice terms:
- Pick attributes whose values vary between loads. An attribute with the same values in every load, such as a status flag, effectively makes the slice the whole table.
- PartialSet limits the damage of an incomplete load, but it does not detect one. Nothing verifies that the source delivered the complete slice.
- Each slice term adds a scan of the source view on every run. On large entities with two or more slice terms, factor this into clustering and indexing.
PartialSet was introduced in biGENIUS-X release 2.2.0
DeletedSet
Contract: the source reports which records were deleted.
How it works: a separate model object carries the deleted business keys, for example from a CDC feed, a deletion log or an audit table. biGENIUS-X only flags keys that are on that list and are either already in the store entity or arriving in the current load:
(SELECT [CUSTOMER_ID] FROM [DS_SE_customer_Result]
INTERSECT
SELECT [CUSTOMER_ID] FROM [DS_SC_deleted_keys_Result])
UNION
(SELECT [CUSTOMER_ID] FROM [DS_SE_customer_Source]
INTERSECT
SELECT [CUSTOMER_ID] FROM [DS_SC_deleted_keys_Result]) DeletedSet is the only method that infers nothing from absence: it waits to be told. Keys on the list that were never loaded are ignored. If your source can report its deletes, it is usually the best choice.
Like the keep list, DeletedSet works with incremental loads. The difference: DeletedSet needs every deletion reported, the keep list needs every remaining record named.
DeletedSet with separate pipelines
If deletes and inserts arrive through separate pipelines, a delete can arrive before the insert it belongs to. In that run, the key isn't in the entity yet, so nothing is flagged. Whether the delete is picked up later depends on how you modeled the object that carries the delete list:
- Rebuilt on every run: the delete signal is lost permanently.
- Accumulating: the key is flagged on the first run after its insert arrives.
Accumulating lists have one catch. The deleted set takes priority over the load. As long as a key stays on the list, it is flagged as deleted on every run, even if it reappears in the source, and its new data never reaches the entity.
The solution is to hold each key's latest event rather than every delete ever recorded. A later insert then removes the key from the list. Decide which type of list you have before you rely on the feed.
What happens to a deleted record?
biGENIUS-X never physically removes a deleted record. It writes a tombstone row: the business key, BG_IsDeleted = 1, and every other attribute set to the project's standard placeholder for unknown values (the singleton value). The last known values are not copied. In generated SQL:
SELECT
1 AS [BG_IsDeleted]
,[CUSTOMER_ID] AS [CUSTOMER_ID]
,N'Unknown' AS [CUSTOMER]
,N'19000101' AS [CUSTOMER_SINCE]
,N'Un' AS [COUNTRY_ISO_CODE]
FROM [DS_SE_customer_DeleteDetectionTable] N'Un' is "Unknown" truncated to fit a two-character column. The placeholder always adapts to the column.
On an SCD2 entity, nothing is lost. The previous version holds the customer's last known state with its own validity period. The tombstone doesn't describe the customer. It records that the customer is gone, and since when.
On an SCD1 entity, there is one row per key and no versions. The placeholders overwrite the last known values, which are then gone. This is how SCD1 works, not a limitation of delete detection. If you need to know what a deleted record looked like while it existed, the entity has to keep versions (SCD2).
Why placeholders instead of the last known values?
A store entity only writes an incoming row when its row hash, a checksum of the descriptive attributes, differs from the stored one. The BG_IsDeleted flag is not part of that hash.
If the tombstone carried the last known values, its hash would match the stored row exactly. biGENIUS-X would see no change and write nothing, and the delete would silently disappear. The placeholders make the change visible to the mechanism that decides whether anything changed. This applies to both SCD1 and SCD2 entities.
What if a deleted record comes back?
With FullSet, the keep list variant and PartialSet, a returning key is no longer in the deleted set. It arrives with BG_IsDeleted = 0 and its real attributes. Its hash differs from the tombstone's, so on SCD2 it becomes a new valid version, and on SCD1 the real values overwrite the tombstone.
DeletedSet behaves the same way only once the key has been removed from the delete list.
What should reports show for a deleted record?
A query on the current row of a deleted key returns placeholder values. Whether a report should hide the record, show it flagged or show its last real version depends on the use case. That decision belongs in a layer above the datastore, which only has to provide the information to make it.
What to know before switching on delete detection
Availability
Delete detection is available on store entities in the datastore. It generates identically on all datastore target platforms: Databricks, Snowflake, Microsoft Fabric (Warehouse and Lakehouse), Microsoft SQL Server and Oracle. All of them build the logic from the same model.
Multi-version load
Delete detection can't be combined with multi-version load (loading several versions of a record in one run). biGENIUS-X rejects the combination.
At least one changeable attribute
The entity needs at least one SCD1 or SCD2 attribute. If every non-key attribute is SCD0, there is nowhere to write the delete, and biGENIUS-X rejects the model. The entity doesn't have to keep history. SCD1 is enough.
Full rebuild every run
The delete detection table is rebuilt in full on every run, never incrementally. On SQL Server, this is a truncate in its own transaction followed by an insert. On Databricks, it is a single INSERT OVERWRITE.
Enabling on an entity with existing history
The first FullSet or PartialSet run flags every key that has gone missing since the entity was built, all at once. Each of these deletes is dated to that run, not to when it actually happened. Plan for this and tell downstream users.
First load into an empty entity
The first load flags nothing. The exception is DeletedSet: a key that is already on the delete list arrives as a deleted row and never gets a real version.
Read more in our knowledge base: Configure delete detection.
Why detect deletes in the datastore rather than downstream?
biGENIUS-X detects deletes in the historized datastore because that is where the calculation is simplest and most reliable:
- It mirrors the source. A store entity is structurally equivalent to its source, which makes comparing loads straightforward.
- It holds the contract. The datastore is where you have a dependable agreement with the source about what it delivers. Without one, detecting deletes isn't worth the effort.
- Downstream layers are more complex. Later layers already join data from several sources, which makes it harder to decide what a delete means.
- Detect once, use everywhere. Once the store entity carries the flag, nothing downstream has to detect anything. A Data Vault satellite or a core dimension treats it as an ordinary attribute, and a data mart can simply filter on it.
Delete detection comes down to one question: what does your source reliably deliver on every load? Answer that, and the method follows: FullSet for full extracts, a keep list or DeletedSet for incremental loads, PartialSet for sliced reloads.
