Firestore is a good operational database and a bad analytics database. The queries a dashboard wants — count orders by status per week, average cart value by cohort, top 50 SKUs — are aggregations over large ranges. Firestore bills them by the document. One "revenue this month" tile over 400,000 order documents is 400,000 reads every time somebody loads the page.
The fix is not a better index. It is a second copy of the data in a columnar warehouse, with Firestore left alone to serve the app. BigQuery is the usual destination because the plumbing already exists. The plumbing also has a meter on it, so here is how to pick a path and what each one costs.
First: are you sure you need the warehouse?
Two cheaper options come before it.
Aggregation queries. count(), sum(), and avg() run server-side and bill one document read per 1,000 index entries scanned, rounded up, with a minimum of one. A count over 400,000 matching documents is 400 reads, not 400,000.
const snap = await getAggregateFromServer(
query(collection(db, 'orders'), where('status', '==', 'paid')),
{ total: count(), revenue: sum('amountCents') }
);
That kills most single-number dashboard tiles outright. It does not do joins, it does not do group-by, and it still scans on every call — so cache the result in a document if the tile is on a page everyone loads.
Rollup documents. A Functions trigger or a scheduled job that maintains stats/2026-02 as orders land. One read per tile, forever. Right answer for a fixed set of known metrics; wrong answer the moment someone asks a question you did not pre-compute.
Go to BigQuery when the questions are open-ended, when analysts want SQL, or when you need to join Firestore data against anything else.
Path A: scheduled managed export to GCS, loaded into BigQuery
The batch path. gcloud firestore export writes a snapshot to a GCS bucket; BigQuery loads it natively.
gcloud firestore export gs://acme-fs-exports/$(date +%Y-%m-%d) \
--collection-ids=orders,users,subscriptions
bq load --source_format=DATASTORE_BACKUP \
analytics.orders \
gs://acme-fs-exports/2026-02-11/all_namespaces/kind_orders/all_namespaces_kind_orders.export_metadata
Schedule it with Cloud Scheduler hitting a small function, or with the firestore-export-scheduled-backups style job in your own repo. Nothing exotic.
What it costs. Export operations bill one document read per document exported, at the standard read rate. Ten million documents at $0.06 per 100,000 reads is about $6 per full export. Daily, that is roughly $180 a month for the export alone before storage and load. Weekly is $26.
What you get. Data as fresh as your last export — hours to a day. No per-write cost. No function in the write path to break. Deleted documents disappear correctly, because each run is a full snapshot.
Choose this when analytics is a daily reporting job and nobody downstream cares about the last six hours.
Path B: the Stream Firestore to BigQuery extension
The streaming path. The firestore-bigquery-export extension installs a Cloud Function on a document-write trigger for a collection path, and appends a row per change to a <table>_raw_changelog table, with a <table>_raw_latest view giving you the current state of each document.
Install it per collection group, with a wildcard for subcollections:
COLLECTION_PATH: orgs/{orgId}/orders
DATASET_ID: firestore_export
TABLE_ID: orders
DATASET_LOCATION: us
WILDCARD_IDS: true
Backfill history separately — the extension only sees writes after install:
npx @firebaseextensions/fs-bq-import-collection \
--non-interactive \
--project acme-prod \
--source-collection-path orders \
--dataset firestore_export \
--table-name-prefix orders \
--query-collection-group true
What it costs. Three meters, and teams usually only budget for one.
- Function invocations. One per document write. An app doing 2,000,000 writes a day is 60,000,000 invocations a month. Past the free tier that is roughly $23 in invocations, plus GB-seconds and vCPU-seconds — call it $60–90/month at a small instance size and short runtimes. Measure yours; it moves with payload size.
- BigQuery ingestion. The extension writes via the streaming path, billed per gigabyte ingested — on the order of a few cents per GB. Small compared to the functions, but not zero at high write volume.
- Storage of a table that never shrinks. The changelog is append-only. Every update to every document is a row, forever.
That third one is the surprise. A collection with 5,000,000 documents averaging 12 updates each is 60,000,000 changelog rows, and a naive SELECT * FROM ..._raw_latest WHERE ... scans all of them.
Making the changelog cheap to query
Three settings, all worth doing on install day.
Partition the changelog. The extension supports time partitioning on the event timestamp. Partitioned, a query for last week reads last week's bytes instead of three years of them. Retrofitting partitioning means rebuilding the table, so set it up front:
TIME_PARTITIONING: DAY
TIME_PARTITIONING_FIELD: timestamp
TIME_PARTITIONING_FIELD_TYPE: TIMESTAMP
TIME_PARTITIONING_FIRESTORE_FIELD: createdAt
Cluster on document_id. The latest-state view deduplicates by document, so clustering on the key cuts the bytes scanned on nearly every analyst query.
Materialize the latest state on a schedule. Do not let dashboards hit the view. Run a scheduled query nightly into a plain table:
CREATE OR REPLACE TABLE analytics.orders_current AS
SELECT * EXCEPT(rn) FROM (
SELECT
document_id,
JSON_VALUE(data, '$.status') AS status,
CAST(JSON_VALUE(data, '$.amountCents') AS INT64) AS amount_cents,
timestamp,
operation,
ROW_NUMBER() OVER (PARTITION BY document_id ORDER BY timestamp DESC) AS rn
FROM `acme-prod.firestore_export.orders_raw_changelog`
)
WHERE rn = 1 AND operation != 'DELETE';
Two details in that query matter. operation != 'DELETE' is how deletes propagate — a deleted Firestore document is a tombstone row, not a missing one, and forgetting the filter is how deleted accounts keep showing up in revenue. And the ROW_NUMBER dedupe is load-bearing: the trigger is at-least-once, so duplicate rows for the same change will appear. Never COUNT(*) the changelog and call it a business metric.
Which path, in practice
Run Path A when writes are high and freshness tolerance is a day: a full snapshot costs reads once, not per write, and there is nothing in the write path to fail.
Run Path B when someone genuinely needs sub-minute data, or when you want the change history itself — audit trails, "what did this document look like on the 3rd", event-shaped analysis. You are paying for the history; make sure you use it.
Running both is also reasonable and we set it up fairly often: stream the two or three collections that drive live operational dashboards, batch-export the rest weekly.
The part people skip
Set a partition expiration on the changelog before the first row lands.
bq update --time_partitioning_expiration 63072000 \
acme-prod:firestore_export.orders_raw_changelog
Two years, in seconds. Without it, the table grows for the life of the product and every unpartitioned query gets slower and more expensive in lockstep. That is the one line that separates a BigQuery offload that holds its shape from one that becomes the next item on the cost audit.
And after the warehouse is live, go delete the Firestore queries it replaced. We have reviewed more than one app paying twice: the dashboard migrated to BigQuery, and the old 400,000-read endpoint still running behind it because nobody checked whether anything still called it.