Google Drive to BigQuery: How to Move Your Data

Moving Google Drive into BigQuery turns a shared folder into queryable tables, and keeps the whole pipeline inside Google. Partner extracts, exported reports and files somebody drops in every week are invisible to your warehouse until something loads them, and Drive is where a great deal of that material already sits.

One distinction decides whether this pipeline does what you expect. A CSV or Parquet file in Drive is a file and arrives as rows. A Google Doc or Sheet is not a file in the same sense, and what reaches BigQuery is a conversion rather than the structured data you may be picturing.

Google Drive to BigQuery at a glance:

Why move data from Google Drive to BigQuery?

Two situations account for most of these pipelines.

The first is a file feed that happens to arrive in Drive rather than on an SFTP server. A partner shares a folder, a system exports to it nightly, and somebody currently downloads those files and uploads them somewhere else by hand. That is a pipeline waiting to be automated.

The second is keeping a permanent, typed copy rather than reading files live. BigQuery can query Drive directly through external tables, so the honest reason to run a pipeline instead is covered below, and it is a real distinction rather than a formality.

What do you need before you start?

One of these is a task for somebody else, and it is the one that decides whether anything appears at all:

Authentication, and a folder shared with it. OAuth is recommended on Airbyte Cloud and simplifies the setup considerably. On self-managed deployments you create a service account key, and then somebody must give that service account access to the folder, exactly as they would a colleague. The Google Drive source documentation covers both routes.

The folder link, copied from the Drive interface. Navigate to the folder you want and copy the current URL. The connector pulls from that single folder and includes every subfolder recursively, so choosing a root too high up sweeps in far more than you intended.

A glob pattern per stream. Format is configured per stream rather than per file, so a folder containing several file types needs patterns that select only the files matching each format. Patterns are relative to the root folder you specified.

A BigQuery service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. Queries can only reference datasets in the same physical location and that cannot be changed after creation, so match it to whatever you intend to join against.

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

How do you build a Google Drive to BigQuery pipeline in Airbyte?

Step 1: Look at what is actually in the folder

Open the folder and note the file types, because that determines everything downstream. A folder of CSV exports is a straightforward structured pipeline. A folder of Google Docs and Sheets is a document extraction problem, and the two have little in common beyond both living in Drive.

Step 2: Configure the Google Drive source

Click Sources in the left navigation, then New Source, and select Google Drive, following adding a source. Authenticate, paste the folder link, then define a stream for each file format you need with a name and a glob pattern selecting only files of that type.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key. Batched standard inserts is the default and is ample for most file feeds, with Cloud Storage staging worth considering for a large initial backlog.

Step 4: Create the connection and schedule against arrivals

Click Connections, then New connection, select your streams and an incremental sync mode so new files are picked up without re-reading what has already been processed. Set the frequency against how often files actually land rather than how fresh anyone would like the data.

Check the resulting table after the first sync, particularly with CSV, since that format carries no type information and the connector has to infer what each column contains.

What happens to Google Docs and Sheets?

They get converted on the way out, because Google-native files are not stored as files in the ordinary sense. Google Docs are exported as Docx to preserve as much formatting as possible, and other exportable Google formats go through PDF. What reaches your pipeline is that export rather than the original.

The consequence catches people out with Sheets specifically. A Google Sheet looks like a table, so it is natural to assume it arrives as rows in BigQuery. It does not, because it is handled as a document rather than as structured data, and the result is text rather than typed columns.

If your folder is full of Sheets, the Google Sheets connector is the right tool and reads them as tabular data directly. Use this connector for genuine files, and use document file type deliberately when you want the prose, which is more often a vector store use case than a warehouse one.

Why not just use a BigQuery external table?

It is a fair question, and the only destination in this series where it arises, because BigQuery can query files in Drive directly without copying anything. If your requirement is reading one spreadsheet occasionally, an external table is less machinery and worth preferring.

A pipeline earns its place when you want a permanent record. An external table reads whatever the file contains now, so a partner overwriting last month's export silently changes your history, and a file somebody deletes takes your data with it. A synced table keeps what arrived, whatever happens in Drive afterwards.

The other arguments are consistency and cost. A loaded table is typed once and queried like any other, rather than being re-parsed on every query, and it sits beside your other sources with the same partitioning and access controls. On a feed anybody reports from regularly, that is the right trade.

Frequently asked questions

Will a Google Sheet arrive as a table in BigQuery?

No. Google-native files are converted on export, with Docs becoming Docx and other formats going through PDF, so a Sheet is handled as a document. Use the Google Sheets connector for tabular data.

Should I use an external table instead?

For occasional reads of a live file, yes. For a permanent record that survives files being overwritten or deleted, and for typed tables queried regularly, a pipeline is the better choice.

Why is my sync returning nothing?

On a service account, check the folder has actually been shared with it. Then check your glob pattern, since it is relative to the root folder you specified.

Can one source cover several folders?

No. It pulls from a single folder, though every subfolder beneath it is included recursively. Create a source per folder, or nest them under a shared root.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven, including the glob patterns. You may want BigQuery views casting CSV columns, since that format carries no type information.

Get your Google Drive data into BigQuery

Decide between an external table and a pipeline on whether you need a permanent record, then look in the folder because CSVs and Google Docs are different projects. Create the dataset in the right location since that is fixed, share the folder explicitly, and write glob patterns per format.

Airbyte's connector catalog includes 700+ pre-built connectors, so file feeds can reach a warehouse alongside the systems they describe. For the same source in another warehouse, see Google Drive to Snowflake, and for another Google source in BigQuery, Google Workspace Admin to BigQuery.

Start syncing now →