Snowflake Testing for Data Warehouses Simplified 101

A Data Warehouse has become the need of the hour today because most companies are shifting to data-driven solutions. Businesses use multiple online tools and services to reach out to customers, optimize workflow, and manage other business activities. All the data generated by these platforms needs to be stored in an analysis form so that BI tools can consume it to generate insights. 

But how can you directly load data from data sources to Data Warehouses such as Snowflake when it’s inconsistent? Snowflake Testing is a method to test data inside the Data Warehouse for integrity, accuracy, reliability, and consistency before feeding it to other BI tools. 

Inconsistent data can generate false reports and negatively affect business decisions. To avoid all these errors, Snowflake Testing plays a vital role in delivering high-quality data. In this article, you will learn about Snowflake Data Warehouse, ETL Testing, and different methods that one can use for Snowflake Testing.

Introduction to Snowflake

Snowflake is a SaaS (Software as a Service) based Data Warehouse platform. It is a fully managed Cloud Data Warehouse that helps companies and organizations store data and run analytics. The users don’t have to maintain their hardware and software upgrades or pay for any hardware failure because companies only rent the storage space and computational power in the Data Warehouse. 

Snowflake is different from other traditional Data Warehouses as it separates storage and computational power for faster query performance. It also allows users to scale up or down storage independently and compute as per the requirements. Snowflake is highly scalable and can handle terabytes of data with the help of its architecture, which involves virtual compute instances and efficient storage buckets that run solely on the Cloud.

Snowflake offers data security using Amazon S3 Policy Controls, SSO, Azure SAS Tokens, and Google Cloud Storage access permissions. It uses elastic Azure Blobs Storage for internal storage engine and Azure Data Lake to store unstructured, structured, and on-premise data.

Key Features of Snowflake

Snowflake provides Data Warehouse services to enterprises to make their data available in the analysis-ready form. It is highly scalable and suitable for running Business Analytics. A few features of Snowflake are listed below.

• Integrations: Snowflake supports integration with many 3rd party tools and services that companies use for daily use. It helps users easily sync data between source and Data Warehouse and feed it to BI (Business Intelligence) tools.
• Security: The communication between client and user is always protected using TLS (Transport Layer Security). Snowflake allows users to select geolocation for storing data.
• Standard SQL: Snowflake contains most DDL and DML in SQL. In addition, it also has the most advanced DML in Snowflake, which helps in analytical extension. 

Introduction to ETL Testing

ETL is the process of Extracting data from data sources, Transforming it as per business requirements, and then Loading it to the Data Warehouse as a destination. While transferring or migrating data from source to destination, such as Snowflake or Google BigQuery, errors commonly occur due to data inconsistency. ETL testing is a process that enables users to test or validate the ETL process by comparing data from source to destination. It is a kind of black-box testing to ensure that all the data from the source is migrated to the Data Warehouse and avoid any data loss and data duplication. ETL Testing consists of different phases, as shown in the image below.

Unlike all other Testing processes, ETL Testing also requires information on business requirements. The information is used to build strategies and estimate the time required to do an entire ETL test. Finally, a detailed report will be created after the execution of all the phases of ETL testing.

Introduction to Snowflake Testing

Snowflake stores data from multiple data sources, and data inconsistency is inevitable, which results in data loss during data loading. An enterprise cannot ignore data loss because it is valuable for them. Snowflake Testing is a process that companies follow to avoid any data loss and maintain data integrity. 

The huge chunks of data have to be accurate to feed it to BI (Business Intelligence) tools and get error-free analytics. Most ETL processes are complex and contain many errors. Transferring or migrating data to Snowflake in such an environment is time-consuming, costly, and effective because it can’t maintain data integrity.

Snowflake Testing Process

