SQL Migration — Teradata SQL to T-SQL / Spark SQL¶

Comparative positioning note

This document is written from the perspective of Microsoft Azure, Cloud Scale Analytics, and CSA Loom. Any description of third-party or competing products, services, pricing, or capabilities is derived from publicly available documentation and sources believed accurate at the time of writing, and is provided for general comparison only. We do not claim expertise in, or authority over, any non-Microsoft product or service; the respective vendor's official documentation is the authoritative source for their offerings, which may change over time. Nothing here is intended to disparage any vendor — where a competing product has genuine advantages, we aim to note them honestly. Verify all third-party details against the vendor's current official documentation before making decisions.

Audience: Data engineers and DBAs converting Teradata SQL scripts to Azure-compatible SQL. This guide provides 25+ conversion patterns with before/after code examples, covering the most common Teradata-specific SQL constructs.

1. Conversion strategy¶

Approach¶

Do not attempt line-by-line translation. Instead:

Classify each SQL artifact (Tier A/B/C/D per the migration overview)
Automate Tier-A conversions using sqlglot or Microsoft SAMA
Refactor Tier-B/C conversions manually, converting to dbt models where possible
Decommission Tier-D artifacts (20-40% of most estates)

Tools¶

Tool	Purpose	Coverage
sqlglot	Open-source SQL transpiler	70-80% of syntax conversion
Microsoft SAMA	Assessment + automated conversion	Schema + basic SQL
dbt + sqlglot	Convert BTEQ to dbt models with auto-translation	End-to-end workflow
Datametica Raven	Commercial Teradata-specific converter	80-90% (paid)

Using sqlglot¶

import sqlglot

# Teradata → Spark SQL
spark_sql = sqlglot.transpile(
    "SELECT * FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY dt DESC) = 1",
    read="teradata",
    write="spark"
)[0]

# Teradata → T-SQL (Synapse / Fabric)
tsql = sqlglot.transpile(
    "SELECT * FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY dt DESC) = 1",
    read="teradata",
    write="tsql"
)[0]

2. Data type conversions¶

Numeric types¶

Teradata	Spark SQL	T-SQL (Synapse/Fabric)	Notes
`BYTEINT`	`TINYINT`	`TINYINT`
`SMALLINT`	`SMALLINT`	`SMALLINT`
`INTEGER`	`INT`	`INT`
`BIGINT`	`BIGINT`	`BIGINT`
`DECIMAL(p,s)`	`DECIMAL(p,s)`	`DECIMAL(p,s)`	Direct mapping
`FLOAT`	`DOUBLE`	`FLOAT`	Teradata FLOAT = 64-bit
`NUMBER`	`DECIMAL(38,0)`	`DECIMAL(38,0)`	Or DECIMAL(p,s) if specified

String types¶

Teradata	Spark SQL	T-SQL	Notes
`CHAR(n)`	`CHAR(n)`	`CHAR(n)`	Pad behavior differs
`VARCHAR(n)`	`STRING` or `VARCHAR(n)`	`VARCHAR(n)`	Spark STRING is unlimited
`CLOB`	`STRING`	`VARCHAR(MAX)`
`BYTE(n)`	`BINARY`	`VARBINARY(n)`
`BLOB`	`BINARY`	`VARBINARY(MAX)`

Date/time types¶

Teradata	Spark SQL	T-SQL	Notes
`DATE`	`DATE`	`DATE`	Teradata DATE allows arithmetic
`TIME`	`STRING`	`TIME`	Spark lacks native TIME
`TIMESTAMP`	`TIMESTAMP`	`DATETIME2`
`TIMESTAMP WITH TIME ZONE`	`TIMESTAMP`	`DATETIMEOFFSET`	Spark TIMESTAMP is UTC
`INTERVAL`	Compute with functions	`DATEDIFF`	No direct interval type

Special types¶

