How it works...

When the user submits a query in a Teradata database, it follows a path from the origin to the data, as shown in the following figure:

Any query submitted to the Teradata system from a Teradata client, such as BTEQ or SQLA, goes through CLIv2 or ODBC to the Parsing Engine (PE), which parses the query to ensure syntax and generates an execution plan for the query. The execution steps are then passed on by the PE to AMPs over the Bynet; AMPs perform their operations and return the result set over the Bynet, back to PE, and PE delivers the result set to the Teradata client tool that submitted the query. It makes sense to mention the various modules of the PE here: 

Syntaxer >> Resolver >> Security >> Optimizer >> Generator >> Dispatcher

Every module has its part to play. 

There are three ways by which we can do data retrieval and insertion:

• BTEQ: It supports SELECT, INSERT, UPDATE, and DELETE. BTEQ also supports IMPORT/EXPORT protocols.
• Utilities: FastLoad, Multiload, and TPump transfer data from the host to Teradata.
• Export: FastExport performs high volume SELECTs to export data from Teradata to the host.

As a BI user/developer and admin, it is your responsibility to write queries in such a way that they utilize the resources such as CPU, IO, and Spool in an efficient way.