Karate Connect : don’t break your SQL code with a Karate test

{
  "account": "xxx.west-europe.azure",
  "user": "MY_USER",
  "privateKeyPath": "/my-path/private-key.pem",
  "privateKeyPassphrase": "my-passphrase"
}

{
  "database": "MY_DB",
  "schema": "MY_SCHEMA"
  "role": "MY_ROLE",
  "warehouse": "MY_WH"
}

Feature: SELECT
  Background:
    * json cliConfig = read('classpath:cli-config.json')
    * json snowflakeConfig = read('classpath:snowflake-config.json')
    * string jwt = snowflake.cli.generateJwt(cliConfig)
    * json restConfig = ({jwt, cliConfig, snowflakeConfig})

  Scenario: Select 1 cutter
    Given text statement =
    """
      SELECT SERIAL_NUMBER, CUTTER_TYPE
      FROM CUTTER
      WHERE SERIAL_NUMBER='MY_VECTOR'
    """
    And def response = snowflake.rest.runSql({...restConfig, statement})
    And table expectedData
      | SERIAL_NUMBER | CUTTER_TYPE |
      | "MY_VECTOR"   | "VECTOR"    |
    And match response.status == "OK"
    And match response.data == expectedData

Feature: Demo
  Background:
    * json cliConfig = read('classpath:cli-config.json')
    * json snowflakeConfig = read('classpath:snowflake-config.json')
    * string jwt = snowflake.cli.generateJwt(cliConfig)
    * json restConfig = ({jwt, cliConfig, snowflakeConfig})
    * string clientId = "😋_"+base.random.uuid()
    * def insert = (table, value) => "INSERT INTO "+table+"(CLIENT_ID, VALUE) VALUES ('"+clientId+"','"+value+"')"

  Scenario Outline: Burger Factory - <bread> + <vegetable> + <meat> = <output>
    Given table inserts
      | table       | value         |
      | "BREAD"     | "<bread>"     |
      | "VEGETABLE" | "<vegetable>" |
      | "MEAT"      | "<meat>"      |
    And json responses = karate.map(inserts, (row) => snowflake.rest.runSql({...restConfig, statement: insert(row.table, row.value)}).status)
    And match each responses == "OK"

    When json dbtResult = dbt.cli.run({})
    And match dbtResult.status == "OK"
    And match dbtResult.output contains "Completed successfully"

    Then string select = "SELECT VALUE FROM BURGER WHERE CLIENT_ID='"+clientId+"'"
    And json response = snowflake.rest.runSql({...restConfig, statement: select })
    And match response.data == [ { "VALUE" : "<output>" } ]

    Examples:
      | bread | vegetable | meat | output       |
      | 🍞    | 🍅        | 🥩   | 🍔           |
      | 🍞    | 🍅        | 🍗   | 🍔           |
      | 🍞    | 🍅        | 🐟   | 🍔           |
      | 🍞    | 🥕        | 🥩   | 🍞 + 🥕 + 🥩 |

{"FOO":1,"BAR":"bar1"}
{"FOO":2,"BAR":"bar2"}

* string fileAbsolutePath = karate.toAbsolutePath("myPath/file.json")
* string tableName = "MY_TABLE"
* json result = snowflake.cli.putJsonIntoTable({ fileAbsolutePath, tableName, cliConfig, snowflakeConfig })
* match result.status == "OK"