InsightViz on Google Cloud - InsightViz Blog

On every cloud the deployment is the same three things: a container, an object store, a Postgres database. What differs is which of that cloud's data can be read live, and on Google Cloud the answer turns into a cost argument rather than a technical one.

Status, stated up front

AWS is the deployment we run in production. Google Cloud is supported by the code — the storage layer reads GCS natively and BigQuery is a first-class connector — but we have not run it in production ourselves. The operational detail below is a design, not a runbook.

What runs where

Piece	On Google Cloud	Notes
The API	Cloud Run, or GKE if you already run one	One container, 1 vCPU / 4 GB. Cloud Run wants a minimum instance so the first request of the morning is not a cold start.
The extracts	A private GCS bucket	Parquet, read through httpfs. Put a lifecycle rule on it.
The metadata	Cloud SQL for PostgreSQL	Models, dashboards, versions, policies, audit.
The console and site	GCS behind Cloud CDN, or Firebase Hosting	Static; no server.
Identity	Google Workspace, or Okta / Entra	Roles from your directory.
Credentials	The service account, via the default credential chain	No key files in configuration.

The engine creates a DuckDB secret of type GCS with PROVIDER CREDENTIAL_CHAIN, so on Cloud Run it resolves to the attached service account. Grant that account object read and write on the extracts bucket and nothing further.

BigQuery: extract only, and why that saves money

The attach paths are PostgreSQL, MySQL and Redshift. BigQuery is not among them, so a BigQuery datasource is extracted on a schedule — streamed out and written as Parquet to your GCS bucket.

On most platforms that would be a compromise. Against BigQuery's pricing model it is the correct design, and the arithmetic is brutal enough to be worth spelling out.

LIVE AGAINST BIGQUERYEvery dashboard open is a scan

On-demand BigQuery bills per byte scanned. Twelve panels, two hundred readers, a working day — that is thousands of scans of the same partitions to answer questions whose answers did not change between them.

EXTRACT TO GCSOne scan, then none

One scheduled extract writes an aggregated Parquet copy. Every dashboard open after that reads a few megabytes from GCS and scans no BigQuery bytes at all.

flowchart LR
    BQ[("BigQuery")] -->|"one scheduled
