S1. What’s under the Snowflake(s)?

Let’s start the first episode of What’s under the Snowflake(s)? The challenge we focus here is Week 38 – Basic from Frosty Friday.

“We’ve had some issues with sales being rung-up but then not showing up in the system anymore. We’ve got a feeling that they’re being removed and would like to very quickly see if that’s the case. Could you help out the store with the following?”
The challenge consists of :
1P: Add a stream to the employee_sales VIEW.
2P: Keep track of every deletion that was made.
3P: Move these into a new table called ‘deleted_sales’.

Frosty Friday ‘Week 38 – Basic’

We proceed step by step in order to guide the reader through a possible resolution strategy.

To quickly start, copy and paste the block of code below. This will be the basis for your table/view:

-- Create first table
CREATE TABLE employees (
id INT,
name VARCHAR(50),
department VARCHAR(50));

-- Insert example data into first table
INSERT INTO employees (id, name, department)
VALUES
(1, "Alice" "Sales"),
(2, "Bob", "Marketing");

-- Create second table
CREATE TABLE sales (id INT,
employee_id INT,
sale_amount DECIMAL(10, 2));

-- Insert example data into second table
INSERT INTO sales (id, employee_id, sale_amount)
VALUES
(1, 1, 100.00),
(2, 1, 200.00),
(3, 2, 150.00);

-- Create view that combines both tables
CREATE VIEW employee_sales AS
SELECT e.id, e.name, e.department, s.sale_amount
FROM employees e
JOIN sales s ON e.id = s.employee_id;

-- Query the view to verify the data
SELECT * FROM employee_sales;

How to proceed? A possible strategy!

• 1P: Add a stream to the employee_sales VIEW.
  • We need to create a stream.
  • Once created, the stream should track changes to the view employee_sales.
    •  
• 2P: Keep track of every deletion that was made.
  • It is always a good practice to test the stream.
  • Hence, you can delete some records.
  • At this point, you can see the stream and what it is still there.
• 3P: Move these into a new table called ‘deleted_sales’.
  • In order to fill a new table with the output deletions, you create a new table using an existing table with the deleted values from the old table. You probably need to use the WHERE parameter.
    •  
  • At the end, you can confirm table contents in order to see whether in the new table there is only the marketing row.

1P: Create a stream to the view

The syntax of the CREATE STREAM command changes based on the type of object it is combined with.

APPEND_ONLY: “Only supported for streams on standard tables or streams on views that query standard tables”. It means that the stream of interest tracks row inserts only. By default, it is FALSE.

SHOW_INITIAL_ROWS: “Specifies the records to return the first time the stream is consumed“. By setting the paramenter equals to FALSE, we want that stream returns any Data Manipulation Language (DML) command changes to the source object since the most recent offset. By default, it is FALSE.

-- Create a stream View
CREATE STREAM challenge_stream
ON VIEW employee_sales  
APPEND_ONLY = FALSE 
SHOW_INITIAL_ROWS = FALSE;

2P: Keep track of every deletion you make

We can use the DELETE command and specify by means of WHERE parameter which rows are going to be deleted. In this particular case we are deleting the first row in the joined table. By imposing the parameter ID = 1, we delete the data relating to Alice, i.e. the employee identified by that ID in the table SALES.

In order to keep track of deletion, we can use the METADATA$ACTION column. It is an additional column that is added to the columns as the source object. You can specify the action you did. In our case, we specify DELETE.

-- Delete records
DELETE FROM sales
WHERE ID = 1;

-- View stream 
SELECT * FROM challenge_stream
WHERE "METADATA$ACTION"  = 'DELETE';

3P: Move these into a new table called ‘deleted_sales’.

The last step of the challenge is to move the deleted data into a new table. In order to do that CREATE the table. By CREATE AS, we are actually creating a populated table.

-- Moving deleted output into a new table
CREATE OR REPLACE deleted_sales
AS SELECT * FROM challenge_stream
WHERE "METADATA$ACTION"  = 'DELETE';

-- Confirm table contents
SELECT * FROM deleted_sales;