BigQuery Table Schemas

Schema reference for the Shopify tables that the Vendo BigQuery destination creates in your dataset.

Last reviewed September 15, 2026

Reference for the Shopify tables that Vendo creates in your BigQuery dataset.

Orders Table

The shopify_orders table contains complete order information.

Column	Type	Description
`id`	INTEGER	Shopify order ID
`order_number`	STRING	Display order number
`email`	STRING	Customer email
`created_at`	TIMESTAMP	Order creation time
`updated_at`	TIMESTAMP	Last update time
`financial_status`	STRING	Payment status
`fulfillment_status`	STRING	Fulfillment state
`currency`	STRING	Currency code
`subtotal_price`	FLOAT	Order subtotal
`total_price`	FLOAT	Order total
`total_tax`	FLOAT	Tax amount
`total_discounts`	FLOAT	Discount amount
`total_shipping_price_set`	FLOAT	Shipping cost
`billing_address`	RECORD	Billing address details
`shipping_address`	RECORD	Shipping address details
`line_items`	REPEATED RECORD	Products in order
`customer`	RECORD	Customer information
`discount_codes`	STRING	Discount codes used
`discount_applications`	REPEATED RECORD	Discount details
`payment_gateway_names`	REPEATED STRING	Payment methods
`tags`	STRING	Order tags
`note`	STRING	Order notes
`custom_order_attributes`	REPEATED RECORD	Custom attributes
`landing_site`	STRING	Landing page URL
`referring_site`	STRING	Referrer URL
`source_name`	STRING	Order source
`app_id`	INTEGER	Creating app ID
`app_name`	STRING	Creating app name
`fulfillments`	REPEATED RECORD	Fulfillment records
`refunds`	REPEATED RECORD	Refund records
`test`	BOOLEAN	Test order flag
`confirmed`	BOOLEAN	Confirmation status
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Line Items Structure

Each line item in an order contains:

Column	Type	Description
`id`	INTEGER	Line item ID
`product_id`	INTEGER	Product ID
`variant_id`	INTEGER	Variant ID
`title`	STRING	Product title
`variant_title`	STRING	Variant title
`sku`	STRING	SKU
`quantity`	INTEGER	Quantity
`price`	FLOAT	Unit price
`total_discount`	FLOAT	Line discount
`vendor`	STRING	Vendor name
`fulfillment_service`	STRING	Fulfillment service
`fulfillment_status`	STRING	Item fulfillment status
`gift_card`	BOOLEAN	Is gift card
`taxable`	BOOLEAN	Is taxable
`tax_lines`	REPEATED RECORD	Tax details
`properties`	REPEATED RECORD	Custom properties
`discount_allocations`	REPEATED RECORD	Discount allocations

Fulfillments Structure

Nested within orders:

Column	Type	Description
`id`	INTEGER	Fulfillment ID
`order_id`	INTEGER	Parent order ID
`status`	STRING	Fulfillment status
`shipment_status`	STRING	Shipping status
`service`	STRING	Fulfillment service
`tracking_company`	STRING	Carrier name
`tracking_number`	STRING	Tracking number
`tracking_url`	STRING	Tracking URL
`tracking_numbers`	REPEATED STRING	All tracking numbers
`tracking_urls`	REPEATED STRING	All tracking URLs
`delivery_date`	TIMESTAMP	Delivery confirmation
`created_at`	TIMESTAMP	Fulfillment creation
`updated_at`	TIMESTAMP	Last update
`line_items`	REPEATED RECORD	Fulfilled items
`location_id`	INTEGER	Origin location
`location`	STRING	Location
`receipt`	RECORD	Receipt details
`origin_address`	RECORD	Ship-from address

Refunds Structure

Nested within orders:

Column	Type	Description
`id`	INTEGER	Refund ID
`order_id`	INTEGER	Parent order ID
`created_at`	TIMESTAMP	Refund creation
`processed_at`	TIMESTAMP	Processing time
`refund_amount`	FLOAT	Total refund amount
`note`	STRING	Refund note
`restock`	BOOLEAN	Items restocked
`refund_line_items`	REPEATED RECORD	Refunded items
`transactions`	REPEATED RECORD	Transaction details
`order_adjustments`	REPEATED RECORD	Order adjustments

