Expectations against lower case columns in snowflake

GX Core (OSS) Support
1

Hi everyone,

I’ve been using Great Expectations in a Python 3.11 environment for a few weeks. It worked perfectly with Redshift, but I’m encountering issues with column names as I transition to Snowflake.

For example, consider the this table definition:

CREATE OR REPLACE TABLE SCHEMA.STATUS (
    "self" VARCHAR(16777216),
    "description" VARCHAR(16777216),
    "icon_url" VARCHAR(16777216),
    "name" VARCHAR(16777216),
    "id" NUMBER(38,0) NOT NULL,
    "status_category" VARIANT,
    PRIMARY KEY ("id")
);

And on of the expectations I’m using:

ExpectationConfiguration(
    expectation_type="expect_compound_columns_to_be_unique",
    kwargs={
        "column_list": ["self", "description"],
        "ignore_row_if": "any_value_is_missing"
    }
)

The error I encounter is:

(42000): SQL compilation error: error line 7 at position 11
invalid identifier 'SELF'
[SQL: SELECT count(*) AS count_1
FROM (SELECT *
      FROM (SELECT *
            FROM (SELECT *
                  FROM DV05_DB_TA_DW_RAW.jira.status) AS anon_1
            WHERE true) AS anon_2
      WHERE NOT (self IS NULL OR description IS NULL)) AS anon_1]

This issue arises because the query generated by Great Expectations does not enclose self and description in quotes.

I’ve tried various solutions without success. Has anyone encountered a similar issue and found a solution?

Thanks!

2

Hi @tiagocncosta, welcome to our community!

I’m happy to hear you were problem free with Redshift. Lets try to get you the same experience with Snowflake.

Have you tried to directly quote the column names in the ExpectationConfiguration? example:
"column_list": ['"self"', '"description"'],

if that doesn’t work we may need to write some python to modify how column names are rendered by SQLAlchemy to enforce the quotes. However, lets take it step by step.