While most third-party data warehousing products usually provide a wide variety of data transformation tools for cleansing and filtering data before loading, Teradata PT’s primary focus is fast data loading data into the database tables where data can be further processed using the power of SQL within Vantage. This feature gives rise to a new loading approach called Extract, Load, and Transform (ELT).
Aside from loading with scalable performance, there are additional advantages to using the ELT approach. For example, the Load and Update operators do not support the loading of target tables with USI (Unique Secondary Index), JI (Join Index), RI (Referential Integrity) or Triggers. With the ELT approach, however, you can avoid the above restrictions by first loading data into a staging table and then using such SQL statements as INSERT-SELECT or MERGE-INTO to move the data from the staging table to the target table.
To implement ELT, you can use the Teradata PT "job step" feature to encapsulate the loading step, the SQL INSERT-SELECT step, and the cleanup step into a single job, as shown in the following figure.
Each step within a job is restartable, which means that whenever a step fails for any reason, the resubmission of the job would cause it to resume execution at the failed step and continue. The ELT approach also offers the flexibility of performing a number of different operations on the source rows, such as data cleansing, transformation, integrity checks, and so on, via different steps within the job, before inserting data into the target table.
