Aquifer provides an optional history mode meaning you can track every version of each record in the source tables of your choosing. It’s similar to a Slowly Changing Dimension Type 2.
As an example, let’s say you’ve enabled history mode on the projects object in Procore. The procore.project table inside your database would have a few additional _aq* columns used for history tracking.
Example simplified table structure:
| id | display_name | description | __aq_id | __aq_valid_from | __aq_valid_to |
|---|---|---|---|---|---|
| 31446 | Office Building 4567 | This is an office building that used to be missing a description. | 6bf5da55-c399-42f0-95a3-5227244be635 | 2023-11-09 00:00:00 | NULL |
| 31446 | Office Building 4567 | NULL | 01bf485a-28de-4e0b-812c-afd8cb4f04c8 | 2023-10-23 00:00:00 | 2023-11-09 00:00:00 |
_aq* column descriptions:
__aq_id - This is an ID used to query an individual versioned row.
__aq_valid_from - When the row was valid from__aq_valid_to - When the row was valid to, a NULL value means it’s still active.There will be a row for each version of the project data with timestamps to represent the time period when that data was active. In the example above, someone realized a description was missing so it was added. To find the current active records, just filter for rows where __aq_valid_to is NULL. We recommend creating a view out of that __aq_valid_to filter to make it easier to write queries in the future.