extract"| GCS[("Parquet in GCS")]
    GCS --> API["The API
Cloud Run"]
    API --> D["Dashboards"]
    API --> E["Explore"]
    API --> A["Ask"]
    BQ -.->|"zero bytes scanned
on a dashboard open"| D
    style GCS fill:#eef1ff,stroke:#c7cdf2
    style BQ fill:#f7f8fb,stroke:#d8dbe8

The dotted line is the saving. After the refresh, reading a dashboard costs GCS reads — fractions of a cent — instead of a BigQuery scan.

A BI tool that queries BigQuery live is charging your warehouse bill for the privilege of showing the same number to the same person twice.

If you are on flat-rate slots rather than on-demand, the shape of the argument changes but the conclusion does not: reporting load stops competing with the transformations and loads that earn money.

A worked example

The BigQuery arithmetic, done

A retailer with a 4 TB orders table, partitioned by date. Twelve panels on the main dashboard, 180 people opening it on a working day. On-demand pricing at roughly $6.25 per TB scanned.

Approach	Scans per day	TB scanned/day	Monthly	Freshness
Live, no caching	12 panels × 180 opens = 2,160	~430 TB (partition-pruned to ~200 GB each)	~$58,000	Current to the second
Live, with a 1-hour cache	12 × 9 hours = 108	~21 TB	~$2,900	Up to an hour old
Extract, refreshed hourly	9 per day (one per refresh)	~1.8 TB	~$240	Up to an hour old
Extract, refreshed nightly	1 per day	~0.2 TB	~$27	Up to a day old

Illustrative composite. The figures are internally consistent and every step is one the product supports today.

Rows two and three deliver identical freshness and differ by a factor of twelve, because a cached live query still re-scans once per cache expiry per panel, while an extract scans once per refresh for the whole dashboard. The more panels you add, the wider that gap gets.

Figures are illustrative and depend heavily on partitioning and clustering — but the ordering does not change, and the ordering is the decision.

xychart-beta horizontal
    title "Monthly BigQuery spend for the same dashboard, USD"
    x-axis ["Live", "Cached 1h", "Hourly", "Nightly"]
    y-axis "USD per month" 0 --> 60000
    bar [58000, 2900, 240, 27]

Live is no caching; Cached 1h is a live query with a one-hour result cache; Hourly and Nightly are extracts on that refresh schedule. Bars two and three deliver identical freshness. The difference is that a cached live query re-scans once per panel per expiry, while an extract scans once per refresh for the whole page.

The rest of the Google estate

Source	Live query	What happens
BigQuery	Extract only	Scheduled extract into Parquet on GCS. See above — this is the right default.
Cloud SQL for PostgreSQL	Yes — attached read-only	Attaches natively. Use this when a screen must be current to the second.
Cloud SQL for MySQL	Yes	Attached read-only.
AlloyDB	Yes	Postgres wire protocol, so it attaches.
GCS (Parquet, CSV)	Effectively	Read directly through httpfs; already the native shape.
Apache Iceberg on GCS	Yes	Scanned by metadata location, never cached.
Databricks on GCP	Extract only	Live Databricks needs a passthrough we have not built.
Looker / Looker Studio	Not a source	A peer tool, not a data source. Migrate the underlying model instead.

Service-account JSON, and where it should not live

The BigQuery connector takes a service-account key. That key is stored encrypted and never shown again after it is entered — but the better pattern on Google Cloud is not to have one at all.

Prefer workload identity to a key file

If the API runs on Cloud Run or GKE with a service account that already has BigQuery read on the datasets in question, the credential chain resolves without a key. A key file in a configuration store is a credential with no expiry, no rotation and no owner — and it will outlive the project that created it.

Cold starts are the one Cloud Run trap

Cloud Run scales to zero by default, which is excellent for a spiky API and wrong for a dashboard. The first reader of the morning pays the container start plus the query, and forms an opinion about the product that is hard to shift.

Set a minimum instance count of one. It costs roughly the price of an always-on container and removes the only genuinely bad experience in this deployment.

A dashboard with three headline cards for sales, profit and profit ratio, a trend chart and breakdowns by region and category — **What a reader should see immediately.** Panels paint from the last known numbers while fresh ones are fetched, and say so on screen. That behaviour only helps if the container is already running.

What it costs

Component	Shape	Rough monthly
Cloud Run	1 always-warm instance, 1 vCPU / 4 GB	~$35–50
GCS	Aggregated Parquet, usually low gigabytes	~$1–5
Cloud CDN / Firebase Hosting	Console and site	~$0–15
Cloud SQL	Small Postgres instance	~$15–50
BigQuery	One scheduled extract per datasource	Down, usually sharply
Per reader	—	Nothing

Zero bytesscanned in BigQuery on a dashboard open

min-instances 1the one Cloud Run setting that matters

Workload identityinstead of a service-account key file

$0per reader, per month

Three things to get right

Set the refresh cadence against the BigQuery cost, not by habit. Hourly on a large table is a real bill; nightly on a table that loads nightly is free money. This is the single highest-leverage decision on GCP.
Lifecycle rule on the extracts bucket from day one. Versions accumulate by design, because that is the rollback story.
Extract aggregated, not raw. The extract should be the grain the dashboards need. Copying a billion-row fact table verbatim wastes the scan you just paid for.

What we have not proven

Not run in production by us. The GCS code paths are real; the surrounding Google Cloud deployment is a design. Ask for a proof of concept, not a date.
No BigQuery BI Engine integration, and none planned — the extract makes it unnecessary for this workload.
Incremental extract by partition is the feature that makes very large BigQuery tables comfortable, and it is not finished. Today a refresh rewrites the extract.

The reference deployment, on AWS → · Why the extract makes dashboards fast →

← All posts Request a demo