Teradata	Spark SQL	T-SQL	Notes
`PERIOD(DATE)`	Two DATE columns	Two DATE columns	Model as start/end
`JSON`	`STRING` (parse with JSON functions)	`NVARCHAR(MAX)`
`XML`	`STRING`	`XML`
`ST_GEOMETRY`	Sedona geometry types	`GEOMETRY` / `GEOGRAPHY`	Requires Sedona library
`ARRAY`	`ARRAY<type>`	JSON array in VARCHAR

3. SQL conversion patterns¶

Pattern 1: QUALIFY clause¶

Teradata:

SELECT customer_id, order_date, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

Spark SQL (Databricks):

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

-- Option B: Subquery (if targeting older Spark)
SELECT customer_id, order_date, amount
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) t WHERE rn = 1;

T-SQL (Synapse / Fabric):

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;

Pattern 2: Date arithmetic¶

Teradata:

-- Teradata allows integer arithmetic on DATE
SELECT order_date + 30 AS due_date FROM orders;
SELECT order_date - hire_date AS days_employed FROM employees;
SELECT ADD_MONTHS(order_date, 3) AS quarter_end FROM orders;

Spark SQL:

SELECT DATE_ADD(order_date, 30) AS due_date FROM orders;
SELECT DATEDIFF(order_date, hire_date) AS days_employed FROM employees;
SELECT ADD_MONTHS(order_date, 3) AS quarter_end FROM orders;

T-SQL:

SELECT DATEADD(DAY, 30, order_date) AS due_date FROM orders;
SELECT DATEDIFF(DAY, hire_date, order_date) AS days_employed FROM employees;
SELECT DATEADD(MONTH, 3, order_date) AS quarter_end FROM orders;

Pattern 3: CASESPECIFIC and case sensitivity¶

Teradata:

-- Teradata is case-insensitive for CHAR by default
-- Use (CASESPECIFIC) or (NOT CASESPECIFIC) to override
SELECT * FROM users WHERE username (CASESPECIFIC) = 'Admin';
SELECT * FROM users WHERE city (NOT CASESPECIFIC) = 'new york';

Spark SQL:

-- Spark is case-sensitive for string comparisons by default
SELECT * FROM users WHERE username = 'Admin';
SELECT * FROM users WHERE LOWER(city) = 'new york';

T-SQL:

-- Synapse uses database collation (case-insensitive by default)
SELECT * FROM users WHERE username = 'Admin' COLLATE Latin1_General_CS_AS;
SELECT * FROM users WHERE city = 'new york'; -- case-insensitive by default

Pattern 4: FORMAT phrases¶

Teradata:

SELECT order_date (FORMAT 'YYYY-MM-DD') FROM orders;
SELECT amount (FORMAT 'ZZZ,ZZ9.99') FROM orders;
SELECT CAST(CURRENT_TIMESTAMP AS VARCHAR(19) FORMAT 'YYYY-MM-DDBHH:MI:SS') AS ts;

Spark SQL:

SELECT DATE_FORMAT(order_date, 'yyyy-MM-dd') FROM orders;
SELECT FORMAT_NUMBER(amount, 2) FROM orders;
SELECT DATE_FORMAT(CURRENT_TIMESTAMP(), 'yyyy-MM-dd HH:mm:ss') AS ts;

T-SQL:

SELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders;
SELECT FORMAT(amount, 'N2') FROM orders;
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS ts;

Pattern 5: SAMPLE clause¶

Teradata:

-- Random sample: 1000 rows
SELECT * FROM orders SAMPLE 1000;
-- Percentage sample
SELECT * FROM orders SAMPLE 0.10;
-- Stratified sample
SELECT * FROM orders SAMPLE WITH REPLACEMENT WHEN region = 'EAST' THEN 0.20
                                              WHEN region = 'WEST' THEN 0.10;

Spark SQL:

-- Row count (approximate)
SELECT * FROM orders TABLESAMPLE (1000 ROWS);
-- Percentage
SELECT * FROM orders TABLESAMPLE (10 PERCENT);
-- Or using DataFrame API for stratified:
-- df.stat.sampleBy("region", {"EAST": 0.2, "WEST": 0.1})

T-SQL:

