Quotes in schema check

[vijaykiran]

Testing checks with quotes on table names and column names.

• Test 001: I can successfully execute a scan for row_count check with quotes on table name.
• Test 002: I can successfully execute a scan for schema check without quotes on table name.
• Test 003: I cannot execute a scan for schema check with quotes on table name.

Set up:

• using sodacl-workshop repo with latest version of soda-core
• db is adventureworks running in docker container on the sodacl-workshop repo
• connected to dev.sodadata.io account with API keys set in configuration.yml

Test 001 - row_count check with table name in quotes; works as expected
checks.yml:

checks for "dim_account":
  - row_count > 0

configuration.yml

data_source adventureworks:
  type: postgres
  connection:
    host: db
    username: postgres
    password: secret
  database: postgres
  schema: public

Scan input:
soda scan -d adventureworks -V -c configuration.yml checks.yml

Scan output:

Soda Core 0.0.1
Reading configuration file "configuration.yml"
Reading SodaCL file "checks.yml"
Scan execution starts
Query adventureworks."dim_account".aggregation[0]:
SELECT 
  COUNT(*) 
FROM "dim_account"
Scan summary:
1/1 query OK
  adventureworks."dim_account".aggregation[0] [OK] 0:00:00.034581
1/1 check PASSED: 
    "dim_account" in adventureworks
      row_count > 0 [PASSED]
        check_value: 99
All is good. No failures. No warnings. No errors.

Test 002 - schema check; works as expected
checks.yml:

checks for dim_account:
 - schema:
     warn:
       when required column missing: [sombrero]

configuration.yml
as above

Scan input:
soda scan -d adventureworks -V -c configuration.yml checks.yml

Scan output:

Soda Core 0.0.1
Reading configuration file "configuration.yml"
Reading SodaCL file "checks.yml"
Scan execution starts
Query adventureworks.dim_account.schema[dim_account]:
SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE lower(table_name) = 'dim_account' 
  AND lower(table_catalog) = 'postgres' 
  AND lower(table_schema) = 'public'
ORDER BY ORDINAL_POSITION
Scan summary:
1/1 query OK
  adventureworks.dim_account.schema[dim_account] [OK] 0:00:00.118499
1/1 check WARNED: 
    dim_account in adventureworks
      schema [WARNED]
        missing_column_names = [sombrero]
        schema_measured = [account_key integer, parent_account_key integer, account_code_alternate_key integer, parent_account_code_alternate_key integer, account_description character varying, account_type character varying, operator character varying, custom_members character varying, value_type character varying, custom_member_options character varying]
Only 1 warning. 0 failure. 0 errors. 0 pass.

Test 003 - schema check with table name in quotes; error
checks.yml:

checks for "dim_account":
 - schema:
     warn:
       when required column missing: [sombrero]

configuration.yml
as above

Scan input:
soda scan -d adventureworks -V -c configuration.yml checks.yml

Scan output:

Soda Core 0.0.1
Reading configuration file "configuration.yml"
Reading SodaCL file "checks.yml"
Scan execution starts
Query adventureworks."dim_account".schema["dim_account"]:
SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE lower(table_name) = '"dim_account"' 
  AND lower(table_catalog) = 'postgres' 
  AND lower(table_schema) = 'public'
ORDER BY ORDINAL_POSITION
Metrics schema were not computed for check schema
Scan summary:
1/1 query OK
  adventureworks."dim_account".schema["dim_account"] [OK] 0:00:00.097976
1/1 check NOT EVALUATED: 
    "dim_account" in adventureworks
      schema [NOT EVALUATED]
1 checks not evaluated.
1 errors.
Oops! 1 error. 0 failures. 0 warnings. 0 pass.
ERRORS:
Metrics schema were not computed for check schema

[tombaeyens]

@vijaykiran Can you create skipped, failing unit tests and create a new issue for the failing ones?