Optimized Real-time Firestore Changelogs with BigQuery

Ambrook models complex financial worlds. For books to be accurate, there must be a database entry that represents each financial record that makes up a business’s finances. These records are interconnected in a complex web that also changes over time, which means that having an auditable changelog is essential. The paper trail enables us to confidently make changes and have the flexibility to reverse engineer how data was changed whenever we need.

Earlier this year, our changelog system hit a breaking point. We’d stored changelog records in Firestore for years, but coming off a massive period of growth (a 40x increase in our customer base and our changelogs collections growing to be 100x the size of the original set of records), we knew a change was needed.

This is how we migrated our changelogs to BigQuery with no downtime and saved over $100K in projected annual infrastructure costs.

The changelog stack

Changelogs tell a story about a resource. They’re useful for analytical insight and debugging, storing a before and after carbon copy of a resource along with some metadata.

type ChangelogRecord<T> = {
  id: string;
  before: T | null; // null on a create
  after: T | null; // null on a delete
  metadata: {
    version: number; // monotonically increasing, derived from the change time
    changeTime: Milliseconds;
    createTime: Milliseconds;
  };
};

Each tracked collection stores its changelogs in a subcollection nested under the document they track. A transaction at ocean/{oceanId}/fish/{fishId} keeps its history at ocean/{oceanId}/fish/{fishId}/fishChangelogs/{changelogId}, where the changelog ID is derived from the nanosecond timestamp of the change.

Changelog generation happens asynchronously. Every tracked collection has a deployed Firestore on-write listener. When a document is created or modified, the listener enqueues a task on a dedicated changelog queue with the before and after snapshots, and the task handler builds and writes the record. Nothing on the request path ever reads or writes a changelog.

We’ve also built read-path tooling on top of changelogs for our two primary use cases:

• Restores. Given a document and a point in time, we can restore it to any prior snapshot. This gives us a way of salvaging data corrupted by a bug or a rogue internal script that wasn’t reversible in our app.
• Diff inspection. Internal tools walk a document’s changelog history to answer questions like “when did this field change to its current value”, ”what did this look like before it was deleted“, or “what was the exact relationship between these twenty entities at 7:33PM thirty days ago”. While the need for asking such specific questions is fortunately rare, having the ability to ask these questions has been critical for incident response or asserting the correctness of our accounting system.

While this previous write path proved to be reliable, it was expensive due to the large volume of documents being stored in a database not designed for high-volume use cases.

To reduce cost, we’d need a different data store, and a replacement for Firestore’s built-in hooks.

Moving to BigQuery

BigQuery was the natural choice for a cheap, queryable interface for changelogs. It is inexpensive to store, ingest, and index at multi-terabyte scale. With the right partitioning and clustering strategy, it supports both console and programmatic querying, supporting our changelogs use cases at a fraction of the price.

Moving changelogs to BigQuery removed approximately 35 TB of billable Firestore storage, including document data, indexes, and metadata. That reduced storage charges by roughly $116/day, with another $116/day saved on point-in-time recovery (PITR). Removing changelogs from the nightly full-database export also reduced billable Firestore reads, saving an estimated $98/day, or approximately $2,950 per 30-day month. Write and delete operation costs were comparatively small.

Firestore’s default indexing amplified the cost of retaining this history. It creates ascending and descending indexes for non-array, non-map fields, including nested fields. Each index entry adds storage overhead. For large, deeply nested changelog records used primarily for debugging and investigations, maintaining those indexes was expensive.

The changelog archive now contains approximately 315 million records and 1.79 TB of logical data in BigQuery, costing about $33/month in storage. BigQuery’s US logical storage rate is $0.02/GiB-month, compared with our Firestore region’s $0.108/GiB-month. The savings come from both the lower storage rate and eliminating Firestore’s indexing overhead. BigQuery also includes time-travel storage under logical billing.