-- Percentage
SELECT * FROM orders TABLESAMPLE (10 PERCENT);
-- Exact row count
SELECT TOP 1000 * FROM orders ORDER BY NEWID();

Pattern 6: SEL / SELECT shorthand¶

Teradata:

SEL customer_id, COUNT(*) FROM orders GROUP BY 1;
SEL TOP 10 * FROM orders ORDER BY order_date DESC;

Spark SQL / T-SQL:

-- Replace SEL with SELECT everywhere
SELECT customer_id, COUNT(*) FROM orders GROUP BY 1;
-- TOP is T-SQL only; use LIMIT in Spark
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;  -- Spark
SELECT TOP 10 * FROM orders ORDER BY order_date DESC;    -- T-SQL

Pattern 7: Named columns in GROUP BY¶

Teradata:

-- Teradata allows GROUP BY column alias
SELECT EXTRACT(YEAR FROM order_date) AS order_year, SUM(amount)
FROM orders
GROUP BY order_year;

Spark SQL:

-- Spark allows GROUP BY alias
SELECT YEAR(order_date) AS order_year, SUM(amount)
FROM orders
GROUP BY order_year;

T-SQL:

-- T-SQL does NOT allow GROUP BY alias
SELECT YEAR(order_date) AS order_year, SUM(amount)
FROM orders
GROUP BY YEAR(order_date);

Pattern 8: TITLE / AS aliasing¶

Teradata:

SELECT customer_id (TITLE 'Customer ID'), SUM(amount) (TITLE 'Total Sales')
FROM orders GROUP BY 1;

Spark SQL / T-SQL:

-- Replace TITLE with AS
SELECT customer_id AS "Customer ID", SUM(amount) AS "Total Sales"
FROM orders GROUP BY customer_id;

Pattern 9: COLLECT STATISTICS → ANALYZE TABLE¶

Teradata:

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

Spark SQL:

ANALYZE TABLE orders COMPUTE STATISTICS;
ANALYZE TABLE orders COMPUTE STATISTICS FOR COLUMNS customer_id, order_date;
-- Delta-specific: OPTIMIZE for file statistics
OPTIMIZE orders ZORDER BY (customer_id, order_date);

T-SQL:

-- Synapse auto-creates statistics; manual if needed:
CREATE STATISTICS stat_cust ON orders (customer_id);
CREATE STATISTICS stat_date ON orders (order_date);
UPDATE STATISTICS orders;

Pattern 10: VOLATILE TABLE → Temp table¶

Teradata:

CREATE VOLATILE TABLE tmp_orders AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    WHERE order_date >= DATE - 30
    GROUP BY customer_id
) WITH DATA ON COMMIT PRESERVE ROWS;

Spark SQL:

CREATE OR REPLACE TEMPORARY VIEW tmp_orders AS
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE(), 30)
GROUP BY customer_id;

T-SQL:

SELECT customer_id, SUM(amount) AS total
INTO #tmp_orders
FROM orders
WHERE order_date >= DATEADD(DAY, -30, GETDATE())
GROUP BY customer_id;

Pattern 11: MERGE with multiple match conditions¶

Teradata:

MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED AND s.status = 'D' THEN DELETE
WHEN MATCHED AND s.status = 'U' THEN UPDATE SET t.value = s.value, t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.value, s.status, CURRENT_TIMESTAMP);

Spark SQL (Delta):

MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED AND s.status = 'D' THEN DELETE
WHEN MATCHED AND s.status = 'U' THEN UPDATE SET t.value = s.value, t.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (id, value, status, updated_at)
    VALUES (s.id, s.value, s.status, CURRENT_TIMESTAMP());

T-SQL:

MERGE INTO target AS t
USING source AS s ON t.id = s.id
WHEN MATCHED AND s.status = 'D' THEN DELETE
WHEN MATCHED AND s.status = 'U' THEN UPDATE SET t.value = s.value, t.updated_at = GETDATE()
WHEN NOT MATCHED THEN INSERT (id, value, status, updated_at)
    VALUES (s.id, s.value, s.status, GETDATE());

Pattern 12: NORMALIZE / PERIOD operations¶

Teradata:

