BigQuery to Teradata: How to Move Your Data

Moving BigQuery into Teradata brings cloud-analysed data back to where an organisation's reporting practice already lives. Plenty of businesses run modern pipelines into BigQuery and decades of modelled data in Teradata, and the interesting questions need both in one place.

This guide covers the managed path with Airbyte. Two things shape the build: reading from this source runs billable query jobs, so somebody pays for every sync, and BigQuery's nested types have no equivalent in a strictly relational destination.

BigQuery to Teradata at a glance:

Why move data from BigQuery to Teradata?

Two situations account for most of these pipelines.

The first is bringing data to the analysts rather than the analysts to the data. An organisation with years of modelled subject areas in Teradata and a reporting practice built around it gets more from adding cloud-sourced data to that environment than from asking everybody to learn a second platform.

The second is consolidation during a migration in either direction, where both systems need to agree for a period. The structural caveat is types: if your BigQuery tables make heavy use of nested and repeated fields, a destination that holds them natively saves real work, and BigQuery to Snowflake avoids the flattening exercise entirely.

What do you need before you start?

Four things, and two of them are decisions with money attached:

A service account with the BigQuery User role. Plus its key in JSON format, copied in full including the brackets. The BigQuery source documentation covers creating the account and the key.

A decision about which project pays. The job execution project determines which project runs and is billed for the query jobs this connector issues, and it need not be the project holding the data.

A dataset identifier, deliberately chosen. It is optional, and leaving it blank widens the scope from one dataset to the whole project, which is a very different amount of data and a very different bill.

A chosen SSL mode on the Teradata side. Encryption is off by default and two of the six modes permit an unencrypted connection, so the default is not the safe option. The Teradata destination documentation lists them.

If your Teradata system restricts traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a BigQuery to Teradata pipeline in Airbyte?

Step 1: Decide which project runs the query jobs

This matters because the cost of this pipeline appears on somebody's Google bill rather than in your Airbyte usage, and the two are often owned by different teams. Setting the job execution project deliberately puts the spend where it belongs and makes it attributable, which is what stops a data platform team from discovering they have been quietly funding somebody else's reporting.

Step 2: Configure the BigQuery source

Click Sources in the left navigation, then New Source, and select BigQuery, following adding a source. Supply the project identifier, the dataset and the service account key. The connection test lists datasets and tables and runs a trivial query, and it reports discovering zero tables if the dataset or project is empty, which is a clearer failure than most.

Step 3: Configure the Teradata destination

Click Destinations, then New Destination, and select Teradata, following adding a destination. Supply the host, credentials, logon mechanism and SSL mode. Tables land in a default schema named airbyte_td unless you specify another, which is worth setting so they sit where your organisation expects.

Step 4: Create the connection and watch the first bill

Click Connections, then New connection, select your streams and a sync mode. Use incremental wherever a reliable cursor exists, because full refreshes against a large BigQuery table are the expensive case. Then check what the first week actually cost in the job execution project rather than assuming your estimate was right.

Then model the arriving tables, since a strongly typed system is not a comfortable place to leave anything BigQuery handed over loosely.

Why does this source have a bill attached?

Because reading from BigQuery means running queries, and BigQuery charges for queries. Most sources cost you nothing but rate limits; this one issues billable jobs on every sync, which makes sync frequency a spending decision rather than a freshness preference.

Two settings govern the size of that bill. The job execution project decides who pays, which is an attribution question worth settling before the invoice arrives. And the dataset identifier decides how much you are reading at all, since leaving it blank takes in the whole project rather than the one dataset you had in mind.

The thing to know is that you cannot trim the read with a WHERE clause, because the connector does not accept custom SQL. Your levers are which tables you select, how often you sync and whether a cursor lets you read incrementally. If you genuinely need only a filtered subset, create a view in BigQuery that applies the filter and sync the view, which puts the restriction where it can actually reduce what is scanned.

What happens to nested and repeated fields?

They meet a system that has no concept of them. BigQuery's structs and arrays are ordinary modelling tools there, letting one row carry a customer and their addresses together, and Teradata is a strictly relational system expecting columns with scalar types. Something has to give, and it is better that you decide what rather than discovering it.

The clean approach is to flatten before the data leaves. Create a view in BigQuery that unnests repeated fields into rows and pulls struct members into named columns, then sync that view rather than the underlying table. The logic sits where the nested types are understood, it is visible to anybody reviewing it, and the destination receives something it can model properly.

Numeric precision deserves the same attention while you are there. BigQuery's high-precision numeric types are generous, and a column carrying financial amounts should be checked against what will hold it on the other side rather than assumed to survive. Both of these are ordinary work in a Teradata environment where modelling is a discipline, and both are much cheaper to do before the first load than after somebody reconciles a total.

Frequently asked questions

Does syncing from BigQuery cost money?

Yes. Reading runs query jobs that BigQuery bills for, so sync frequency and table selection are spending decisions rather than only freshness ones.

Which project gets charged?

The job execution project, which is configurable and need not be the project holding the data. Set it deliberately so the spend is attributable.

Can I filter what gets read with SQL?

Not in the connector, which does not accept custom queries. Create a filtered view in BigQuery and sync that instead, which reduces what is actually scanned.

How do structs and arrays arrive?

Awkwardly, since Teradata has no native equivalent. Flatten them in a BigQuery view before syncing so the destination receives scalar columns it can model.

Can I do this without writing code?

The pipeline, yes. The BigQuery views that flatten nesting and restrict what is read are SQL, and they are what makes this both affordable and modellable.

Get your BigQuery data into Teradata

Set the job execution project deliberately, because this source bills for every sync and the cost lands on a Google invoice rather than in your pipeline tooling. Name your dataset rather than leaving it blank. Set the SSL mode, since encryption is off by default. Then flatten nested and repeated fields in a BigQuery view before they travel, and check numeric precision against what the destination will hold.

Airbyte's connector catalog includes 600+ pre-built connectors, so cloud and on-premises analytics can share one environment. For the same source into another warehouse, see BigQuery to Amazon Redshift, and for file-based data into the same destination, Parquet File to Teradata.

Start syncing now →