What's the status of your inventory ?

WHAT IS THE PURPOSE OF THIS ARTICLE ??

This documentation contains the complete working flow of the Dashboard widget of “ WHAT'S THE STATUS OF YOUR INVENTORY “ in the below manner :

• Covers the entire flow of making new view for the widget which contains UPSERT mechanism.

• It has detailed summary of things happening under the hood when you fetch data from BigQuery till showing it to workspace.

PREVIOUS FLOW :

Previously, We were fetching data from different resources such as SQL, Mongo , Algolia etc and then was showing it at workspace.

CURRENT FLOW :

Now, we are focusing on single resource. So All the data of different sources such as SQL, Mongo, Algolia are synced at bigQuery. And then from bigQuery we are fetching data and then showing it at workspace.

HOW DATA ARE POPULATING AT BIGQUERY ?

SQL (original data) and Mongo Data (original data) are getting synced at bigQuery via airbyte

The new view which we will make will pick data from bigQuery tables which are getting synced via airbyte.

NEW VIEW DETAILS :

TABLE REQUIRED IN THIS VIEW FROM BIGQUERY :

→ InventoryProd.warehouse_inventories

→ MongoProduct.product

SEQUENCE DIAGRAM OF UPSERT CRON IMPLEMENTATION :

Open

HOW TO IMPLEMENT UPSERT(Update & Insert) MECHANISM FOR THIS WIDGET FROM BIGQUERY DATA ???

STEP 1: A cron will be made, which will run at every 10 mins

CASE 1 : For the first entry at new view table, we will fetch MIN updatedAt timestamp.

CASE 2 : To know which data we need to pick from InventoryProd.warehouse_inventories & MongoProduct.product we need to fetch the MAX updatedAt timestamp of new view table (which means last sync latest timestamp).

Condition by which we will able to identify which query needs to be executed will be :

If MAX updatedAt will be null, then we will fetch MIN updatedAt. If not we will fetch MAX updatedAt

STEP 2 : MAX updatedAt (fetched from the above query) , OFFSET (which initially will be set as 0 at starting of the cron) & LIMIT (Value will be 2000 and it will be set at Config - different for staging and prod)

All the above three parameters will be sent to a taskqueue

STEP 3 : Under the TaskQueue Listener, We will fetch the data from the request :

PARAMETERS ARE : MAX updatedAt , OFFSET & LIMIT

and then will pass it to the query below :

From the data we have received above :

We will make a composite unique key via code that will be a combination of esin and warehouse

We will make composite unique key from the data we have received in the listener.

Based on this we will put condition that :

→ If we will find the composite key in the our new view, we will UPDATE the record.

→ If we don't find the composite key in the our new view, we will INSERT the record.

A condition needs to be checked after sending the data in the taskqueue 2 :

NEW VIEW TABLE COLUMNS TO BE USED :

TABLE NAME : DashboardView.report_warehouse_inventory_status

TABLE COLUMN :