☰
Navigation :

Incremental Refresh

Incremental refresh overview

Incremental refresh allows you to refresh only a subset of your DirectQuery data. Incremental Refresh operations can significantly reduce the time and resources required for a full application refresh. This is useful when the source DirectQuery database contents have been updated or new data is available. For more information about the refresh, see the Atoti Server’s documentation for Data Refresh.

Incremental refresh REST service

Incremental Refresh operations can be performed via the /refresh endpoint in the DirectQuery REST service.

Direct incremental refresh

When data is updated in the source database, an incremental refresh operation can be performed to update the aggregates in the application’s cubes. This is done by specifying the table, fields, and values that have been updated. The application then only refreshes the specified data, rather than performing a full refresh of all data.

This can be done by specifying a TableUpdateDTO object in the request body of the /refresh endpoint.

The table and field names must match the names in the local database schema, not the remote database table and field names.

Example

A new Trade has been added to the remote database: Trade1 for Date 2025 to the BaseTable and a new Trade Attribute1 and Date 2025 to the TradeAttributesTable. The remote database schema is as follows:

CREATE TABLE BASE_TABLE (
    TRADE_ID VARCHAR(255),
    AS_OF_DATE DATE
);
CREATE TABLE TRADE_ATTRIBUTES_TABLE (
    TRADE_ATTRIBUTE VARCHAR(255),
    AS_OF_DATE DATE
);

The application can be refreshed to reflect the database changes by sending the following request to the /refresh endpoint:

{
    "tableUpdates": [
        {
            "changeType": "ADD_ROWS",
            "tableName": "BaseTable",
            "fields": [
                {
                    "fieldName": "TradeId", 
                    "values": ["Trade1"]
                },
                {
                    "fieldName": "AsOfDate",
                    "values": ["5-22-2025"]
                }
            ]
        },
        {
            "changeType": "ADD_ROWS",
            "tableName": "TradeAttributesTable",
            "fields": [
                {
                    "fieldName": "TradeAttribute", 
                    "values": ["Attribute1"]
                },
                {
                    "fieldName": "AsOfDate",
                    "values": ["5-22-2025"]
                }
            ]
        }
    ]
}

Template incremental refresh

For known incremental refresh operations, templates can be used to define the structure of the refresh operations that are expected.

Templates define the tables and fields that to be used in the incremental refresh operation. This allows for a more structured and reusable approach to incremental refresh operations.

Additionally, aliases can be defined for the fields in the template. This allows for a more readable and understandable request body when performing the incremental refresh operation. Aliases can exist across multiple tables, but must be unique per table. By using an alias across multiple tables, the request body can be simplified and the values provided are reused across the multiple tables.

Only one changeType can be defined for the entire template at request time.

Example

Template definition

The template is first defined via configuration properties.

The template comprises:

  • A unique name for the template
  • The tables to include in the incremental refresh operation
  • The fields to update in each table

The table and field names must match the local names used in the application. These are the names of the in-memory tables and fields.

Assuming you plan on only updating the BaseTable.TradeId, BaseTable.Date, TradeTable.TradeAttribute and TradeTable.Date fields, then you can define a template that allows you to update these fields with the following configuration:

directquery:
  incremental-refresh:
    templates:
      MyIncrementalRefreshTemplate:
        tables:
          BaseTable:
            fields:
              - name: TradeId
              - name: AsOfDate
                alias: Date
          TradeTable:
            fields:
              - name: TradeAttribute
              - name: AsOfDate
                alias: Date

Now that the template is defined, it can be used to perform incremental refresh operations. Within the template, aliases have been defined on the date fields of the BaseTable and TradeTable to simplify the request body.

Template execution

Assuming that a new trade Trade1 for Date 2025 has been added to BaseTable and a new trade attribute Attribute1 and date 2025 to the TradeAttribute table on the remote database, an incremental refresh can be performed to update the aggregates in the application’s cubes for this new data.

A request can be sent to the /refresh endpoint to pick up the data change using the following request body:

{
  "template": {
    "templateName": "MyIncrementalRefreshTemplate",
    "changeType": "ADD_ROWS",
    "fieldValues": {
      "TradeId": ["Trade1"],
      "TradeAttribute": ["Attribute1"],
      "Date": ["5-22-2025"]
    }
  }
}

This performs the same operation as the previous example, but with a more structured and reusable approach. The request body is much simpler and easier to read, and the template can be reused for future incremental refresh operations. Users can also take advantage of the aliases so they do not need to specify the same values across multiple Table Fields.

Cached tables

Cached and isolated tables also need to be included in the templates. This can be done by reference the cached table’s local name. This name is simply the name of the in-memory table suffixed with _CACHE_SOURCE.

Here is an example where a template is defined to include the FXRates cached table:

directquery:
  incremental-refresh:
    templates:
      MyIncrementalRefreshTemplate:
        tables:
          FXRates_CACHE_SOURCE:
            fields:
              - name: AsOfDate
                alias: Date