Complete schema reference for the Transmute Engine BigQuery dataset. This covers all 4 tables, their fields, relationships, and example queries.

Note: Replace YOUR_PROJECT.YOUR_DATASET in all SQL examples with your actual Google Cloud project ID and dataset name.

View Your Schema in BigQuery #

Run these SQL queries in the BigQuery Console or via the MCP AI connection to inspect your schema directly.

List all datasets #

SELECT schema_name
FROM INFORMATION_SCHEMA.SCHEMATA

List all tables in your dataset #

SELECT table_name, table_type
FROM YOUR_DATASET.INFORMATION_SCHEMA.TABLES
ORDER BY table_name

View columns for a specific table #

SELECT column_name, data_type, is_nullable
FROM YOUR_DATASET.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'outpipe_events'
ORDER BY ordinal_position

View all columns across all tables #

SELECT table_name, column_name, data_type, is_nullable
FROM YOUR_DATASET.INFORMATION_SCHEMA.COLUMNS
ORDER BY table_name, ordinal_position

View table partitioning and clustering #

SELECT table_name,
  ddl
FROM YOUR_DATASET.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'

Row counts per table #

SELECT 'outpipe_events' AS table_name, COUNT(*) AS row_count
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_events
UNION ALL
SELECT 'outpipe_event_items', COUNT(*)
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_event_items
UNION ALL
SELECT 'outpipe_user_data', COUNT(*)
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_user_data
UNION ALL
SELECT 'outpipe_user_properties', COUNT(*)
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_user_properties

Preview recent data #

SELECT *
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_events
WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY event_timestamp DESC
LIMIT 10

Table List #

Table Relationships #

outpipe_events
  |-- event_id -----> outpipe_event_items.event_id       (1:many)
  |-- event_id -----> outpipe_user_data.event_id         (1:1)
  |-- client_id ----> outpipe_user_data.client_id        (many:many)
  |-- client_id ----> outpipe_user_properties.client_id  (many:many)
  |-- user_id ------> outpipe_user_data.user_id          (many:many)
  |-- user_id ------> outpipe_user_properties.user_id    (many:many)
  |-- pii_mode -----> outpipe_user_data.pii_mode         (controls PII storage)

1. outpipe_events #

Main events table storing all tracked interactions — online page views, purchases, offline conversions, webhook events, CRM syncs, and more.

• Partitioned by: event_timestamp (DATE)
• Clustered by: event_name, client_id, conversion_source, utm_campaign
• Total columns: 86 (including JSON custom_fields)

Universal Fields #

Present on every event row regardless of event type.

Event-Specific Fields #

Populated based on event_name type.

Email Marketing #

Ad Creative Tracking #

Affiliate #

Video Tracking #

E-commerce #

Lead Generation #

Search #

Authentication #

Gaming / Gamification #

Conversion Source and Offline #

Attribution Linking #

Webhook Events #

Meeting / Call #

Sales / CRM #

Identity Resolution #

Audit Fields #

Custom Fields #

Custom Fields Example:

{
  "lead_quality": "high",
  "industry_vertical": "saas",
  "deal_size": 50000,
  "demo_scheduled": true,
  "contract_length_months": 12
}

Query custom fields:

SELECT
  event_name,
  JSON_VALUE(custom_fields, '$.lead_quality') AS lead_quality,
  SAFE_CAST(JSON_VALUE(custom_fields, '$.deal_size') AS FLOAT64) AS deal_size
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_events
WHERE JSON_VALUE(custom_fields, '$.lead_quality') = 'high'

2. outpipe_event_items #

Normalized e-commerce line items. One row per product per event. Join to outpipe_events via event_id.

• Partitioned by: event_timestamp (DATE)
• Total columns: 14

3. outpipe_user_data #

PII identity table — stores hashed and optionally plaintext PII for Enhanced Conversions. Populated per-event based on the pii_mode flag set on each event in outpipe_events. Default is none (no PII processing) unless explicitly set per event.

• Partitioned by: event_timestamp (DATE)
• Clustered by: gorilla_food, client_id
• Total columns: 26

Identity Fields #

Hashed PII #

Always populated when raw PII is present, regardless of pii_mode.

Plaintext PII #

Only populated when pii_mode = "full" — requires explicit user consent.

Address Fields #

User-declared address data (NOT geo-IP resolved). Only populated when pii_mode = "full".

Metadata #

4. outpipe_user_properties #

GA4 custom user properties as key-value pairs. Multiple rows per user (one per property). Use PIVOT queries to convert rows to columns.

• Partitioned by: created_at (DATE)
• Total columns: 7

Data Type Reference #

Query Best Practices #

Always filter on partition key to control query costs:

-- Events: filter on event_timestamp
WHERE DATE(event_timestamp) = '2026-02-23'
WHERE DATE(event_timestamp) BETWEEN '2026-02-01' AND '2026-02-28'

-- User data/properties: filter on created_at
WHERE DATE(created_at) >= '2026-01-01'

Join events to items:

SELECT e.event_name, e.transaction_id, i.item_name, i.price, i.quantity
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_events e
JOIN YOUR_PROJECT.YOUR_DATASET.outpipe_event_items i
  ON e.event_id = i.event_id
WHERE DATE(e.event_timestamp) = '2026-02-23'
  AND e.event_name = 'purchase'

Pivot user properties:

SELECT
  client_id,
  MAX(IF(property_name = 'membership_tier', property_value, NULL)) AS membership_tier,
  MAX(IF(property_name = 'preferred_language', property_value, NULL)) AS preferred_language
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_user_properties
WHERE DATE(created_at) >= '2026-01-01'
GROUP BY client_id

Identity resolution (events to user data):

SELECT e.event_name, e.client_id, e.pii_mode,
  u.sha256_email, u.email, u.gorilla_food
FROM YOUR_PROJECT.YOUR_DATASET.outpipe_events e
JOIN YOUR_PROJECT.YOUR_DATASET.outpipe_user_data u
  ON e.event_id = u.event_id
WHERE DATE(e.event_timestamp) = '2026-02-23'

Supported Event Types #