Tech-Discussion

Finding Gaps in Sequential Data - GENERATOR Suppose you store data sequentially and need to find gaps in the numbers. In this case, we have to find 1004, 1007 & 1008 MS-SQL Server You can achieve this by using built-in functions like DATEADD as below: In Snowflake, we can use a similar approach as above; however, we can use the GENERATOR as follows: For more details, refer to the Generator section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series                  

Interval Data Type In certain scenarios, a start date and an interval are available, and the end date must be derived by applying the interval to the start date. MS-SQL Server You can achieve this by using built-in functions like DATEADD as below: In Snowflake,   we can use a similar approach as above; however,  we can use  the  INTERVAL DATA TYPE  as follows: For more details, refer to the Interval_DataType section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series                

Error Handling - DML When you run bulk DML and a row's data doesn't match the table structure, the statement fails. MS-SQL Server We can see it here; this statement will fail with " Cannot insert the value NULL into column does not allow nulls" or " Conversion failed when converting". As a result, zero records will be inserted. In Snowflake, we can use ERROR_LOGGING as follows; as a result, two records with errors will be skipped, and a single record will be inserted: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series              

Row-Timestamp Last Updated To support auditing and tracking requirements, the transactional table should include a column that records the date and time of record creation and subsequent updates. MS-SQL Server We can achieve it by creating  an additional column as follows: In Snowflake, we can use a similar approach as above; however, we can make use of a Row-Timestamp as follows: You can access the same column as below: For more details, refer to the Row-Timestamp section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series                

Recursive View - Hierarchy Sometimes data is stored in a hierarchical structure within a table. To display the complete lineage or parent-child relationship, you can use a recursive query. MS-SQL Server We can achieve it by creating a CTE as follows: In Snowflake,   we can use a similar approach as above; however, we can make use of a  Recursive View  as follows: For more details, refer to the Recursive_View section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series              

Period Data Type In the warehouse, you usually save a record with start_date and end_date. If you want to filter a record based on these dates, then you have to use both date columns. MS-SQL Server We can use a WHERE clause as below: In Snowflake,   we can use a similar approach as above; however,  we can use the PERIOD DATA TYPE  as follows: For more details, refer to the Period-DataType  or  Period-Function   sections. You can see multiple use cases for the PERIOD data type. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series              

Clear the cache When you are working on query performance, you have to clear the cache. MS-SQL Server We can achieve it as follows: In Snowflake,   we can use ALTER_SESSION or ALTER WAREHOUSE  as follows: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series          

Show Columns of a Table Sometimes, we need to check the columns of a particular table. MS-SQL Server We can achieve it as follows: In Snowflake , we can  use SHOW_COLUMNS  as follows: For more details, refer to the SHOW_COLUMNS section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series        

Extract a Year from the DATE Sometimes, we need to extract the year component from a given date. MS-SQL Server We can achieve it as follows: In Snowflake , we use EXTRACT as follows: For more details, refer to the EXTRACT section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Parse and Save XML Sometimes, we need to parse an XML file and save it in a table. MS-SQL Server We can achieve it as follows: In Snowflake , we use PARSE_XML  as follows: For more details, refer to the PARSE_XML section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Convert Data Type - Decimal to Integer Sometimes, for various reasons, we need to convert decimal values to integer values. MS-SQL Server We can achieve it as follows: In Snowflake , we use EXTRACT as follows: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Filter Columns in Result Grid When the result set contains numerous columns, it becomes challenging to identify the columns we need. MS-SQL Server The result grid does not allow us to select only the required columns. As a workaround, we need to explicitly limit the columns in the SELECT clause. In Snowflake , we use the filter provided in the Result Grid  as follows: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Create or Alter Table - Add Column You design a table to meet your current requirements, and after a few days, you might need to add an additional column to the existing table. MS-SQL Server We can achieve it by altering the table structure as follows: In Snowflake ,   we can use a similar approach as above; however, we can make use of ALTER  as follows: For more details, refer to the ALTER section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series    

Table Structure - Describe  Sometimes, we need to look at how a table is arranged to understand it better. MS-SQL Server We can achieve it as follows: In Snowflake , we use DESC  as follows: For more details, refer to the DESC section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series