obt_inventory_positions

inventory_quantity_available is the recommended field for most inventory analyses. Negative values indicate oversold or backordered positions and are preserved as-is from the source.

Amazon rows always have is_inventory_quantity_trusted = FALSE. Never sum Amazon quantities for total inventory reports. Use is_inventory_default_total_eligible = TRUE as a conservative starting filter for any inventory totals.

When to use this table

Use obt_inventory_positions when you need current inventory by product, variant, SKU, location, source system, or fulfillment method. It is a current-state table, not an inventory movement ledger. Common questions this table can answer:

Which products are out of stock or oversold?
How many units are available across trusted physical locations?
Which SKUs exist in Shopify versus Amazon inventory sources?
Which rows could not be matched back to SourceMedium product or variant records?

Recommended filters

For most inventory totals, start with:

WHERE is_inventory_default_total_eligible = TRUE

This filter keeps rows that are attributed to the store, quantity-trusted, physical, product-matched, and have a non-null available quantity. It excludes Amazon rows by design.

Common joins

Join to dim_product_variants on sm_product_variant_key for product-variant attributes.
Join to obt_order_lines on sm_product_variant_key when comparing current inventory to recent product sales.

For ready-to-run templates, see the Products SQL Query Library.

Columns

Column	Type	Description
`sm_inventory_position_key`	STRING	Stable surrogate key for the public inventory-position grain. Unique per row.
`sm_product_key`	STRING	Best-match SourceMedium product key. Null when unmatched.
`sm_product_variant_key`	STRING	Best-match SourceMedium product-variant key. Null when the inventory row could not be matched to a product record. Key joins: dim_product_variants (many:1).
`sm_store_id`	STRING	SourceMedium store identifier. Unique per workspace.
`inventory_fulfillment_method`	STRING	Standardized fulfillment method for the inventory row. Accepted values: merchant, amazon_fulfilled, third_party_fulfilled, unknown.
`inventory_item_id`	STRING	Inventory item identifier when the source provides one.
`inventory_location_id`	STRING	Source location identifier normalized to string.
`inventory_location_name`	STRING	Source location name or derived virtual location name.
`inventory_location_type`	STRING	Standardized inventory location type. Derived from the Shopify v3 location schema (shipsinventory, isfulfillmentservice flags). Rows from classic-API Shopify workspaces without v3 location data will have type ‘unknown’, which also means is_inventory_location_physical will be false for all their rows.
`inventory_quantity_available`	INT64	Default business quantity to sum. Signed values are preserved as-is from the source; negative values can represent oversold or backordered positions and are not clamped to zero.
`inventory_quantity_committed`	INT64	Source committed quantity when available.
`inventory_quantity_damaged`	INT64	Source damaged quantity when available.
`inventory_quantity_incoming`	INT64	Source incoming quantity when available.
`inventory_quantity_on_hand`	INT64	Source on-hand quantity when available.
`inventory_quantity_pending`	INT64	Source pending quantity when available.
`inventory_quantity_quality_control`	INT64	Source quality-control quantity when available.
`inventory_quantity_reserved`	INT64	Source reserved quantity when available. This is the closest standardized analogue to an “on hold” quantity in v1, but exact source semantics can vary by platform.
`inventory_quantity_safety_stock`	INT64	Source safety-stock quantity when available.
`inventory_quantity_unsellable`	INT64	Source unsellable quantity when available.
`inventory_skus_csv`	STRING	Distinct normalized upstream SKUs for the public inventory position, sorted and comma-separated. This preserves SKU visibility when multiple source rows collapse into one public position.
`inventory_snapshot_at`	TIMESTAMP	Timestamp representing the source snapshot used for the row.
`inventory_source_name`	STRING	Specific source adapter name for the inventory row. Accepted values: shopify_inventory_levels, amazon_active_listings.
`inventory_updated_at`	TIMESTAMP	Latest source update timestamp used for the row.
`is_inventory_default_total_eligible`	BOOL	Conservative default flag for safe public inventory totals. Requires is_inventory_location_physical, is_inventory_source_attributed, is_inventory_quantity_trusted, is_inventory_product_matched, and a non-null available quantity. Use is_inventory_quantity_eligible instead when location classification is unavailable (e.g. classic-API Shopify workspaces without v3 location schema).
`is_inventory_location_physical`	BOOL	True when inventory_location_type is warehouse or fulfillment_service. Requires v3 location schema (shipsinventory / isfulfillmentservice columns). Always false for classic-API Shopify workspaces that lack v3 location data, use is_inventory_quantity_eligible rather than is_inventory_default_total_eligible for those workspaces.
`is_inventory_product_matched`	BOOL	True when the row matched a SourceMedium product or variant record. When false, sm_product_variant_key and sm_product_key are null.
`is_inventory_quantity_eligible`	BOOL	True when the quantity data is trustworthy for aggregation, regardless of whether the location type is classified. Relaxes the is_inventory_location_physical requirement from is_inventory_default_total_eligible, making classic-API Shopify workspaces (which lack v3 location schema) usable for quantity totals. Amazon is always false here because is_inventory_quantity_trusted is false for Amazon active listings. Use this flag when location classification is unavailable but quantity data is sound. Use is_inventory_default_total_eligible when you additionally require a confirmed physical or fulfillment-service location.
`is_inventory_quantity_trusted`	BOOL	True only when the source is approved as a quantity-of-truth source. Always false for Amazon rows.
`is_inventory_source_attributed`	BOOL	True only when the source account or location is explicitly attributable to the workspace. One of four required flags for is_inventory_default_total_eligible.
`marketplace_id`	STRING	Marketplace-specific product identifier, such as Amazon ASIN.
`product_collection_handles_csv`	STRING	Product collection handles for grouping and downstream rules.
`product_id`	STRING	Source product identifier when available.
`product_tags_csv`	STRING	Product tags for grouping and downstream rules.
`product_title`	STRING	Product title from the matched product record.
`product_type`	STRING	Product type from the matched product record.
`product_variant_id`	STRING	Source product-variant identifier when available.
`product_variant_title`	STRING	Product-variant title from the matched product record.
`product_vendor`	STRING	Product vendor from the matched product record.
`sku`	STRING	Canonical normalized SKU when the public inventory position resolves to exactly one distinct upstream SKU after normalization. Null when multiple upstream SKUs collapse into the same public inventory position.
`source_system`	STRING	Inventory source family.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

obt_inventory_positions

When to use this table

Recommended filters

Common joins

Columns

​When to use this table

​Recommended filters

​Common joins

​Columns

When to use this table

Recommended filters

Common joins

Columns