Quite often people need to write a new T-SQL application to replace an existing one for various reasons. For instance, Existing report is generated by a complex T-SQL procedure which pulls data from OLTP directly. The performance of the procedre is poor since everything is calculated while report is running. Company decided to pre-calculate and save the result in a denormalized table, and the report runs off that denormalized table. Obviously, the original code need to be re-written. Let’s say you are new to the company. Before starting your work, you may want to know what databases, tables, and columns are referenced by the procedure. It will not be hard if the procedure code block only has few lines of code. However, if the procedure includes few or tens thousand lines, accomplishing such work might takes days. The worth thing is you might miss some refereces and finally you might abandon your work result at the end due to the inaccuracy.
Changed data collecting/capturing is a topic frequently discussed as part of the design of auditing and ETL systems. In auditing system, changed data collection processes are usually just build on the system to be audited. In very few cases, the system needs to audit over the collected historical data, But this is not the case in ETL system. Especially in complex ETL systems, in order to get the desired data shape at target, data from different sources need to be processed into a series of format and saved in the staging area and one or more level posted processes might also be needed.