Guide

Converting T-SQL, SSIS, Teradata BTEQ and Hive: the patterns that break, and how to handle them

Q: Which T-SQL patterns cause the most conversion failures?

In our assessments, the hardest are cursors, dynamic SQL built from table contents, TRY/CATCH with transactions, and temp-table chains. Quieter failures come from DATEDIFF boundary rules, case-insensitive collations and NULL concatenation. Those return plausible but wrong results. Each needs a rule-based conversion and a reconciliation on inputs that exercise the edge: ties, NULLs, mixed case and boundary dates.

Q: How does an SSIS package map to Databricks or Fabric?

The control flow becomes a Databricks job or a Fabric pipeline. Precedence constraints become task dependencies and failure branches. The data flow becomes SQL or notebook code. Execute SQL tasks are converted as T-SQL. Script Tasks with C# or VB go through the resolver, with an engineer approving each proposal. Each data flow is reconciled on its own, then the whole job runs to schedule.

Q: What happens to BTEQ control commands such as .IF ERRORCODE and .GOTO?

They are a program, not comments, so they become orchestration. Each script turns into job tasks or a SQL script with handlers. Each .QUIT exit code maps to a task outcome the scheduler can read. Testing runs every branch, including each tested error code and the zero-row case, then compares exit codes with the original.

Q: Why does converted Teradata code return more rows than the original?

Usually because the source table was a SET table. In Teradata session mode, INSERT...SELECT into a SET table silently discards duplicate rows. Delta tables keep them. Add explicit deduplication to every insert into a former SET table. Then seed exact duplicate rows in the test inputs and compare counts after each insert.

Q: How much of a conversion needs a language model?

About 95% of objects convert by fixed, versioned rules, with no model calls. The resolver handles about 5% of objects, at 700 to 1,000 tokens per edge case. It runs on a model the customer approves, including a local one. A named engineer approves every proposal, and nothing deploys unapproved.

Q: How do I prove a converted object matches the original?

Use two tests. First, convert the object twice and diff the outputs; the diff should be empty. Second, run the original and converted code on the same inputs. Compare schema, row counts and column-level values. Mark each object pass, fail or review, and list every miss with its cause and fix.

By Ted Ghose, Co-founder and CTO · Published 2026-10-30 · 17 minutes read

The short answer

Most conversion failures come from a small set of patterns: cursors, dynamic SQL, error handling, session-scoped temp objects, case and NULL semantics, date boundaries and orchestration. Convert them with fixed, versioned rules so two runs give identical output. Send the edge cases to an engineer for approval. Prove each object by reconciling original and converted code on the same inputs.

Why do the same patterns break on every legacy estate?

Data migration is solved. Logic migration is not. The data in a warehouse moves with well-understood tools. The stored procedures, packages, scripts and schedules around it are where conversions stall.

This guide lists the patterns that break most often when legacy warehouse code moves to Databricks, Microsoft Fabric, Snowflake, Apache Iceberg or Delta Lake. It is organized by source dialect. Each pattern gets one table row: why it breaks, how to convert it, and how to test it.

The patterns come from the estates we assess and deliver at Novuz. The same few constructs cause most of the trouble: cursors, dynamic SQL, error handling, temp-table chains and orchestration. Most of them do not fail loudly. They return a plausible answer that is slightly wrong.

Every target behavior below was checked against the vendor's own documentation, listed in the sources. Version numbers are as documented at the time of writing. Confirm them against your own workspace before you rely on them.

How to read the tables in this guide

Each row names a pattern, explains why a literal translation breaks, gives the conversion we apply, and says how to prove it. Code fragments are short and illustrative, not complete programs.

Two tests appear throughout. A two-run diff converts the same object twice and compares the outputs; with fixed rules the diff is empty. Reconciliation runs the original and the converted object on the same inputs, then compares schema, row counts and column-level values.

A test that never exercises the risky branch proves nothing. For each pattern, the last column names the input that does.

Which T-SQL patterns break, and how do you convert them?

SQL Server estates carry more procedural code than any other source we see. The hard cases are rarely single statements. They are procedures that mix row-by-row loops, session state and error handling.

The rows below assume a Databricks SQL target unless stated. The same reasoning applies to Fabric and Snowflake, with each platform's own syntax.

