Feature Mapping — Teradata to Azure (Complete)¶

Audience: Data architects and engineers mapping Teradata capabilities to Azure equivalents. This is the comprehensive reference covering 40+ features with migration guidance for each.

1. SQL language features¶

1.1 QUALIFY clause¶

Teradata: Native QUALIFY filters window function results inline.

-- Teradata: Get latest order per customer
SELECT customer_id, order_date, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

Azure — Databricks / Spark SQL: QUALIFY is supported natively in Databricks SQL (since DBR 12.0+).

-- Databricks: QUALIFY is supported
SELECT customer_id, order_date, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

Azure — Synapse / Fabric T-SQL: Use a subquery or CTE.

-- Synapse / Fabric: CTE pattern
WITH ranked AS (
    SELECT customer_id, order_date, amount,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT customer_id, order_date, amount
FROM ranked
WHERE rn = 1;

Migration effort: Low. Automated via sqlglot.

1.2 MERGE statement¶

Teradata: Full ANSI MERGE with Teradata extensions.

MERGE INTO target_table tgt
USING source_table src
ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET tgt.value = src.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (src.id, src.value);

Azure — Databricks: Delta Lake MERGE INTO with identical syntax.

MERGE INTO target_table tgt
USING source_table src
ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET tgt.value = src.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (src.id, src.value);

Azure — Synapse / Fabric: T-SQL MERGE with semicolon termination.

MERGE INTO target_table AS tgt
USING source_table AS src
ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET tgt.value = src.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (src.id, src.value);

Migration effort: Low. Minor syntax adjustments.

1.3 COLLECT STATISTICS¶

Teradata: Explicit statistics collection for the optimizer.

COLLECT STATISTICS ON orders COLUMN (customer_id);
COLLECT STATISTICS ON orders COLUMN (order_date);
COLLECT STATISTICS ON orders INDEX (orders_pk);

Azure — Databricks: ANALYZE TABLE computes Delta statistics.

ANALYZE TABLE orders COMPUTE STATISTICS FOR COLUMNS customer_id, order_date;
-- Or use OPTIMIZE for file-level statistics
OPTIMIZE orders ZORDER BY (customer_id);

Azure — Synapse: Automatic statistics creation (or manual).

CREATE STATISTICS stat_customer ON orders (customer_id);
UPDATE STATISTICS orders;
-- Auto-create is enabled by default

Azure — Fabric: Automatic statistics managed by the engine.

Migration effort: Low. Replace COLLECT STATISTICS scripts with ANALYZE TABLE or rely on automatic statistics.

1.4 SET vs MULTISET tables¶

Teradata: SET tables reject duplicate rows automatically; MULTISET allows duplicates.

CREATE SET TABLE unique_customers (...);
CREATE MULTISET TABLE all_events (...);

Azure: All Delta tables are MULTISET equivalent. For SET table behavior, enforce uniqueness through:

-- Databricks: MERGE-based dedup or constraints
ALTER TABLE unique_customers ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id);
-- Note: Delta constraints are informational in Databricks, enforced in Fabric

-- dbt approach: unique test
-- schema.yml:
--   models:
--     - name: unique_customers
--       columns:
--         - name: customer_id
--           tests:
--             - unique
--             - not_null

Migration effort: Medium. Requires identifying SET tables and adding explicit uniqueness enforcement.

1.5 Temporal tables¶

Teradata: Built-in temporal support with VALIDTIME and TRANSACTIONTIME.

CREATE TABLE employee_history (
    emp_id INTEGER,
    salary DECIMAL(10,2),
    valid_start DATE,
    valid_end DATE
) PRIMARY INDEX (emp_id);

-- Temporal query
SELECT * FROM employee_history
WHERE VALIDTIME AS OF DATE '2024-01-01';

Azure — Databricks: Use Delta time travel for transaction-time queries.

-- Delta time travel (transaction time)
SELECT * FROM employee_history VERSION AS OF 3;
SELECT * FROM employee_history TIMESTAMP AS OF '2024-01-01';

-- For valid-time (application time), model explicitly:
SELECT * FROM employee_history
WHERE '2024-01-01' BETWEEN valid_start AND valid_end;

Azure — Synapse: Temporal tables supported in T-SQL.

CREATE TABLE employee_history (
    emp_id INT PRIMARY KEY,
    salary DECIMAL(10,2),
    valid_start DATETIME2 GENERATED ALWAYS AS ROW START,
    valid_end DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (valid_start, valid_end)
) WITH (SYSTEM_VERSIONING = ON);

Migration effort: Medium. Transaction-time maps to Delta time travel. Valid-time requires explicit modeling or Synapse temporal tables.

1.6 Recursive views / CTEs¶

