Migrating Teradata to BigQuery – Out with the old, in with the new

You are ready to migrate your data from Teradata to BigQuery as quickly and efficiently as possible. 

You’re looking for a solution that doesn’t require you to manually migrate code, risking human error that can slow down your migration. 

In this guide, we discuss:

  • Why you shouldn’t migrate manually
  • How CompilerWorks offers simple solutions to your migration needs

Keep reading to learn more about data migration from Teradata to BigQuery and what your business can do to speed up the process.

Table of Contents

  • Teradata To BigQuery Migration – The Manual Way 
  • Potential Problems With Manual Migration
  • What is CompilerWorks?
  • CompilerWorks Platform Migration Benefits
  • CompilerWorks’ Best Migration Practices
  • Simplify Your Teradata TO BigQuery Platform Migration With CompilerWorks

Teradata To BigQuery Migration – The Manual Way 

83% of all data migrations fail to meet an organization’s expectations or fail completely. This is usually because when an organization or business starts a code migration, they are not aware of the fundamentals that make up a code migration. 

Although most migrations involve manual code migration as the most common code migration method — it is also the most time consuming and the most likely to have errors.

Converting Teradata code involves reading the code, understanding what it is doing, and manually converting it.  

Migrations can be complex and can become multi-year projects. 

If you are going to migrate from Teradata to BigQuery manually, there are a number of steps to take. Google refers to this as it’s migration framework, which involves:

  • Preparation
  • Planning
  • Migration
  • Verification and Validation

Preparation

Prepare for your migration — conduct an analysis, ask questions like: 

  • What are your cases for BigQuery? 
  • What databases are being migrated? — What can be migrated with little effort?
  • Which users and applications have access to these databases?
  • How is the data being used?

Planning

Start planning your migration by:

  • Assessing the current state
  • Create a backlog
  • Prioritizing cases 
  • Define your measures of success
  • Define done
  • Design a proof of concept (POC) 
  • Estimate time and costs of migration

Migration

It’s important to keep in mind that BigQuery and Teradata have different data types so conversions may be needed. 

Manually converting code is a tedious and difficult process that leaves a lot of room for human errors.

Then, you’ll perform an offload migration or a full migration

Verification and Validation

After converting the data, you have to test all the codes to make sure everything is working properly. Teradata migrations involve testing millions of lines of code in order to ensure that everything is running correctly.

Potential Problems With Manual Migration

Manual migration isn’t an easy task — a number of problems can arise. 

Teradata is one of the most complex systems on the market. Substantial amounts of code need to be added, read, and understood in order to work around the lack of syntax when doing a manual migration.

During this process, human error is inevitable. Code error can delay your migration project for weeks or even months.

Instead, Compilerworks eliminates human error by relying on smart technology to provide the same accurate results every time.

What is CompilerWorks?

CompilerWorks has developed a powerful solution that accelerates migration to the cloud. This solution covers: 

  • Structuring of the migration project
  • Automatic and accurate SQL code migration
  • Automated testing and verification

This technological solution involves two core applications: 

  1. The Transpiler Solution: This aids in the migration of SQL code between platforms.
  2. The Lineage Solution: This provides detailed insights concerning how data is used across an enterprise, including by who, for what, and at what cost.


CompilerWorks’ Core Technology

CompilerWorks’ core technology ingests source code and converts into Algebraic Representation, which will mathematically represent what the ingested code does.

Traditional compilers only work when given the complete code and full description of the execution environment. However, it’s impossible to meet these requirements in the realm of data processing code. 

In order to overcome this obstacle, Compilerworks’ software makes the same intelligent inferences that a human would and then reports these deductions to the user. 

Additionally, Compilerworks’ compilers can emit code in a high-level language (Transpiler solution) and in the lineage fabric (Lineage solution) which represents all actions of an entire code base.

CompilerWorks’ Supporting Infrastructure

In the real world, code rarely exists as simple .sql files. 

Database code is typically wrapped in scripts, B reports, and ETL tools. CompilerWorks provides the tools to extract the SQL code from various wrappers and then transpile and re-wraps it so that it is ready for execution and testing immediately. 

In the transpiler solution, there are hundreds of transformers embedded, including platform-specific optimization transpilers.

The lineage fabric takes advantage of the wealth of information captured by delivering global static analysis of data processing activities and providing GUI, CLI,  Graph QL, and API interfaces. The seamless integration of the CompilerWorks’ core technology and infrastructure combined represent the Transpiler Solution, which delivers fast, accurate, and predictable migration between data processing platforms.

CompilerWorks’ Platform Migration Benefits

Manual code migration is one giant mess waiting to happen. Human error is almost inescapable. 

With Compilerworks, software scopes out the entire project at the beginning of the migration process by automatically creating a comprehensive data lineage of source systems. This makes it possible for the system to automatically identify gaps in the source code to avoid project delays that can last up to months.

This automated process using the CompilerWorks Transpiler has three key benefits: 

  1. Accuracy
  2. Predictability
  3. Speed

