🔗 BigQuery: Data Mapping & Synchronisation

Database connectors, including BigQuery, empower you to craft tailored SQL queries to map your internal data objects directly into Optiply. The primary goal is to retrieve essential data and arrange it systematically, ensuring seamless synchronisation via our integration platform.

🔑 Core Concepts

1. Fixed Query Names

To ensure Optiply recognises your data, you must utilise fixed query names when crafting your SQL queries in the connector. Do not deviate from these names:

Products: Item details (names, SKUs, identifiers).
SalesOrders: Order headers (IDs, dates, totals).
SalesOrdersLines: Order line items (product ID, quantity).
Suppliers: Supplier contact info.
SupplierProducts: Links between products and suppliers (prices, codes).
BuyOrders: Purchase order headers.
BuyOrdersLines: Purchase order lines.
ItemDeliveries: Goods receipt data (Receipt Lines).
ProductCompositions: Bills of materials/bundles.

2. Incremental Data Synchronisation (Replication Key)

To optimise performance, Optiply uses Incremental Sync.

The Logic: Each query uses a specific column (usually updated_at) as a Replication Key.
The Benefit: Optiply tracks the last synced timestamp and only pulls records that have changed since then ({replication_key} >= {last_value}). This minimises load and speeds up data transfer.

⏱️ Synchronisation General Board

Entity	Direction	Frequency
Products	BigQuery → Optiply	Every 60 min
Suppliers	BigQuery → Optiply	Every 60 min
Supplier Products	BigQuery → Optiply	Every 60 min
Sell Orders	BigQuery → Optiply	Every 60 min
Buy Orders	BigQuery → Optiply	Every 60 min
Receipt Lines (Item Deliveries)	BigQuery → Optiply	Every 60 min
Product Compositions	BigQuery → Optiply	Every 60 min

🗺️ Import Mapping: BigQuery → Optiply

Below are the field requirements and SQL templates for each entity.

1. Products

Query Name: Products

Optiply Field	Type	Required	Notes
remoteId	ID	Yes	Unique ID in the remote system.
name	String	Yes	Max 255 chars.
skuCode	String	-	Max 255 chars - The SKU (Stock Keeping Unit) code that your own company uses
articleCode	String	-	Max 255 chars - An article code that your own company uses that is not the SKU or the EAN
price	Decimal	-	Max 9 digits, will round to 2 decimals.
unlimitedStock	Boolean	Yes	`TRUE` = Disable stock tracking. `FALSE` = Track stock.
stockLevel	Integer	Yes	Can be negative - The current physical level of stock minus the already sold quantity. This calculation needs to be done in the Source system. Often called freeStock in other systems.
status	Enum	-	`enabled` or `disabled`.
eanCode	String	-	Barcode - The EAN code used for selling this Product
notBeingBought	Boolean	-	`TRUE` = Advice will always be 0.
created_at	datetime with time zone		Replication Key (Format: "%Y-%m-%dT%H:%M:%SZ").
updated_at	datetime with time zone	Yes	Replication Key (Format: `%Y-%m-%dT%H:%M:%SZ`).
deleted_at	datetime with time zone		Replication Key (Format:"%Y-%m-%dT%H:%M:%SZ")

SQL Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.sku as skuCode,

yt.Code as articleCode,

yt.price as price,

yt.managedStock as unlimitedStock,

yt.stock as stockLevel,

yt.status as status,

yt.EAN as eanCode,

yt.created_at as created_at,

yt.updated_at as updated_at,

yt.deleted as deleted_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

2. Suppliers

Query Name: Suppliers

Optiply Field	Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
name	String	Yes	Max 255 chars.
emails	String[]	-	Format: `["a@test.com"]` or `["a@test.com";"b@test.com"]`.
deliveryTime	Integer	-	General supplier lead time.
created_at	datetime with time zone		Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone	Yes	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
deleted_at	datetime with time zone		Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`

SQL Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.email as emails,

yt.deliveryTime as deliveryTime,

yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

3. Supplier Products

Query Name: SupplierProducts

Optiply Field	Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
name	string	Yes	Max 255 chars.
skuCode	string		Max 255 chars.
eanCode	string		Max 255 chars.
articleCode	string		Max 255 chars.
price	Decimal		Purchase price. Max 9 digits, will round to 2 decimals.
minimumPurchaseQuantity	Integer		Default 1 (e.g., set to 6 for six-packs).
lotSize	Integer		Default 1 (e.g., set to 6 for six-packs)
productId	Long	Yes	Must match `remoteId` in Products table.
supplierId	Long	Yes	Must match `remoteId` in Suppliers table.
preferred	boolean		Indicates this is a preferred supplier
status	enum		enabled or disabled
deliveryTime	integer		Supplier lead time per supplier product (days)
created_at	datetime with time zone		Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone	Yes	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
deleted_at	datetime with time zone		Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`

SQL Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.sku as skuCode,

yt.EAN as eanCode,

yt.Code as articleCode,

yt.price as price,

yt.moq as minimumPurchaseQuantity,

yt.lot as lotSize,

yt.pId as productId,

yt.sId as supplierId,

yt.preferred as preferred,

yt.status as status,

yt.deliveryTime as deliveryTime,

yt.created_at as created_at,

yt.updated_at as updated_at,

yt.deleted as deleted_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

4. Sell Orders

Query Name: SalesOrders

Optiply Field	Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
placed	DateTime	Yes	Date order was placed.
totalValue	Decimal	Yes	Total order value.
updated_at	datetime with time zone	Yes	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
deleted_at	datetime with time zone		Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`

SQL Example:

SELECT yt.id as remote_id,

yt.created as placed,

yt.total as totalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

5. Sell Order Lines

Query Name: SalesOrdersLines

Optiply	Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
sellOrderId	Long	Yes	Must match `remoteId` in SalesOrders table.
productId	Long	Yes	Must match `remoteId` in Products table.
quantity	Integer	Yes	-
subtotalValue	Decimal	Yes	Purchase price. Max 17 digits, will round to 2 decimals.
updated_at	datetime with time zone	Yes	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
deleted_at	datetime with time zone		Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`