Teradata: Recursive views with Teradata-specific syntax.

CREATE RECURSIVE VIEW org_hierarchy AS (
    SELECT emp_id, manager_id, emp_name, 1 AS lvl
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.manager_id, e.emp_name, h.lvl + 1
    FROM employees e JOIN org_hierarchy h ON e.manager_id = h.emp_id
);

Azure — Databricks: Recursive CTEs supported (Spark 3.4+ / DBR 13.0+).

WITH RECURSIVE org_hierarchy AS (
    SELECT emp_id, manager_id, emp_name, 1 AS lvl
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.manager_id, e.emp_name, h.lvl + 1
    FROM employees e JOIN org_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM org_hierarchy;

Azure — Synapse / Fabric: Standard T-SQL recursive CTE.

WITH org_hierarchy AS (
    SELECT emp_id, manager_id, emp_name, 1 AS lvl
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.manager_id, e.emp_name, h.lvl + 1
    FROM employees e JOIN org_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM org_hierarchy
OPTION (MAXRECURSION 100);

Migration effort: Low. Syntax is nearly identical.

2. Data loading and ETL tools¶

2.1 TPT (Teradata Parallel Transporter)¶

Teradata: High-throughput parallel data loading/extraction.

Azure equivalents:

Migration effort: High. TPT scripts must be redesigned as ADF pipelines + dbt models. See Tutorial — TPT to ADF.

2.2 BTEQ (Basic Teradata Query)¶

Teradata: Interactive/batch SQL execution with scripting, error handling, flow control.

.LOGON server/user,password
.SET WIDTH 200
DATABASE my_db;
SELECT COUNT(*) FROM orders;
.IF ERRORCODE <> 0 THEN .GOTO ERROR_HANDLER
.EXPORT FILE=output.csv
SELECT * FROM summary;
.EXPORT RESET
.LOGOFF

Azure equivalents:

Migration effort: High. BTEQ scripts are the bulk of migration work. See Tutorial — BTEQ to dbt.

2.3 FastLoad¶

Teradata: High-speed bulk loading into empty tables (no indexes, no triggers).

.LOGON server/user,password
.BEGIN LOADING orders_staging;
.LAYOUT order_layout;
INSERT INTO orders_staging VALUES (:col1, :col2, :col3);
.END LOADING;

Azure: ADF Copy Activity with bulk insert mode.

{
    "type": "Copy",
    "source": { "type": "DelimitedTextSource" },
    "sink": {
        "type": "DeltaLakeSink",
        "writeBatchSize": 1000000,
        "tableActionOption": "Overwrite"
    }
}

Migration effort: Medium. Conceptual mapping is direct; implementation differs.

2.4 MultiLoad¶

Teradata: Batch load into populated tables with INSERT, UPDATE, DELETE, UPSERT.

Azure: ADF Copy Activity → staging table → Delta MERGE.

-- Step 1: ADF loads data to staging
-- Step 2: Delta MERGE handles upsert
MERGE INTO target USING staging
ON target.id = staging.id
WHEN MATCHED AND staging.action = 'D' THEN DELETE
WHEN MATCHED AND staging.action = 'U' THEN UPDATE SET *
WHEN NOT MATCHED AND staging.action = 'I' THEN INSERT *;

Migration effort: Medium. Pattern is well-established in Azure.

3. Workload management¶

3.1 TASM (Teradata Active System Management)¶

Teradata: Classification rules, workload groups, priority, throttles, exceptions.

Azure mapping:

Migration effort: High. Requires architectural redesign. See Workload Migration.

3.2 TIWM (Teradata Intelligent Workload Manager)¶

Teradata: AI-driven workload management that auto-adjusts priorities.

Azure: No direct equivalent. Implement via:

• Databricks: Auto-scaling SQL warehouses + query queuing
• Synapse: Workload management with workload groups and classifiers
• Custom: Azure Monitor alerts triggering Azure Functions for dynamic adjustment

Migration effort: High. Often simplified during migration (which is usually acceptable).

4. Security and access control¶

4.1 Access logging¶

Teradata: Built-in access logging to DBC.AccessLog, DBC.DeleteAccessLog.

BEGIN LOGGING ON EACH ALL ON TABLE sensitive_data;
-- Queries against sensitive_data are now logged
SELECT * FROM DBC.AccessLog WHERE TableName = 'sensitive_data';

Azure: Azure Monitor + Diagnostic Logs + Microsoft Purview.

Migration effort: Medium. Different mechanism, equivalent coverage. See Security Migration.

4.2 Row-level security (RLS)¶

Teradata: Row-level security via views or constraint assignments.

CREATE VIEW secure_orders AS
SELECT * FROM orders
WHERE region = SESSION.region_access;

Azure — Fabric / Power BI: Native RLS.

-- Power BI RLS rule
[Region] = USERPRINCIPALNAME()
-- Or via Fabric SQL:
CREATE SECURITY POLICY region_filter
ADD FILTER PREDICATE dbo.fn_region_access(region) ON dbo.orders;

Azure — Databricks: Row/column-level filters (Unity Catalog).

ALTER TABLE orders SET ROW FILTER region_filter ON (region);

Migration effort: Medium. Different mechanism but well-supported.

4.3 Column-level security¶

Teradata: Column-level access via GRANT SELECT on specific columns.

GRANT SELECT (customer_id, order_date) ON orders TO analyst_role;
-- analyst_role cannot see amount, discount columns

Azure — Databricks (Unity Catalog): Column masking.

ALTER TABLE orders ALTER COLUMN ssn SET MASK mask_ssn;

Azure — Purview: Dynamic data masking policies.

Azure — Synapse: Dynamic data masking.

ALTER TABLE orders
ALTER COLUMN ssn ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');

Migration effort: Medium. Multiple Azure options depending on target.

4.4 Teradata roles and profiles¶

Teradata: Database-level roles and profiles.

CREATE ROLE data_analyst;
GRANT SELECT ON my_db TO data_analyst;
GRANT data_analyst TO user1;

CREATE PROFILE analyst_profile AS
    DEFAULT DATABASE = my_db,
    SPOOL = 1e10,
    TEMPORARY = 1e9;

Azure: Entra ID groups + RBAC.

Migration effort: Medium. Conceptual mapping is straightforward; implementation requires Entra ID integration.

4.5 Unity (Teradata ecosystem manager)¶

Teradata Unity: Multi-system coordination, connection management, query routing.

Azure: No single equivalent. Distributed across:

Migration effort: Medium-High. Architectural redesign needed.

5. Data distribution and indexing¶

5.1 Primary Index (PI)¶

Teradata: Determines data distribution across AMPs. Critical for join performance.

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE
) PRIMARY INDEX (customer_id);

Azure mapping:

Migration effort: Medium. Requires analysis of PI choices and translation to distribution strategy.

5.2 Partitioned Primary Index (PPI)¶

Teradata: Partition elimination for range queries.

CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE
) PRIMARY INDEX (customer_id)
  PARTITION BY RANGE_N(order_date BETWEEN DATE '2020-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' MONTH);