Customers Table

The shopify_customers table contains customer information.

Column	Type	Description
`id`	INTEGER	Shopify customer ID
`email`	STRING	Customer email
`first_name`	STRING	First name
`last_name`	STRING	Last name
`phone`	STRING	Phone number
`created_at`	TIMESTAMP	Account creation
`updated_at`	TIMESTAMP	Last update
`orders_count`	INTEGER	Total orders
`total_spent`	STRING	Lifetime spend
`verified_email`	BOOLEAN	Email verified
`accepts_marketing`	BOOLEAN	Marketing consent
`tax_exempt`	BOOLEAN	Tax exemption
`tags`	STRING	Customer tags
`note`	STRING	Admin notes
`state`	STRING	Marketing state
`addresses`	REPEATED RECORD	Saved addresses
`default_address`	RECORD	Default address
`email_marketing_consent`	RECORD	Email consent details
`sms_marketing_consent`	RECORD	SMS consent details
`last_order_id`	INTEGER	Last order ID
`last_order_name`	STRING	Last order number
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Products Table

The shopify_products table contains product information.

Column	Type	Description
`id`	INTEGER	Shopify product ID
`admin_graphql_api_id`	STRING	Product GraphQL ID
`title`	STRING	Product title
`handle`	STRING	Product handle
`body_html`	STRING	Product description
`product_type`	STRING	Product type
`vendor`	STRING	Vendor name
`status`	STRING	Product status
`tags`	STRING	Product tags
`template_suffix`	STRING	Theme template suffix
`published_at`	TIMESTAMP	Publication time
`published_scope`	STRING	Publication scope
`created_at`	TIMESTAMP	Product creation
`updated_at`	TIMESTAMP	Last update
`image`	RECORD	Main image
`images`	REPEATED RECORD	All images
`options`	REPEATED RECORD	Product options
`variants`	REPEATED RECORD	Product variants
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Fulfillment Services Table

The shopify_fulfillment_services table contains the fulfillment services of the store.

Column	Type	Description
`id`	INTEGER	Fulfillment service ID
`admin_graphql_api_id`	STRING	Fulfillment service GraphQL ID
`name`	STRING	Service name
`service_name`	STRING	Service display name
`handle`	STRING	Service handle
`email`	STRING	Service email
`callback_url`	STRING	Callback URL
`provider_id`	INTEGER	Provider ID
`location_id`	INTEGER	Service location ID
`fulfillment_orders_opt_in`	BOOLEAN	Uses fulfillment orders
`include_pending_stock`	BOOLEAN	Includes pending stock
`inventory_management`	BOOLEAN	Tracks inventory
`tracking_support`	BOOLEAN	Supports tracking numbers
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Events Table

The shopify_admin_events table contains Shopify admin events. Client-side storefront events go to the events table. See Client-Side Events for that schema.

Column	Type	Description
`id`	INTEGER	Event ID
`subject_id`	INTEGER	Related entity ID
`subject_type`	STRING	Entity type (for example, Order or Customer)
`verb`	STRING	Event action
`message`	STRING	Event message
`description`	STRING	Event description
`body`	STRING	Event payload (JSON)
`path`	STRING	URL path
`author`	STRING	Event author
`arguments`	REPEATED STRING	Event arguments
`created_at`	TIMESTAMP	Event timestamp
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Abandoned Checkouts Table

The shopify_abandoned_checkouts table contains abandoned checkout information.

Column	Type	Description
`id`	INTEGER	Checkout ID
`token`	STRING	Checkout token
`email`	STRING	Customer email
`created_at`	TIMESTAMP	Checkout creation
`updated_at`	TIMESTAMP	Last update
`completed_at`	TIMESTAMP	Completion time (null if abandoned)
`abandoned_checkout_url`	STRING	Recovery URL
`currency`	STRING	Currency code
`subtotal_price`	FLOAT	Cart subtotal
`total_price`	FLOAT	Cart total
`total_tax`	FLOAT	Tax amount
`total_discounts`	FLOAT	Discounts applied
`billing_address`	RECORD	Billing info
`shipping_address`	RECORD	Shipping info
`line_items`	REPEATED RECORD	Cart items
`discount_codes`	REPEATED RECORD	Discount codes
`buyer_accepts_marketing`	BOOLEAN	Marketing consent
`source_name`	STRING	Source
`note_attributes`	REPEATED RECORD	Custom attributes
`customer`	RECORD	Customer details
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Vendo Metadata Columns