Pattern	Why it breaks	How to convert	How to test
Cursors and WHILE loops	Row-by-row loops assume ordered, stateful execution. Translated literally, they run slowly on distributed engines and hide ordering assumptions.	Rewrite as set-based SQL by rule, often a window function or a MERGE. Where order truly matters, Databricks SQL scripting supports WHILE, FOR and, on recent runtimes, cursors.	Reconcile on inputs with ties, empty sets and a single row. Check that running totals and ordering-dependent values match.
Temp tables and table variables	#temp tables are session-scoped. Table variables ignore ROLLBACK, so some logic depends on rows surviving a failed transaction.	Map to temporary tables, temporary views or CTEs by usage. Databricks temporary tables do not support DELETE FROM, so deletes become MERGE or a rebuild.	Force a failure and compare what survives. Confirm no temp object leaks between sessions.
TRY/CATCH and transactions	TRY/CATCH does not catch compile errors or same-level name-resolution errors. XACT_STATE and XACT_ABORT decide what can still commit.	Map to SQL scripting EXIT handlers, or to task failure branches. Use BEGIN ATOMIC where multi-statement atomicity is needed and the target tables support it.	Inject a failure mid-procedure. Compare final table state and the error log row with the original.
@@ROWCOUNT and @@ERROR	Both describe only the last statement. A SET option, PRINT or BEGIN TRANSACTION sets @@ROWCOUNT to 0. Code that reads it late reads the wrong value.	Capture the count into a variable straight after the DML it describes. Replace @@ERROR checks with handler logic.	Compare audit and log tables, not only target tables. Include a zero-row update.
Dynamic SQL (EXEC, sp_executesql)	Strings built at run time hide table names and logic from static parsing. Some are built from table contents.	Convert fixed-shape strings to EXECUTE IMMEDIATE with USING parameters. Send strings built from data to the resolver.	Reconcile on inputs that drive each branch of the string. List every distinct statement generated.
TOP WITH TIES	WITH TIES returns extra rows that tie on the ORDER BY columns. A plain LIMIT drops them.	Rewrite with RANK and QUALIFY. Illustrative: QUALIFY RANK() OVER (ORDER BY amount DESC) <= 10.	Use inputs with ties at the cutoff. Compare row counts exactly.
CROSS APPLY and OUTER APPLY	APPLY evaluates a table expression per left row. OUTER APPLY keeps left rows with an empty right side.	Convert to JOIN LATERAL or LEFT JOIN LATERAL. Convert TOP 1 per group patterns to a window function.	Include left rows with no right-side match. Check that NULL-filled rows survive.
MERGE semantics	Both engines reject several source rows matching one target row on update. Databricks allows it for unconditional deletes. OUTPUT $action has no direct equivalent.	Deduplicate the source by rule before the MERGE. Rebuild per-action counts from a change log or a follow-up query.	Seed duplicate source keys on purpose. Compare insert, update and delete counts separately.
IDENTITY columns	Delta and Fabric identity values are unique but not contiguous. Fabric does not accept a custom seed. A Delta identity column disables concurrent transactions.	Use identity columns only for new internal keys. Load migrated keys as explicit values. Remove any logic that assumes contiguous values.	Check uniqueness and referential integrity. Do not expect surrogate values to match.
Implicit conversions	SQL Server converts by data type precedence, so a varchar compared with an int becomes an int. Spark coerces differently, and ANSI mode rejects invalid casts.	Make every conversion explicit by rule. Use try_cast only where the original tolerated bad values.	Reconcile on dirty inputs: blanks, non-numeric strings and overflow values.
DATEADD and DATEDIFF boundaries	T-SQL DATEDIFF counts boundaries crossed. Databricks timestampdiff counts whole elapsed units. One second across midnight is one day in T-SQL and zero in timestampdiff.	Convert to boundary logic by rule. Illustrative: DATEDIFF(day, a, b) becomes datediff(CAST(b AS DATE), CAST(a AS DATE)).	Test spans across midnight, month ends, year ends and leap days.
Collation and case sensitivity	Many SQL Server estates we assess run a case-insensitive collation. Databricks defaults to UTF8_BINARY, which is case-sensitive. Joins and GROUP BY change silently.	Apply UTF8_LCASE or another case-insensitive collation where the source relied on one. Otherwise normalize case explicitly.	Seed mixed-case keys. Compare group counts and join matches.
NULL in string concatenation	With CONCAT_NULL_YIELDS_NULL on, 'a' + NULL is NULL. Older code may assume the deprecated OFF setting.	Map + to concat, which also returns NULL. Map to concat_ws or coalesce where the source suppressed NULLs, because concat_ws skips them.	Put a NULL in every concatenated column. Compare output strings exactly.

What changes when an SSIS package becomes a job?

