One of the tricks that I have used with SQL, particularly during development, debugging and unit testing is to use the pattern of INPUT + PROCESS -> OUTPUT. IN

One of the tricks that I have used with SQL, particularly during development, debugging and unit testing is to use the pattern of INPUT + PROCESS -> OUTPUT. INPUT is the transactions we wish to process and PROCESS is the SQL that takes the INPUT and the set of tables required for processing (essentially an SQL join) and create the OUTPUT table. This is an atomic step and can be defined using a CREATE TABLE XX AS SELECT ...; statement. This is quite fast and even for large tables, this can be parallelized (using the parallel query extensions for the database engine). It is also testable by comparing the INPUT with the OUTPUT and ensuring that the OUTPUT meets the specs. In batch environments, this is an invaluable trick.