Skip to main content

History Tables Load Options

Data can be loaded into Fabric with different options. Full load is the most common, but can take a long time in case of a lot of data. So for those situations, it would be nice to load just a fraction of the data. This can be done in several different ways, often depending on what is possible in the source. This page explains the following situation:

  • Updating based on one or more column values

Updating Based on One or More Column Values​

This is the simplest way to partially update. The column value is usually something related to a period, for example, a year. The source, for instance, provides the current year and the year before. In that case, these rows are stored in the temporary bronze table and if keephistory is enabled, the following is done:

  • New rows are added to the table (just like in a full load)
  • Modified rows are applied to the table (just like in a full load)

The crux lies in the deletion. Here, the filter must be taken into account.

  • Only rows that no longer appear in the delivery and that meet the specified filter are deleted. Everything outside the filter remains intact.

Of course, this is not limited to years; it can also be a value such as a company column, for example. In the configuration of the object, this is recorded as follows:

  Table:
  Connection: exa-example
  SourceTable: example.csv
  DataPlatformObjectname: example
  KeepHistory: true
  HistorySettings: 
    BronzeDeleteFilterQuery: "SELECT deliverydate as deliverydate FROM his.exa_example WHERE YEAR(DeliveryDate) = 2025" 
  Columns:
  - SourceColumn: ExampleID
    SourceDataType: int
    IsPrimaryKey: true
  - SourceColumn: ExampleText
    SourceDataType: varchar(250)
  - SourceColumn: DeliveryDate
    SourceDataType: datetime    
  - SourceColumn: Company
    SourceDataType: varchar(100)

This query will result in all the dates that are already in the history table for the year 2025. Normally all rows that are not delivered by the source are expected to be deleted. But if the source only delivers a subset of the data, this will mean rows that should not be set to deleted, are deleted anyway. The BronzeDeleteFilterQuery query will return all DeliveryDate values in the current history table. Only the values in this set will be checked by the delete.

This is used as the join condition for the dataframe

df_delete = df_tgt.alias('tgt').join(df_tst.alias('del'), on=expr("tgt.deliverydate = del.deliverydate"), how="left_semi")

This will result in a subset of the history table, on this subset there is a second filter based on the primary keys that don't exist anymore in the delivered dataset.

The columns from the BronzeDeleteFilterQuery are automatically mapped as a join condition. The columns in this query must exist in the history table. The source of this query can be any table as long as the values can be compared with the history table. So a query like this one, is also valid:

SELECT date as deliverydate FROM dbo.dates WHERE YEAR(DeliveryDate) = 2025
The filter set must be able to contain keys the source no longer delivers

The landing table Bronze.dbo.<table> is overwritten with the current delivery before the history load runs. A BronzeDeleteFilterQuery that reads from the table's own landing table therefore returns exactly the keys of the batch that was just loaded. If its columns cover the full primary key, the delete scope equals the delivery, the second filter on the primary keys can never find a missing row, and no row is ever set to deleted.

Scope on a column that is not part of the primary key — a period, a location, a company — or read from Bronze.his., so that the set can hold keys the delivery is missing. Both examples above do this: the first reads the history table, the second an unrelated date table.

To never delete at all, use BronzeSkipDelete instead of a filter that returns an empty scope.

The two ends of the range behave as you would expect, and both are covered by a variant scenario. A filter that returns no rows produces no deletes at all — the run succeeds and the history is left untouched, so an empty scope is a silent no-op rather than an error. A filter that returns every key in the history table makes the delete scope equal to a full load: every row the delivery no longer carries is set to deleted. That is legitimate, but it means the filter is buying you nothing; leave it out if that is what you want.

Filter on multiple columns is also possible.

  Table:
  Connection: exa-example
  SourceTable: example.csv
  DataPlatformObjectname: example
  KeepHistory: true
  HistorySettings: 
    BronzeDeleteFilterQuery: "SELECT deliverydate as deliverydate, 'Fav' as company FROM his.exa_example WHERE YEAR(DeliveryDate) = 2025" 
  Columns:
  - SourceColumn: ExampleID
    SourceDataType: int
    IsPrimaryKey: true
  - SourceColumn: ExampleText
    SourceDataType: varchar(250)
  - SourceColumn: DeliveryDate
    SourceDataType: datetime    
  - SourceColumn: Company
    SourceDataType: varchar(100)

This query will result in all the dates that are already in the history table for the year 2025 and the value 'Fav' as an extra filter on the company field. So in this case all primary keys that don't exist in the source anymore for the company 'Fav' will be deleted. All other companies are out of scope.

This is used as the join condition for the dataframe

df_delete = df_tgt.alias('tgt').join(df_tst.alias('del'), on=expr("tgt.deliverydate = del.deliverydate and tgt.company = del.company"), how="left_semi")

This will result in a subset of the history table, on this subset there is a second filter based on the primary keys that don't exist anymore in the delivered dataset.

The columns from the BronzeDeleteFilterQuery are automatically mapped as a join condition. The columns in this query must exist in the history table. The source of this query can be any table as long as the values can be compared with the history table. So a query like this one, is also valid:

SELECT date as deliverydate FROM dbo.dates WHERE YEAR(DeliveryDate) = 2025

Skip the delete for Bronze completely

  Table:
  Connection: exa-example
  SourceTable: example.csv
  DataPlatformObjectname: example
  KeepHistory: true
  HistorySettings: 
    BronzeSkipDelete: true
  Columns:
  - SourceColumn: ExampleID
    SourceDataType: int
    IsPrimaryKey: true
  - SourceColumn: ExampleText
    SourceDataType: varchar(250)
  - SourceColumn: DeliveryDate
    SourceDataType: datetime    
  - SourceColumn: Company
    SourceDataType: varchar(100)

This will skip the delete completely for the Bronze history table. New and existing rows with changes will be inserted with a current timestamp. Rows not delivered anymore by the source will be untouched.

BronzeSkipDelete and BronzeDeleteFilterQuery cannot be set both.

A SourceFilterQuery does not scope the delete but the delivery: it decides which rows enter the history table at all. It can therefore be combined with either BronzeDeleteFilterQuery or BronzeSkipDelete, and both settings are applied.

Your scenarios

We like to hear other scenarios as well.