What Is Google BigQuery?

Google BigQuery is a fully-managed, server-less data warehouse that enables scalable and fast SQL queries using the processing power of Google's infrastructure. It is designed to handle large datasets, allowing users to analyze terabytes of data in seconds and petabytes in minutes.

MetaRouter provides an efficient Extract, Transform and Load (ETL) platform that streams structured customer data into BigQuery in real-time. Ensure that data is always structured properly with MetaRouter's parameter-by-parameter data mapping and transformation playbooks.

Capabilities

• Streams structured customer event data directly into BigQuery as events arrive, with no batching required.
• Applies in-stream transformations so data lands already mapped and structured, improving data availability and reducing costly errors.
• Delivers a private data stream that is never co-mingled with any other customer's data.
• Stores each event in its own table by default, with optional table overrides to control table names.
• Supports time-based table partitioning to lower query cost and speed up date-filtered queries on high-volume events.
• Adds addressable third-party IDs to your warehouse data when paired with the Sync Injector.

Important Notes and Limitations

• Workload Identity:

  • If your MetaRouter deployment runs on Google Kubernetes Engine (GKE), you can authenticate with BigQuery using Workload Identity instead of a JSON service account key. Workload Identity allows your MetaRouter pods to authenticate directly with Google Cloud APIs without managing static credentials, providing a more secure and maintenance-free authentication method.
  • The flow for Workload Identity is the recommended path.
  • Workload Identity is supported only for clusters running in Google Cloud (GKE). Deployments hosted outside of GCP must use the Service Account JSON key authentication method.

• Event Name and Table Matching:

  • By default, each event is stored in a BigQuery table named after the event. Changing the name of an event creates a new table.
  • To choose your own table names, or to store events in partitioned tables, see Step 3 below.

• Dataset Creation:

  • If you select false for BIG_QUERY_OPTIONS under the connection parameters, the integration will not create the dataset, so you do not have to grant the bigquery.datasets.create permission in the IAM role. If the dataset does not exist, no events will be delivered.

    

• Event Table Schema Consistency:

  • Once an event table is created in BigQuery, its schema must remain consistent. MetaRouter does not programmatically delete or alter the structure of existing event tables to prevent data inconsistencies and ensure data integrity. This means any changes to the event structure should be carefully planned and manually handled.
  • MetaRouter cannot alter or delete existing columns, but we can add new columns based on new parameters being included in an event payload and mapped to the destination. This allows for extending the data model as new requirements emerge, without disrupting the existing data flow. However, ensure that new columns follow the established schema and data type conventions to maintain consistency.

• Data Type Consistency:

  • It is crucial to maintain data type consistency for each field across all events. For instance, if a field is initially set as a string, all subsequent events must adhere to this data type. Inconsistent data types can cause ingestion errors and complicate data analysis. Establishing and following a strict schema ensures smooth data processing and accurate analysis.
  • If MetaRouter infers the wrong type for a field, for example a formatted date string that should be stored as a TIMESTAMP, use FIELD_OVERRIDES to set the column type explicitly.

• Table Overrides Behavior:

  • Events with a custom table name defined in TABLE_OVERRIDES will be routed to their specified tables.
  • If DEFAULT_TABLE_OVERRIDE is not provided, any event not listed in TABLE_OVERRIDES will be stored in a table named after the event.
  • If DEFAULT_TABLE_OVERRIDE is provided, events not defined in TABLE_OVERRIDES will be sent to the specified default table.
  • Both TABLE_OVERRIDES and DEFAULT_TABLE_OVERRIDE fields are optional. If neither is provided, each event will be stored in a BigQuery table named after the event (default behavior).
  • Example:

DEFAULT_TABLE_OVERRIDE: "catch_all_events"
TABLE_OVERRIDES:
  identify: identities
  page: page_view
  products_searched: search

• Partitioning:
  • The optional PARTITIONING parameter lets you store some or all events in BigQuery tables that are split into time-based partitions (by hour, day, month, or year). Partitioned tables are cheaper and faster to query when you filter by date, so this is most useful for your highest-volume events.
  • Partitioning is set when MetaRouter first creates a table. BigQuery does not allow partitioning to be added, removed, or changed on a table that already exists. For that reason, partitioned tables get a new name by default, such as page_daily, rather than reusing the existing page table.
  • TABLE_OVERRIDES takes priority: an event listed there always goes to that plain table and is never partitioned. DEFAULT_TABLE_OVERRIDE is only a fallback for events with no other rule.
  • See the Partitioning section below for the full configuration.