Accuracy 

With manual migration, a series of rules are followed to rewrite a query. To ensure the query will run on the target platform, an execution test is performed. This traditional approach is prone to error. 

To be crystal clear: manually rewriting code can always lead to errors that go undetected by basic testing strategies. 

Instead of this approach, the Transpiler is designed to produce the same answer on both the source and target systems. 

Unlike human-driven conversions that can provide unpredictable results, the Transpiler provides accuracy by giving you the same correct answer, every time.

Predictability

With the CompilerWorks’ Transpiler, you can expect a predictable end-to-end solution for managing and executing platform migration projects.

Code migration projects must be: 

  • Located
  • Extracted
  • Converted (applying code transformations)
  • Tested and validated

Through processing the execution logs from the source system, the Transpiler systemically and immediately identifies: 

  • Code that is missing from the source provided for the migration project
  • Functionalities on the source system that need to be replicated on the target system
  • Any gaps in functionality in the target system that will need human intervention to migrate

The result? 

  • No more surprises in the migration project. 
  • No re-scoping because a new functionality/code is found.
  • No delays caused by missing functionality in the target system that was discovered half-way through the migration project. 

Beyond the predictability created by transpiling all of the code in the planning stage of the migration project, the lineage model provides a roadmap for structuring the migration project.

CompilerWorks offers the ability to strategically plan where you want to start your migration project and then provides guidance to order the migration in the most efficient and expeditious way possible. 

Speed

The Transpiler delivers performant and accurate code at lightning speeds. 

CompilerWorks can reduce the time spent on a migration project by 50% or more.

This is because the compiler has an understanding of all the nuances of the code being converted and the capabilities of the platform that it is generating code for. This information is used to generate performant code for the target platform.

As a testament to the Transpiler’s speed, CompilerWorks’ largest customer compiles 10TB of SQL on a single machine, on a daily basis. 


CompilerWorks’ Best Migration Practices

CompilerWorks’ Transpiler solution offers four key migration best practices: 

  • Structured migration
  • Iterative process
  • Integrated testing
  • Security review

Structured Migration

The CompilerWorks lineage fabric guides the entire migration project. 

Instead of manually reviewing the code to try to understand discrepancies between queries, relations, and attributes, CompilerWorks automates the process and provides a rich user interface to plan the migration project. 

If you are working on a “lift, improve, and shift” migration, the lineage model will immediately show you where you can wipe out unused processing and data, while also directing you to modifications in the data processing landscape that make the most logical sense.

 If you are working on a “redesign, re-architect, and consolidate” migration, the lineage model will provide the information (from across multiple source systems) to drive the entire migration project, which is made possible by the Transpiler itself.

An ideal approach to “lift and shift” migration involves these eight steps: 

  1. Select a key management report that you wish to migrate
  2. Discover all immediate upstream requirements by reviewing the lineage
  3. Transpile the upstream table DDL on the target system.
  4. Execute the translated DDL on the target system.
  5. Copy the required data.
  6. Execute the transpiled DML.
  7. Execute the provided verification queries.
  8. Use the lineage model to guide the next level of migration (loop back to step 1).

Iterative Process

To deliver a complete migration solution, CompilerWorks leverages the core capabilities of the transpiler.

This solution enables the testing of multiple migration strategies and selects the best approach for the particular migration project involved.

The iterative process works in five steps: 

  1. Assemble all inputs.
  2. Configure the transpiler as desired.
  3. Execute the transpiler.
  4. Inspect the outputs.
  • If missing inputs are discovered, loop back to step 1.
  • If the transpiler configuration needs tuning, loop back to step 2.
  1. Copy the required data.

This fast cycle in the iterative process enables experimentation so you can compare/test the code in order to best meet your requirements.

Integrated Testing

In integrative testing, the transpiler generates a comprehensive suite of test queries to validate DML and DDML migration. 

Integrated testing works in four steps: 

  1. Create the table on the target system.
  2. Compare SourceReadDQ to TargetReadDQ.
  3. Execute the pipeline on the source and target system.s.
  4. Compare SourceWriteDQ to TargetWriteDQ.

To facilitate automation of test query execution on both the source and target systems, the test queries are compiled in a machine-readable file. Correct migration is confirmed by the verified execution of the test query suite. 

Security Review

With Compilerworks, security reviews are a breeze. All of CompilerWorks’ software is designed with security in mind as a top priority:

  • CompilerWorks never touches data. It only processes code.
  • CompilerWorks is a standalone package that can run on an air-gapped machine.
  • CompilerWorks generates clean logs— values are obfuscated.
  • CompilerWorks has frequent updates.

CompilerWorks leaves zero footprint.

Simplify Your Teradata to BigQuery Platform Migration With CompilerWorks

The CompilerWorks Transpiler Solution is the logical choice for simplifying and ensuring the success of your platform migration.Turn your large, high risk, slow, manual migration from Teradata to BigQuery into a predictable, fast, accurate, and painless automated process with CompilerWorks.