What belongs in a Teradata migration assessment?
Inventory a complete consumer workload: tables and views, BTEQ scripts, macros or procedures, scheduled dependencies, file exchanges and the report or application that uses the result. Include the execution identity, session transaction mode and business calendar. These inputs reveal what a target implementation actually has to preserve.
Start with the source DDL and the statement that created the accepted output. Record whether a table is SET or MULTISET, its constraints, column types and relevant character attributes. Teradata's current Python driver documentation identifies session-mode differences in default table type, character comparisons and truncation behavior. The environment that executes a statement therefore belongs in the inventory alongside the SQL text.
Group objects by a consumer and a shared business date. A staging table used by several overnight jobs can be a stronger wave dependency than an application's department name. Use the existing warehouse migration playbook to connect the source-specific inventory to the wider sequencing and acceptance model.
- Record explicit settings: distinguish declared behavior from a default inherited through a client or session.
- Trace the last mile: include exported files, downstream filters and reconciliation spreadsheets that determine whether a result is accepted.
- 01
Relational result
The responsibility is a query or calculation with explicit inputs.
Evaluate conversion against typed result fixtures.Example: a daily sales aggregation with a declared duplicate rule.
- 02
Control and side effects
BTEQ, procedures or scripts decide what runs or gets published.
Design the execution and publication path separately.Example: a return-code branch stops a downstream file delivery.
- 03
Shared dependency
Another consumer still needs the source object or calendar.
Retain it with an owner or move the dependency in the same wave.Example: month-end reference data used by two reporting chains.
Illustrative routing decisions. One workload can contain all three routes; an object's disposition is not a claim of automated conversion coverage.
How can duplicate handling change the reported result?
Test row multiplicity as a business rule. Repeated full rows, repeated business keys and legitimate repeated transactions are different conditions. A target append can preserve more rows than an accepted source output, while a blanket DISTINCT can remove valid activity. The right comparison starts with the declared source behavior and the consumer's grain.
In the illustrative fixture below, the transport contains A/10 twice and B/5 once. The assumed consumer contract counts each identical event once, giving two accepted rows and a total of 15. Appending all three produces 25. This arithmetic demonstrates a contract difference; it does not assert how every Teradata loader or target statement handles duplicates.
Loading method matters too. The Teradata driver documentation lists a FastLoad restriction on duplicate rows in a MULTISET table with a primary index. Inspect the actual path and its error tables rather than inferring accepted rows from the table label alone. If the two A/10 records are distinct real transactions, keep their event identities and change the expected contract accordingly.
Illustrative contract: identical event rows count once. Amounts are unitless; distinct real transactions must retain their own identities.
- 01
A · 10 - 02
A · 10 - 03
B · 5
Transport
3 rows
All delivered rows; amount total 25.
Declared result
2 rows · 15
A/10 and B/5 after the stated identical-event rule.
Unqualified append
3 rows · 25
The same transport becomes a different consumer result.
The fixture checks the business contract. Actual SET/MULTISET, loader and constraint behavior must be established for the chosen source path.
Which source responsibilities need separate target decisions?
Classify each object by the responsibility it carries before selecting a replacement. Relational transformation, conditional job control, operational side effects and physical workload design need different evidence. A successful SQL translation covers only the responsibilities represented by that statement.
Retain the original object reference and the reason for its disposition. That record makes a later discrepancy traceable to a concrete decision. The Snowflake platform guide provides the destination context; the disposition remains specific to this source workload.
Separate a translator's output from its unresolved inventory. For every material source feature, record whether the tool emitted a candidate statement, reported an unsupported construct or left behavior outside its scope. Keep the tool version, source settings and the exact target selected. Evaluate an emitted statement with the same consumer fixtures used for a manually implemented replacement; a conversion percentage is not a denominator for business acceptance.
| Dimension | Target decision | Acceptance evidence |
|---|---|---|
| Table and comparison semantics | Specify duplicate, null, precision and character-comparison behavior explicitly. | Boundary rows reproduce the consumer's accepted result under recorded session settings. |
| BTEQ and scheduler control | Map conditions, return handling, dependencies and publication steps to an owned execution path. | A failed upstream step cannot publish a downstream extract as current. |
| Macros and procedures | Separate reusable query logic from procedural state and external effects. | Each represented branch has defined inputs, outputs and a failure disposition. |
| Physical workload assumptions | Reconsider source layout and concurrency against target query patterns. | Equivalent consumer queries are compared on representative data and concurrency. |
| Consumer and access | Name the extract, report identity, business date and permitted population. | Values and access are compared through the actual consuming path. |
How do extraction and restart boundaries affect correctness?
Define which consistent source state the first load represents and how later changes reach the destination. Record the handoff between extraction, transport, target commit and consumer publication. A file arriving successfully establishes a transport event; acceptance also needs evidence that the intended source state and change boundary are represented.
Specify how updates, deletes and corrections are identified, how much source history is retained and which checkpoint is authoritative after a partial run. Include the case where target data was committed but publication acknowledgement was lost. Repeating the extraction should have an understood effect on accepted rows and downstream files.
Choose batch, capture or another change mechanism from the source's actual capabilities and the consumer's freshness requirement. The CDC, batch and streaming comparison helps distinguish those mechanisms. Keep a reference workload bounded enough that its restore and replay behavior can be explained without promising a whole-estate cutover method.
The handoff map makes three states visible: the source boundary represented, the candidate state committed and the consumer reference acknowledged. If a transport retry succeeds after acknowledgement was lost, recover from those recorded states rather than assuming the whole batch was absent. Keep the source retention interval and any already delivered external file with that decision. The data contracts guide helps specify the permitted consumer population and publication meaning.
- Accepted source boundaryBusiness date identified
Keep DDL, session and the source state represented.
- Candidate target stateSubsequent changes bounded
Record updates, deletes and the retained replay interval.
- Consumer referenceSource dependency disposition
Keep or retire each producer only within its agreed scope.
- Accepted source boundaryLoad identity retained
Connect files or batches to the source boundary.
- Candidate target stateMeaning reconciled
Check multiplicity, comparisons and accepted totals.
- Consumer referenceVersion retained
Keep the candidate and the result evidence used to accept it.
- Accepted source boundaryOld output stays authoritative
A target table's arrival does not change the report reference.
- Candidate target statePublication decision recorded
Use the actual reporting identity and business cutoff.
- Consumer referenceAcknowledgement captured
Name the output version, run owner and recovery exclusions.
Illustrative state handoff, not an elapsed-time chart. A committed target load and a consumer's acknowledged publication are different events.
What must be true before a consumer switches?
A consumer switches after the agreed data, meaning, access and operating checks pass for its dependency wave. Compare the accepted source and candidate target at the same business boundary. Keep unresolved differences attached to the affected consumer rather than hiding them in an aggregate row-count score.
Include duplicate multiplicity, nulls, rounding boundaries, late corrections and records near the business-day cutoff. Reconcile component values as well as totals: duplicated positive and negative amounts can cancel in a grand total. Test the report or extract identity, because administrator access does not demonstrate the consumer's allowed and denied paths.
The workflow below carries the consumer reference through evidence capture and the switch decision. Name the last recoverable state and what remains outside it, such as a file already delivered or a business action already taken. Use the existing Snowflake migration service to scope the workload and its required acceptance evidence.
- 01Trace the workload
Connect tables, batch branches and the final extract.
Keep: source DDL, session and dependency record.
- 02Compare the meaning
Check duplicates, nulls, rounding and business-day boundaries.
Keep: typed inputs and expected consumer values.
- 03Prove the handoff
Compare accepted publications and test the actual report identity.
Keep: output versions and allow/deny observations.
- 04Switch one wave
Move the agreed consumer reference with a recoverable boundary.
Keep: switch decision, acknowledgement and run owner.
Hold the consumer reference when a semantic or access difference remains unresolved. A successful statement alone is not the acceptance decision.
How should performance and operating cost be compared?
Compare equivalent results under the consumer's actual workload envelope: data volume, refresh window, concurrency, access rules and query mix. Preserve the result contract while evaluating target choices. A smaller isolated query time can be irrelevant if the full dependency wave misses its publication window.
Keep configuration, cache conditions, data snapshot and measurement window with each observation. Include source and target running together, data transfer, retained copies and operating responsibilities in the economic comparison. A source appliance's layout settings do not establish a one-to-one target tuning prescription.
Use the platform cost assessment for the wider attribution boundary. The guide supplies a comparison structure, not a published benchmark, savings figure or a fixed project duration.
Estimate effort by responsibility: source behavior to disposition, control branches to rebuild, consumer comparisons to evidence and operating boundaries to prove. Keep bulk transfer, retained source operation, target serving and exception handling in separate cost lines. The edge-case pack below shows why a simple table count omits work: two identically valued events, a comparison rule and a calendar correction require different acceptance evidence.
Illustrative contracts: event identity distinguishes real activity; comparisons follow a declared case rule; the reporting calendar owns business-day assignment.
- 01
Repeated value, distinct events
Input / conditione1/A/10, e2/A/10, e3/B/5
Expected contract3 events · total 25
Evidence to retainRetain event IDs. Removing identical value pairs would incorrectly produce 15.
- 02
Character comparison
Input / conditionCustomer codes a and A
Expected contractSeparate or equal only by the declared contract
Evidence to retainKeep column/session comparison settings with each expected match set.
- 03
Business-day boundary
Input / conditionA correction arrives after the agreed cutoff
Expected contractAn explicit date and correction disposition
Evidence to retainCompare the original publication, corrected result and consumer notification boundary.
These are acceptance specifications. They do not assert universal Teradata/Snowflake defaults or results from an executed migration.
What should the migration scoping brief contain?
Bring a representative consumer, its source objects and execution settings, an accepted output, the required business date, and the conditions that make a result usable. Include peak workload evidence, access boundaries, operational dependencies and the available extraction history. This creates a concrete starting point for a migration assessment.
Identify which facts are measured and which remain assumptions. A sample containing only uncomplicated SELECT statements can leave the hard parts outside the estimate. Include one meaningful edge case and the branch that handles a failed run. Use synthetic or appropriately permitted examples when sharing the brief.
For another source estate, read the Oracle to Snowflake guide or Netezza to Snowflake guide. Their source-specific checks complement this guide; they do not imply that one conversion rule applies across all three platforms.
Questions and trade-offs
Does a successful SQL conversion establish migration parity?
It establishes that the represented statement can execute under the tested conditions. Parity also depends on its source state, row multiplicity, session semantics, dependencies and consuming identity. Keep those comparisons with the object and the consumer they protect.
Keep a feature-by-feature disposition alongside the converted SQL. An unrepresented BTEQ branch, loader reject or external file can still change the business outcome. Use typed fixtures and accepted source publications to evaluate both translated and manually implemented logic under the same conditions.
Should every repeated row be removed?
No. First distinguish duplicate transport delivery from valid repeated business activity and from a source table's declared behavior. The worked fixture assumes an identical-event rule. An accepted source contract with two distinct events needs both events preserved.
Give events an identity before choosing a deduplication rule. In the guide's negative control, e1/A/10 and e2/A/10 are different events, so the accepted total is 25. Keep both that case and the identical-event case in the pack; one blanket DISTINCT cannot satisfy both contracts.
Can a migration run as several dependency waves?
Yes, where dependencies and consumer boundaries permit it. Each wave needs its own data boundary, acceptance conditions and operating handoff. Shared staging, calendars, permissions and downstream files can require several objects to move together.
A useful first wave ends at a consumer reference with bounded upstream dependencies. Keep shared calendar/reference data in the dependency map and identify which source responsibilities continue afterwards. Wave size is an output of that analysis, not an arbitrary number of schemas or tables.
What determines the effort of a Teradata migration?
The source feature inventory, orchestration and side effects, data/change volume, consumer dependencies, access model and acceptance requirements shape the effort. The number of tables alone does not describe those responsibilities. Scope a representative workload before extending the estimate.
Assess one representative dependency chain, including a procedural or failed-run branch, then explain how its feature mix compares with the remaining estate. Separate measured inventory, assumptions and exclusions. Do not extend a small SQL-only sample into a fixed whole-estate duration or savings claim.
How should BTEQ scripts and scheduler dependencies be handled?
Inventory the decisions the script makes: parameters, conditions, return handling, dependencies, file movement and publication. Assign each to an actual target execution component or a retained source responsibility. SQL calculation and job control can have different owners.
Compare a failed-upstream path as well as a successful path. The useful acceptance question is whether a stale or partial extract can become current, not whether the replacement scheduler started a task.
Can bulk loading and change capture be combined?
They can be considered together when the source and chosen tools support a consistent first-load state and a defined subsequent-change boundary. Record how updates, deletes, late corrections and the source retention interval cross that boundary. This guide does not prescribe a connector or an untested capture method.
Use a bounded workload to demonstrate how the target resumes after a partial load or a lost acknowledgement. A successful bulk copy alone does not establish continuous freshness.
Can a Teradata cutover be guaranteed to have no downtime?
A downtime promise needs evidence from the actual dependencies, source/target overlap, change mechanism and consumer switch. Define the permitted publication gap and which consumers can switch independently. Shared files, application actions and business calendars can constrain the window.
Rehearse the agreed boundary and record recovery exclusions. Keeping two warehouses available is useful, but it does not automatically reverse an extract already delivered or a business action already taken.
Which evidence should a migration tool or provider supply?
Ask for the supported source/target and tool versions, represented feature inventory, unresolved constructs, generated candidates, comparison method and exception disposition. Request a reproducible result on a permitted workload that includes a meaningful edge case and a control dependency.
Keep the acceptance denominator explicit: objects parsed, statements emitted, rows compared and consumers accepted describe different things. None alone establishes total migration coverage or an approved delivery timeline.