Pub/Sub handles approximately 331,000 changelog messages and 2.33 GB of payload per day. Publishing costs $40/TiB, and delivery into BigQuery costs $50/TiB, including ingestion. Together, those charges total approximately $0.19/day, or $5.72/month. Even at 25 times the current ingestion volume, streaming would cost only about $143/month, with storage increasing separately as the archive grows.

Together, storage, PITR, and estimated Firestore read reductions represent approximately $330/day in savings, or $9,900 per 30-day month, after accounting for the main BigQuery archive’s storage and current streaming costs. Current streaming adds less than $6/month. These estimates exclude query costs, network charges, and storage for the retained backfill table.

Designing the migration

For a migration of this scale where data loss is completely unacceptable, we wanted to have a zero-trust approach, assuming anything can break. The design had three main requirements:

1. Operate atomically;
2. Check redundantly; and
3. Fail loudly.

The migration steps overlap by design, and all steps are independent, and can be paused to confirm stability before moving on. Here’s the approach we landed on:

We started with a core, unopinionated Pub/Sub publisher class that is deliberately schema-agnostic and extensible to changelogs and beyond. This layer handles all the inner-working of the intermediate Pub/Sub layer. Early on, we started dual-writing to Firestore and BigQuery in order to monitor Pub/Sub health, primarily watching for backpressure and any dropped messages. We left the system dual-writing for a period of time so we can build up enough data to confidently compare the diffs.

For backfilling and parity-checks, a one-off script across 26TiB of data would have taken days to run. In addition to being slow, it would risk hotspotting Firestore read throughput and affecting production latency, overwhelming our disk space with intermediate local files, and potentially causing machines to fail unrecoverably.

We chose instead to launch an Apache Beam pipeline and run it on GCP DataflowGCP Dataflow. For each collection it issues a Firestore collection-group query to create several hundred partitions, which workers then read in parallel. From there, the pipeline is a chain of ParDo transforms, each doing one unit of work per changelog: resolve the Ambrook organization ID, serialize the row, write it to BigQuery. Each transform is atomic, so a failed worker can retry without a partial write.

The real run copied 295M changelog documents in four hours. 3,000 Firestore partitions fanned across a worker pool that peaked at 500 workers and 388 vCPU-hours. Dataflow automatically scaled to the appropriate amount of workers, and our total migration cost was less than $100 for both backfilling and parity check.

As a precautionary measure, all of the rows written to BigQuery were written initially to a temporary table that was eventually merged into the finalized production table, to prevent pipeline defects affecting production tables. Rows could be validated against Firestore before the merge, and any bad batch could be discarded by dropping the temporary table.

Finally, came deleting the Firestore changelogs. To build confidence that we could take this final step, we verified signals below that made us confident that the migration had completed without failures and that we were ready to cut over:

1. Our dual-writing to BigQuery had run for several weeks without producing any unexpected diffs or monitoring alerts.
2. We had backfilled and verified parity in isolated and separate Dataflow jobs.
3. We had refactored all in-app changelog reads to read from BigQuery, monitoring read traffic to confirm that the Firestore collections were unused.
4. We ran the deletion on a test collection in our staging environment before running it in production.

While Dataflow worked well for backfill, it’s not a viable option for deletion. Firestore recommends ramping writes at 500 operations per second, increasing 50% every 5 minutes. Dataflow at 500 workers would exceed that immediately and hotspot the collection. Instead, we built a simple throttled read-then-delete process built on Firestore’s BulkWriter, tuned to what Firestore could actually sustain. Within several hours of writing, the Firestore collections were deleted.

Scale ready

This migration removed over $100K of annual Firestore costs while preserving full changelog fidelity, and the pattern we established generalizes to any large scale data migration where the old and new systems need to run in parallel before cutover.

Our new BigQuery-based changelog system holds more than 300M records at a compressed storage size of 46 GB, a 38x compression factor from 2TB. Pub/Sub processes around 330,000 messages per day and only operates at just $50 in daily storage cost.