During a recent build of a Snowflake data environment, I encountered a challenge while translating my SQL Server code to Snowflake. As any seasoned database developer knows, dealing with staging tables and SCD (Type-2 slowly changing dimension) tables is par for the course in data warehousing.
In the realm of table creation, there’s the classic brute force copy/paste method, but let’s face it – it’s not foolproof. Over the years, I’ve found myself occasionally missing columns during development, and default values tend to slip through the cracks.
Entering the world of SCD tables, where default values can play a crucial role in maintaining data integrity. Adding columns to an existing table with default values is a breeze in SQL Server; however, the same can’t be said for Snowflake at the time of writing this piece. That’s when I stumbled upon the magic of CREATE TABLE LIKE in Snowflake SQL.
Now, you might be wondering, why bother with CREATE TABLE LIKE when a simple copy/paste could do the trick? Well, here’s the twist. In my Snowflake setup, I leverage TRANSIENT tables for staging – they get truncated and loaded frequently. The beauty of TRANSIENT tables lies in their ability to save storage space in my Snowflake account, thanks to their exclusion from FAIL-SAFE storage and limited time travel features. However, attempting to use CLONE to create a transient raw table for my permanent SCD tables throws an error.
Sure, my approach might seem a tad elaborate, but I found it intriguing – using a table of one type as a template for another. Here’s the drill: I kick things off by creating my permanent SCD table, including all the necessary columns for the stage and SCD tables, complete with default values. Once the SCD table is created, I use CREATE TABLE LIKE to create the transient stage table.
With both tables in existence, it’s time to trim the fat – drop any unnecessary columns from the stage and SCD tables. Now is also the opportune moment to fine-tune data retention time settings.
CREATE OR REPLACE TABLE DATA_SCD
DATA_RETENTION_TIME_IN_DAYS = 7
(
CUSTOMER_ID NUMBER(12,0)
,CUSTOMER_NAME VARCHAR(100)
,CUSTOMER_EMAIL VARCHAR(100)
,START_DATE DATE DEFAULT CURRENT_DATE() /*SCD ONLY*/
,END_DATE DATE DEFAULT ('12/31/9999'::DATE) /*SCD ONLY */
,INSERTED_DATE TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP() /*STG ONLY*/
,REC_ID NUMBER(38,0)
);
CREATE OR REPLACE TRANSIENT TABLE DATA_STG LIKE DATA_SCD;
ALTER TABLE DATA_STG SET DATA_RETENTION_TIME_IN_DAYS=0;
ALTER TABLE DATA_STG DROP COLUMN START_DATE;
ALTER TABLE DATA_STG DROP COLUMN END_DATE;
ALTER TABLE DATA_SCD DROP COLUMN INSERTED_DATE;
As a database developer navigating the dynamic landscape of SQL Server and Snowflake, this unconventional yet effective method adds a touch of creativity to the routine tasks of table creation and maintenance.
Eric Heilman 