GitLocker: The Coding Marketplace

Description:

pytestsnowflakebdd 0.2.2

pytest-snowflake_bdd

Setup test data and run tests on snowflake in BDD style!

Features
Provides pytest-bdd step definitions for testing snow-sql scripts against a snowflake account.

Installation
You can install “pytest-snowflake_bdd” via pip.

$ pip install pytest-snowflake-bdd

Usage
This plugin relies on pytest-bdd to run bdd tests.
You can pass your snowflake account details using the cli arguments to pytest command.
custom options:
--snowflake-user=SNOWFLAKE_USER
snowflake user for test environment
--snowflake-password=SNOWFLAKE_PASSWORD
snowflake password for test environment
--snowflake-account=SNOWFLAKE_ACCOUNT
snowflake password for test environment
--snowflake-role=SNOWFLAKE_ROLE
optional snowflake role for test environment
--snowflake-warehouse=SNOWFLAKE_WAREHOUSE
optional snowflake warehouse for test environment
Below example illustrates the usage of step definitions provided by the plugin.
Feature: ExampleFeature for snowflake testing

Scenario: example_scenario
Given a snowflake connection
When a temporary table called "SNOWFLAKE_LIQUIBASE.PUBLIC.DEPARTMENT" has
| dept_id: INTEGER | dept_name: STRING |
| 1 | "Computer Science" |
| 2 | "Software Engineering" |
When a temporary table called "SNOWFLAKE_LIQUIBASE.PUBLIC.PEOPLE" has
| people_id: INTEGER | name: STRING | dept_id: INTEGER |
| 10 | "tilak" | 1 |
Then a sql script "./sql/example.sql" runs and the result is
| people_id: INTEGER | name: STRING | dept_id: INTEGER | dept_name: STRING |
| 10 | "tilak" | 1 | "Computer Science" |

dept_id: INTEGER. dept_id is the column name and INTEGER is the snowflake data type.
The step a temporary table called "<fully_qualified_table_name>" has
Replaces the existing table with a temporary table. And adds data to the temporary table. This shadows the existing
table in snowflake for the entire session. Any changes done to the temporary table does not reflect on the actual
database. If the table does not exists creates a new temporary table.

The step Then a sql script "<sql_script_path>" runs and the result is
This runs the sql script and compares the output with given dataframe.

Available Step definitions
Creating a new snowflake session
Given a snowflake connection
Setting up a temporary snowflake table for test

Replaces the existing table with a temporary table. And adds data to the temporary table. This shadows the existing
table in snowflake for the entire session. Any changes done to the temporary table does not reflect on the actual
database. If the table does not exists creates a new temporary table.

When a temporary table called "SNOWFLAKE_LIQUIBASE.PUBLIC.DEPARTMENT" has
| dept_id: INTEGER | dept_name: STRING |
| 1 | "Computer Science" |
| 2 | "Software Engineering" |
Setting up a snowflake table for test

Creates a normal table. Will fail if table already exists.

When a table called "SNOWFLAKE_LIQUIBASE.PUBLIC.DEPARTMENT" has
| dept_id: INTEGER | dept_name: STRING |
| 1 | "Computer Science" |
| 2 | "Software Engineering" |
Running a sql script and validating results
Then a sql script "./sql/example.sql" runs and the result is
| people_id: INTEGER | name: STRING | dept_id: INTEGER | dept_name: STRING |
| 10 | "tilak" | 1 | "Computer Science" |
Representing null in table data
Use {null}
| people_id: INTEGER | name: STRING | dept_id: INTEGER | dept_name: STRING |
| 10 | "tilak" | 1 | {null} |
Stubbing current time related functions
Supports stubbing the following functions with the fixture value.
current_timestamp, localtimestamp, getdate, systimestamp, sysdate, current_time, localtime
These functions will be replaced in the sql query by statements like
CAST ('2022-01-05 04:12:17' as TIMESTAMP) or CAST ('04:12:17' as TIME)
Feature: ExampleFeature for snowflake testing

Scenario: example_scenario
Given a snowflake connection
And current timestamp "2022-01-05 04:12:17"
And current time "04:12:17"
When a temporary table called "SNOWFLAKE_LIQUIBASE.PUBLIC.DEPARTMENT" has
| dept_id: INTEGER | dept_name: STRING |
| 1 | "Computer Science" |
| 2 | "Software Engineering" |
When a temporary table called "SNOWFLAKE_LIQUIBASE.PUBLIC.PEOPLE" has
| people_id: INTEGER | name: STRING | dept_id: INTEGER |
| 10 | "tilak" | 1 |
Then a sql script "./sql/example.sql" runs and the result is
| people_id: INTEGER | name: STRING | dept_id: INTEGER | dept_name: STRING |
| 10 | "tilak" | 1 | "Computer Science" |
Representing different data types in table
| a: CHAR | b: CHARACTER | c: STRING | d: TEXT | e: BINARY | f: VARBINARY |
| sample | sample | sample | sample | sample | sample |

| a: FLOAT | b: DOUBLE | c: INT | d: INTEGER | e: BIGINT | f: SMALLINT | g: TINYINT | h: BYTEINT |
| 1.0 | 1.0 | 1 | 1 | 1 | 1 | 1 | 1 |

| a: DATE | b: DATETIME | c: TIME | d: TIMESTAMP |
| 2021-05-05 | 2021-05-05 01:35:00 | 01:35:00 | 2021-05-05 01:35:00 |

Understanding data-type mismatch errors
For assertion of tables we are using pandas. Differences are shown
in-terms of pandas dataframe.
Below snowflake to pandas type table can help in understanding the
errors:

Snowflake datatype
Pandas datatype

BIGINT
int64

BINARY
bytes

BOOLEAN
bool

CHAR
str

CHARACTER
str

DATE
object

DATETIME
object

DEC
object

DECIMAL
object

DOUBLE
float64

FIXED
object

FLOAT
float64

INT
int64

INTEGER
int64

NUMBER
object

REAL
float64

BYTEINT
int64

SMALLINT
int64

STRING
str

TEXT
str

TIME
object

TIMESTAMP
object

TINYINT
int64

VARBINARY
bytes

VARCHAR
str

Contributing
Contributions are very welcome. Tests can be run with tox, please ensure
the coverage at least stays the same before you submit a pull request.

License
Distributed under the terms of the MIT license, “pytest-snowflake_bdd” is free and open source software

Issues
If you encounter any problems, please file an issue along with a detailed description.