Connect Google BigQuery

Learn how to integrate Google BigQuery with Harness FME to enable Warehouse Native Experimentation.

Warehouse Native Experimentation allows you to run experiments on data that already lives in your data warehouse. By connecting Harness FME directly to your Google BigQuery instance, you can securely query and analyze experiment data from your source of truth.

To begin, connect your Google BigQuery instance as a data source.

Prerequisites

Ensure that you have the following before getting started:

• Access to your Google Cloud project with BigQuery enabled

• A dataset containing your experiment data

• A designated results table where experiment results are stored

• A service account with Read access to assignment and metric source tables, and Write access to a results table

• A service account key in JSON format

Setup

Harness recommends the following best practices:

• Use a service account rather than a personal Google Cloud user.

• Grant read-only access to the datasets containing the assignment and event data.

• Grant write access only to the experiment results table.

To integrate BigQuery as a data warehouse for Warehouse Native Experimentation:

1. From the Harness FME navigation menu, click FME Settings and click View on a project on the Projects page. Then, navigate to the Data Source tab.

2. Select BigQuery as your data warehouse. In the Data Sources tab of your Harness FME project, select BigQuery from the list of supported data warehouses.

   PROJECT EXPERIMENTATION TYPE

   A project uses a single experimentation type based on the metric source used.

   When you add a data source to a project, the project’s experimentation type is set to Warehouse Native. All metrics in the project must then use Warehouse Native metric sources.

   If a project instead uses metrics created from an ingested event source, the project’s experimentation type is set to Cloud.

3. Enter the following connection details:

   Field	Description	Example
   Project ID	Your Google Cloud project ID.	my-gcp-project
   Dataset	The BigQuery dataset containing experiment data.	analytics_dataset
   Service Account Email	The Google Cloud service account used for authentication.	[e-mail]
   Results Table Name	The table where experiment results are stored.	metric_results

   Harness FME respects BigQuery IAM permissions. The connection only has access to resources granted to the service account.

4. Provide authentication credentials by clicking Upload file to upload a JSON key file for your service account or clicking Paste text to enter the JSON key contents. Ensure the key corresponds to the service account email provided.

5. Test the connection by clicking Test Connection. Harness FME confirms the following:

   • The service account credentials are valid.

   • The dataset exists and is accessible.

   • The service account has required read and write permissions.

   If the test fails, verify that:

   • The service account has sufficient BigQuery permissions.

   • The Project ID and dataset are correct.

   • The JSON key is valid and active.

6. Select a dataset. After authentication, you can browse available datasets and tables based on your permissions. Select the dataset containing your assignment and metric source tables.

7. Specify a results table. Create a results table where Harness FME will write experiment analysis results, and ensure that:

   • The table exists in your database.

   • The schema matches the expected format for experiment results below.

   Field	Type	Description
   METRICID	STRING	Unique identifier for the metric being calculated.
   METRICNAME	STRING	Human-readable name of the metric being calculated.
   METRICRESULTID	STRING	Unique identifier representing a specific calculation per metric, per experiment, per analysis run.
   EXPID	STRING	Unique identifier for the experiment associated with this metric calculation.
   EXPNAME	STRING	Human-readable name of the experiment associated with this metric calculation.
   TREATMENT	STRING	The experiment variant (e.g., Control or Treatment) associated with the metric results.
   DIMENSIONNAME	STRING	The name of the dimension being analyzed (e.g., country, platform).
   DIMENSIONVALUE	STRING	The corresponding value of the analyzed dimension.
   ATTRIBUTEDKEYSCOUNT	INT64	Count of unique keys (users, sessions, etc.) attributed to this metric result.
   REQUESTTIMESTAMP	TIMESTAMP	Timestamp when the metric computation request occurred.
   MIN	FLOAT64	Minimum observed value for the metric.
   MAX	FLOAT64	Maximum observed value for the metric.
   COUNT	INT64	Total number of observations included in the metric calculation.
   SUM	FLOAT64	Sum of all observed metric values.
   MEAN	FLOAT64	Average (mean) of the metric values.
   P50	FLOAT64	50th percentile (median) metric value.
   P95	FLOAT64	95th percentile metric value.
   P99	FLOAT64	99th percentile metric value.
   VARIANCE	FLOAT64	Variance of the metric values.
   EXCLUDEDUSERCOUNT	INT64	Number of users excluded from the analysis (due to filters, SRM, etc.).
   ASOFTIMESTAMP	TIMESTAMP	Timestamp representing when the result snapshot was written.

   To create the results table with the correct structure, run the following SQL statement in Google BigQuery:

   CREATE OR REPLACE TABLE `your_project.your_dataset.metric_results` (
      METRICID STRING,
      METRICNAME STRING,
      METRICRESULTID STRING,
      EXPID STRING,
      EXPNAME STRING,
      TREATMENT STRING,
      DIMENSIONNAME STRING,
      DIMENSIONVALUE STRING,
      ATTRIBUTEDKEYSCOUNT INT64,
      REQUESTTIMESTAMP TIMESTAMP,
      MIN FLOAT64,
      MAX FLOAT64,
      COUNT INT64,
      SUM FLOAT64,
      MEAN FLOAT64,
      P50 FLOAT64,
      P95 FLOAT64,
      P99 FLOAT64,
      VARIANCE FLOAT64,
      EXCLUDEDUSERCOUNT INT64,
      ASOFTIMESTAMP TIMESTAMP
   );

8. Save and activate. Once the test passes, click Save to create the connection.

Your BigQuery data source can now be used to create assignment and metric sources for Warehouse Native Experimentation.

Example BigQuery configuration

Setting	Example
Vendor	BigQuery
Project ID	my-gcp-project
Dataset	analytics_dataset
Service Account	[e-mail]
Results Table	metric_results