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
Useobt_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
Common joins
- Join to
dim_product_variantsonsm_product_variant_keyfor product-variant attributes. - Join to
obt_order_linesonsm_product_variant_keywhen comparing current inventory to recent product sales.
version: 2
models:
- name: obt_inventory_positions
description: >
Current-state inventory visibility across supported inventory sources.
Grain: one row per sm_store_id, source_system, inventory_source_name, inventory_location_id,
and a source-aware inventory-position identity.
Sources: Shopify (primary, quantity-trusted) and Amazon (conservative, read-only).
Critical filter: is_inventory_default_total_eligible = TRUE for safe public totals;
requires is_inventory_source_attributed AND is_inventory_quantity_trusted AND
is_inventory_location_physical AND is_inventory_product_matched AND non-null available quantity.
Key joins: dim_product_variants via sm_product_variant_key (many:1).
columns:
- name: sm_store_id
description: >
SourceMedium store identifier. Unique per tenant.
- name: sm_inventory_position_key
description: >
Stable surrogate key for the public inventory-position grain. Unique per row.
- name: sm_product_variant_key
description: >
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).
- name: sm_product_key
description: >
Best-match SourceMedium product key. Null when unmatched.
- name: source_system
description: >
Inventory source family. Accepted values: Shopify, Amazon.
- name: inventory_source_name
description: >
Specific source adapter name for the inventory row.
Accepted values: shopify_inventory_levels, amazon_active_listings.
- name: inventory_snapshot_at
description: >
Timestamp representing the source snapshot used for the row.
- name: inventory_updated_at
description: >
Latest source update timestamp used for the row.
- name: sku
description: >
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; use inventory_skus_csv in that case.
- name: inventory_skus_csv
description: >
Distinct normalized upstream SKUs for the public inventory position, sorted and comma-separated.
Preserves SKU visibility when multiple source rows collapse into one public position.
- name: inventory_item_id
description: >
Inventory item identifier when the source provides one.
- name: marketplace_id
description: >
Marketplace-specific product identifier, such as Amazon ASIN.
- name: product_variant_id
description: >
Source product-variant identifier when available.
- name: product_id
description: >
Source product identifier when available.
- name: product_title
description: >
Product title from the matched product record.
- name: product_variant_title
description: >
Product-variant title from the matched product record.
- name: product_type
description: >
Product type from the matched product record.
- name: product_vendor
description: >
Product vendor from the matched product record.
- name: product_tags_csv
description: >
Product tags for grouping and downstream rules, comma-separated.
- name: product_collection_handles_csv
description: >
Product collection handles for grouping and downstream rules, comma-separated.
- name: inventory_location_id
description: >
Source location identifier normalized to string.
- name: inventory_location_name
description: >
Source location name or derived virtual location name.
- name: inventory_location_type
description: >
Standardized inventory location type.
Accepted values: warehouse, fulfillment_service, marketplace_virtual, non_physical, unknown.
- name: inventory_fulfillment_method
description: >
Standardized fulfillment method for the inventory row.
Accepted values: merchant, amazon_fulfilled, third_party_fulfilled, unknown.
- name: inventory_quantity_available
description: >
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.
- name: inventory_quantity_on_hand
description: >
Source on-hand quantity when available.
- name: inventory_quantity_committed
description: >
Source committed quantity when available.
- name: inventory_quantity_reserved
description: >
Source reserved quantity when available. Closest standardized analogue to an "on hold"
quantity; exact source semantics can vary by platform.
- name: inventory_quantity_incoming
description: >
Source incoming quantity when available.
- name: inventory_quantity_quality_control
description: >
Source quality-control quantity when available.
- name: inventory_quantity_damaged
description: >
Source damaged quantity when available.
- name: inventory_quantity_safety_stock
description: >
Source safety-stock quantity when available.
- name: inventory_quantity_pending
description: >
Source pending quantity when available.
- name: inventory_quantity_unsellable
description: >
Source unsellable quantity when available.
- name: is_inventory_source_attributed
description: >
True only when the source account or location is explicitly attributable to the tenant.
One of four required flags for is_inventory_default_total_eligible.
- name: is_inventory_quantity_trusted
description: >
True only when the source is approved as a quantity-of-truth source.
Always false for Amazon rows.
- name: is_inventory_location_physical
description: >
True when the row represents physical inventory rather than a virtual or non-physical location.
- name: is_inventory_product_matched
description: >
True when the row matched a SourceMedium product or variant record.
When false, sm_product_variant_key and sm_product_key are null.
- name: is_inventory_default_total_eligible
description: >
Conservative composite flag for safe public inventory totals. True only when
is_inventory_source_attributed AND is_inventory_quantity_trusted AND
is_inventory_location_physical AND is_inventory_product_matched AND
inventory_quantity_available IS NOT NULL. Always false for Amazon rows.