Write-back 101: Use cases and data models Part 2

Associated comments

Commentary becomes increasingly more valuable when we associate it with data. What we are creating in this example is a table with a comment associated with a product. Users will be able to update the comment. Since write-back in Astrato uses insert, we will have to create a view that loads only the latest comment per product id. Let’s start with creating the table.

Step 1: Setting up the Table

Begin by creating a table in Snowflake named PRODUCT_COMMENTS_WRITE_BACK in Snowflake:

CREATE OR REPLACE TABLE PRODUCT_COMMENTS_WRITE_BACK (
    ID INT IDENTITY(1,1),
  PRODUCT_ID INT,
    COMMENT VARCHAR,
    UPDATED_BY VARCHAR(320),
  UPDATED_AT timestamp default current_timestamp()
);

Column	Description
ID	It’s a good practice to add an ID to your write-back. The IDENTITY(1,1) will start at 1 and auto increment the ID with 1. When doing the write-back the ID will be excluded and Snowflake will insert the next ID in the series. In this example we will use this when creating the View to show the latest comment per product.
PRODUCT_ID	PRODUCT_ID will be the key we use to associate the comments with a product.
COMMENT	This is the column where we store the text content of the comment.
UPDATED_BY	The user id of the user who updated the comments goes into this column.
UPDATED_AT	UPDATED_AT is using the current_timestamp() function as its default value . When doing the write-back the UPDATED_AT will be excluded and Snowflake will insert the current timestamp for us.

If you want to follow along, please use an existing table with data and a key. The key is important, but it doesn’t have to be a PRODUCT_ID, any key or id will work. Next step is to create the PRODUCT_COMMENTS view that loads only the latest comment per product.

-- Create the PRODUCT_COMMENTS view
CREATE OR REPLACE VIEW PRODUCT_COMMENTS AS
WITH LATEST_METADATA AS (
  SELECT MAX(ID) AS MAX_ID
  FROM PRODUCT_COMMENTS_WRITE_BACK
  GROUP BY PRODUCT_ID
)
SELECT 
  PRODUCT_COMMENTS_WRITE_BACK.PRODUCT_ID,
  PRODUCT_COMMENTS_WRITE_BACK.COMMENT,
  PRODUCT_COMMENTS_WRITE_BACK.UPDATED_BY,
  PRODUCT_COMMENTS_WRITE_BACK.UPDATED_AT
FROM PRODUCT_COMMENTS_WRITE_BACK
INNER JOIN LATEST_METADATA ON LATEST_METADATA.MAX_ID = PRODUCT_COMMENTS_WRITE_BACK.ID;

It might look complicated, but let's break it down.

WITH LATEST_METADATA AS (
  SELECT MAX(ID) AS MAX_ID
  FROM PRODUCT_COMMENTS_WRITE_BACK
  GROUP BY PRODUCT_ID
)

This first part of the query selects the MAX(ID) of the comments and we group it by PRODUCT_ID. The result of this sub-select will be a row for each PRODUCT_ID that has one or several comments, with the latest comments id as the MAX_ID column. Since we are using the IDENTITY(1,1) we guarantee that we get the latest comment per PRODUCT_ID.

INNER JOIN LATEST_METADATA ON LATEST_METADATA.MAX_ID = PRODUCT_COMMENTS_WRITE_BACK.ID;

This part of the query joins the result of the sub-select back on to the write-back table.

SELECT 
  PRODUCT_COMMENTS_WRITE_BACK.PRODUCT_ID,
  PRODUCT_COMMENTS_WRITE_BACK.COMMENT,
  PRODUCT_COMMENTS_WRITE_BACK.UPDATED_BY,
  PRODUCT_COMMENTS_WRITE_BACK.UPDATED_AT
FROM PRODUCT_COMMENTS_WRITE_BACK
INNER JOIN LATEST_METADATA ON LATEST_METADATA.MAX_ID = PRODUCT_COMMENTS_WRITE_BACK.ID;

PRODUCT_COMMENTS_WRITE_BACK table. Feel free to add a table with products data or something that you would like to add comments to, below is an example with the Northwind demo data set.

For this example we need some more variables. The Comment variable from the previous example can be reused. ProductId is the important one to associate the comment with the product. ProductName and ProductDisplayName are only used to display a nice title of the modal we will create in an upcoming step.

Column	Type	Description
Comment	Text	Text variable to store the COMMENT, used in write-back action.
ProductId	Number	Number variable to store the PRODUCT_ID, to associate the comment with the product when writing back the comment.
ProductName	Text	Text variable to store the name of the selected product.
ProductDisplayName	Text	Combines Product name and Product ID for a nice title of the input modal, where users updates the product comments.