BigQuery

​

Connection fields

​

Creating a service account

1. In the Google Cloud console, open IAM & Admin → Service Accounts for the project that holds your data.
2. Create service account. Name it something descriptive — traffical-reader works.
3. Grant the roles below.
4. Open the new service account, go to Keys → Add key → JSON. Download the file.
5. Paste the entire JSON into the Traffical connection form.

​

IAM roles

# Source datasets — read only
bq add-iam-policy-binding \
  --member="serviceAccount:[email protected]" \
  --role="roles/bigquery.dataViewer" \
  your-project:analytics

# Traffical dataset — read and write
bq mk --dataset your-project:traffical
bq add-iam-policy-binding \
  --member="serviceAccount:[email protected]" \
  --role="roles/bigquery.dataEditor" \
  your-project:traffical

​

Cost

• Always scope by date in your fact/assignment definitions. Use {{start_date}} and {{end_date}} to restrict scans to the pipeline’s window. A WHERE event_time BETWEEN '{{start_date}}' AND '{{end_date}}' on a partitioned table scans only the relevant partitions.
• Partition on time. A partitioned table is dramatically cheaper to scan when the pipeline filters by event_time or assigned_at.
• Cluster on user/entity columns. Clustering on user_id makes joins between assignments and facts cheaper.
• Avoid SELECT *. BigQuery is columnar — project only the columns you need.

​

Recommended schema layout

CREATE TABLE `your-project.analytics.experiment_assignments` (
  user_id          STRING NOT NULL,
  assigned_at      TIMESTAMP NOT NULL,
  experiment_name  STRING NOT NULL,
  variant          STRING NOT NULL
)
PARTITION BY DATE(assigned_at)
CLUSTER BY user_id, experiment_name;

CREATE TABLE `your-project.analytics.orders` (
  order_id     STRING NOT NULL,
  user_id      STRING NOT NULL,
  order_total  NUMERIC NOT NULL,
  currency     STRING,
  event_time   TIMESTAMP NOT NULL
)
PARTITION BY DATE(event_time)
CLUSTER BY user_id;

​

Example definitions

SELECT
  user_id,
  assigned_at,
  experiment_name AS policy_key,
  variant         AS allocation_key
FROM `your-project.analytics.experiment_assignments`
WHERE assigned_at BETWEEN TIMESTAMP('{{start_date}}')
                      AND TIMESTAMP('{{end_date}}')

SELECT
  user_id,
  event_time,
  order_total
FROM `your-project.analytics.orders`
WHERE event_time BETWEEN TIMESTAMP('{{start_date}}')
                     AND TIMESTAMP('{{end_date}}')

​

Network access

​

Gotchas

• Service account expiry. Service account JSON keys don’t expire automatically, but rotating them periodically is a good practice. After rotation, paste the new JSON into the connection and re-save.
• Multi-region datasets. If your dataset is multi-region (EU, US), the pipeline still works — queries run in the dataset’s region.
• Slot reservations. If you use BigQuery slot reservations rather than on-demand billing, Traffical’s queries consume slots from whatever reservation the service account inherits.
• Streaming buffer. Recently-inserted streaming rows may take a few minutes to appear in queries. If your application writes events to BigQuery and the pipeline runs immediately afterwards, those events might be missed in the first run. The next run picks them up.