Skip to main content

EMaC BigQuery Import Flow

Overview​

This page shows how the BigQuery Import Strategy is applied to the EMaC import.

It focuses on the BigQuery table flow, the order in which the tables are used, and what each stage is doing. This is mainly useful when tracing the import flow or debugging an unexpected result.

EMaC Flow in BigQuery​

The EMaC import follows the same high-level flow as the generic strategy:

  1. load the validated import data into BigQuery
  2. shape the imported data into the way the record should appear in ViSN
  3. aggregate rows by VRM and generate the hash
  4. reconcile the imported state with the current ViSN state
  5. run the delta check
  6. import only the rows that have changed
  7. if enabled, run termination separately after the main import

Main Table Order​

The main EMaC flow uses the following tables in this order:

  1. emac_customer_contract_fleet_file_raw
  2. emac_customer_contract_fleet_file
  3. emac_customer_contract_benefits_file
  4. emac_customer_contract_with_benefits
  5. emac_customer_contract_aggregated_by_vrm
  6. emac_customer_contract_with_merged_policies
  7. emac_customer_contract_delta

What Each Table Does​

emac_customer_contract_fleet_file_raw​

This table holds the processed fleet input used as the raw import scope.

It is mainly used to:

  1. preserve the latest supplied fleet scope
  2. support the optional termination flow

emac_customer_contract_fleet_file​

This table holds the validated fleet rows loaded from the CSV.

It is close to the original import rows, but only includes rows that passed validation.

emac_customer_contract_benefits_file​

This table holds the validated benefits rows loaded from the CSV.

It is close to the original import rows, but only includes rows that passed validation.

emac_customer_contract_with_benefits​

This is the shaped table.

At this stage:

  1. fleet data and benefits data are combined
  2. the imported rows are structured into the way the vehicle record should appear before aggregation
  3. the data is ready to be grouped by vehicle

emac_customer_contract_aggregated_by_vrm​

This is the aggregate stage.

At this stage:

  1. rows are grouped by VRM
  2. multiple policies for the same vehicle are grouped into one vehicle record
  3. when multiple rows exist for the same vehicle, the row with the latest StartDate is used for the shared vehicle-level details
  4. _docHash is generated from that final grouped record

This is the point where the import stops being compared row by row.

emac_customer_contract_with_merged_policies​

This is the reconciliation stage.

At this stage:

  1. if the vehicle already exists in ViSN, the newly imported vehicle record is compared with the existing vehicle record
  2. the imported policies are merged with the policies already attached to that vehicle in ViSN
  3. policies missing from the latest import are added back as cancelled where required

emac_customer_contract_delta​

This is the delta stage.

At this stage:

  1. the final merged state is checked for changes
  2. only changed rows are kept for import
  3. unchanged rows are excluded
  4. if reimportAll is enabled, all rows are kept for import even if no changes are detected

This is the table that drives the final import into ViSN.

Termination Flow​

If shouldTerminate is enabled, termination runs separately after the main import.

The termination flow uses these tables:

  1. emac_customer_contract_fleet_file_raw
  2. leaseCompany_stock_latest_view
  3. emac_customer_contract_terminated_vehicles

emac_customer_contract_terminated_vehicles​

This table holds the active records that already exist in ViSN but are not present in the latest supplied EMaC fleet scope.

Those records are then marked as terminated.

Debugging Notes​

If the import does not behave as expected, the table order above gives the best path for tracing the issue.

Typical checks:

  1. if benefits are missing, inspect emac_customer_contract_benefits_file and emac_customer_contract_with_benefits
  2. if vehicle grouping looks wrong, inspect emac_customer_contract_aggregated_by_vrm
  3. if expected updates were not imported, inspect emac_customer_contract_delta
  4. if policies were cancelled or preserved unexpectedly, inspect emac_customer_contract_with_merged_policies
  5. if termination behaved unexpectedly, inspect emac_customer_contract_terminated_vehicles
Status: Approved
Category: Protected
Authored By: Hadley on Apr 22, 2026