Trigger-based audit tables are the right answer for "who changed this and when" — until they are three times the size of the data they describe and every index rebuild takes the weekend. The history is valuable; it just does not belong in the transactional database.
The pattern: a scheduled job sweeps audit rows older than a retention window out of SQL Server into Parquet files on object storage, registered as a Delta Lake table in Databricks. Partition by month and by the source table name — those are the two predicates every forensic query starts with. Keep the OLTP audit table to a rolling window the application actually reads.
The trade-off is scan cost versus latency. Too few partitions and a one-record lookup reads gigabytes; too many and the small-file problem eats the metadata layer. In practice, monthly partitions with periodic OPTIMIZE and Z-ordering on the primary key land in a comfortable middle.
The payoff is that the questions nobody expected — "show me every change to this purchase order across five years" — go from "we would have to restore a backup" to a Spark SQL query that returns in seconds.