Skip to main content
All CollectionsIntegrationsBigQuery
BigQuery Data Mapping & Synchronization
BigQuery Data Mapping & Synchronization

This article will show you in detail how we map and sync BigQuery data to Optiply.

Carla Domingos avatar
Written by Carla Domingos
Updated over 4 months ago

How it works

Database connectors, including BigQuery, empower users to craft tailored SQL queries for mapping data objects into Optiply. The primary goal of these queries is to retrieve essential data and systematically arrange it, ensuring seamless synchronization with Optiply via our integration platform. This process facilitates efficient data flow and optimizes the integration experience.

Query Names and Descriptions

Customers must utilize the following fixed query names when crafting their SQL queries. These names correspond to specific data categories essential for synchronization with Optiply:

  1. Products: Retrieves detailed information about products, including identifiers, names, and specifications.

  2. SalesOrders: Gathers all sales order data, encompassing order IDs, dates, and order details.

  3. SalesOrdersLines: Fetches line item details for each sales order, such as product ID, quantity, and price.

  4. Suppliers: Collects information on suppliers, including supplier IDs, names, and contact details.

  5. SupplierProducts: Obtains details on products supplied by each supplier, including product IDs and supplier-specific data.

  6. BuyOrders: Retrieves purchase order information, covering order IDs, purchase dates, and supplier IDs.

  7. BuyOrdersLines: Fetches line item details for each purchase order, including product ID, quantity, and cost.

  8. ItemDeliveries: Gathers data on the delivery of items, including delivery IDs, dates, and statuses.

  9. ProductCompositions: Retrieves component details and quantities for each product and its parts, essential for inventory and production management.

Query Usage

  • Query: Utilize tailored SQL queries for each of the above names to extract the necessary data from BigQuery. These queries are designed to map data objects directly to Optiply, facilitating data organization and synchronization.

Incremental Data Synchronization

  • Replication Key: To ensure efficient and incremental data synchronization, each query utilizes a specific column name as its replication key. This key is essential for identifying new or updated records since the last synchronization point, thereby optimizing data transfer and minimizing load.

Synchronisation General Board

This is an overview of what data we synchronise.
​

Entity

Sync FROM -> TO

Products

BQ > OP

Supplier

BQ > OP

Supplier Products

BQ > OP

Sell Orders

BQ > OP

Buy Orders

BQ > OP

Receipt Lines

BQ > OP

Product Compositions

BQ > OP

Products

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

name

string max length 255

yes

skuCode

string max length 255 - The SKU (Stock Keeping Unit) code that your own company uses

articleCode

string max length 255 - An article code that your own company uses that is not the SKU or the EAN

price

decimal max 9 integer digits, will round decimals to 2 places

unlimitedStock

boolean - By setting this boolean to TRUE, stock tracking can be disabled. Set this to FALSE for products you want to buy.

yes

stockLevel

integer, can be negative - The current physical level of stock minus already sold quantity. This calculation needs to be done in the Source system. Often called freeStock in other systems.

yes

status

enum - enabled or disabled

eanCode

Barcode - The EAN code used for selling this Product

created_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"(to be used as Replication Key)

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

Suppliers

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

name

string max length 255

yes

emails

string[] - (exemples: ["example@example.com"] or ["example@example.com";"example2@example.com"]

deliveryTime

integer

created_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

Supplier Products

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

name

string max length 255

yes

skuCode

string max length 255

eanCode

string max length 255

articleCode

string max length 255

price

decimal max 9 integer digits, will round decimals to 2 places

minimumPurchaseQuantity

integer - The minimum quantity that you will purchase of this product if you purchase it. This is 1 by default and is only allowed to be greater than or equal to 1.

lotSize

integer - The lot size used when buying this product. E.g. when products are sold in sixpacks, the lotSize is 6. This is 1 by default and is only allowed to be greater than or equal to 1.

productId

long - The remoteId used in Products Table

yes

supplierId

long - The remoteId used in Products Table

yes

preferred

boolean - Indicates this is a preferred supplier - can you be set one per Product

status

enum - enabled or disabled. Use to mimic deletes.

deliveryTime

integer - Supplier lead time per supplier product (days)

created_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

Sell Orders

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

placed

datetime with time zone - date on which the order was placed and is to be sent

yes

totalValue

decimal max 17 integer digits, will round decimals to 2 places

yes

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"


Sell Order Lines

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

quantity

integer

yes

productId

long - The remoteId used in Products Table - ID of the product that's related with this sellOrderLine

yes

sellOrderId

long - The remoteId used in Sell Order Table - ID of the sellOrder that this sellOrderLine is part of

yes

subtotalValue

decimal max 17 integer digits, will round decimals to 2 places

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

Buy Orders

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

completed

datetime with time zone - Used to close the order. Fill when no products are left to be received.

placed

datetime with time zone - This field denotes the date and the time BuyOrder is placed.

yes

totalValue

decimal max 17 integer digits, will round decimals to 2 places

yes

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

reference

BuyOrders.id

integer - this should only be filled for BuyOrders synced from Optiply to Bigquery.

Yes

supplierId

long - The remoteId used in Suppliers Table - ID of the supplierID that's related with this buyOrder


Buy Order Lines

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

quantity

integer

yes

productId

long - The remoteId used in Products Table - ID of the product that's related with this sellOrderLine

yes

BuyOrderId

long - The remoteId used in Buy Order Table - ID of the BuyOrder that this BuyOrderLine belongs to

yes

subtotalValue

decimal max 17 integer digits, will round decimals to 2 places

yes

created_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

reference

BuyOrders.line_items.line_id

integer - this should only be filled for BuyOrders synced from Optiply to Bigquery.

Receipt Lines

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

quantity

integer

yes

buyOrderLineId

integer - ID of the BuyOrderLine that owns this ReceiptLine.

yes

occurred

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

yes

deleted_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"

updated_at

datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

yes

reference

ReceiptLines.id

integer - this should only be filled for ReceiptLines synced from Optiply to Bigquery.

Product Compositions

Optiply

BigQuey

Required

remoteId

The ID in the remote System

yes

composedProductId

long

yes

partProductId

long

yes

partQuantity

integer - Must be at least 1.

yes

created_at

datetime with time zone, format

"%Y-%m-%dT%H:%M:%SZ"

updated_at

datetime with time zone, format

"%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)

yes

deleted_at

datetime with time zone, format

"%Y-%m-%dT%H:%M:%SZ"


​

Buy Orders from Optiply to Bigquery

Receipt Lines from Optiply to Bigquery

Optiply

BigQuey

receiptLines.id

id

receiptLines.occurred

occurred

receiptLines.buyOrderLineId

buyOrderLineId

products.remoteId

product_remoteId

products.skuCode

product_sku

receiptLines.quantity

quantity

Did this answer your question?