Getting Started - From Your Google BigQuery Account

Step 1: Gather Project ID

• Navigate to the Google Cloud Console:
  • Go to the Google Cloud Console.
• Select Your Project:
  • Click on the project drop-down menu at the top of the page. If you have multiple projects, you can select the appropriate one from this list. If you don’t have a project, you can create a new one by clicking on "New Project".
• Locate the Project ID:
  • Once you have selected your project, the Project ID will be displayed on the project dashboard. It is typically in the format of my-project-id.

Step 2: Choose Your Authentication Method

Path A - Workload Identity Flow

• Set up a service account and gather credentials:
  • If you are using the Workload Identity flow, please reach out to your MetaRouter representative. We will generate a service account email for you.
• Grant Permissions:
  • Assign the necessary roles to the service account email provided by the MetaRouter team. You will need to grant at least the "BigQuery Data Editor" roles. Please see more information on user roles below:
    • BigQuery IAM Roles and Permissions - If you select BIG_QUERY_OPTIONS as false in the connection parameters, then you do not have to give permission to create a dataset.
    • Control access to resources with IAM
  • Authentication with Workload Identity
    1. Leave the CREDENTIALS field empty in your MetaRouter BigQuery integration configuration. When no credentials are provided, the integration automatically falls back to GCP default credentials, which Workload Identity supplies.

Path B - Service Account Key Flow

• Create a Service Account:
  • Navigate to the Google Cloud Console.
  • Go to "IAM & Admin" section and select "Service Accounts".
  • Click "Create Service Account", enter a name, and provide a description.
    • Please be sure to associate this account with the project associated with the project ID you will use for this integration.
• Grant Permissions:
  • Assign the necessary roles to your service account. You will need to grant at least the "BigQuery Data Editor" roles. Please see more information on user roles below:
    • BigQuery IAM Roles and Permissions - If you select BIG_QUERY_OPTIONS as false in the connection parameters, then you do not have to give permission to create a dataset.
    • Control access to resources with IAM
• Create and Download Key File:
  • After creating the service account, go to the "Keys" section and add a key.
  • Select "Create new key", choose "JSON", and download the key file. This JSON file contains the credentials needed to authenticate your application with BigQuery.
• Use the Key File as CREDENTIALS:
  • Upload the key file to your MetaRouter integration configuration as part of the setup process for BigQuery.

Step 3: Decide How Events Map to Tables

Decide how you want events stored before MetaRouter delivers the first one. MetaRouter never alters or deletes a table once it exists, and BigQuery does not allow partitioning to be changed on an existing table, so these choices are easiest to make up front.

• One table per event (default): each event, such as page or order_completed, gets its own plain table named after the event. No configuration is needed.
• Custom table names: use TABLE_OVERRIDES to send specific events to tables you name, and DEFAULT_TABLE_OVERRIDE to collect every other event into a single table. See Important Notes above.
• Partitioned tables: use PARTITIONING to store high-volume events in time-partitioned tables, which lowers query cost and speeds up queries that filter by date. See Partitioning below.

You can change these settings later, but tables that already exist keep their current structure. New settings only affect tables MetaRouter creates after the change.

Getting Started - From Your MetaRouter Account

Adding a Google BigQuery integration

From the integration library, add a Google BigQuery integration. Then, fill out the Connection Parameters:

Partitioning

BigQuery can split a table into partitions by time, so a query that filters by date only scans the days (or hours, months, or years) it needs. This lowers query cost and speeds up queries on large tables. Google's partitioned tables guide explains the concept in detail.

The PARTITIONING connection parameter tells MetaRouter which events to store in partitioned tables and how to partition them. It is optional. Events you do not include are unaffected. See Which table does an event go to? below.

Settings

The value is a short YAML block. This example partitions every event by day:

granularity: BIG_QUERY_PARTITION_GRANULARITY_DAILY
partitionField: received_at
events:
  - eventName: "*"

