Context: I have an ETL process setup to populate tables in a datawarehouse using a Python script. The script executes a truncate statement via sqlalchemy to empty each of the tables then uses the pandas .to_sql method to load fresh source data from a dataframe. See below snippet:
engine.execute(sa_text(f'''TRUNCATE TABLE {schema}.{table}''').execution_options(autocommit=True))
df.to_sql(table, con=engine, schema=schema, if_exists='append', index=False, chunksize=chunksize,
method=method)
Questions: Strangely, SQL Server's sys.tables modify_date column for each table is not capturing the table changes reflecting the up to date modify_date. Can someone please explain to me why this is and how I can modify my Python code and or SQL Server settings to accurately capture the changes in sys.tables?
modify_date doesn't capture TRUNCATE (or any changes to the data). From the documentation on sys.objects (which is where sys.tables gets its columns):
Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when an index on the table or view is created or altered.
SQL Server doesn't track data modifications by default, and it seems you're after tracking specifically just these truncate operations, in which case I would suggest a logging table that your application writes to as each operation succeeds (or fails, I suppose).