Azure: Delta table partitioning.

-- Databricks
CREATE TABLE orders (...)
USING DELTA
PARTITIONED BY (order_month);
-- Where order_month is derived: date_format(order_date, 'yyyy-MM')

-- Synapse
CREATE TABLE orders (...)
WITH (
    DISTRIBUTION = HASH(customer_id),
    PARTITION (order_date RANGE RIGHT FOR VALUES ('2020-01-01', '2020-02-01', ...))
);

Migration effort: Medium. Conceptual mapping is direct; partition granularity may need adjustment.

5.3 Secondary Index (SI)¶

Teradata: Non-PI access path for queries that do not use the PI column.

CREATE INDEX idx_order_date ON orders (order_date);

Azure: No direct equivalent in Delta Lake. Alternatives:

Migration effort: Medium. Requires workload analysis to determine which SIs to replace and how.

5.4 Join Index (JI)¶

Teradata: Materialized join of two or more tables, automatically maintained.

CREATE JOIN INDEX ji_order_customer AS
SELECT o.order_id, o.amount, c.customer_name
FROM orders o JOIN customers c ON o.customer_id = c.customer_id
PRIMARY INDEX (order_id);

Azure:

Migration effort: Medium. Identify JIs and replace with materialized views or dbt models.

6. Stored procedures, macros, and UDFs¶

6.1 Stored procedures¶

Teradata: SPL (Stored Procedure Language) — SQL + control flow.

CREATE PROCEDURE update_summary()
BEGIN
    DELETE FROM daily_summary WHERE report_date = CURRENT_DATE;
    INSERT INTO daily_summary
    SELECT CURRENT_DATE, category, SUM(amount)
    FROM orders WHERE order_date = CURRENT_DATE
    GROUP BY category;
END;

Azure equivalents:

Migration effort: Medium-High. Each procedure must be analyzed individually.

6.2 Teradata macros¶

Teradata: Parameterized SQL blocks (simpler than stored procedures).

CREATE MACRO get_customer_orders (cust_id INTEGER) AS (
    SELECT * FROM orders WHERE customer_id = :cust_id;
);
EXEC get_customer_orders(12345);

Azure: dbt macros or parameterized views.

