Setup test data and run tests on snowflake in BDD style!
Provides pytest-bdd step definitions for testing snow-sql scripts against a snowflake account.
You can install "pytest-snowflake_bdd" via pip.
$ pip install pytest-snowflake-bdd
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 environmentBelow 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_idis the column name andINTEGERis the snowflake data type.The step
a temporary table called "<fully_qualified_table_name>" hasReplaces 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 isThis runs the sql script and compares the output with given dataframe.
Creating a new snowflake session
Given a snowflake connectionSetting 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 |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 |
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.
Distributed under the terms of the MIT license, "pytest-snowflake_bdd" is free and open source software
If you encounter any problems, please file an issue along with a detailed description.