Skip to main content

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

Project description

pytest-snowflake_bdd

PyPI version Python versions

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.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

pytest-snowflake_bdd-0.2.2.tar.gz (7.1 kB view hashes)

Uploaded source

Built Distribution

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Huawei Huawei PSF Sponsor Microsoft Microsoft PSF Sponsor NVIDIA NVIDIA PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page