An SSIS package is two programs. The control flow decides what runs and when. The data flow moves and transforms rows. They convert to different things on the target.

On the estates we have delivered, the control flow becomes a Databricks job or a Fabric pipeline. The data flow becomes SQL or notebook code. SQL inside Execute SQL tasks is converted as T-SQL, using the rules above.

C# Script Tasks are among the risks we flag most often in assessments. They are where an SSIS estate stops being declarative.

Pattern	Why it breaks	How to convert	How to test
Control flow vs data flow	Treating a package as one unit mixes orchestration with transformation. The result is hard to test.	Split by rule. Control flow goes to job tasks or pipeline activities. Data flow goes to SQL or notebooks.	Reconcile each data flow alone, then run the whole job against the original schedule.
Execute SQL tasks that call procedures	The task binds parameters and result sets to package variables. That wiring is invisible in the procedure itself.	Convert the procedure as T-SQL. Map each binding to a job or pipeline parameter.	Compare variable values after each task, not just final tables.
Script Tasks and Script Components	C# or VB code can do anything: move files, call APIs, transform rows. No fixed rule covers arbitrary code.	Convert to notebook code through the resolver. An engineer reviews each proposal. External calls are listed.	Reconcile outputs. Review every listed external call with its owner.
Foreach loops over files	The enumerator picks files by mask and order. Variables change on each iteration.	Map to a For each task in Databricks, or ForEach with Get Metadata in Fabric. Keep the file mask and order explicit.	Test zero files, one file, many files and one malformed file.
Event handlers	Events bubble up the container hierarchy to the nearest handler. OnError logging often sits at package level.	Map OnError and OnTaskFailed to failure branches. Use Run if 'At least one failed' in Databricks, or UponFailure in Fabric.	Force a failure in a nested task. Check the handler logic ran once, at the right level.
Package configurations and parameters	Configurations, environments and expressions set connections at run time. Values differ per environment.	Extract every configured value into job or pipeline parameters and a secret store. Keep one value set per environment.	Diff the resolved parameters between environments before the first run.
Precedence constraints	Constraints combine Success, Failure or Completion with optional expressions. Several constraints join by logical AND or OR.	Map Success to 'All succeeded', Completion to 'All done' and Failure to 'At least one failed'. Expressions become If/else condition tasks. Mixed OR groups go to review.	Walk every path: success, failure and each expression branch. Compare task run order.
Lookups and slowly changing dimensions	Full-cache lookups match case- and trailing-space-sensitively and return the first match. The SCD transformation splits Type 1, Type 2, fixed and inferred members into separate outputs.	Convert lookups to joins with explicit duplicate handling. Convert each SCD output to its own MERGE branch, with history columns kept.	Seed duplicate reference keys, case variants and late-arriving members. Compare current and historical rows.

Which Teradata and BTEQ patterns break?

Teradata estates put logic in two places: the SQL, and the BTEQ scripts that run it. In our assessments, the BTEQ control flow nobody wants to read is often the part that matters most.

BTEQ is a scripting layer, not SQL. Its commands decide whether the next statement runs. Convert the script as text and that logic disappears.

Pattern	Why it breaks	How to convert	How to test
BTEQ control commands (.IF ERRORCODE, .GOTO, .LABEL, .QUIT)	Together they form a branching program. GOTO only skips forward. Exit codes from .QUIT signal the scheduler.	Convert each script into job tasks, or a SQL script with handlers. Map each .QUIT code to a task outcome the scheduler reads.	Run every branch: success, each tested error code and the zero-row case. Compare exit codes.
Volatile and global temporary tables	With ON COMMIT DELETE ROWS, rows vanish when a transaction ends. In Teradata session mode, each request is its own transaction.	Map to temporary tables or views. State the preserve-or-delete behavior explicitly in the converted code.	Compare the temp object's row count after each step, not only at the end.
QUALIFY	Common in Teradata code. It filters on window function results.	Keep QUALIFY where the target supports it, as Databricks SQL and Snowflake do. Elsewhere, rewrite as a subquery with a window filter.	Seed ties on the window ORDER BY. Add a deterministic tiebreaker, then compare rows.
SET vs MULTISET tables	In Teradata session mode, INSERT...SELECT into a SET table silently discards duplicate rows. Delta tables keep them.	Add explicit deduplication to every insert into a former SET table. Illustrative: INSERT INTO t SELECT DISTINCT ...	Seed exact duplicate rows. Compare counts after each insert.
Case-insensitive default comparisons	In Teradata mode, most character columns default to NOT CASESPECIFIC. 'dbc' matches 'DBC'.	Apply a case-insensitive collation or explicit UPPER, by rule from each column definition.	Seed mixed-case keys. Compare joins, filters and DISTINCT counts.
CHAR padding	Fixed-length CHAR values carry trailing spaces. Engines differ in how comparison, length and concatenation treat them.	Choose by rule: trim on load, or use an RTRIM collation where the target supports it. Check every CHAR join key.	Seed values with trailing spaces. Compare join matches and string lengths.
Macros	Macros are stored, parameterized SQL, often called from BTEQ. One macro can hold several statements.	Convert to SQL procedures or parameterized scripts. Keep the statements in one unit.	Call with the parameters production uses. Reconcile every table the macro touches.
FastLoad, MultiLoad and TPT jobs	Load utilities carry logic outside the SQL: error tables, duplicate options, skip rules and checkpoints.	Convert to the target's bulk-load path. Keep the rules that sent rows to error tables, as explicit reject handling.	Reconcile loaded rows and rejected rows. Both counts must match.
Date formats	FORMAT clauses and session defaults shape how date strings parse and print. Comparisons against string literals inherit them.	Make every date parse and format explicit by rule. Never rely on a session default.	Test month ends, century boundaries and NULL dates. Compare formatted output strings too.