-- Normalize overlapping periods
SELECT NORMALIZE customer_id, PERIOD(start_date, end_date) AS coverage
FROM subscriptions;

Spark SQL:

-- No NORMALIZE equivalent; use window functions
WITH sorted AS (
    SELECT customer_id, start_date, end_date,
           LAG(end_date) OVER (PARTITION BY customer_id ORDER BY start_date) AS prev_end
    FROM subscriptions
),
grouped AS (
    SELECT *,
           SUM(CASE WHEN prev_end IS NULL OR start_date > prev_end THEN 1 ELSE 0 END)
               OVER (PARTITION BY customer_id ORDER BY start_date) AS grp
    FROM sorted
)
SELECT customer_id, MIN(start_date) AS start_date, MAX(end_date) AS end_date
FROM grouped
GROUP BY customer_id, grp;

Migration effort: High. NORMALIZE requires significant manual rewrite.

Pattern 13: HASH functions¶

Teradata:

SELECT HASHROW(customer_id) FROM orders;
SELECT HASHBUCKET(HASHROW(customer_id)) FROM orders;

Spark SQL:

SELECT HASH(customer_id) FROM orders;
SELECT MD5(CAST(customer_id AS STRING)) FROM orders;
SELECT SHA2(CAST(customer_id AS STRING), 256) FROM orders;

T-SQL:

SELECT HASHBYTES('MD5', CAST(customer_id AS VARCHAR(50))) FROM orders;
SELECT HASHBYTES('SHA2_256', CAST(customer_id AS VARCHAR(50))) FROM orders;

Pattern 14: ZEROIFNULL / NULLIFZERO¶

Teradata:

SELECT ZEROIFNULL(discount) AS discount FROM orders;
SELECT NULLIFZERO(quantity) AS quantity FROM orders;

Spark SQL:

SELECT COALESCE(discount, 0) AS discount FROM orders;
SELECT NULLIF(quantity, 0) AS quantity FROM orders;

T-SQL:

SELECT ISNULL(discount, 0) AS discount FROM orders;
SELECT NULLIF(quantity, 0) AS quantity FROM orders;

Pattern 15: RANK / PARTITION with Teradata extensions¶

Teradata:

SELECT customer_id, order_date, amount,
       RANK(order_date DESC) AS date_rank,
       CSUM(amount, order_date) AS running_total
FROM orders;

Spark SQL:

SELECT customer_id, order_date, amount,
       RANK() OVER (ORDER BY order_date DESC) AS date_rank,
       SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

T-SQL:

SELECT customer_id, order_date, amount,
       RANK() OVER (ORDER BY order_date DESC) AS date_rank,
       SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

Pattern 16: EXTRACT function¶

Teradata:

SELECT EXTRACT(YEAR FROM order_date) AS yr,
       EXTRACT(MONTH FROM order_date) AS mo,
       EXTRACT(DAY FROM order_date) AS dy
FROM orders;

Spark SQL:

SELECT YEAR(order_date) AS yr,
       MONTH(order_date) AS mo,
       DAY(order_date) AS dy
FROM orders;
-- EXTRACT also works: EXTRACT(YEAR FROM order_date)

T-SQL:

SELECT YEAR(order_date) AS yr,
       MONTH(order_date) AS mo,
       DAY(order_date) AS dy
FROM orders;
-- Or: DATEPART(YEAR, order_date)

Pattern 17: String functions¶

Teradata	Spark SQL	T-SQL
`TRIM(BOTH FROM col)`	`TRIM(col)`	`LTRIM(RTRIM(col))`
`INDEX(str, substr)`	`INSTR(str, substr)`	`CHARINDEX(substr, str)`
`SUBSTR(str, pos, len)`	`SUBSTRING(str, pos, len)`	`SUBSTRING(str, pos, len)`
`OREPLACE(str, old, new)`	`REPLACE(str, old, new)`	`REPLACE(str, old, new)`
`OTRANSLATE(str, from, to)`	`TRANSLATE(str, from, to)`	Custom function
`CHAR2HEXINT(str)`	`HEX(str)`	`CONVERT(VARBINARY, str)`
`CHARACTERS(str)`	`LENGTH(str)`	`LEN(str)`

