Dynamic data models: how to deal with continuously new tables and columns

Go to Dutch version

For most drivers of Invantive the content of the data model changes per release of Invantive SQL-based products such as Invantive Cloud.

The Autotask SQL and Power BI tables, among others, have a dynamic data model: Autotask independently and regularly takes new releases into production. These new releases may be accompanied by an updated data model. This new Autotask data model is normally processed automatically within a few days in Invantive Cloud and Invantive SQL without the need for a separate release of Invantive Cloud or SQL.

At this moment the Autotask API driver contains about 3396 tables and almost 36.358 columns.

New releases of Autotask can be accompanied by new tables, new columns, but also expired tables and columns. A new Autotask release may also be accompanied by a changed meaning, but this will rarely be the case as it may harm existing further processing.

Invantive does not publish release notes for the data models of platform partners at this moment, as it does for its own software on https://releasenotes.invantive.com. There are also no plans at this time to release release notes for data models managed by third parties. Most platform partners currently issue no or limited release notes or only an overview of the current situation; this seems to be the industry standard.

Users can compile release notes themselves if desired to suit their own needs by tracking the contents of the tables SystemTables@DataDictionary and SystemTableColumns@DataDictionary over time.

Advice is to design the processing of all data models such that new columns and/or new tables have no impact. Expiring or changing the meaning of tables or columns usually requires a check.

In addition, it is recommended that an SLA be agreed upon internally or externally for the software for further processing, as many platforms do not have private hosted frozen versions.

The data models known to have a dynamic data model per release are:

In addition, the following data models are very dynamic in that they are partially or completely determined by the user:

In the case of Teamleader, adding a multi-option custom field can already cause a data model change, but also, for example, renaming a custom field.