Skip to main content

BigQuery Pipeline and Firestore Import

This document covers the second stage of the import pipeline: everything that runs inside Queue Services after the cloud task is dispatched. Queue Services loads validated CSV data into BigQuery, transforms and reconciles it through a series of intermediate tables, then writes the final results to Firestore.

Flow​

Load​

Queue Services receives the HTTP request and verifies that the required CSV files exist in GCS. The validated files are loaded into BigQuery staging tables and the source files in GCS are archived.

Shape​

Once loaded, the shape stage transforms the imported data into a standardised structure that matches how vehicle and policy records appear in ViSN. For imports that include a separate benefits file, it is joined to the fleet data at this stage.

Aggregate and Hash​

Rows are grouped by VRM so that each vehicle appears as a single record. When multiple policy rows exist for the same VRM, they are combined under one vehicle record containing multiple policies. The row with the latest start date provides the shared vehicle-level details — make, model, registration, customer, and driver information.

After grouping, a _docHash is computed from the final grouped record. This hash represents the complete ViSN-ready state of the vehicle and is the basis for all downstream comparison.

This is the point where the import stops being compared row by row. From here on, the import compares complete vehicle records, not individual CSV rows.

Reconcile and Delta​

Reconcile​

The aggregated imported state is merged with the current persisted state from existing Firestore data. Each imported vehicle record is compared against the existing record for the same vehicle:

  • Imported policies are merged with the policies already attached to that vehicle
  • Policies present in the existing record but missing from the latest import are added back as cancelled

Delta​

After reconciliation, the delta check keeps only the records where the final merged state differs from the existing state. Unchanged records are excluded from the import.

If reimportAll is enabled, all records are kept regardless of whether changes are detected, forcing a full re-import.

Import to Firestore​

The delta rows are streamed from BigQuery in batches of 1000. For each row, sub-customer details are resolved by matching the customer account number against known sub-customers. The row is then assembled into a Firestore vehicle stock document containing vehicle details, policies, lease information, and timestamps.

If the vehicle already has a stockId, the existing document is updated. Otherwise, a new document is created. Each batch completes before the next one starts, with a short delay between batches to avoid Firestore contention.

Per-row results are logged with the VRM, VIN, stock ID, and whether the row was created, updated, or failed.

Termination​

If shouldTerminate is enabled, termination runs after the main import completes. The raw fleet table — loaded from the processed file created during validation — represents the latest import scope. Vehicles that exist in active Firestore stock but are not present in this scope are candidates for termination.

Termination candidates are written to a BigQuery table and then streamed to Firestore in batches. Each vehicle's contractStatus and _docHash are updated to TERMINATED.

BigQuery Table Reference​

The pipeline uses a series of intermediate tables. Each table builds on the previous one, transforming the data closer to its final ViSN-ready shape. Understanding the table order is the primary way to trace an import when debugging.

Table Order​

  1. Raw — processed fleet input, preserves the latest fleet scope
  2. Valid — validated rows loaded from CSV
  3. Benefits — validated benefits rows, where applicable
  4. Shaped — fleet and benefits combined, structured into vehicle record shape
  5. Aggregated — grouped by VRM, multiple policies under one vehicle, _docHash generated
  6. Merged — imported state reconciled with existing persisted state
  7. Delta — only changed records, drives the final Firestore import
  8. Terminated — vehicles to be marked as terminated

Raw Table​

Holds the processed fleet input used as the raw import scope. This table serves two purposes: it preserves a record of the latest supplied fleet data, and it provides the scope against which termination is performed.

Valid Table​

Holds the validated rows loaded from the CSV. This table is close to the original import data but only includes rows that passed validation.

Benefits Table​

Where applicable, holds validated benefits rows loaded from the CSV. Benefits are linked to fleet rows by a shared reference field and are joined during the shape stage.

Shaped Table​

This is where the imported data is transformed into the structure it should have in ViSN. Fleet and benefits data are combined, columns are mapped to their ViSN equivalents, and the data is arranged into the vehicle record shape ready for grouping.

Aggregated Table​

Rows are grouped by VRM. Multiple policies for the same vehicle are collected into a single vehicle record with an array of policies. When multiple rows exist for the same VRM, the row with the latest start date populates the shared vehicle-level fields. A _docHash is generated from the final grouped record at this stage.

This is the table where the import shifts from comparing individual rows to comparing complete vehicle records.

Merged Table​

The imported aggregated state is joined against the existing persisted state. Imported policies are merged with the existing policies already attached to each vehicle. Policies that exist in the current Firestore record but are missing from the latest import are added back with a cancelled status.

Delta Table​

The final merged state is compared against the existing state. Only rows where changes are detected are kept. Unchanged rows are excluded from the import. If reimportAll is enabled, all rows are kept.

This is the table that directly drives the Firestore import — only these rows will be written.

Terminated Table​

Holds vehicles that are active in Firestore but not present in the latest raw fleet scope. These vehicles are candidates for termination and their contractStatus and _docHash are updated to TERMINATED in Firestore.

Status: Approved
Category: Protected
Authored By: Hadley on Jul 08, 2026