Skip to main content

​
Columns

ColumnDescriptionAPI Field Name
item_idPrimary key and item unique identifier.items.id
createdThe timestamp when the item record was created.
modifiedThe timestamp when the item record was last modified.
is_deletedIndicates whether the item record is deleted.
upcThe Universal Product Code (UPC) barcode identifier for the item.
ndcThe National Drug Code (NDC) identifier for the item, used for pharmaceutical products.
skuThe Stock Keeping Unit (SKU) identifier for the item.items.sku
msrpThe Manufacturer’s Suggested Retail Price (MSRP) for the item in dollars. Snowflake displays dollars, API returns cents.items.msrp
manufacturer_noThe manufacturer’s item number or catalog number.items.manufacturer_no
item_full_nameThe full name of the catalog item.items.full_name
duration_unitThe unit of time for the item’s duration (e.g., days, months).items.duration_unit
duration_valueThe numeric value associated with the item’s duration.items.duration_value
weightThe weight of the item.items.weight
is_activeIndicates whether the item is currently active and available.items.is_active
is_human_genericIndicates whether the item is a human generic (non-veterinary) product.
unitsThe unit of measure for the item (e.g., tablets, mL).items.name
etailer_priceThe e-tailer price for the item in dollars. Snowflake displays dollars, API returns cents.items.etailer_price
vendor_stock_availabilityThe stock availability status from the vendor.items.vendor_stock_availability
product_line_idForeign key to the product_line table. The identifier of the product line associated with this item.product_lines.id
brand_nameThe brand name of the product line associated with this item.product_lines.brand_name
manufacturer_idForeign key to the manufacturer table. The identifier of the manufacturer of this item.product_lines.manufacturer_id
manufacturer_nameThe name of the manufacturer of this item.product_lines.manufacturer_name
primary_category_nameThe primary category name for the product line (e.g., Pharmacy, Food, Supplies).product_lines.primary_category_name
secondary_category_namesThe secondary category names for the product line, if applicable.product_lines.secondary_category_names
prescription_requiredIndicates whether a prescription is required to purchase this item.product_lines.prescription_required
refrigeration_requiredIndicates whether this item requires refrigeration for storage or shipping.product_lines.refrigeration_required
descriptionThe product line description for this item.product_lines.description
groupingThe grouping classification for the product line.product_lines.grouping
product_idForeign key to the product table. The identifier of the product associated with this item.products.id
product_nameThe name of the product associated with this item.products.name
default_sigThe default sig (prescription instructions) for the product associated with this item.products.default_sig
last_updatedThe timestamp when the item record was last updated in the source table. This is the most recent timestamp from all related tables.

​
SQL Definition

/*
    This model provides a business-ready, denormalized view of item data
    pre-joined for easy consumption, mirroring the item API.
*/

"
    )
}}

with items as (
    select * from {{ ref('item') }}
)

, products as (
    select * from {{ ref('product') }}
)

, product_lines as (
    select * from {{ ref('product_line') }}
)

select
    -- Item identifiers & timestamps
    items.id as item_id
    , items.created
    , items.modified
    , items.is_deleted
    , items.upc
    , items.ndc
    , items.sku
    , items.denormed_msrp as msrp
    , items.manufacturer_no
    , items.name as item_full_name
    -- Item details
    , items.duration_unit
    , items.duration_value
    , items.weight
    , items.is_active
    , items.is_human_generic
    , items.units
    , items.denormed_etailer_price as etailer_price
    , items.vendor_stock_availability
    -- Product line data
    , product_lines.id as product_line_id
    , product_lines.brand_name
    , product_lines.manufacturer_id
    , items.manufacturer_name
    , product_lines.primary_category_name
    , product_lines.secondary_category_names
    , product_lines.prescription_required
    , product_lines.refrigeration_required
    , product_lines.description
    , product_lines.grouping
    -- Product data
    , items.product_id
    , products.name as product_name
    , products.default_sig
    -- Last updated timestamp
    , greatest(
        coalesce(items.last_updated, '1900-01-01')
        , coalesce(products.last_updated, '1900-01-01')
        , coalesce(product_lines.last_updated, '1900-01-01')
    ) as last_updated
from items
left join products
    on items.product_id = products.id
left join product_lines
    on products.product_line_id = product_lines.id