SQL Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.productId as productId,

yt.sellOrderId as sellOrderId,

yt.subtotalValue as subtotalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

6. Buy Orders

Retrieves purchase order information.

Optiply Field	BigQuery Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
completed	datetime with time zone	-	Used to close order when no products left to receive.
placed	datetime with time zone	Yes	Date/Time BuyOrder was placed.
totalValue	Decimal	Yes	Max 17 digits, will round to 2 decimals.
updated_at	datetime with time zone	Yes	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
deleted_at	datetime with time zone	-	format `"%Y-%m-%dT%H:%M:%SZ"`
reference	Integer	Yes	(Export Only) `BuyOrders.id`
supplierId	Long	Yes	Matches `remoteId` in Suppliers.

SQL Example:

SELECT yt.id as remote_id,

yt.completed as completed,

yt.created as placed,

yt.total as totalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

7. Buy Order Lines

Fetches line item details for each purchase order.

Optiply Field	BigQuery Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
quantity	Integer	Yes	-
productId	Long	Yes	Matches `remoteId` in Products.
BuyOrderId	Long	Yes	Matches `remoteId` in BuyOrders.
subtotalValue	Decimal	Yes	Max 17 digits, will round to 2 decimals.
created_at	datetime with time zone	-	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone	Yes	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
deleted_at	datetime with time zone	-	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
reference	Integer	Yes	(Export Only) `BuyOrders.line_items.line_id`.

SQL Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.buyOrderLineId as buyOrderLineId,

yt.occurred as occurred,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

8. Receipt Lines (Item Deliveries)

Gathers data on the delivery of items.

Optiply Field	BigQuery Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
quantity	Integer	Yes	-
buyOrderLineId	Integer	Yes	ID of the BuyOrderLine owning this receipt.
occurred	datetime with time zone	Yes	Format `%Y-%m-%dT%H:%M:%SZ`.
deleted_at	datetime with time zone	-	Format `%Y-%m-%dT%H:%M:%SZ`.
updated_at	datetime with time zone	Yes	Replication Key, Format `%Y-%m-%dT%H:%M:%SZ`.
reference	Integer	Yes	(Export Only) `ReceiptLines.id`.

SQL Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.buyOrderLineId as buyOrderLineId,

yt.occurred as occurred,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

9. Product Compositions

Retrieves component details and quantities (BOM).

Optiply Field	BigQuery Type	Required	Notes
remoteId	ID	Yes	The ID in the remote System
composedProductId	Long	Yes	-
partProductId	Long	Yes	-
partQuantity	Integer	Yes	Must be at least 1.
created_at	datetime with time zone	-	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone	Yes	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`
deleted_at	datetime with time zone	-	Replication Key, format `"%Y-%m-%dT%H:%M:%SZ"`

SQL Example:

SELECT yt.id as remote_id,

yt.composedProductId as composedProductId,

yt.partProductId as partProductId,

yt.qty as partQuantity,

yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

📤 Export Mapping: Optiply → BigQuery

For outbound data, Optiply will automatically create tables in your BigQuery project.

1. Buy Orders (Export)

Table Name: BuyOrders

BigQuery Column		Optiply Source
id		buyOrder.id
placed		buyOrder.placed
delivery_date		buyOrder.expectedDeliveryDate
supplier_remoteId		supplier.remoteId
supplier_name		supplier.name
line_items	line_id	buyOrderLines	buyOrderLine.id
(JSON with the line attributes)	product_remoteId		products.remoteId
	product_sku		products.skuCode
	quantity		buyOrderLine.quantity

Example:

2. Receipt Lines (Export)

Table Name: ReceiptLines

BigQuery Column	Optiply Source	Notes
id	receiptLines.id	-
occurred	receiptLines.occurred	Date of receipt.
buyOrderLineId	receiptLines.buyOrderLineId	-
product_remoteId	products.remoteId
product_sku	products.skuCode	-
quantity	receiptLines.quantity	-

Example:

❓ Frequently Asked Questions (FAQs)

Can I rename the queries (e.g., "MyProducts")?

No. The system looks for the exact strings "Products", "SalesOrders", etc. If you rename them, the sync will fail.

What if I don't have an `updated_at` column?

You must have a way to identify new/changed records for incremental sync to work. If your table lacks this, you may need to create a view in BigQuery that adds a timestamp or use a different incremental logic if supported (consult support).

Do I need to create the export tables manually?

No. When Optiply pushes Buy Orders or Receipt Lines, it will automatically generate the BuyOrders and ReceiptLines tables in your dataset if they do not exist.

BigQuery Data Mapping & Synchronisation

🔗 BigQuery: Data Mapping & Synchronisation

🔑 Core Concepts

1. Fixed Query Names

2. Incremental Data Synchronisation (Replication Key)

⏱️ Synchronisation General Board

🗺️ Import Mapping: BigQuery → Optiply

1. Products

2. Suppliers

3. Supplier Products

4. Sell Orders

5. Sell Order Lines

6. Buy Orders

7. Buy Order Lines

8. Receipt Lines (Item Deliveries)

9. Product Compositions

📤 Export Mapping: Optiply → BigQuery

1. Buy Orders (Export)

2. Receipt Lines (Export)

❓ Frequently Asked Questions (FAQs)

Can I rename the queries (e.g., "MyProducts")?

What if I don't have an updated_at column?

Do I need to create the export tables manually?

What if I don't have an `updated_at` column?