Introduction to loading, transforming, and exporting data

This document describes the data integration approaches to load and transformdata in BigQuery using the extract, load, and transform (ELT) or theextract, transform, load (ETL) processes. It also describes exporting data fromBigQuery to apply insights in other systems, known asreverseETL.

Deciding between ELT or ETL

It's common to transform your data before or after loading it intoBigQuery. A fundamental decision is whether to transform thedata before loading it into BigQuery (extract-transform-load or ETLapproach) or load the raw data into BigQuery and perform transformations usingBigQuery (extract-load-transform or ELT approach).

The following chart shows the various options for data integration intoBigQuery - either using ELT or ETL.

In general, we recommend the ELT approach to most customers. The ELT workflowsplits the complex data integration in two manageable parts - extract & load, thentransform. Users can choose from a variety data load methodsthat suit their needs. Once their data is loaded into BigQuery,users familiar with SQL can develop transformation pipelines with tools such asDataform.

The following sections describe each workflow in further detail.

Loading and transforming data

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

ELT data integration approach

With the extract-load-transform (ELT) approach, you perform data integration intwo discrete steps:

• Extract and load data
• Transform data

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

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

• 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 andload it into BigQuery using any of the supportedmethods of loading or accessing external data.

Transforming data in BigQuery

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

• To collaboratively build, test, document, and schedule advanced SQL datatransformation pipelines, useDataform.
• For smaller data transformation workflows executing SQL code, Pythonnotebooks, or data preparations on a schedule, useBigQuery pipelines.
• To clean your data for analysis, use AI-augmenteddata preparation.

Each of these tools is powered by theDataform API.

For more information, seeIntroduction to transformations.

ETL data integration approach

In the extract-transform-load (ETL) approach, you extract andtransform data before it reaches BigQuery. This approach isbeneficial if you have an existing process in place for data transformation, orif you aim to reduce resource usage in BigQuery.

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

Exporting data

After you process and analyze data in BigQuery, you can exportthe results to apply them in other systems. BigQuery supports thefollowing 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, seeIntroduction to data export in BigQuery.

What's next

• Learn more aboutloading data in BigQuery.
• Learn more abouttransforming data in BigQuery.
• Learn more aboutexporting data in BigQuery.