Pattern 18: Teradata HELP commands¶

Teradata:

HELP DATABASE my_db;
HELP TABLE my_db.orders;
HELP COLUMN my_db.orders.*;
HELP STATISTICS my_db.orders;

Spark SQL:

SHOW TABLES IN my_db;
DESCRIBE TABLE EXTENDED my_db.orders;
DESCRIBE TABLE my_db.orders;
SHOW TBLPROPERTIES my_db.orders;

T-SQL:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'my_db';
EXEC sp_columns @table_name = 'orders';
EXEC sp_helpstats @objname = 'orders';

Pattern 19: CAST with Teradata-specific formats¶

Teradata:

SELECT CAST(order_date AS CHAR(10) FORMAT 'YYYY-MM-DD') FROM orders;
SELECT CAST('2024-01-15' AS DATE FORMAT 'YYYY-MM-DD') AS dt;
SELECT CAST(amount AS FORMAT '$ZZZ,ZZ9.99') FROM orders;

Spark SQL:

SELECT DATE_FORMAT(order_date, 'yyyy-MM-dd') FROM orders;
SELECT TO_DATE('2024-01-15', 'yyyy-MM-dd') AS dt;
SELECT FORMAT_NUMBER(amount, 2) FROM orders;

T-SQL:

SELECT CONVERT(VARCHAR(10), order_date, 120) FROM orders;
SELECT CAST('2024-01-15' AS DATE) AS dt;
SELECT FORMAT(amount, '$#,##0.00') FROM orders;

Pattern 20: Error handling in procedural SQL¶

Teradata SPL:

CREATE PROCEDURE safe_insert()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    BEGIN
        INSERT INTO error_log VALUES (CURRENT_TIMESTAMP, 'Duplicate key');
    END;

    INSERT INTO target SELECT * FROM source;
END;

Spark SQL (Databricks notebook):

try:
    spark.sql("INSERT INTO target SELECT * FROM source")
except Exception as e:
    spark.sql(f"""
        INSERT INTO error_log VALUES (current_timestamp(), '{str(e)}')
    """)

T-SQL:

CREATE PROCEDURE dbo.safe_insert AS
BEGIN
    BEGIN TRY
        INSERT INTO target SELECT * FROM source;
    END TRY
    BEGIN CATCH
        INSERT INTO error_log VALUES (GETDATE(), ERROR_MESSAGE());
    END CATCH
END;

Pattern 21: CREATE TABLE AS (CTAS)¶

Teradata:

CREATE TABLE new_orders AS (
    SELECT * FROM orders WHERE order_date >= DATE '2024-01-01'
) WITH DATA PRIMARY INDEX (customer_id);

Spark SQL:

CREATE TABLE new_orders
USING DELTA
PARTITIONED BY (order_month)
AS SELECT *, DATE_FORMAT(order_date, 'yyyy-MM') AS order_month
FROM orders WHERE order_date >= '2024-01-01';

T-SQL:

CREATE TABLE new_orders
WITH (
    DISTRIBUTION = HASH(customer_id),
    CLUSTERED COLUMNSTORE INDEX
)
AS SELECT * FROM orders WHERE order_date >= '2024-01-01';

Pattern 22: LOCKING modifiers¶

Teradata:

LOCKING TABLE orders FOR ACCESS
SELECT * FROM orders WHERE order_date = CURRENT_DATE;

LOCKING ROW FOR WRITE
SELECT * FROM orders WHERE order_id = 12345;

Spark SQL:

-- Delta Lake uses MVCC; no explicit locking needed
-- Read isolation is automatic
SELECT * FROM orders WHERE order_date = CURRENT_DATE();

T-SQL:

-- Synapse: NOLOCK hint (similar to ACCESS lock)
SELECT * FROM orders WITH (NOLOCK) WHERE order_date = CAST(GETDATE() AS DATE);

Pattern 23: EXPLAIN / query plan¶

Teradata:

EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

