Docs

BigQuery setup.

Everything Hooktopus needs from GCP, with the exact gcloud and bq commands. About 5 minutes if you've never used gcloud before.

You'll do this once per workspace. The wizard at /w/[slug]/destinations/new generates these same commands with your project / dataset substituted in. This page is the manual version.

1 · Pick (or create) a GCP project

Hooktopus writes to your BigQuery, so you need a project to host the dataset. If you already have one, you can reuse it — we only need scoped permissions on one dataset, not project-wide access.

bash

# If you don't already have one
gcloud projects create hooktopus-warehouse-prod \
  --name "Hooktopus warehouse (prod)"

2 · Enable the BigQuery API

bash

gcloud services enable bigquery.googleapis.com \
  --project hooktopus-warehouse-prod

3 · Create a dataset

Pick a location. US and EU are the most common.

bash

bq --project_id hooktopus-warehouse-prod mk \
  --dataset \
  --location US \
  hooktopus_raw

4 · Create the events table

The table is single-wide with JSON payload + headers. Schema-on-read keeps you safe from upstream payload churn — your dbt staging models are where shape gets enforced.

bq query --use_legacy_sql=false

CREATE TABLE IF NOT EXISTS `hooktopus-warehouse-prod.hooktopus_raw.events` (
  event_id      STRING    NOT NULL,
  received_at   TIMESTAMP NOT NULL,
  endpoint_name STRING    NOT NULL,
  source_ip     STRING,
  http_method   STRING,
  content_type  STRING,
  headers       JSON,
  payload       JSON
)
PARTITION BY DATE(received_at)
CLUSTER BY endpoint_name;

We use JSON not STRING for headers/payload. This is deliberate — it preserves apostrophes, emojis, escape sequences, and lets you use JSON_VALUE safely from dbt.

5 · Create a service account

bash

gcloud iam service-accounts create hooktopus-writer \
  --project hooktopus-warehouse-prod \
  --display-name "Hooktopus destination writer"

6 · Grant scoped permissions (dataset, not project)

We need bigquery.dataEditor on the one dataset. Don't grant project-wide — there's no reason to.

bash

bq add-iam-policy-binding \
  --member='serviceAccount:hooktopus-writer@hooktopus-warehouse-prod.iam.gserviceaccount.com' \
  --role='roles/bigquery.dataEditor' \
  hooktopus-warehouse-prod:hooktopus_raw

7 · Download the JSON key

bash

gcloud iam service-accounts keys create ~/hooktopus-sa.json \
  --iam-account hooktopus-writer@hooktopus-warehouse-prod.iam.gserviceaccount.com

How we handle this credential

When you paste the JSON into the wizard, we encrypt it with an AES-256-GCM key held in Cloudflare KV (per-workspace, root-key rotated quarterly). The plaintext never touches D1, our logs, or our observability tools. Re-decryption happens only inside the destination-writer Worker at the moment a BQ write is needed.

8 · Paste into the wizard, click "Test connection"

Test connection writes one row, reads it back, and deletes it. If anything's wrong with IAM, you'll see the BQ error message inline. The most common one: dataset-level permissions weren't picked up yet — IAM propagation can take 60 seconds. Wait and retry.

Common errors

Permission denied on dataset: the SA doesn't havedataEditor on the dataset yet. Check step 6 ran. IAM can take 60s to propagate.
Invalid JSON: you pasted a truncated SA key. The JSON should be ~2.5KB and contain "type": "service_account" at the top.
Dataset not found: wrong project ID or region. The dataset must exist before destination creation; we don't auto-create.