What breaks in Hive and Impala code?

Hive and Impala code looks like SQL, but much of its behavior lives in the table layout. Partitions, file formats and settings change results without changing the query.

The orchestration usually sits in cron, Oozie or shell scripts nobody owns. We map it with the rest of the estate before converting anything.

Pattern	Why it breaks	How to convert	How to test
Partition handling	Queries rely on partition columns for pruning and for knowing which data exists. Partitions added by scripts may never be registered.	Keep partition columns as ordinary columns. Choose the target layout by data size and query pattern, not by habit.	Reconcile per partition value: row counts and aggregates.
INSERT OVERWRITE semantics	Hive jobs often expect only the written partitions to be replaced. Spark's default partition overwrite mode is static, which can replace more.	Convert to INSERT INTO ... REPLACE WHERE, or REPLACE USING on recent runtimes, scoped to the partitions written.	Run with one partition of input and confirm the others are unchanged. Test an empty source, since REPLACE WHERE can then delete rows.
UDFs	Java UDF libraries carry custom logic, often undocumented and untested.	Replace with built-in functions where a rule matches. Port the rest through the resolver, with engineer approval.	Unit-test each UDF on boundary values, then reconcile the queries that call it.
Complex types	ARRAY, MAP and STRUCT columns are flattened with LATERAL VIEW explode. OUTER decides whether rows with empty arrays survive.	Keep the OUTER semantics exactly. Databricks deprecates LATERAL VIEW, so prefer generator functions as table references.	Seed empty and NULL arrays. Compare exploded row counts.
File-format-specific behavior	Text, ORC and Parquet tables differ in NULL markers, delimiters and schema handling. Results can depend on the format, not the SQL.	Read each table definition, including SerDe and NULL markers. Load into the target table format with explicit types.	Reconcile on raw files with NULLs, empty strings and delimiters inside values.

What breaks when Snowflake is the source?

Snowflake estates are newer, but they still hold procedural logic and orchestration. Most of it lives in procedures, tasks and streams.

Procedures come in JavaScript, Snowflake Scripting or Python. Each needs its own conversion pattern.

Pattern	Why it breaks	How to convert	How to test
JavaScript and SQL procedures	JavaScript procedures build SQL as strings and step through result sets. Argument names are case-sensitive in JavaScript but uppercased in SQL.	Parse each language on its own terms. Convert control flow and SQL by rule to SQL procedures or notebooks.	Reconcile each procedure on the same inputs. Include the error paths its try/catch handles.
Streams and tasks	A stream's offset advances only when DML consumes it. A child task runs after all predecessors succeed. WHEN conditions skip runs.	Rebuild task graphs as jobs with the same dependencies. Convert streams to change data feed reads with explicit checkpoints. WHEN becomes an If/else task.	Replay the same change batch twice. The second run must process nothing new.
VARIANT and semi-structured data	Element names are case-sensitive. Casts with :: and LATERAL FLATTEN carry implicit behavior.	Map to Databricks VARIANT with colon paths and variant_explode, or to an explicit schema.	Reconcile field by field on documents with missing, NULL and mixed-type fields.
QUALIFY	Supported on Databricks SQL too, so it is low risk. Ordering ties can still differ.	Keep QUALIFY. Add a deterministic tiebreaker where the original had none.	Seed ties. If results differ, the original was nondeterministic as well; record that.
Time travel assumptions	Snowflake's standard retention is one day, up to 90 on Enterprise Edition and higher. Delta keeps 30 days of history but VACUUM removes files after 7 by default.	Inventory every AT, BEFORE, CLONE and UNDROP. Set target retention explicitly, or replace recovery logic with snapshots.	Run each restore or point-in-time query on the target, inside its retention window.