Spark SQL:

EXPLAIN EXTENDED SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
EXPLAIN COST SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

T-SQL:

-- Enable estimated plan
SET SHOWPLAN_XML ON;
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
SET SHOWPLAN_XML OFF;

Pattern 24: IDENTITY columns and sequences¶

Teradata:

CREATE TABLE audit_log (
    log_id INTEGER GENERATED ALWAYS AS IDENTITY,
    event_type VARCHAR(50),
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Spark SQL:

CREATE TABLE audit_log (
    log_id BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type STRING,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
) USING DELTA;

T-SQL:

CREATE TABLE audit_log (
    log_id INT IDENTITY(1,1),
    event_type VARCHAR(50),
    event_time DATETIME2 DEFAULT GETDATE()
);

Pattern 25: MULTISET operations (EXCEPT, INTERSECT with ALL)¶

Teradata:

SELECT * FROM table_a EXCEPT ALL SELECT * FROM table_b;
SELECT * FROM table_a INTERSECT ALL SELECT * FROM table_b;

Spark SQL:

SELECT * FROM table_a EXCEPT ALL SELECT * FROM table_b;
SELECT * FROM table_a INTERSECT ALL SELECT * FROM table_b;

T-SQL:

-- T-SQL supports EXCEPT and INTERSECT but NOT the ALL variant
-- For EXCEPT ALL, use a ROW_NUMBER() workaround:
WITH a AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY (SELECT NULL)) AS rn FROM table_a),
     b AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY (SELECT NULL)) AS rn FROM table_b)
SELECT col1, col2 FROM a
EXCEPT
SELECT col1, col2 FROM b;

4. Stored procedure conversion patterns¶

Control flow mapping¶

Teradata SPL	Spark (Python)	T-SQL
`IF ... THEN ... ELSEIF ... END IF`	`if ... elif ... else`	`IF ... ELSE IF ... ELSE`
`WHILE ... DO ... END WHILE`	`while ...:`	`WHILE ... BEGIN ... END`
`FOR ... DO ... END FOR`	`for ... in ...:`	`WHILE` or cursor loop
`CASE ... WHEN ... END CASE`	`match ... case` (3.10+) or `if/elif`	`CASE ... WHEN ... END`
`DECLARE cursor FOR SELECT`	`spark.sql("SELECT ...").collect()`	`DECLARE cursor CURSOR FOR SELECT`
`CALL procedure(args)`	Function call	`EXEC procedure @args`
`LEAVE label`	`break`	`BREAK`
`ITERATE label`	`continue`	`CONTINUE`

Best practice: convert to dbt models¶

Most Teradata stored procedures perform transformations that are better expressed as dbt models:

# dbt model: models/marts/daily_summary.sql
-- Replaces: CALL sp_update_daily_summary()

{{ config(
    materialized='incremental',
    unique_key='report_date',
    incremental_strategy='merge'
) }}

SELECT
    CURRENT_DATE() AS report_date,
    category,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_date >= (SELECT MAX(report_date) FROM {{ this }})
{% endif %}
GROUP BY category

5. Batch conversion workflow¶

Step 1: Extract SQL inventory¶

# Export all BTEQ/SQL scripts from Teradata
find /path/to/teradata/scripts -name "*.bteq" -o -name "*.sql" | \
    while read f; do
        echo "=== $f ===" >> sql_inventory.txt
        head -50 "$f" >> sql_inventory.txt
    done

Step 2: Classify each script¶

import sqlglot

def classify_script(sql_text):
    """Classify Teradata SQL by migration difficulty."""
    teradata_features = {
        'QUALIFY': 'A',           # Auto-translatable
        'MERGE INTO': 'A',       # Nearly identical
        'COLLECT STAT': 'A',     # Simple replacement
        'NORMALIZE': 'B',        # Manual rewrite
        'PERIOD(': 'B',          # Schema change needed
        'CASESPECIFIC': 'A',     # Simple removal/addition
        'CREATE PROCEDURE': 'B', # Manual conversion
        'HASHROW': 'A',          # Simple replacement
        'TASM': 'C',             # Architectural change
        'QUERYGRID': 'C',        # Architectural change
    }
    worst = 'A'
    for feature, tier in teradata_features.items():
        if feature in sql_text.upper():
            if tier > worst:
                worst = tier
    return worst

