Teradata offers multiple methods for external data loading, which can be categorized into two groups.
Bulk and transactional load paths differ in journaling, locking and recovery. Their relative speed depends on workload, batching, utility/operator and configuration; transactional loading is not always slower.
Every type has its own set of pros and cons. You can load your data in either manner based on your specific needs.
Traditionally, individual load utilities offered varying levels of usability and syntax. However, Teradata now offers a universal tool, the Teradata Parallel Transporter (TPT), that integrates the functionality of all these distinct utilities. Despite this, the specialized tools remain available for use.
TPT now boasts many useful features, yet its scripting language remains inconvenient.
The Teradata Utilities for Loading
The utilities for transactional load are BTEQ and TPump, while Fastload and Multiload are known as bulk load utilities as they bypass the transient journal.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThis article does not aim to teach syntax for these tools. Refer to the Official Teradata Documentation for syntax information. The purpose of this article is to provide guidance on the appropriate use of each tool.
Block sizes and request batching vary by version, utility and TPT operator. BTEQ and TPump are not necessarily limited to one client request per row. Consult operator-specific documentation and measure the intended load.
Bulk load utilities have certain restrictions due to their lack of transaction logs.
- Restrictions are utility/operator specific. MultiLoad supports NUSIs; do not remove all indexes indiscriminately. Check constraints, triggers, index types and target-state requirements for the chosen load path.
- Fastload requires the target table to be empty.
To load set tables, a duplicate row check is necessary. However, duplicate row checks cannot be performed since data is loaded in blocks. Teradata addresses this issue by filtering rows before the database entry.
MultiLoad supports UPDATE, DELETE and UPSERT and does not require an empty target. Work tables and protocol stages contribute costs, but do not establish a universal performance ranking against FastLoad.
BTEQ supports session configuration and request-level retry; it does not lack multiple-session capability outright. TPump offers transactional loading with packing and rate controls. Compare the actual import mode and configuration. After a deadlock aborts a transaction, an application must retry the entire transaction rather than assume a request-level retry restores its earlier statements.
The required load utility is determined primarily by the number of source rows and the byte consumption per data row.
Fastload and Multiload are recommended for handling large amounts of data, while BTEQ and TPump are better suited for smaller amounts. TPump is particularly useful for active warehousing as it allows for controlled data loading at a specific rate (measured in rows per second).
The 500,000-row cutoff was a personal historical rule of thumb, not a system limit. Choose by row width, target semantics, concurrent access, restart needs and measured throughput. Audit fixtures exercised BTEQ import/export and TPT LOAD/UPDATE/EXPORT protocols, not a comparative throughput benchmark or standalone MultiLoad/TPump restart test.
Building or improving a data pipeline?
Discuss ingestion, transformations, orchestration, quality checks and operational handover.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book