Refreshing data mart views
A materialized view or a table published for a data mart holds a snapshot of the result and goes out of date as the source data changes. DataForge can keep those objects up to date for you: update rules say when an object is updated, and the update calendar shows what is planned and what has already run.
Where updates are managed
- On the Data marts screen — the Update rules and Update calendar tabs, which cover every data mart of the open project version
- In the details dialog of a view (Open data mart view details) — the Assigned update rules and Update calendar tabs, which cover that data mart alone
Creating and editing rules, cancelling a planned update and restarting a past one are available to the Super administrator, Administrator and Developer roles, and only while the company licence is valid. Everyone who can see the data mart can read both calendars.
What an update does
An update always brings the deployed object in step with the data mart, and what it actually performs depends on the state of that object:
| State of the object | What the update does |
|---|---|
| The data mart changed after the object was deployed (status Stale) | The object is dropped and created again from the current configuration of the data mart |
| The object matches the data mart and is a materialized view or a table | Its data is reloaded from the deployed definition |
| The object matches the data mart and is a regular view | Nothing is done in the database — a regular view always executes its query, so its data is never out of date. The run is recorded as "Definition is current" |
While an update runs, the view has the Updating status, and it returns to Active or to Update error when the run finishes.
An update reloads the data of the object from the definition that is deployed in the database. If the data mart configuration changed after that definition was deployed, reloading the data cannot apply those changes, so the view stays Stale — the calendar reports such a run as "Succeeded, data mart stale" and explains that the object has to be recreated to apply the changes.
Update rules
An update rule describes when the objects of one or several data marts are updated. Rules are listed on the Update rules tab and are created with the Add rule button.
| Field | Description |
|---|---|
| Rule name | Name of the rule. Mandatory |
| Rule description | Optional description of the rule's purpose |
| Update type | Scheduled — the update runs at a defined time; Event-based — the update runs after a change in the data mart |
| Update frequency | For a scheduled rule: Every day or Every week |
| Update day | For a weekly scheduled rule: the day of the week the update runs on |
| Update time | For a scheduled rule: the time of day the update runs at, entered as HH:MM:SS. An invalid value is reported as "Enter the time as HH:MM:SS" |
| Event type | For an event-based rule: Data mart configuration change |
| Update delay | For an event-based rule: how long the system waits after the change before it updates — 30 seconds, 5 minutes, 1 hour, 8 hours or 24 hours |
| Incremental refresh | Checkbox. When selected, the runs of this rule load only the changed data instead of rebuilding the whole object |
| Linked data marts | Data marts the rule updates. At least one is required. Only data marts that have a view in the database are offered |
Notes:
- One rule can update several data marts, and one data mart can be covered by several rules
- A rule created from the details dialog of a view is linked to that data mart from the start
- The delay of an event-based rule lets a series of edits settle: a further change made while the delay is running moves the update back, so a burst of edits leads to a single update
The Update rules tab lists the rules with their Rule name, Rule description, Update type, Owner, Linked data marts, the date they were Added and their Status, and the columns can be sorted and filtered.
Actions on a rule:
- Activate the rule and Disable the rule — switch the rule between the Active and Disabled statuses. A disabled rule plans nothing
- Editing — opens Edit update rule with the same fields
- Deleting — asks for confirmation and warns about the consequence: "Are you sure you want to delete the rule "…"? The updates it has planned will be cancelled"
Update calendar
The Update calendar selects between two lists with the period selector:
- Future updates — updates that are planned but have not finished yet
- Past updates — updates that have already run, newest first
The Refresh button re-reads both lists, so a run that finished in the meantime appears without reloading the screen.
Both lists share the same columns:
| Column | Description |
|---|---|
| Data mart name | Data mart the update belongs to |
| View in the DB | Name of the object in the database |
| Update type | Manual run, Scheduled or Event-based |
| Incrementality | Full or Incremental |
| Changes | What the run applied — the number of changes, or "Definition is current" when there was nothing to apply |
| Access rights | The number of access rights transferred by the run. The value opens the Permission report of that run |
| Date and time | When the update is planned for, or when it ran |
| Status | State of the update |
| Actions | Cancelling a planned update, or restarting a past one |
Statuses in Future updates:
| Status | Meaning |
|---|---|
| Not started | The update is planned and its time has not come yet |
| Queued | The update is waiting for its turn to run |
| In progress | The update is running |
Statuses in Past updates:
| Status | Meaning |
|---|---|
| Success | The update finished and the object is up to date |
| Succeeded, data mart stale | The data was reloaded, but the data mart changed after the deployed definition was created, so the view is still outdated and has to be recreated |
| Update error | The update failed. The message returned by the database is shown in the tooltip |
| Cancelled | The update was cancelled before it ran |
| Skipped | The update did not run — for example, another operation on the same object was still in progress |
Actions in the calendar:
- Cancel the planned update — removes one planned occurrence from Future updates. The rule itself stays in place and plans the following occurrences as usual
- Restart the update with the same settings — runs a past update again, with the settings it originally had. It is offered only for entries that can be re-run
Incremental refresh
An incremental refresh writes only the difference between the current result of the data mart and the data already stored in the object, instead of rebuilding it from scratch. On large data marts this shortens the update considerably.
- Incremental refresh is requested with the Incremental refresh checkbox of an update rule
- It applies to materialized views and tables. A regular view is never refreshed, because it always reflects its query
- It is available on PostgreSQL and ClickHouse (materialized views and tables) and on Microsoft SQL Server (tables only)
When an incremental refresh cannot be performed, the update is carried out in full instead — the object still ends up correct. The Incrementality column of Past updates reports what actually happened, and a run that fell back explains why in its tooltip, for example:
- The target database does not support incremental refresh
- The previous incremental refresh failed, so the object was rebuilt in full
- The materialized view contains repeating rows, or columns that cannot be indexed, so the database cannot refresh it incrementally
The run that recreates an object rebuilds it in full — it is built from scratch. The updates that follow can be incremental again.
Schema change without recreation. When an event-based update rule with Incremental refresh selected recreates a table because the set of its columns has changed, the table is altered in place — new columns are added, vanished ones are dropped, and the data is reloaded — instead of being dropped and created again, so the data, the access rights and the accumulated refresh state survive. The run appears in Past updates like any other update and can be restarted. A manual Update data mart view always executes the drop-and-create script shown in the dialog.
Daily check of incremental objects. Once a day the product compares the number of rows in the objects that are refreshed incrementally by appending or merging rows with the result of their definition. A mismatch is recorded in Past updates as a failed check, and the next update of such an object runs in full.