-- dbt macro (macros/get_customer_orders.sql)
{% macro get_customer_orders(cust_id) %}
    SELECT * FROM {{ ref('orders') }} WHERE customer_id = {{ cust_id }}
{% endmacro %}

Migration effort: Low-Medium. Macros are simpler to convert than stored procedures.

6.3 UDFs (User Defined Functions)¶

Teradata: SQL UDFs and Java/C UDFs.

-- SQL UDF
CREATE FUNCTION fiscal_quarter(dt DATE) RETURNS VARCHAR(6)
RETURN CAST(EXTRACT(YEAR FROM dt) AS VARCHAR(4)) || 'Q' ||
       CAST(((EXTRACT(MONTH FROM dt) - 1) / 3 + 1) AS VARCHAR(1));

Azure — Databricks: Spark UDFs (SQL or Python).

-- SQL UDF
CREATE FUNCTION fiscal_quarter(dt DATE) RETURNS STRING
RETURN CONCAT(YEAR(dt), 'Q', QUARTER(dt));

-- Python UDF (for complex logic)
-- @udf(returnType=StringType())
-- def fiscal_quarter(dt):
--     return f"{dt.year}Q{(dt.month - 1) // 3 + 1}"

Azure — Synapse: T-SQL scalar functions.

CREATE FUNCTION dbo.fiscal_quarter(@dt DATE) RETURNS VARCHAR(6) AS
BEGIN
    RETURN CAST(YEAR(@dt) AS VARCHAR(4)) + 'Q' +
           CAST(DATEPART(QUARTER, @dt) AS VARCHAR(1));
END;

Migration effort: Medium. SQL UDFs translate easily; Java/C UDFs require rewrite in Python/Scala.

6.4 UDTs (User Defined Types)¶

Teradata: Custom data types.

Azure: Not directly supported in Delta Lake. Model as:

• Struct types in Spark (for nested data)
• JSON columns for flexible schemas
• Domain validation in dbt tests

Migration effort: Medium. Requires schema redesign for UDT-heavy schemas.

7. Monitoring and administration¶

7.1 ViewPoint¶

Teradata ViewPoint: Web-based system monitoring — query performance, space usage, session management.

Azure equivalents:

Migration effort: Low-Medium. Azure has equivalent or better monitoring, but dashboards must be rebuilt.

7.2 QueryGrid¶

Teradata QueryGrid: Federated queries across Teradata, Hadoop, Spark, Presto, other DBs.

-- Teradata QueryGrid: query Hadoop from Teradata
SELECT * FROM hadoop_server.db.table@hadoop_connector;

Azure:

Migration effort: Medium. Federation patterns exist but require redesign.

7.3 ARC (Archive/Recovery)¶

Teradata ARC: Backup and restore utility.

Azure: Platform-managed backups.

Migration effort: Low. Azure backups are largely automatic.

8. Advanced features¶

8.1 Compression (multivalue, algorithmic)¶

Teradata: Multi-value compression (MVC), block-level compression, algorithmic compression (ALC).

Azure: Delta Lake inherits Parquet compression:

• Snappy compression (default, fast)
• ZSTD compression (better ratio)
• Z-ORDER for data co-location

Synapse: Columnstore compression (automatic, very efficient).

Migration effort: Low. Azure compression is automatic and generally more effective.

8.2 Data dictionary (DBC tables)¶

Teradata: DBC system views (DBC.Tables, DBC.Columns, DBC.Indices, etc.).

SELECT * FROM DBC.TablesV WHERE DatabaseName = 'my_db';
SELECT * FROM DBC.ColumnsV WHERE DatabaseName = 'my_db' AND TableName = 'orders';

Azure:

Migration effort: Low-Medium. Script-based DBC queries need rewriting.

8.3 Global temporary tables¶

Teradata: Volatile and global temporary tables.

CREATE VOLATILE TABLE tmp_calc AS (...) WITH DATA ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE shared_tmp (...);

Azure — Databricks: Temporary views or Delta tables in a temp schema.

CREATE OR REPLACE TEMPORARY VIEW tmp_calc AS SELECT ...;
-- Or use Delta table in a temp database
CREATE TABLE temp.tmp_calc AS SELECT ...;

Azure — Synapse: T-SQL temporary tables.

CREATE TABLE #tmp_calc AS SELECT ...;
-- Global temp tables
CREATE TABLE ##shared_tmp (...);

Migration effort: Low. Direct equivalents available.

9. Quick reference matrix¶

10. Related resources¶

• SQL Migration — Detailed conversion patterns with before/after code
• Data Migration — TPT/BTEQ replacement patterns
• Workload Migration — TASM/TIWM replacement
• Security Migration — Access control mapping
• Teradata Migration Overview — Original feature mapping table

Maintainers: csa-inabox core team Last updated: 2026-04-30