Skip to main content
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?
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. 
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.