The Left Shift Snowflake Testing approach is based on constant testing in between each of the ETL data transfer steps. There are many tools available in the market for automated Snowflake Testing. The tools are designed to provide detailed test coverage during the Snowflake Testing process. The Snowflake ETL Testing process compares the data in a data source with data loaded to Snowflake. It involves the process of comparing data row by row to make sure all the data loads correctly. In this section, you will learn about different methods for Snowflake Testing using the example of data in Teradata as a data source and Snowflake. The following methods for Snowflake Testing are listed below: 

• Testing the Database Schema
• Testing Initial Data Loads
• Testing Delta Loads
• Testing Downstream Systems

1) Testing the Database Schema

The first step is to transfer database objects from the data source to the Snowflake Data Warehouse. It will move Schemas, Tables, Views, sequences, and other objects supported by Snowflake.

One can run tests after a successful schema transfer to detect any missing columns or incorrectly mapped data types between the data source and Snowflake.

Schema Compare Test

In this Snowflake Testing phase, you need to compare Schema names, Table names, View names, Column names, and corresponding Datatypes. While creating tests, one must ensure correct datatype mapping between the data source and Snowflake.

You can query the system tables of data source and Snowflake to compare names of Schema, Columns, Views, etc. Based on how you mapped data in the Data Warehouse during the ETL process, you need to change the queries for the data source in this Snowflake Testing step. The code for comparing Schema is given below.

Teradata

SELECT
UPPER(TableName),
UPPER(ColumnName),
CASE
WHEN ColumnType = 'CF' AND CharType = 1 THEN 'TEXT'
WHEN ColumnType = 'CV'THEN 'TEXT'
WHEN ColumnType = 'CO'THEN 'TEXT'
WHEN ColumnType = '11'THEN 'NUMBER'
WHEN ColumnType = '12'THEN 'NUMBER'
WHEN ColumnType = '18'THEN 'NUMBER'
WHEN ColumnType = 'T'THEN 'NUMBER'
WHEN ColumnType = 'D'THEN 'NUMBER'
WHEN ColumnType = 'F'THEN 'FLOAT'
WHEN ColumnType = 'N'THEN 'NUMBERIC'
WHEN ColumnType = 'DA'THEN 'DATE'
WHEN ColumnType = 'AT'THEN 'TIME'
WHEN ColumnType = 'SZ'THEN 'TIMESTAMP WITH TIME ZONE'
WHEN ColumnType = 'TS' THEN 'TIMESTAMP_NTZ'
WHEN ColumnType = 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN ColumnType = 'JN' THEN 'VARIANT'
WHEN ColumnType = 'A1'THEN 'ARRAY'
WHEN ColumnType = 'AN' THEN 'ARRAY'
WHEN ColumnType = 'CF'AND CharType = 4 THEN 'BINARY'
WHEN ColumnType = 'BO' THEN 'BINARY'
WHEN ColumnType = 'BF' THEN 'BINARY'
WHEN ColumnType = 'BV' THEN 'BINARY'
END as equivalent_snowflake_datatype
FROM DBC.ColumnsV

Snowflake

SELECT
UPPER(t.table_name) table_name,
UPPER(c.column_name),
c.data_type
FROM
information_schema.tables t
INNER JOIN information_schema.columns c ON (t.table_name = c.table_name)
WHERE t.table_name = 'SAMPLE_DATATYPE_TABLE' AND t.table_schema = 'PUBLIC'

2) Testing Initial Data Loads

The Snowflake Testing step involves the one-time loading of historical data from data sources to Amazon S3 Azure Blob or Google Cloud Storage and then into Snowflake. In this phase, one needs to detect any data truncation, transformation, or other data issues while loading data. Loading historical data contains many rows, which makes the dataset huge. So, this Snowflake testing stage requires different tests for timely execution and thorough testing.

Row Count Test

This test compares the number of rows available in the data source and Snowflake. It is a basic test but provides information about missing data. The code for Row Count Test is given below.

Teradata

SELECT CAST(COUNT(*) AS BIGINT) AS SOURCE_COUNT FROM TABLE

Snowflake

SELECT COUNT (*) AS TARGET_COUNT FROM TABLE

Data Compare Test