Why is orchestration as important as the SQL?

A converted procedure that runs at the wrong time gives the wrong answer. Schedules, dependencies and failure paths are logic too. On the estates we have delivered, they are often the least documented part.

We rebuild orchestration to the original schedule and dependency order. That covers SQL Agent jobs, SSIS precedence, BTEQ exit codes, cron, Oozie and Snowflake task graphs. Jobs that depend on an external scheduler are mapped where a rule exists and flagged where not.

Dependency order also sets the conversion order. Convert and reconcile upstream objects first. Otherwise a downstream miss caused by an unreconciled upstream object costs a day of debugging.

Dead code is logic with no caller, no schedule and no reads. Every estate we assess has some. It comes out of scope before pricing and out of the denominator of every percentage we report.

Dead by reference is not always dead in practice. A procedure may be called from outside the warehouse. The business owner of each domain confirms the list before cutover.

Why do rules beat sampling for repeatability?

If a conversion gives a different answer each run, you cannot prove it. Auditors do not accept probabilities. A converter that samples from a model can return different code each time, so every rerun needs a fresh review.

The usual alternatives each fall short somewhere. Manual rewrite is slow and varies by engineer. AI code assistants are quick to start but probabilistic. Platform-native converters are a good start for SQL translation, and many teams run one first.

Novuz converts each object with fixed, versioned rules. The same code in gives the same code out. Run it twice and the diff is empty. Change a rule and the diff shows exactly which objects changed.

About 95% of objects convert this way, with no prompts, no sampling and no model cost. That is the rule share: objects that finish in rule-based conversion. It is a different number from the automation rate.

The automation rate is typically 95% or more of objects converted with no manual edit. A one-line fix counts as manual, and dead code is excluded from the denominator. First-pass reconciliation is typically above 95%, and the report names the rest.

When does an object need the resolver and an engineer?

Some constructs have no safe rule. In the tables above, they include dynamic SQL built from data, Script Tasks, custom UDFs and logic that depends on undefined ordering.

Those objects go to the resolver. It handles about 5% of objects, at 700 to 1,000 tokens per edge case, on a model the customer approves, including a local one. A named engineer approves every proposal. Nothing deploys unapproved.

The resolver is not an agent, and it is not autonomous. It proposes and the engineer decides. The approval log records the engineer, the object and the timestamp.

Undocumented business rules held by two people get different treatment. Their checks are added to the reconciliation. They become approvers, not converters.

SQL strings assembled from table contents or configuration tables.
Script Tasks or Script Components with arbitrary C# or VB code.
Custom UDF libraries with no built-in equivalent on the target.
Results that depend on row order the source never defined.
Business rules that live in someone's head, not in the code.

How do you test each pattern?

Two tests cover every row in this guide: the two-run diff and reconciliation on the same inputs. Use both on every object, in this order.

Convert twice and diff. Run the same object through conversion twice. The diff must be empty, or no later test can be trusted.
Choose inputs that exercise the pattern. Use the how-to-test column. Seed ties, NULLs, duplicates, mixed case, trailing spaces, boundary dates and failures.
Run the original and converted code on the same inputs. Capture every output, including log tables, audit rows and exit codes.
Compare schema, row counts and column-level values. Add aggregates on numeric and key columns, and compare sampled rows field by field.
Mark each object pass, fail or review. List every miss by line, with the cause and the fix.
Run the orchestration end to end. Compare task order and failure paths against the original schedule.

Where should you start on your own estate?

Start by counting. Know how many objects you have, which patterns they contain, and which are dead. Then convert one domain and reconcile every object before you plan the rest.

Coverage is confirmed per estate during the assessment.

Inventory every procedure, package, script, schedule and UDF in scope.
Flag the patterns in this guide per object, with counts.
Remove dead code from scope, with evidence and owner sign-off.
Convert one domain and reconcile every object on the same inputs.
Rebuild the orchestration and run it against the original schedule.

FAQ

Frequently asked questions

Which T-SQL patterns cause the most conversion failures?