How to track data rows changes using 'insert_date' field

What is 'insert_date'?

The insert_date field records the exact date and time each row is added to the database using the YYYY-MM-DD HH:MM:SS format.

Once new data is inserted, the insert_date appears immediately. Its behavior varies based on the insertion method:

• UPSERT (Incremental):
  • Rows that match a defined dimension set (such as date and campaign) are overwritten.
  • Rows that don’t match the dimension set are newly inserted.
• OVERWRITE (Snapshot):
  • Rows matching the data set are overwritten, while non-matching rows are ignored.
  • If rows from the new data already exist, they will not be reinserted, and their original timestamps will remain unchanged.

How the 'insert_date' affects the Load Process

During the Load process, the insert_date moves with your data to its destination (e.g., file storage, databases), helping you track when rows were added or updated.

Use Cases

• Timestamp Tracking: Not all rows added on the same day will have the same insert_date because existing rows keep their original timestamps.
• Change Identification: By checking the insert_date, you can see which rows were updated during a specific data insertion.

‍