Skip to main content
All CollectionsIntegrationsSnowflake integration
Example database schema for the Snowflake connector
Example database schema for the Snowflake connector
S
Written by Sara Jaffer
Updated over 9 months ago

Here is an example database schema with all required and optional fields.

CREATE OR REPLACE TABLE WAREHOUSES (
    WAREHOUSE_ID VARCHAR(256) NOT NULL,
    DISPLAY_NAME VARCHAR(256) NOT NULL,
    BILLING_ADDRESS VARCHAR(256),
    SHIPPING_ADDRESS VARCHAR(256),
    CURRENCY CHAR(3),
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (WAREHOUSE_ID)
);
CREATE OR REPLACE TABLE VENDORS (
    VENDOR_ID VARCHAR(256) NOT NULL,
    DISPLAY_NAME VARCHAR(256) NOT NULL,
    CURRENCY CHAR(3) NOT NULL,
    DISCOUNT FLOAT,
    TAX FLOAT,
    EMAIL VARCHAR(256),
    VENDOR_ADDRESS VARCHAR(256),
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (VENDOR_ID)
);
CREATE OR REPLACE TABLE VARIANTS (
    VARIANT_ID VARCHAR(256) NOT NULL,
    CREATED_AT TIMESTAMP_NTZ NOT NULL,
    TYPE VARCHAR(256),
    SKU VARCHAR(256) NOT NULL,
    TITLE VARCHAR(256) NOT NULL,
    BARCODE VARCHAR(256) NOT NULL,
    IMAGE VARCHAR(256),
    HANDLE VARCHAR(256),
    INVENTORY_MANAGEMENT BOOLEAN,
    PUBLISHED BOOLEAN,
    PUBLISHED_AT TIMESTAMP_NTZ,
    PRODUCT_ID VARCHAR(256) NOT NULL,
    PRODUCT_TITLE VARCHAR(256),
    BRAND VARCHAR(256),
    CATEGORY VARCHAR(256),
    TAGS VARCHAR(256),
    OPTIONS VARCHAR(256),
    COLLECTION VARCHAR(256),
    PRICE FLOAT NOT NULL,
    REGULAR_PRICE FLOAT,
    CBM FLOAT,
    NET_WEIGHT FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (VARIANT_ID)
);
CREATE OR REPLACE TABLE VARIANT_WAREHOUSE (
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    WAREHOUSE_ID VARCHAR(256) NOT NULL,
    IN_STOCK FLOAT NOT NULL,
    GROSS_WEIGHT FLOAT,
    MIN_STOCK FLOAT,
    MAX_STOCK FLOAT,
    MAX_ORDER FLOAT,
    TRANSFER_UOM FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE VARIANT_VENDOR (
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    VENDOR_ID VARCHAR(256) NOT NULL,
    COST_PRICE FLOAT,
    LANDING_COST_PRICE FLOAT,
    VENDOR_REFERENCE VARCHAR(256),
    VENDOR_NAME VARCHAR(256),
    MOQ FLOAT,
    UOM FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE VARIANT_COMPONENT (
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    COMPONENT_VARIANT_ID VARCHAR(256) NOT NULL,
    QUANTITY FLOAT NOT NULL,
    PRICE FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE SALES_ORDERS (
    ORDER_ID VARCHAR(256) NOT NULL,
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    REFERENCE VARCHAR(256),
    REFERENCE2 VARCHAR(256),
    CREATED_AT TIMESTAMP_NTZ NOT NULL,
    WAREHOUSE VARCHAR(256) NOT NULL,
    CURRENCY CHAR(3) NOT NULL,
    QUANTITY FLOAT NOT NULL,
    PRICE FLOAT NOT NULL,
    DISCOUNT FLOAT,
    TAX FLOAT,
    TAX_INCLUDED BOOLEAN,
    SHIPPING FLOAT,
    SHIPPING_TAX FLOAT,
    CANCELLED BOOLEAN,
    STATUS VARCHAR(256),
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ORDER_ID)
);
CREATE OR REPLACE TABLE PURCHASE_ORDERS (
    ID VARCHAR(256) NOT NULL,
    TYPE VARCHAR(256),
    REFERENCE VARCHAR(256) NOT NULL,
    REFERENCE2 VARCHAR(256),
    STATUS VARCHAR(256) NOT NULL,
    VENDOR VARCHAR(256) NOT NULL,
    WAREHOUSE VARCHAR(256) NOT NULL,
    SOURCE_WAREHOUSE VARCHAR(256),
    CURRENCY CHAR(3) NOT NULL,
    CREATED_DATE TIMESTAMP_NTZ NOT NULL,
    EXPECTED_DATE TIMESTAMP_NTZ NOT NULL,
    LAST_MODIFIED TIMESTAMP_NTZ,
    SHIPMENT_DATE TIMESTAMP_NTZ,
    EMAIL VARCHAR(256),
    VENDOR_ADDRESS VARCHAR(256),
    SHIPPING_ADDRESS VARCHAR(256),
    BILLING_ADDRESS VARCHAR(256),
    SHIPMENT_METHOD VARCHAR(256),
    PAYMENT_TERMS VARCHAR(256),
    NOTES VARCHAR(256),
    ATTN VARCHAR(256),
    TAX_INCLUDED BOOLEAN,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL,
    LINE_ITEM_ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    SKU VARCHAR(256) NOT NULL,
    BARCODE VARCHAR(256),
    TITLE VARCHAR(256),
    REPLENISHMENT FLOAT NOT NULL,
    RECEIVED FLOAT NOT NULL,
    RECEIVED_DATE TIMESTAMP_NTZ,
    COST_PRICE FLOAT NOT NULL,
    TAX FLOAT,
    DISCOUNT FLOAT,
    REMOVED BOOLEAN NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE STOCK_ORDER_STATUSES (
    NAME VARCHAR(256) NOT NULL,
    STATUS_TYPE VARCHAR(256) NOT NULL,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (NAME)
);

CREATE OR REPLACE TABLE WAREHOUSES (
    WAREHOUSE_ID VARCHAR(256) NOT NULL,
    DISPLAY_NAME VARCHAR(256) NOT NULL,
    BILLING_ADDRESS VARCHAR(256),
    SHIPPING_ADDRESS VARCHAR(256),
    CURRENCY CHAR(3),
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (WAREHOUSE_ID)
);
CREATE OR REPLACE TABLE VENDORS (
    VENDOR_ID VARCHAR(256) NOT NULL,
    DISPLAY_NAME VARCHAR(256) NOT NULL,
    CURRENCY CHAR(3) NOT NULL,
    DISCOUNT FLOAT,
    TAX FLOAT,
    EMAIL VARCHAR(256),
    VENDOR_ADDRESS VARCHAR(256),
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (VENDOR_ID)
);
CREATE OR REPLACE TABLE VARIANTS (
    VARIANT_ID VARCHAR(256) NOT NULL,
    CREATED_AT TIMESTAMP_NTZ NOT NULL,
    TYPE VARCHAR(256),
    SKU VARCHAR(256) NOT NULL,
    TITLE VARCHAR(256) NOT NULL,
    BARCODE VARCHAR(256) NOT NULL,
    IMAGE VARCHAR(256),
    HANDLE VARCHAR(256),
    INVENTORY_MANAGEMENT BOOLEAN,
    PUBLISHED BOOLEAN,
    PUBLISHED_AT TIMESTAMP_NTZ,
    PRODUCT_ID VARCHAR(256) NOT NULL,
    PRODUCT_TITLE VARCHAR(256),
    BRAND VARCHAR(256),
    CATEGORY VARCHAR(256),
    TAGS VARCHAR(256),
    OPTIONS VARCHAR(256),
    COLLECTION VARCHAR(256),
    PRICE FLOAT NOT NULL,
    REGULAR_PRICE FLOAT,
    CBM FLOAT,
    NET_WEIGHT FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (VARIANT_ID)
);
CREATE OR REPLACE TABLE VARIANT_WAREHOUSE (
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    WAREHOUSE_ID VARCHAR(256) NOT NULL,
    IN_STOCK FLOAT NOT NULL,
    GROSS_WEIGHT FLOAT,
    MIN_STOCK FLOAT,
    MAX_STOCK FLOAT,
    MAX_ORDER FLOAT,
    TRANSFER_UOM FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE VARIANT_VENDOR (
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    VENDOR_ID VARCHAR(256) NOT NULL,
    COST_PRICE FLOAT,
    LANDING_COST_PRICE FLOAT,
    VENDOR_REFERENCE VARCHAR(256),
    VENDOR_NAME VARCHAR(256),
    MOQ FLOAT,
    UOM FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE VARIANT_COMPONENT (
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    COMPONENT_VARIANT_ID VARCHAR(256) NOT NULL,
    QUANTITY FLOAT NOT NULL,
    PRICE FLOAT,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE SALES_ORDERS (
    ORDER_ID VARCHAR(256) NOT NULL,
    ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    REFERENCE VARCHAR(256),
    REFERENCE2 VARCHAR(256),
    CREATED_AT TIMESTAMP_NTZ NOT NULL,
    WAREHOUSE VARCHAR(256) NOT NULL,
    CURRENCY CHAR(3) NOT NULL,
    QUANTITY FLOAT NOT NULL,
    PRICE FLOAT NOT NULL,
    DISCOUNT FLOAT,
    TAX FLOAT,
    TAX_INCLUDED BOOLEAN,
    SHIPPING FLOAT,
    SHIPPING_TAX FLOAT,
    CANCELLED BOOLEAN,
    STATUS VARCHAR(256),
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (ORDER_ID)
);
CREATE OR REPLACE TABLE PURCHASE_ORDERS (
    ID VARCHAR(256) NOT NULL,
    TYPE VARCHAR(256),
    REFERENCE VARCHAR(256) NOT NULL,
    REFERENCE2 VARCHAR(256),
    STATUS VARCHAR(256) NOT NULL,
    VENDOR VARCHAR(256) NOT NULL,
    WAREHOUSE VARCHAR(256) NOT NULL,
    SOURCE_WAREHOUSE VARCHAR(256),
    CURRENCY CHAR(3) NOT NULL,
    CREATED_DATE TIMESTAMP_NTZ NOT NULL,
    EXPECTED_DATE TIMESTAMP_NTZ NOT NULL,
    LAST_MODIFIED TIMESTAMP_NTZ,
    SHIPMENT_DATE TIMESTAMP_NTZ,
    EMAIL VARCHAR(256),
    VENDOR_ADDRESS VARCHAR(256),
    SHIPPING_ADDRESS VARCHAR(256),
    BILLING_ADDRESS VARCHAR(256),
    SHIPMENT_METHOD VARCHAR(256),
    PAYMENT_TERMS VARCHAR(256),
    NOTES VARCHAR(256),
    ATTN VARCHAR(256),
    TAX_INCLUDED BOOLEAN,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL,
    LINE_ITEM_ID VARCHAR(256) NOT NULL,
    VARIANT_ID VARCHAR(256) NOT NULL,
    SKU VARCHAR(256) NOT NULL,
    BARCODE VARCHAR(256),
    TITLE VARCHAR(256),
    REPLENISHMENT FLOAT NOT NULL,
    RECEIVED FLOAT NOT NULL,
    RECEIVED_DATE TIMESTAMP_NTZ,
    COST_PRICE FLOAT NOT NULL,
    TAX FLOAT,
    DISCOUNT FLOAT,
    REMOVED BOOLEAN NOT NULL, constraint pkey1 primary key (ID)
);
CREATE OR REPLACE TABLE STOCK_ORDER_STATUSES (
    NAME VARCHAR(256) NOT NULL,
    STATUS_TYPE VARCHAR(256) NOT NULL,
    REMOVED BOOLEAN NOT NULL,
    UPDATED_AT TIMESTAMP_NTZ NOT NULL, constraint pkey1 primary key (NAME)
);
Did this answer your question?