Currently I have seen an ETL process where a user created tables that are used to store an intermediate result and then drop the tables at the end of the ETL process.
At first glance it seemed to me that this was not the right way to do it as I had always learned that this should be done through a Temp Table, but when it came to reasoning with arguments to the user why he should use temporary tables instead of normal tables (which at the end drop) I could not find any reason.
Temporary tables in general have these advantages:
Logging of DML operations on them can be turned off, reducing the I/O load on the system (at the risk of losing data in them in case of an abnormal termination of a transaction).
Data in temporary tables are only visible to the sessions that inserted those data, even if multiple sessions reference the same global temporary table simultaneously; each session has its own materialised copy of a temporary table. This improves security when processing sensitive data.
Data in temporary tables are automatically deleted at the end of the transaction or session, depending on the table definition, thus relieving you from having to do that manually. This operation is also not logged (i.e. less I/O again).
On the other hand, temporary tables have some functional limitations compared to the regular tables (e.g. some types of constraints may not be supported), so you cannot use them if such functionality is required for your ETL process.
