dim_product_variants

version: 2

models:
  - name: dim_product_variants
    description: >
      Product variant dimension for enriching line items with stable keys and attributes. Grain: One row per sm_product_variant_key. Date field: product_variant_created_at. Critical filters: source_system for platform-specific variant coverage and naming. Key joins: dim_order_lines via sm_product_variant_key (1:many).
    columns:
      - name: inventory_item_id
        description: >
          The unique identifier for the inventory item.

      - name: is_product_gift_card
        description: >
          Whether the product is a gift card.

      - name: marketplace_id
        description: >
          Marketplace-specific identifier for the product variant when sold through third-party marketplaces (e.g., Amazon ASIN). NULL for direct-to-consumer sales; used to track marketplace product listings.

      - name: primary_product_image
        description: >
          Primary product image URL (display).

      - name: product_collection_handles_csv
        description: >
          Unique, human-readable strings for the collections a product belongs to automatically generated from their titles.

      - name: product_collection_titles_csv
        description: >
          The titles of collections the product belongs to. Collections are groupings of products that merchants can create to make their stores easier to browse.

      - name: product_created_at
        description: >
          The date and time when the product was created.

      - name: product_id
        description: >
          A unique identifier for the product generated by the source_system.

      - name: product_tags_array
        description: >
          Tags that the shop owner has attached to the product in an array format.

      - name: product_tags_csv
        description: >
          Tags that the shop owner has attached to the product.

      - name: product_title
        description: >
          The title of the product.

      - name: product_type
        description: >
          A categorization for the product used for filtering and searching for products.

      - name: product_updated_at
        description: >
          The date and time when the product was last modified.

      - name: product_variant_compare_at_price
        description: >
          The original/compare-at price for this product variant (typically MSRP or pre-sale price). Used to show savings/discounts to customers; NULL if no compare-at price is set.

      - name: product_variant_created_at
        description: >
          The date and time when the product variant was created.

      - name: product_variant_id
        description: >
          A unique identifier for the product variant generated by the source system.

      - name: product_variant_price
        description: >
          The current retail price for this product variant (per unit). Used for pricing analysis and comparison with order line prices.

      - name: product_variant_title
        description: >
          The title of the product variant.

      - name: product_variant_updated_at
        description: >
          The date and time when the product variant was last modified.

      - name: product_vendor
        description: >
          The name of the vendor of the product.

      - name: sku
        description: >
          The stock keeping unit (SKU) of the product variant.

      - name: sm_product_key
        description: >
          Stable SourceMedium join key for products to related tables.

      - name: sm_product_variant_key
        description: >
          Stable SourceMedium join key for product variants to related tables.

      - name: sm_store_id
        description: >
          SourceMedium's unique store identifier. For Shopify stores, derived from the myshopify.com domain; for other platforms (Amazon, TikTok Shop, Walmart.com), uses platform-specific identifiers.

      - name: source_system
        description: >
          Originating platform for the record (e.g., Shopify, Amazon, TikTok Shop, Chargebee). Used for platform‑specific behavior and coverage.