This phase becomes an essential part of Snowflake Testing because it compares data row by row and column by column between the data source and Snowflake data. It detects the exact data issues that occurred during the ETL phase, and users can fix those issues before resuming data loading. 

It helps to identify the following issues listed below: 

• Data Truncation
• Floating-point issues
• Incorrect DateTime
• Special character conversion issues

Data Aggregation Test

This Snowflake Testing phase is suitable for numeric data instead of comparing data row by row, you can run the aggregate tests by summing up the column in both data source and Snowflake and comparing the values. If the test fails then you have to go for a row by row comparison for the faulty columns in the data. Code for performing data aggregation tests in Teradata and Snowflake is given below.

Teradata

SELECT
1 DUMMY_ID,
SUM(CAST(INT_COLUMN AS DECIMAL(38,0))) AS INT_COLUMN,
SUM(CAST(FLOAT_COLUMN AS DECIMAL(38,0))) AS FLOAT_COLUMN,
SUM(CAST(NUMERIC_COLUMN AS DECIMAL(38,0))) AS FLOAT_COLUMN
...
FROM TABLE

Snowflake

SELECT
1 DUMMY_ID,
SUM(INT_COLUMN) AS INT_COLUMN,
SUM(FLOAT_COLUMN) AS FLOAT_COLUMN,
SUM(NUMERIC_COLUMN) AS FLOAT_COLUMN
...
FROM TABLE

3) Testing Delta Loads

Delta Loads are the daily transactions occurring from data source to Data Warehouse. It is important to treat these data loads separately. You need to ensure that the delta loads are in sync while doing Snowflake Testing.

Data Compare Test

This phase of Snowflake testing is used to create tests to compare delta loads and detect data issues constantly until the data source is switched off. 

Data Validation Test

The real-time data loads don’t have primary key constraints. So you have to also check for any duplicate rows in data. This Snowflake Testing helps you quickly identify all the upstream processes.

Snowflake

SELECT
COLUMN_NAME,
COUNT(*) DUPLICATE_ROWS
FROM TABLE
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1

4) Testing Downstream Systems

It is essential to maintain uniform data flow from both data sources and Snowflake. ETL Tools and BI Tools that are taking data from these sources. 

Data Compare Test

This phase of Snowflake Testing ensures that the downstream applications are delivering the same output, whether the data is coming from a data source or Snowflake. So one needs to run Data Compare Tests.

Teradata

SELECT * FROM TABLE

Snowflake

SELECT * FROM TABLE

The above code will throw all the rows of the table. You can perform row by row comparison test manually. 

Challenges of Snowflake Testing

Snowflake Testing is a time-consuming process, and one error can cost companies huge data losses. Users face many challenges in Snowflake Testing. A few of them are listed below: 

• ETL Testing is a type of black-box testing that involves testing without looking at the internal structure, so there are no specific rules, and there is no graphical user interface for easy workflow.
• Comparing large datasets manually is a time-consuming process and highly error-prone. Even comparing small chunks of data can cause human errors.
• A company uses many data sources, and reaching out to all the data sources manually and comparing data with Snowflake is a tedious process.

Conclusion

In this article, you learned about ETL Testing and why it is needed in the first place to get high-quality data. You also read about different Snowflake Testing Techniques and how Snowflake Testing maintains data integrity. Manually performing Snowflake Testing is a time-consuming process. There are many ETL Testing tools available in the market that automate the ETL Testing process for you. Snowflake allows users to load structured and unstructured data and eliminates the Snowflake Testing.

Explore our guide on Snowflake data quality to learn best practices for managing and improving data integrity in Snowflake.

Companies have business data available in multiple sources, and it’s a tedious process to load data manually from data sources to Snowflake. Hevo Data is a No-code Data Pipeline that can help you transfer data from any data source to the desired Snowflake. It fully automates the process of loading and transforming data from 150+ sources to a destination of your choice without writing a single line of code.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Share your experience of learning about Snowflake Testing in the comments section below!

FAQs