Docs / Firestore → BigQuery

Connect Firestore → BigQuery

Firebase Firestore via BigQuery export

Firestore is a document store, not a warehouse, so climpt does not query it directly. You export it to BigQuery — a first-party Firebase feature — and climpt reads the export. Two steps: turn on the export, then connect to the resulting dataset.

Step 1 · Enable the BigQuery export

  1. In the Firebase console, select your project and open Firestore Database.
  2. Use the ⋮ menu → Manage BigQuery Export → Enable BigQuery Export.
  3. Choose which collections to export, the dataset location, and whether you want scheduled exports.
  4. Enable it and wait for the initial sync — typically 5 to 30 minutes. Note the dataset name, usually firestore_export.

What the export looks like

Each collection becomes a BigQuery table, document fields become columns, and changes sync within a few minutes. Subcollections can be included.

collection_raw_latest
Current state of each document. This is what you normally query.
collection_raw_changelog
Historical changes, if enabled. Useful, but subject to retention limits.

Step 2 · A service account for the dataset

This is an ordinary BigQuery connection, so it uses the same three read-only roles — two at project level, one on the export dataset.

roles/bigquery.jobUser
Project level. Lets climpt create a query job.
roles/bigquery.readSessionUser
Project level. Lets climpt read results through the Storage Read API. Without it, discovery succeeds and every query fails with a bigquery.readsessions.create error.
roles/bigquery.dataViewer
On the export dataset only — not the whole project.
  1. In the Google Cloud console, go to IAM & Admin → Service Accounts and create one named, say, firestore-bigquery-readonly.
  2. Grant it the two project-level roles: BigQuery Job User and BigQuery Read Session User.
  3. Open BigQuery, find the export dataset, and use ⋮ → Share to grant the service account BigQuery Data Viewer.
  4. Back in Service Accounts, open the account, go to Keys and create a new JSON key.
  5. In climpt, add a Google BigQuery connection and paste the whole JSON file.

Querying the export

Document fields sit under a data column, and document_id holds the Firestore ID.

SELECT
  document_id,
  data.email,
  data.name,
  data.created_at
FROM `your-project-id.firestore_export.users_raw_latest`
WHERE data.status = 'active'
LIMIT 100;

Worth knowing

  • The initial sync takes 5 to 30 minutes; ongoing changes land within 2 to 5.
  • BigQuery bills on data scanned. Filter and limit, and consider views for queries you run often.
  • Rotate the service account key roughly every 90 days.