Step 3: Batch transpile Tier-A scripts¶

import sqlglot
from pathlib import Path

source_dir = Path("teradata_scripts")
output_dir = Path("spark_scripts")
output_dir.mkdir(exist_ok=True)

for sql_file in source_dir.glob("*.sql"):
    with open(sql_file) as f:
        teradata_sql = f.read()
    try:
        spark_sql = sqlglot.transpile(teradata_sql, read="teradata", write="spark")
        with open(output_dir / sql_file.name, "w") as f:
            f.write("\n;\n".join(spark_sql))
        print(f"OK: {sql_file.name}")
    except Exception as e:
        print(f"FAIL: {sql_file.name} - {e}")

Step 4: Validate converted SQL¶

# Run converted SQL against test data and compare results
def validate_conversion(teradata_result_path, azure_result_path, tolerance=0.001):
    """Compare row counts and checksums between Teradata and Azure outputs."""
    td = pd.read_csv(teradata_result_path)
    az = pd.read_csv(azure_result_path)

    assert len(td) == len(az), f"Row count mismatch: {len(td)} vs {len(az)}"

    for col in td.select_dtypes(include='number').columns:
        td_sum = td[col].sum()
        az_sum = az[col].sum()
        diff = abs(td_sum - az_sum) / max(abs(td_sum), 1)
        assert diff < tolerance, f"Column {col}: {td_sum} vs {az_sum} (diff: {diff:.6f})"

    print("Validation PASSED")

Feature Mapping — Complete feature-to-feature mapping
Tutorial — BTEQ to dbt — Step-by-step BTEQ conversion
Data Migration — Data loading patterns
Teradata Migration Overview — SQL translation overview
sqlglot documentation: https://github.com/tobymao/sqlglot
Microsoft SAMA: https://aka.ms/sama

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

SQL Migration — Teradata SQL to T-SQL / Spark SQL¶

1. Conversion strategy¶

Approach¶

Tools¶

Using sqlglot¶

2. Data type conversions¶

Numeric types¶

String types¶

Date/time types¶

Special types¶

3. SQL conversion patterns¶

Pattern 1: QUALIFY clause¶

Pattern 2: Date arithmetic¶

Pattern 3: CASESPECIFIC and case sensitivity¶

Pattern 4: FORMAT phrases¶

Pattern 5: SAMPLE clause¶

Pattern 6: SEL / SELECT shorthand¶

Pattern 7: Named columns in GROUP BY¶

Pattern 8: TITLE / AS aliasing¶

Pattern 9: COLLECT STATISTICS → ANALYZE TABLE¶

Pattern 10: VOLATILE TABLE → Temp table¶

Pattern 11: MERGE with multiple match conditions¶

Pattern 12: NORMALIZE / PERIOD operations¶

Pattern 13: HASH functions¶

Pattern 14: ZEROIFNULL / NULLIFZERO¶

Pattern 15: RANK / PARTITION with Teradata extensions¶

Pattern 16: EXTRACT function¶

Pattern 17: String functions¶

Pattern 18: Teradata HELP commands¶

Pattern 19: CAST with Teradata-specific formats¶

Pattern 20: Error handling in procedural SQL¶

Pattern 21: CREATE TABLE AS (CTAS)¶

Pattern 22: LOCKING modifiers¶

Pattern 23: EXPLAIN / query plan¶

Pattern 24: IDENTITY columns and sequences¶

Pattern 25: MULTISET operations (EXCEPT, INTERSECT with ALL)¶

4. Stored procedure conversion patterns¶

Control flow mapping¶

Best practice: convert to dbt models¶

5. Batch conversion workflow¶

Step 1: Extract SQL inventory¶

Step 2: Classify each script¶

Step 3: Batch transpile Tier-A scripts¶

Step 4: Validate converted SQL¶

6. Related resources¶