Skip to content

Google BigQuery

The test-estate safety rule: stand the test estate up only beside a test instance, never a production deployment. Its systems are throwaway test services with published credentials: change-me is every test credential. They are still encrypted and still ask for a login, because FACE refuses a connection that is not (see Connector security). Use a scratch machine or a scratch cluster.

Grade D · dataset: SQL (DDL and rows in the engine’s dialect) · type bigquery

What it is

A Google BigQuery dataset. In the test estate it holds fleet history: GPS pings and planned routes in dataset fleet.

Grade

D. Dataset and model: the quickstart dataset and its expected model ship. There is no vendor system to stand up for this type; load the dataset and fetch it.

See the grade table for all 32 types.

Settings and credentials

From the connection catalogue. Settings are ordinary fields on the connection record. Credentials are kept in a sealed bundle and never returned.

Names
Typebigquery
Other spellings that resolvebq
Settingsproject_id, dataset_id
Credentialsservice_account_json

The test connection

Quillmere fleet history (BigQuery)

SettingTest value
project_idquillmere-demo
dataset_idfleet

Credentials: service_account_json. In the test estate, every credential is change-me. Credentials are write-only: they go into the sealed credential bundle and are never returned.

Stand up the test system

The safety rule at the top of this page comes first: a scratch machine or a scratch cluster, never a production or platform one.

  1. Download bigquery.zip and unzip it. It holds bigquery/01_schema.sql, bigquery/02_data.sql and bigquery/expected.json, in the engine’s own dialect.
  2. Run 01_schema.sql, then 02_data.sql, in bq query --use_legacy_sql=false in a project you own. The DDL names the project quillmere-demo: replace it with your project id first.
  3. Add the connection with the settings above, pointed at your own project and dataset.

What FACE finds

FACE’s BigQuery connector reports that it has no catalogue to read, rather than inventing one. The expected model is what INFORMATION_SCHEMA returns for this dataset.

DatasetKindDomainPrimary keyRowsFields
fleet.gps_pingstabletelematicsping_id2318
fleet.planned_routestabletelematicsshipment_id, seq2404

expected.json in the download has the full model: every field with its logical type, the source’s native type and nullability.

How it joins the Quillmere universe

This source carries 2 of the universe’s shared keys. The same ID means the same thing in every source: a shipment here is the same shipment in the database, the event bus, the GPS track and the claim PDF.

Universe entityFields hereAlso in
devicegps_pings.device_idcctv, databricks, files, gps, iot, kafka, mqtt, postgres, rfid, servicenow
shipmentgps_pings.shipment_id, planned_routes.shipment_idd365, databricks, document, excel, files, github, guidewire, iot, kafka, logisticsapi, mqtt, mysql, objectstore, postgres, redshift, salesforce, sap, servicenow, sharepoint, tms, whs, yms

Evidence it holds for the planted anomalies:

  • ANOM-5, A GPS route deviation. bigquery/02_data.sql in bigquery.zip, look for 'PG-000205' (first off-corridor ping).
  • ANOM-5, A GPS route deviation. bigquery/02_data.sql in bigquery.zip, look for 'SHP-50021' (the planned corridor).

See all connectors at once for the walkthrough across every source.

Download

This is one of 25 zips: the 7 streaming types ship as replay recordings instead.

What is in the zip
  • bigquery/01_schema.sql
  • bigquery/02_data.sql
  • bigquery/expected.json

The whole universe is universe.json. manifest.json lists every download with its size and SHA-256, and SHA256SUMS is the same list for sha256sum -c.