Update columns are used by Numetric to reduce the time required to update large dataset tables from an SQL Source. When Numetric initially queries a table, all rows in the table are ingested. After the initial ingest Numetric will track the Update Column and determine the most recent value in the table and store that value for later queries. If, for example, the most recent time was Wednesday the 3rd at 8am, the next time an update is run, Numetric will select all rows where the update column is more recent that Wednesday the 3rd at 8am. This will capture all newly added, and changed rows.
In order for a column to work as an Update Column, it must meet the following criteria:
1. The Update Column is most often a date and time value.
2. Each time a row is added or modified the update column is populated with a current timestamp.
3. Every row has a value in the update column. Rows with NULL values will not get pulled into Numetric.
When an Update Column is not specified, Numetric will upload all rows of data each time the Source is updated, which could significantly increase the burden on your SQL servers and increase upload times into Numetric. This longer upload time could impact your ability to refresh large data sets with your desired frequency.
This is recommended for smaller tables, or tables that don’t need to be updated frequently.
If your SQL Source does not currently contain an update column that fits the criteria above, you may be able to add one by following the steps outlined below:
- See if you can add a column to your tables. For example, Microsoft SQL server has a rowversion datatype that could be added, and function as an Update Column.
- Create a view. If you are comfortable with SQL, you can create a view in which you can construct an update column from existing fields in your tables.
Update Columns may not be available in all situations, in which case it may be easier to fully reingest your tables with each update.
If none of the above options will work, don’t hesitate to reach out to our Numetric Customer Success team. We would be happy to help find a way to keep your data up-to-date.