Teradata Tutorial: Learn Massively Parallel SQL from Scratch (2026)
I have worked with Teradata for enterprise-scale data warehousing where performance and reliability are paramount. Teradata is a massively parallel processing (MPP) relational database system.
We will cover Teradata architecture (AMPs, BYNET, PE), SQL features, indexing strategies, performance tuning, and utilities.
Teradata Architecture: AMPs, BYNET, and PEs
Teradata's MPP architecture has three components: Parsing Engine (PE) processes SQL, BYNET interconnect moves data, Access Module Processors (AMPs) store and retrieve data.
Each AMP owns a portion of the data (hash-partitioned). AMPs operate independently in parallel. Adding AMPs linearly scales performance.
SHOW VARS;
HELP SYSTEM;
HELP SESSION;
SELECT * FROM DBC.TABLES WHERE DatabaseName = 'mydb';SQL Features and Extensions
Teradata SQL supports standard SQL plus extensions for analytics: OLAP functions (RANK, DENSE_RANK, NTILE), statistical functions, and time series.
Qualify clause filters window function results. WITH clause for CTEs. Recursive queries for hierarchical data.
SELECT department_id, employee_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS sal_rank
FROM employees
QUALIFY sal_rank <= 3;Primary Index and Data Distribution
Primary Index (PI) determines data distribution across AMPs. Unique PI (UPI) evenly distributes rows. Non-unique PI (NUPI) may cause skew.
Secondary Indexes (SI) provide alternate access paths. USI (unique) is efficient for single-row lookups. NUSI causes AMP-level scans.
CREATE TABLE employees (
emp_id INTEGER NOT NULL,
dept_id INTEGER,
salary DECIMAL(10,2)
) PRIMARY INDEX (emp_id);
CREATE INDEX dept_idx (dept_id) ON employees;
SHOW TABLE employees;Performance Tuning: Collect Statistics and Explain
Collect Statistics (COLLECT STATS) provides the optimizer with data distribution info for better query plans. UPDATE STATS for current data.
EXPLAIN shows the plan: which AMPs accessed, join strategies, spool space estimates. Look for full-table scans and redistribute/replicate steps.
COLLECT STATISTICS COLUMN (dept_id) ON employees;
EXPLAIN SELECT e.emp_id, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE e.salary > 50000;Joins and Query Optimization
Teradata join strategies: Merge Join (sorted, efficient), Hash Join (one table fits in memory), Nested Join (small table driving large table).
Join columns should match in data type. Use PRIMARY INDEX columns in JOIN conditions for collocation. Redistribution and duplication add cost.
EXPLAIN SELECT e.emp_id, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 50000
ORDER BY e.salary DESC;Teradata Utilities: FastLoad, MultiLoad, TPump, and BTEQ
FastLoad loads data into empty tables at high speed (no journaling). MultiLoad loads into populated tables with upsert capability.
TPump for continuous low-latency inserts. BTEQ for scripted SQL. Teradata Parallel Transporter (TPT) for complex ETL.
LOGON myhost/mydb,password;
.SET SESSION TRANSACTION BTET;
DATABASE mydb;
CREATE TABLE temp_load (id INT, name VARCHAR(100)) PRIMARY INDEX (id);
USING (id INT, name VARCHAR(100))
SELECT * FROM temp_load;
LOGOFF;Frequently Asked Questions
Is Teradata free?
Teradata offers a free Express Edition (limited to 10 GB data and 2 AMPs). Full enterprise licenses are commercial.
How does Teradata compare to Snowflake?
Teradata is on-prem MPP with fixed resources. Snowflake is cloud-native with separated compute/storage. Teradata has richer indexing; Snowflake has auto-scaling.
What is a Teradata AMP?
AMP (Access Module Processor) is a vproc that owns and manages a portion of the data. More AMPs = more parallelism and capacity. Physically, AMPs are threads on nodes.
Does Teradata support JSON or semi-structured data?
Teradata supports JSON through its SQL extensions and the native JSON data type. It also supports XML, Avro, and Parquet formats.
Originally published on Ayodhyyya. Last updated June 1, 2026.