Define and execute SQL integration tests for AWS Redshift

gem install squcumber-redshift -v 0.1.4


sQucumber Redshift

Gem Version Dependency Status Build Status Test Coverage Gitter

Bring the BDD approach to writing SQL for your Amazon Web Services Redshift cluster and be confident that your scripts do what they're supposed to do. Define and execute SQL unit, acceptance and integration tests and let them serve as a living documentation for your queries. It's Cucumber - for SQL!


Suppose you want to test that kpi_reporting.sql is producing correct results; its .feature file could look as follows:

# features/kpi_reporting.feature

Feature: KPI Reporting

  Scenario: There are some visitors and some orders
    Given the existing table "access_logs":
      | req_date   | req_time | request_id |
      | 2016-07-29 | 23:45:16 | 751fa12d-c51e-4823-8362-f85fde8b7fcd |
      | 2016-07-31 | 22:13:54 | 35c4699e-c035-44cb-957c-3cd992b0ad73 |
      | 2016-07-31 | 11:23:45 | 0000021d-7e77-4748-89f5-cddd0a11d2f9 |
    And the existing table "orders":
      | order_date | product |
      | 2016-07-31 | Premium |
    When the SQL file "kpi_reporting.sql" is executed
    And the resulting table "kpi_reporting" is queried
    Then the result exactly matches:
      | date       | visitors | orders |
      | 2016-07-29 | 1        | 0      |
      | 2016-07-31 | 2        | 1      |


Add this line to your application's Gemfile:

gem 'squcumber-redshift'

And then execute:

$ bundle

Or install it yourself as:

$ gem install squcumber-redshift


Put your .feature files in the directory feature in your project's root. (You may use subfolders.) In order to take advantage of auto-generated Rake tasks, add this to your Rakefile:

require 'squcumber-redshift/rake/task'

The following folder structure

└── features
    ├── marketing
    │   ├── sales.feature
    │   └── kpi.feature
    └── development
        └── logs
            └── aggregate.feature

Leads to the following Rake tasks:

$ rake -T
rake test:sql:marketing                                         # Run SQL tests for all features in marketing
rake test:sql:marketing:sales[scenario_line_number]             # Run SQL tests for feature marketing/sales
rake test:sql:marketing:kpi[scenario_line_number]               # Run SQL tests for feature marketing/kpi
rake test:sql:development                                       # Run SQL tests for all features in development
rake test:sql:development:logs                                  # Run SQL tests for all features in development/logs
rake test:sql:development:logs:aggregate[scenario_line_number]  # Run SQL tests for feature development/logs/aggregate

Run a whole suite of features by executing a Rake task on the folder level:

rake test:sql:marketing

Or execute a specific scenario only by specifying its line number in the corresponding .feature file:

rake test:sql:marketing:sales[12]

Environment Variables

Make sure the following environment variables are set when running sQucumber's Rake tasks:

Name Description
REDSHIFT_HOST Hostname of the AWS Redshift cluster
REDSHIFT_PORT Redshift port to connect to
REDSHIFT_USER Name of the Redshift user to use to create a testing database, must be a superuser
REDSHIFT_PASSWORD Password of the Redshift user
REDSHIFT_DB Name of the DB on the Redshift cluster

Optional environment variables:

Name Value Description Default
SPEC_SHOW_STDOUT 1 Show output of statements executed on the Redshift cluster 0
KEEP_TEST_DB 1 Do not drop the database after test execution (useful for manual inspection) 0
TEST_DB_NAME_OVERRIDE String Define a custom name for the testing database created on the cluster. Setting this to foo will result in the database test_env_foo being created random 5-digit integer


  1. Fork it ( https://github.com/moertel/squcumber-redshift/fork )
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am 'Add some feature')
  4. Push to the branch (git push origin my-new-feature)
  5. Create a new Pull Request