A Beginner’s Guide to Teradata Multiload and Fastload

Teradata Multiload is a utility that supports loading data (INSERT) and other DML statements ( UPDATE, DELETE, UPSERT). All DML statements are executed optimally, and changed data blocks are written only once. Furthermore, Teradata can process several tables at once.

The target tables must not have a unique secondary index (USI); forced referential integrity is impossible, but we can use soft referential integrity. Furthermore, the target table must not have a join or hash index. Triggers are also not allowed (but this will be rare in a data warehouse). Nevertheless, NUSI is permitted.
A difference must be considered when using a MultiLoad instead of a FastLoad: the target tables must have a primary index; NOPI tables are not allowed. We can not load column partitioned tables with the MultiLoad.

There are two main tasks available. IMPORT tasks allow you to combine DML statements to multiple target tables simultaneously, but consider that the Primary Index column(s) updates are not allowed.
DELETE tasks allow the non-transactional deletion of rows from a table.

Like the Fastload, the Multiload is a Buldload variant that does not need the Transient Journal. Data is efficiently brought to the AMPs in blocks, and the parallelism of the Shared Nothing Architecture is fully exploited. Unlike the Fastload, the target tables must not be empty with the Teradata Multiload.

– The Preliminary Phase does the initialization
– The DML phase moves the DML steps to the AMPS
– The Acquisition phase sends the data to the AMPS and sorts it
– The Application phase applies the input data to the tables
– The end phase, does the cleanup

The preliminary phase is responsible for syntax checking, locking tables, connecting the sessions, creating work tables, error tables, and the restart log.

Teradata stores the DML steps in work tables and links DML statements with the related records in the DML phase.

Data is moved in blocks “round-robin” to the AMPs, bypassing any processing by the Parsing Engine, making it very efficient. Each receiving AMP inspects the PI, calculates the ROWHASH, and forwards the rows to the target AMP’s work table(s). Next, the rows are sorted by ROWHASH by each AMP.

The work table changes are individually applied to the target table(s) by the AMPs. Each data block is read and written precisely once. Any existing NUSI is maintained afterward. As changes are applied as one transaction, no transient journal is needed.

Multiload drops all work tables, error tables, and the log table; locks are released, and the dictionary cache for target tables is flushed.

Multiload DELETE is a more efficient way of deleting many rows, as no transient journal is used; less I/Os and disk space are required than a normal DELETE.

The Multiload DELETE is restartable from the point of failure, while a transactional DELETE causes a ROLLBACK and has to restart from the beginning.

The DELETE task is not using work tables, as changes are applied to each record of exactly one target table.

As no exclusive locks are used, dirty reads (with access locks) are possible during the load with Multiload.