Write a test to check the code written to compare Teradata and snowflake table data using python mock

Database infrastructure is a critical component of any data-driven organization. It is important to ensure that the data in your database is accurate and up to date. In this article, we will discuss how to use Python mocking and patching to compare data in two Teradata and Snowflake table, in case infrastructure is not available for testing and you want to check your code is working fine. We will be leveraging the Python unittest module to write our tests and the unittest.mock module to mock the behavior of our database objects.

Python code for comparing two tables in Teradata and Snowflake:

To demo this concept, I have written a simple Python script that compares two tables in Teradata and Snowflake. The script takes the following parameters:

• Teradata database name
• Teradata table name
• Snowflake database name
• Snowflake table name

After reading the data from the tables, the data is stored in a Pandas DataFrame. The script then compares the two Data Frames and prints the number of rows that are different.

The script is available on GitHub at

Writing the Tests:

The first step is to write the tests for our script. We will be using the Python unittest module to write our tests. The tests will be written in a separate file called test_compare_tables.py. The tests will be written in the following order:
There are three tests that we will be writing:

1. Test that the script can connect to Teradata.
2. Test that the script can connect to Snowflake.
3. Test that the script can compare two tables.

Test that the script can connect to Teradata

Test will be written to check if the script can connect to Teradata. We will be using the unittest.mock module to mock the behavior of the Teradata database object. The mock object will be used to return a Pandas DataFrame with some sample data. The test will then check if the script can connect to Teradata and return the data in the DataFrame.

Test that the script can connect to Snowflake

The test will be written to check if the script can connect to Snowflake. We will be using the unittest.mock module to mock the behavior of the Snowflake database object. The mock object will be used to return a Pandas DataFrame with some sample data. The test will then check if the script can connect to Snowflake and return the data in the DataFrame.

Test that the script can compare two tables

The test will be written to check if the script can compare two tables. We will be using the unittest.mock module to mock the behavior of the Teradata and Snowflake database objects. The mock objects will be used to return Pandas Data Frames with some sample data. The test will then check if the script can compare the two Data Frames and return the number of rows that are different.

You can find Test file at

How to test code used in this blog:

To test the script, we will need to install the following:
I. Install Python 3.8 or higher.
II. Crate a virtual environment using the following command:

python3 -m venv venv

III. Activate the virtual environment using the following command:

source venv/bin/activate

IV. Install the required packages using the following command:

pip install -r requirements.txt

V. Run the tests as shown below:

$ python -m pytest tests/test_compare_tables.py -sv
============================= test session starts =============================
platform win32 -- Python 3.9.5, pytest-7.2.2, pluggy-1.0.0 -- C:\Users\rahulbhave\code\mock_teradata_snowflake_db\venv3\Scripts\python.exe
cachedir: .pytest_cache
rootdir: C:\Users\rahulbhave\code\mock_teradata_snowflake_db
collecting ... collected 3 items

tests/test_compare_tables.py::TestConnectToTeradata::test_connect_to_teradata PASSED
tests/test_compare_tables.py::TestConnectToSnowflake::test_connect_to_snowflake PASSED
tests/test_compare_tables.py::TestCompareTables::test_compare_tables PASSED

============================== 3 passed in 2.28s ==============================

Conclusion:

I hope you found this article useful. In this article, we discussed how to use Python mocking and patching to compare two tables in Teradata and Snowflake in case infrastructure is not available for testing and you want to check your code is working fine. We used the Python unittest module to write our tests and the unittest.mock module to mock the behavior of our database objects.

The code for this article is available on GitHub at

If you like the article, please share it with your friends and colleagues and don't forget to give star to the GitHub repo.

You can connect with me on LinkedIn.