How to Export Firestore Data to BigQuery

Go to the Firebase Console, click Extensions in the left sidebar, and search for 'Stream Firestore to BigQuery'. Click Install and select your Firebase project. The installation wizard asks you to configure the collection path to monitor (e.g., 'orders'), the BigQuery dataset ID (it creates one if it does not exist), and the table ID for the changelog. The extension deploys a Cloud Function that triggers on every Firestore document write.

During installation, set the Collection path to the Firestore collection you want to export (e.g., 'orders'). Set the BigQuery Dataset ID (e.g., 'firestore_export'). Choose whether to use wildcard collection monitoring to include subcollections. Enable 'Use new query syntax' for the latest BigQuery views. The extension creates a changelog table that records every CREATE, UPDATE, and DELETE operation with timestamps.

1// Extension configuration (set during installation):
2// Collection path: orders
3// Dataset ID: firestore_export
4// Table ID: orders_raw_changelog
5// Wildcard: No (or Yes for subcollections)
6// Partition: timestamp (recommended for cost optimization)

The extension only captures changes made after installation. To import existing documents, run the backfill script included with the extension. This script reads all documents in the configured collection and writes them to BigQuery as initial records. For large collections, the backfill can take several minutes. Run it from your terminal with npx.

1npx @firebaseextensions/fs-bq-import-collection \
2  --non-interactive \
3  --project your-project-id \
4  --source-collection-path orders \
5  --dataset firestore_export \
6  --table-name-prefix orders

The changelog table contains every version of every document. To get the current state, the extension provides a script that creates a BigQuery view showing only the latest version of each document. This view deduplicates the changelog and gives you a clean snapshot table for analytics queries.

1npx @firebaseextensions/fs-bq-schema-views \
2  --non-interactive \
3  --project your-project-id \
4  --dataset firestore_export \
5  --table-name-prefix orders

Open the BigQuery Console and run SQL queries against your exported data. You can now perform JOINs, aggregations, date-range filtering, and complex analytics that are impossible in Firestore. The changelog table records the full history of changes, while the latest view shows current document states.

1-- Total revenue by month from orders collection
2SELECT
3  FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP(data.createdAt)) AS month,
4  SUM(CAST(data.total AS FLOAT64)) AS revenue,
5  COUNT(*) AS order_count
6FROM `your-project.firestore_export.orders_schema_orders_schema_latest`
7WHERE data.status = 'completed'
8GROUP BY month
9ORDER BY month DESC;
10
11-- Find users with more than 10 orders
12SELECT
13  data.userId,
14  COUNT(*) AS order_count,
15  SUM(CAST(data.total AS FLOAT64)) AS total_spent
16FROM `your-project.firestore_export.orders_schema_orders_schema_latest`
17GROUP BY data.userId
18HAVING order_count > 10
19ORDER BY total_spent DESC;