Before integrating with Snowflake, you should familiarize yourself with the following domain terminology and dependencies.
Domain terminology
This section provides a brief overview of the data that Inventory Planner works with. To learn more about each type of data, please view the relevant guides in the Help Center.
Products and variants
A product is a record in the system indicating an item that can be sold. A variant is a particular kind of product. A product can have several variants, each with different options.
Options are the attributes of a variant within its product (e.g. Large, White). An option set is a group of options (e.g. Size, Color).
Variants can be set up to have components in one of two ways:
A bundle groups other variants together. Selling a bundle reduces the stock of its components.
An assembly is produced in advance. Producing an assembly increases the stock of the assembly variant and reduces the stock of its components. Selling an assembly only reduces the stock of the assembly, not the components.
A simple variant does not have components.
Grouping products and variants
A vendor is an entity (company or person) that supplies products. Purchase orders are sent to vendors. Vendors are not necessarily visible to consumers.
A brand allows consumers to see a product’s identity in a store’s front end.
A category or product type is a group of different products in a store’s front end, potentially from different vendors and brands. Categories are usually mutually exclusive and are used for open-to-buy planning.
A collection is a set of products presented together in a store’s front end. It may contain products from different categories, and a product can belong to several collections.
Tags are an internal set of labels assigned to a product to enable efficient filtering. IP tags are additional tags assigned in Inventory Planner.
A total is the aggregated data for all products in the system.
Stock
A warehouse is a physical or virtual location which holds products. Each variant has a stock record per warehouse that contains stock of that variant.
On hand (physical) stock describes how many units of a product are present in a warehouse. Available stock describes how many units are available to be sold on new sales orders, calculated by subtracting reserved stock from the on hand quantity. Inventory Planner uses the available stock for planning.
Orders
A sales order is an order used to send stock to a customer. It results in a stock decrease in the fulfillment warehouse.
A stock order is one of the following three kinds of order:
A purchase order, used to obtain stock from a vendor. It has a vendor and a destination warehouse. Purchase orders result in a stock increase in the destination warehouse once the stock is received.
A transfer order, used to move stock between warehouses. It has a source and a destination warehouse. Transfer orders reduce stock in a source warehouse and increase stock in a destination warehouse.
An assembly order, used to produce assembly stock from component products. It has a source and a destination warehouse. Assembly orders decrease component stock in the source warehouse and increase assembly stock in the destination warehouse.
A stock order’s status type defines how Inventory Planner treats that stock order. There are four types: “active”, “closed”, “cancelled” and “draft”.
Stock orders can have several line items, each of which usually corresponds to a variant.
Dependencies
This section discusses the assumptions Inventory Planner makes when it operates the connector.
General
Inventory Planner is able to work with limited data, but some of its features will be restricted.
It makes the following assumptions:
Entity identifiers will not change during the lifetime of the entity. For example, it’s assumed that SKUs and barcodes will not change.
If an identifier does change, Inventory Planner will treat it as a new entity.
Inventory Planner uses an incremental sync to determine what data to pull, looking at the updated_at field. The updated_at field must be updated any time the value of any column changes.
Instead of deleting data rows, any data to be deleted will be soft removed:
Updated with removed=true
Updated updated_at date
The following sections contain notes on specific entities.
Currencies
Inventory Planner fully supports different currencies. It identifies currencies using their 3-letter ISO code. Currency exchange rates are approximate and taken when data is updated.
The account currency is the default currency when not otherwise specified
If a currency code is not recognised, it will be treated as the account currency.
The warehouse currency is used for reports in that warehouse (e.g. revenue, stock retail value)
A combined warehouse may have a different currency to its child warehouses. Currency is converted automatically when the data is aggregated.
A vendor’s currency is the default currency for that vendor’s purchase orders.
Sales and purchase orders have their own sales or purchase order currency, which is converted to a warehouse currency for reporting purposes.
Variants
Your variants’ SKUs and barcodes should be globally unique, as these values are used when searching for variants in Inventory Planner. It’s possible to have non-unique SKUs or barcodes with a unique variant_id, but that can cause issues when detecting replaced variants.
When an old variant is marked with removed=true and a new row is created with a different identifier, but the same globally unique SKU and/or barcode, some data can be copied from the old variant to the new variant.
Note that with an immutable variant identifier (variant_id), the SKU and barcode can be safely changed in the same row without Inventory Planner treating it as a new variant.
Ensure the product-variant relationship is accurate using the product_id and product_title columns. If your account is configured such that each product only has a single variant, you can set product_id=variant_id and product_title=title. In Inventory Planner, the Product and Variant views will be the same.
Non-stock tracked variants (inventory_management=false) will always have zero stock and zero replenishment in Inventory Planner.
The attribute columns (brand, product_type and collection) can be left empty. If empty, these fields will display “unknown” in Inventory Planner. For options and tags, an empty list is used.
cbm and net_weight are only used for reporting and can be left empty.
Variant warehouses
As with all identifiers, the id in the variant warehouse table should be globally unique.
If there’s only one warehouse, the variant warehouse table can be the same as the variant table, with the id matching the variant_id. Variant warehouse fields such as in_stock and warehouse_id will need to be added, and if at a later stage a second warehouse is added, the tables may need to be separated.
If there’s more than one warehouse, you can’t just use the variant_id to identify variants. However, you can use a combination of the variant_id and the warehouse_id.
The available quantity (on hand – allocated) should be used when describing variant stock. New sales orders and outgoing transfers decrease the stock, while purchase orders and incoming transfers increase it.
Vendors and variant vendors
As with all identifiers, the id in the variant vendor table should be globally unique.
If there’s only one vendor, the variant vendor table can be the same as the variant table, with the id matching the variant_id. Variant vendor fields such as cost_price and vendor_id will need to be added, and if at a later stage a second vendor is added, the tables may need to be separated.
If there’s more than one vendor, you can’t just use the variant_id to identify variants. However, you can use a combination of the variant_id and the vendor_id.
Sales orders
If any changes are made to sales orders, it’s important to update the updated_at field even after the orders are created.
Inactive, removed or cancelled sales should be marked either cancelled=true or removed=true in order to be picked up by the incremental sync.
Stock orders
It is important to make sure your stock order statuses match the corresponding stock of line item variants, so that they are accounted for correctly in various reports:
Stock orders with a status type of “Draft” or “Cancelled” are not accounted for anywhere
Purchase orders with a status type of “Active” are accounted for in the replenishment calculation for the destination warehouse (stock added)
Transfers and assembly orders with a status type of “Active” are accounted for in the replenishment calculation for both the source warehouse (stock subtracted) and destination warehouse (stock added)
Stock orders with a status type of “Closed” are not accounted for
Moving a stock order from “Active” to “Closed” should accompany a stock increase in the destination warehouse, and (for transfers and assembly orders) a stock decrease in the source warehouse
Stock orders with a status type of “Cancelled” are not accounted for
Moving a stock order from “Active” or “Draft” to “Cancelled” does not create any corresponding stock increase or decrease
Deleting data
Normally when an entity is removed, Inventory Planner keeps its data to include in its aggregated metrics. If data in Snowflake is hard removed (by deleting the row), Inventory Planner will not be able to detect the deletion during the sync process and will treat it as though it has not been removed.
Note that while it is possible to do a full non-incremental sync by contacting Inventory Planner support, all data will be cleared, including stock history, stockouts history and forecast settings.