Macros that generate unit tests are capitalizing non-quoted column names for Snowflake

[dbeatty10]

Describe the bug

The integration tests in CI are failing in Snowflake. Currently, there's a password issue masking another failure underneath: for Snowflake only, the unit test generating macros return column names that are capitalized (the default casing for non-quoted identifiers in Snowflake).

See #251 for where I confidently (!) merged because I thought everything was all good 😬.

Steps to reproduce

Run the tests within the integration_tests folder manually for Snowflake and see the differences in actual vs. expected that are just a difference in casing.

Expected results

No failures for integration tests.

So currently, this is the expected format across all adapters:

models:
  - name: data__a_relation
    columns:
      - name: col_a
      - name: col_b

Actual results

But for dbt-snowflake only, getting something like this instead:

models:
  - name: data__a_relation
    columns:
      - name: COL_A
      - name: COL_B

Screenshots and log output

System information

The contents of your packages.yml file:

Which database are you using dbt with?

• snowflake

The output of dbt --version:

<output goes here>

The operating system you're using:

The output of python --version:

Additional context

Here's a possible approach (that I don't particularly like!):

diff --git a/macros/generate_unit_test_template.sql b/macros/generate_unit_test_template.sql
index 36afba2..7767d4b 100644
--- a/macros/generate_unit_test_template.sql
+++ b/macros/generate_unit_test_template.sql
@@ -29,7 +29,7 @@
             {%- set columns = adapter.get_columns_in_relation(ref(item_dict.alias)) -%}
         {%- endif -%}
         {%- for column in columns -%}
-            {{ input_columns_list.append(column.name) }}
+            {{ input_columns_list.append(column.name|lower) }}
         {%- endfor -%}
         {{ ns.input_columns_list.append(input_columns_list) }}
     {%- endfor -%}
@@ -40,7 +40,7 @@
         {%- set ns.expected_columns_list = [] -%}
         {%- set columns = adapter.get_columns_in_relation(ref(model_name)) -%}
         {%- for column in columns -%}
-            {{ ns.expected_columns_list.append(column.name) }}
+            {{ ns.expected_columns_list.append(column.name|lower) }}
         {%- endfor -%}
     {% endif %}

The pros is that it would make the integration tests pass with the minimal effort and align with dbt conventions of lowercasing model and column names by default.

The cons is that it would not properly handle column names that are case-sensitive and quoted within the database.

Side note: whenever I see Jinja |upper or |lower to standardize the casing, it's a red flag / code smell for me that something is missing or not quite right, and its just a band-aid that doesn't address the root cause.

Are you interested in contributing the fix?