Vendo adds these columns to each Shopify table that it copies (shopify_orders, shopify_customers, shopify_products, shopify_fulfillment_services, shopify_abandoned_checkouts and shopify_admin_events):

Column	Type	Description
`_timestamp`	TIMESTAMP	Source time of the row (`updated_at`, `created_at` or `extracted_at`, set by table)
`_vendo_export_job_id`	STRING	ID of the Vendo export job that wrote the row
`_vendo_export_task`	STRING	Export task that wrote the row
`_vendo_export_batch`	INTEGER	Batch number in the export job
`_vendo_export_delivery_id`	STRING	Delivery ID of the write
`_vendo_exported_at`	TIMESTAMP	When Vendo wrote the row

Identity Tables

The Vendo ingest API writes identify calls to the users table, group calls to the groups table and alias calls to the aliases table.

Users Table

Column	Type	Description
`event_id`	STRING	Unique call ID
`timestamp`	TIMESTAMP	When the call occurred
`sent_at`	TIMESTAMP	When the browser sent the call
`received_at`	TIMESTAMP	When the ingest API received it
`user_id`	STRING	User ID
`anonymous_id`	STRING	Anonymous ID
`email`	STRING	Email
`phone`	STRING	Phone number
`name`	STRING	Full name
`first_name`	STRING	First name
`last_name`	STRING	Last name
`created_at`	TIMESTAMP	User creation time
`source`	STRING	Source identifier
`library`	STRING	Tracking library
`context_ip`	STRING	IP address
`context_user_agent`	STRING	Browser user agent
`context_locale`	STRING	Browser language
`context`	JSON	Full call context
`traits`	JSON	User traits

Groups Table

Column	Type	Description
`event_id`	STRING	Unique call ID
`timestamp`	TIMESTAMP	When the call occurred
`sent_at`	TIMESTAMP	When the browser sent the call
`received_at`	TIMESTAMP	When the ingest API received it
`group_id`	STRING	Group ID
`group_type`	STRING	Group type
`name`	STRING	Group name
`source`	STRING	Source identifier
`library`	STRING	Tracking library
`context_ip`	STRING	IP address
`context_user_agent`	STRING	Browser user agent
`context_locale`	STRING	Browser language
`context`	JSON	Full call context
`traits`	JSON	Group traits

Aliases Table

Column	Type	Description
`event_id`	STRING	Unique call ID
`timestamp`	TIMESTAMP	When the call occurred
`sent_at`	TIMESTAMP	When the browser sent the call
`received_at`	TIMESTAMP	When the ingest API received it
`user_id`	STRING	New user ID
`previous_id`	STRING	Earlier ID that the alias links
`anonymous_id`	STRING	Anonymous ID
`source`	STRING	Source identifier
`library`	STRING	Tracking library
`context_ip`	STRING	IP address
`context_user_agent`	STRING	Browser user agent
`context_locale`	STRING	Browser language
`context`	JSON	Full call context

Table Names in the Vendo Web App

When you add BigQuery as a destination in the Vendo web app, Vendo puts the source ID in front of each table name: {source_id}_{table}. For example, the orders table is {source_id}_orders. The default copied tables are orders, customers, products and events.

User Identification

Customer ID Mapping

Source	BigQuery Column	Description
Shopify Customer ID	`customer.id`	Primary identifier
Email	`customer.email` / `email`	Email address
Phone	`customer.phone`	Phone number

Joining Data

Link events to orders by customer:


SELECT
  e.*,
  o.order_number,
  o.total_price
FROM shopify_admin_events e
JOIN shopify_orders o ON e.subject_id = o.customer.id
WHERE e.subject_type = 'Customer'