What are the Different Types of Tables in Teradata?

Teradata, a powerful relational database management system, offers a diverse range of table types, each designed to serve specific data storage, processing, and management needs. Understanding these distinctions is key to optimizing performance and data handling within a Teradata environment.

Overview of Teradata Table Types

Teradata supports various table types, allowing users to choose the most appropriate structure based on data volatility, persistence requirements, and operational goals. These types range from standard permanent tables that store long-term data to temporary tables used for session-specific or intermediate results, and specialized tables for handling complex data scenarios like temporal data or error logging.

Here's a quick overview of some common Teradata table types:

Table Type	Purpose	Key Characteristic
Permanent Table	Stores long-term, persistent data for ongoing operations and analytics.	Data persists until explicitly dropped; visible to all users with privileges.
Global Temporary Table	Stores temporary data for the duration of a user's session. Data is private to the session.	Data is session-specific and dropped at logoff or session termination.
Volatile Table	Similar to Global Temporary Tables but generally simpler and quicker to create for immediate, short-term use.	Data is session-specific, dropped at logoff, and typically not logged.
Derived Table	A logical table created from a subquery within a larger query.	Not a physical table; its data is the result set of an inline view or subquery.
Error Logging Table	Used to capture and store detailed information about errors encountered during DML operations.	System-generated or user-defined to log specific error conditions for analysis.
ANSI Temporal Table	Manages data that changes over time, tracking historical, current, and future states of data.	Supports system-versioning (tracking data history) and application-versioning.
Columnar Table	Optimizes storage and retrieval for analytical queries by storing data in columns rather than rows.	Ideal for read-heavy analytical workloads and offers high compression ratios.
Queue Table	Used for message queuing applications, facilitating asynchronous processing of data.	Supports First-In, First-Out (FIFO) processing of messages.

Detailed Explanation of Common Teradata Table Types

Let's delve deeper into the characteristics and use cases for these different table types.

Permanent Tables (Standard Tables)

Permanent tables are the most common type in Teradata. They are designed for long-term data storage and persist in the database until explicitly dropped. Data in permanent tables is accessible to all users with appropriate privileges, making them suitable for core business data, master data, and large fact tables.

• Purpose: To store persistent data that needs to be available across sessions and users.
• Key Characteristics:
  • Data is durable and survives system restarts.
  • Requires permanent disk space and contributes to the database's overall storage consumption.
  • Supports full ACID (Atomicity, Consistency, Isolation, Durability) properties.
• Example:

  CREATE TABLE SalesData (
      SaleID INTEGER,
      ProductID INTEGER,
      SaleDate DATE,
      Amount DECIMAL(10, 2)
  ) PRIMARY INDEX (SaleID);

Global Temporary Tables (GTTs)

Global Temporary Tables are powerful tools for managing temporary datasets that are private to a specific user session. While the table definition is global and persistent, the data within a GTT is session-specific and automatically cleared or dropped when the session ends.

• Purpose: To store intermediate results or temporary data needed for complex queries or multi-step processes within a single user session.
• Key Characteristics:
  • Table definition persists across sessions, but data is private to the session that populates it.
  • Data is automatically dropped at the end of the session, or it can be explicitly deleted using DELETE or TRUNCATE TABLE.
  • Often used in conjunction with ETL processes or complex analytical queries.
• Example:

  CREATE GLOBAL TEMPORARY TABLE Temp_CustomerSales (
      CustomerID INTEGER,
      TotalSalesAmount DECIMAL(18, 2)
  ) ON COMMIT PRESERVE ROWS;

  (Note: ON COMMIT PRESERVE ROWS keeps data after a commit; ON COMMIT DELETE ROWS clears it.)

Volatile Tables

Volatile tables are another form of temporary tables, primarily used for very short-term, session-specific data. They are simpler and quicker to create than GTTs and are implicitly dropped when the user logs off or the session ends. The definition of a volatile table is not persistent.

• Purpose: For immediate, one-time, session-specific data storage, often for testing or ad-hoc analysis.
• Key Characteristics:
  • Both the table definition and data are session-specific and removed upon logoff.
  • No logging is performed for volatile tables, making their creation and data manipulation faster.
  • Typically used for small to medium-sized datasets.
• Example:

  CREATE VOLATILE TABLE TempResults (
      ResultID INTEGER,
      Description VARCHAR(100)
  ) ON COMMIT PRESERVE ROWS;

Derived Tables

