pytest-snowflake-bdd 0.2.2

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

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.