Each entry in the events list has:

Examples

Partition every event, one table per event. Creates page_daily, identify_daily, order_completed_daily, and so on.

granularity: BIG_QUERY_PARTITION_GRANULARITY_DAILY
partitionField: received_at
events:
  - eventName: "*"

Partition only your busiest events, and delete data after 90 days. page and product_viewed go to page_daily and product_viewed_daily. Every other event follows the default behavior and is written to a plain table with a matching name, such as order_completed.

granularity: BIG_QUERY_PARTITION_GRANULARITY_DAILY
partitionField: received_at
expirationDays: 90
events:
  - eventName: page
  - eventName: product_viewed

Combine a group of events into one partitioned table. Every event that starts with product_ goes into a single table called product_events, partitioned by month. Every other event follows the default behavior and is written to a plain table with a matching name, such as page.

granularity: BIG_QUERY_PARTITION_GRANULARITY_MONTHLY
partitionField: timestamp
events:
  - eventName: product_*
    tableOverride: product_events

Choose your own table names. tableOverride can be any table name BigQuery accepts. The {event} token is replaced with each event's name and the rest is used as typed, so here page is written to page_by_hour instead of the default page_hourly. Because the pattern is "*", every event matches and gets its own table, and no event falls back to the default behavior.

granularity: BIG_QUERY_PARTITION_GRANULARITY_HOURLY
partitionField: received_at
events:
  - eventName: "*"
    tableOverride: "{event}_by_hour"

Which table does an event go to?

Every event is written to exactly one table. MetaRouter decides which one by asking three questions, in order.

1. Is there a specific rule for this event?

There are two kinds of specific rules. An event follows at most one of them.

If the same event appears in both places, TABLE_OVERRIDES wins and the event is not partitioned. This protects table names you set up before partitioning was available.

2. If not, is DEFAULT_TABLE_OVERRIDE set?

The event goes to that single plain table. DEFAULT_TABLE_OVERRIDE is only a fallback. It never pulls an event away from a specific rule, so setting it does not stop any event from being partitioned.

3. If not, the event goes to a plain table named after the event.

This is the default behavior, and it is what happens when none of the three settings is used. page goes to page, order_completed goes to order_completed, and so on.

Example with all three settings

Suppose the connection parameters are set as follows:

Here is where four different events are written:

If DEFAULT_TABLE_OVERRIDE were blank, order_completed would go to a plain table called order_completed instead.

Good to know

• TABLE_OVERRIDES and tableOverride are different settings. TABLE_OVERRIDES names plain tables and turns partitioning off for the events listed in it. tableOverride, inside a PARTITIONING entry, names a partitioned table. To rename a partitioned table, use tableOverride.
• Partitioning cannot be changed on an existing table. To change the granularity or partition field for an event, give it a new tableOverride name so MetaRouter creates a fresh table.
• If an event arrives without the partitionField, or the field is not a timestamp, MetaRouter still delivers it, but to the unpartitioned table for that event (for example page instead of page_daily). If data is landing in the wrong table, check that the field is mapped in your playbook and typed as a timestamp.
• Wildcard patterns and tableOverride values are checked when you save the integration, so mistakes are caught before any data is sent.
• Partitioning was added to the Google BigQuery integration in Q3 2026. If your integration was set up before then, the PARTITIONING parameter may not appear in the connection parameters yet. Reach out to your MetaRouter representative and we will enable it for you. Your existing tables and event flow are not affected.

Event Mappings

Due to the complexity of event mapping in BigQuery, it is highly recommended to reach out to the MetaRouter team and collaborate with a solutions engineer. While MetaRouter includes recommended parameters under Global mapping, you will need to accurately map events when initially building out the integration. This collaboration ensures precise and efficient configuration tailored to your specific needs.

Global

Global mappings will be applied to all events. If your parameter names do not match the Expected Inputs provided, you will need to overwrite the Inputs provided with your own.

Ensuring event and data consistency is crucial when using BigQuery with MetaRouter. Please see Notes and Limitations above.

Additional Google BigQuery Documentation

Google BigQuery - Data Types

BigQuery IAM Roles and Permissions

Control access to resources with IAM