A derived table is not a physical table but rather a logical one, represented by a subquery within the FROM clause of a SELECT statement. It allows you to treat the result set of a subquery as if it were a temporary table for the duration of the query's execution.

• Purpose: To simplify complex queries, break down logic, or perform aggregations before joining with other tables.
• Key Characteristics:
  • Exists only for the duration of the query.
  • Does not consume permanent disk space.
  • Provides a way to encapsulate complex logic into a manageable unit within a query.
• Example:

  SELECT
      dt.DepartmentName,
      dt.AvgSalary
  FROM (
      SELECT
          DepartmentName,
          AVG(Salary) AS AvgSalary
      FROM Employees
      GROUP BY DepartmentName
  ) AS dt
  WHERE dt.AvgSalary > 50000;

Error Logging Tables

Error logging tables are specialized tables used to capture detailed information about rows that cause errors during Data Manipulation Language (DML) operations (e.g., INSERT, UPDATE, DELETE). They are implicitly or explicitly defined to store error codes, messages, and the problematic row data.

• Purpose: To facilitate error handling and debugging during bulk data loading or manipulation.
• Key Characteristics:
  • Typically system-generated or associated with INSERT...SELECT or MERGE statements that include an ERRORLOG clause.
  • Stores information like error codes, field IDs, and the problematic data itself.
  • Essential for identifying and correcting data quality issues.
• Example (Conceptual):
  When using INSERT...SELECT with LOG ERRORS clause, Teradata creates an error logging table (or uses a specified one) to store rows that fail insertion due to data type mismatches, constraint violations, etc.

ANSI Temporal Tables

Teradata's ANSI Temporal tables are designed to manage data that changes over time, providing built-in support for historical tracking and future planning. They allow users to query data based on specific points in time or periods, enabling "as-of" or "between dates" analysis without complex application logic.

• Purpose: To manage time-sensitive data, providing historical accuracy and future planning capabilities.
• Key Characteristics:
  • System-Versioned: Automatically tracks the history of changes to data, recording when rows were active in the database.
  • Application-Versioned: Tracks validity periods defined by the application, representing when data was valid in the real world.
  • Adheres to ANSI SQL:2011 temporal standards.
• Example:

  CREATE TABLE EmployeeHistory (
      EmployeeID INTEGER,
      EmployeeName VARCHAR(100),
      Salary DECIMAL(10, 2),
      VALIDTIME AS VALIDTIME(BeginDate, EndDate)
  );

Columnar Tables

Columnar tables store data on disk organized by column rather than by row. This structure significantly benefits analytical queries that often access a subset of columns across many rows, as it reduces the amount of data read from disk and improves compression efficiency.

• Purpose: To optimize performance for analytical workloads, data warehousing, and business intelligence.
• Key Characteristics:
  • Data is stored column by column, leading to higher compression ratios.
  • Improved I/O performance for queries that only access a few columns.
  • Less efficient for transactional (OLTP) workloads that require frequent updates to individual rows.
• Example:

  CREATE TABLE FactSales_Columnar (
      SaleDate DATE,
      ProductID INTEGER,
      RegionID INTEGER,
      Amount DECIMAL(10, 2)
  ) COLUMNAR;

Queue Tables

Queue tables in Teradata are used in conjunction with user-defined functions (UDFs) and stored procedures to implement message queuing functionalities. They facilitate asynchronous processing, allowing applications to send and receive messages in a First-In, First-Out (FIFO) manner.

• Purpose: To enable reliable, asynchronous message passing between applications or processes.
• Key Characteristics:
  • Behaves like a queue, supporting INSERT (enqueue) and SELECT (dequeue) operations.
  • Often used for batch processing, job scheduling, or integrating disparate systems.
  • Requires specific UDFs (ADD_TO_QUEUE, GET_FROM_QUEUE) for message manipulation.
• Example (Conceptual):

  CREATE TABLE OrderQueue (
      OrderID INTEGER,
      OrderDetails XML
  ) QUEUE;

Choosing the Right Table Type

Selecting the appropriate table type depends on the data's nature, its intended use, and performance considerations. For persistent, shared data, permanent tables are the default. For intermediate, session-specific results, Global Temporary or Volatile Tables are ideal. When dealing with temporal data or complex DML operations, ANSI Temporal and Error Logging tables provide specialized capabilities. Columnar tables are best suited for large analytical datasets. By leveraging these distinct table types, Teradata users can design highly efficient and flexible database solutions.