Introduction to loading, transforming, and exporting data

This document provides an overview of the following data integration capabilities in BigQuery:

  • Loading and transforming data in BigQuery, using either the extract, load, and transform (ELT) approach or the extract, transform, load (ETL) approach.
  • Exporting data from BigQuery to apply insights in other systems, also referred to as reverse ETL.

Loading data from sources, transforming data, then exporting the results

Loading and transforming data

It's common to transform your data before or after loading it into BigQuery. The two common approaches to data integration, ETL and ELT, are described in the following sections.

ELT data integration approach

With the extract, load, and transform approach, you perform data integration in two discrete steps:

  • Extract and load data
  • Transform data

For example, you can extract and load data from a JSON file source into a BigQuery table. Then, you can use pipelines to extract and transform fields into target tables.

The ELT approach can simplify your data integration workflow in the following ways:

  • Eliminates the need for other data processing tools
  • Splits the often complex data integration process into two manageable parts
  • Fully utilizes BigQuery's capabilities to prepare, transform, and optimize your data at scale

Extracting and loading data

In the ELT data integration approach, you extract data from a data source and load it into BigQuery using any of the supported methods of loading or accessing external data.

Transforming data

After loading the data into BigQuery, you can prepare and transform the data with the following tools:

  • To collaboratively build, test, document, and schedule advanced SQL data transformation pipelines, use Dataform.
  • For smaller data transformation workflows executing SQL code or Python notebooks on a schedule, use workflows (in Preview).
  • To clean your data for analysis, use AI-augmented data preparation (in Preview).

For more information, see Introduction to transformations.

ETL data integration approach

In the extract, transform, and load approach, you extract and transform data before it reaches BigQuery. This approach is beneficial if you have an existing process in place for data transformation, or if you aim to reduce resource usage in BigQuery.

Cloud Data Fusion can help facilitate your ETL process. BigQuery also works with 3rd-party partners that transform and load data into BigQuery.

Exporting data

After you process and analyze data in BigQuery, you can export the results to apply them in other systems. BigQuery supports the following exports:

  • Exporting query results to a local file, Google Drive, Google Sheets
  • Exporting tables or query results to Cloud Storage, Bigtable, Spanner, and Pub/Sub

This process is referred to as reverse ETL.

For more information, see Introduction to data export.

What's next