Repository: databricks/dbt-databricks
Pull request #971 (2025-03-20 21:21:10)

Adding more macro unit tests, cleaning up some macros
<!---
  Include the number of the issue addressed by this PR above if applicable.

  Example:
    resolves #1234

  Please review our pull request review process in CONTRIBUTING.md before your proceed.
-->

### Description

Using copilot to help me troubleshoot the very annoying to write macro tests, but I did discover some bugs while doing it.  Will need to chip away at this over time.  A few other minor fixes that I will comment on in the PR.

### Checklist

- [x] I have run this code in development and it appears to resolve the stated issue
- [x] This PR includes tests, or tests are not required/relevant for this PR
- [ ] I have updated the `CHANGELOG.md` and added information about my change to the "dbt-databricks next" section.



Patch:
diff --git a/dbt/adapters/databricks/credentials.py b/dbt/adapters/databricks/credentials.py
index 3a8ad394..4e2a2a36 100644
--- a/dbt/adapters/databricks/credentials.py
+++ b/dbt/adapters/databricks/credentials.py
@@ -110,6 +110,7 @@ class DatabricksCredentials(Credentials):
             "catalog",
             "schema",
             "_user_agent_entry",
+            "user_agent_entry",
         ):
             if key in connection_parameters:
                 raise DbtValidationError(f"The connection parameter `{key}` is reserved.")
diff --git a/dbt/adapters/databricks/handle.py b/dbt/adapters/databricks/handle.py
index 62a11d1c..7529ec8f 100644
--- a/dbt/adapters/databricks/handle.py
+++ b/dbt/adapters/databricks/handle.py
@@ -320,5 +320,6 @@ class SqlUtils:
             "use_inline_params": "silent",
             "schema": creds.schema,
             "_user_agent_entry": user_agent_entry,
+            "user_agent_entry": user_agent_entry,
             **connection_parameters,
         }
diff --git a/dbt/adapters/databricks/relation.py b/dbt/adapters/databricks/relation.py
index de27167f..e6a0070f 100644
--- a/dbt/adapters/databricks/relation.py
+++ b/dbt/adapters/databricks/relation.py
@@ -183,6 +183,9 @@ class DatabricksRelation(BaseRelation):
         processed = map(process_constraint, self.create_constraints)
         return ", ".join(c for c in processed if c is not None)
 
+    def render(self) -> str:
+        return super().render().lower()
+
 
 def is_hive_metastore(database: Optional[str]) -> bool:
     return database is None or database.lower() == "hive_metastore"
diff --git a/dbt/include/databricks/macros/adapters/catalog.sql b/dbt/include/databricks/macros/adapters/catalog.sql
index fc398de4..4166bb72 100644
--- a/dbt/include/databricks/macros/adapters/catalog.sql
+++ b/dbt/include/databricks/macros/adapters/catalog.sql
@@ -1,99 +1,95 @@
 {% macro databricks__get_catalog(information_schema, schemas) -%}
+  {% set query %}
+WITH tables AS (
+  {{ databricks__get_catalog_tables_sql(information_schema) }}
+  {{ databricks__get_catalog_schemas_where_clause_sql(information_schema.database, schemas) }}
+),
+columns AS (
+  {{ databricks__get_catalog_columns_sql(information_schema) }}
+  {{ databricks__get_catalog_schemas_where_clause_sql(information_schema.database, schemas) }}
+)
+{{ databricks__get_catalog_results_sql() }}
+  {%- endset -%}
 
-    {% set query %}
-        with tables as (
-            {{ databricks__get_catalog_tables_sql(information_schema) }}
-            {{ databricks__get_catalog_schemas_where_clause_sql(information_schema.database, schemas) }}
-        ),
-        columns as (
-            {{ databricks__get_catalog_columns_sql(information_schema) }}
-            {{ databricks__get_catalog_schemas_where_clause_sql(information_schema.database, schemas) }}
-        )
-        {{ databricks__get_catalog_results_sql() }}
-    {%- endset -%}
-
-    {{ return(run_query(query)) }}
+  {{ return(run_query(query)) }}
 {%- endmacro %}
 
 {% macro databricks__get_catalog_relations(information_schema, relations) -%}
+  {% set query %}
+WITH tables AS (
+  {{ databricks__get_catalog_tables_sql(information_schema) }}
+  {{ databricks__get_catalog_relations_where_clause_sql(information_schema.database, relations) }}
+),
+columns AS (
+  {{ databricks__get_catalog_columns_sql(information_schema) }}
+  {{ databricks__get_catalog_relations_where_clause_sql(information_schema.database, relations) }}
+)
+{{ databricks__get_catalog_results_sql() }}
+  {%- endset -%}
 
-    {% set query %}
-        with tables as (
-            {{ databricks__get_catalog_tables_sql(information_schema) }}
-            {{ databricks__get_catalog_relations_where_clause_sql(relations) }}
-        ),
-        columns as (
-            {{ databricks__get_catalog_columns_sql(information_schema) }}
-            {{ databricks__get_catalog_relations_where_clause_sql(relations) }}
-        )
-        {{ databricks__get_catalog_results_sql() }}
-    {%- endset -%}
-
-    {{ return(run_query(query)) }}
+  {{ return(run_query(query)) }}
 {%- endmacro %}
 
 {% macro databricks__get_catalog_tables_sql(information_schema) -%}
-    select
-        table_catalog as table_database,
-        table_schema,
-        table_name,
-        lower(table_type) as table_type,
-        comment as table_comment,
-        table_owner,
-        'Last Modified' as `stats:last_modified:label`,
-        last_altered as `stats:last_modified:value`,
-        'The timestamp for last update/change' as `stats:last_modified:description`,
-        (last_altered is not null and table_type not ilike '%VIEW%') as `stats:last_modified:include`
-    from `system`.`information_schema`.`tables`
-    where table_catalog = '{{ information_schema.database|lower }}'
+SELECT
+  table_catalog AS table_database,
+  table_schema,
+  table_name,
+  lower(table_type) AS table_type,
+  comment AS table_comment,
+  table_owner,
+  'Last Modified' AS `stats:last_modified:label`,
+  last_altered AS `stats:last_modified:value`,
+  'The timestamp for last update/change' AS `stats:last_modified:description`,
+  (last_altered IS NOT NULL AND table_type NOT ILIKE '%VIEW%') AS `stats:last_modified:include`
+FROM `system`.`information_schema`.`tables`
 {%- endmacro %}
 
 {% macro databricks__get_catalog_columns_sql(information_schema) -%}
-    select
-        table_catalog as table_database,
-        table_schema,
-        table_name,
-        column_name,
-        ordinal_position as column_index,
-        lower(full_data_type) as column_type,
-        comment as column_comment
-    from `system`.`information_schema`.`columns`
-    where table_catalog = '{{ information_schema.database|lower }}'
+SELECT
+  table_catalog AS table_database,
+  table_schema,
+  table_name,
+  column_name,
+  ordinal_position AS column_index,
+  lower(full_data_type) AS column_type,
+  comment AS column_comment
+FROM `system`.`information_schema`.`columns`
 {%- endmacro %}
 
 {% macro databricks__get_catalog_results_sql() -%}
-    select *
-    from tables
-    join columns using (table_database, table_schema, table_name)
-    order by column_index
+SELECT *
+FROM tables
+JOIN columns USING (table_database, table_schema, table_name)
+ORDER BY column_index
 {%- endmacro %}
 
 {% macro databricks__get_catalog_schemas_where_clause_sql(catalog, schemas) -%}
-    where table_catalog = '{{ catalog|lower }}' and ({%- for relation in schemas -%}
-        table_schema = '{{ relation[1]|lower }}'{%- if not loop.last %} or {% endif -%}
-    {%- endfor -%})
+WHERE table_catalog = '{{ catalog|lower }}' AND (
+  {%- for relation in schemas -%}
+  table_schema = '{{ relation[1]|lower }}'{%- if not loop.last %} OR {% endif -%}
+  {%- endfor -%})
 {%- endmacro %}
 
 
 {% macro databricks__get_catalog_relations_where_clause_sql(catalog, relations) -%}
-    where table_catalog = '{{ catalog|lower }}' and (
-        {%- for relation in relations -%}
-            {% if relation.schema and relation.identifier %}
-                (
-                    table_schema = '{{ relation.schema|lower }}'
-                    and table_name = '{{ relation.identifier|lower }}'
-                )
-            {% elif relation.schema %}
-                (
-                    table_schema = '{{ relation.schema|lower }}'
-                )
-            {% else %}
-                {% do exceptions.raise_compiler_error(
-                    '`get_catalog_relations` requires a list of relations, each with a schema'
-                ) %}
-            {% endif %}
-
-            {%- if not loop.last %} or {% endif -%}
-        {%- endfor -%}
-    )
+WHERE table_catalog = '{{ catalog|lower }}' AND (
+  {%- for relation in relations -%}
+    {%- if relation.schema and relation.identifier %}
+  (
+    table_schema = '{{ relation.schema|lower }}'
+    AND table_name = '{{ relation.identifier|lower }}'
+  )
+    {%- elif relation.schema %}
+  (
+    table_schema = '{{ relation.schema|lower }}'
+  )
+    {% else %}
+      {% do exceptions.raise_compiler_error(
+        '`get_catalog_relations` requires a list of relations, each with a schema'
+      ) %}
+    {% endif %}
+    {%- if not loop.last %} OR {% endif -%}
+  {%- endfor -%}
+)
 {%- endmacro %}
diff --git a/dbt/include/databricks/macros/adapters/columns.sql b/dbt/include/databricks/macros/adapters/columns.sql
index 5a384da7..e9f14068 100644
--- a/dbt/include/databricks/macros/adapters/columns.sql
+++ b/dbt/include/databricks/macros/adapters/columns.sql
@@ -1,29 +1,33 @@
 
 {% macro get_columns_comments(relation) -%}
-  {% call statement('get_columns_comments', fetch_result=True) -%}
-    describe table {{ relation|lower }}
-  {% endcall %}
+  {{ return(run_query(get_columns_comments_sql(relation))) }}
+{% endmacro %}
 
-  {% do return(load_result('get_columns_comments').table) %}
+{% macro get_columns_comments_sql(relation) %}
+DESCRIBE TABLE {{ relation.render() }}
 {% endmacro %}
 
 {% macro get_columns_comments_via_information_schema(relation) -%}
   {% call statement('repair_table', fetch_result=False) -%}
-    REPAIR TABLE {{ relation|lower }} SYNC METADATA
-  {% endcall %}
-  {% call statement('get_columns_comments_via_information_schema', fetch_result=True) -%}
-    select
-      column_name,
-      full_data_type,
-      comment
-    from `system`.`information_schema`.`columns`
-    where
-      table_catalog = '{{ relation.database|lower }}' and
-      table_schema = '{{ relation.schema|lower }}' and 
-      table_name = '{{ relation.identifier|lower }}'
+    {{ repair_table_sql(relation) }}
   {% endcall %}
+  {{ return(run_query(get_columns_comments_via_information_schema_sql(relation))) }}
+{% endmacro %}
+
+{% macro repair_table_sql(relation) %}
+REPAIR TABLE {{ relation.render() }} SYNC METADATA
+{% endmacro %}
 
-  {% do return(load_result('get_columns_comments_via_information_schema').table) %}
+{% macro get_columns_comments_via_information_schema_sql(relation) %}
+SELECT
+  column_name,
+  full_data_type,
+  comment
+FROM `system`.`information_schema`.`columns`
+WHERE
+  table_catalog = '{{ relation.database|lower }}' and
+  table_schema = '{{ relation.schema|lower }}' and 
+  table_name = '{{ relation.identifier|lower }}'
 {% endmacro %}
 
 {% macro databricks__alter_relation_add_remove_columns(relation, add_columns, remove_columns) %}
@@ -32,32 +36,24 @@
       {{ exceptions.raise_compiler_error('Delta format required for dropping columns from tables') }}
     {% endif %}
     {%- call statement('alter_relation_remove_columns') -%}
-      ALTER TABLE {{ relation.render() }} DROP COLUMNS ({{ api.Column.format_remove_column_list(remove_columns) }})
+      {{ drop_columns_sql(relation, remove_columns) }}
     {%- endcall -%}
   {% endif %}
 
   {% if add_columns %}
+    {% if not relation.is_delta %}
+      {{ exceptions.raise_compiler_error('Delta format required for dropping columns from tables') }}
+    {% endif %}
     {%- call statement('alter_relation_add_columns') -%}
-      ALTER TABLE {{ relation.render() }} ADD COLUMNS ({{ api.Column.format_add_column_list(add_columns) }})
+      {{ add_columns_sql(relation, add_columns) }}
     {%- endcall -%}
   {% endif %}
 {% endmacro %}
 
-{% macro databricks__get_empty_subquery_sql(select_sql, select_sql_header=none) %}
-  {%- if select_sql_header is not none -%}
-  {{ select_sql_header }}
-  {%- endif %}
-  select * from (
-    {{ select_sql }}
-  ) as __dbt_sbq
-  where false
-  limit 0
+{% macro drop_columns_sql(relation, remove_columns) %}
+ALTER TABLE {{ relation.render() }} DROP COLUMNS ({{ api.Column.format_remove_column_list(remove_columns) }})
 {% endmacro %}
 
-{% macro databricks__get_columns_in_query(select_sql) %}
-  {{ log("Getting column information via empty query")}}
-  {% call statement('get_columns_in_query', fetch_result=True) -%}
-     {{ get_empty_subquery_sql(select_sql) }}
-  {% endcall %}
-  {{ return(load_result('get_columns_in_query').table.columns | map(attribute='name') | list) }}
-{% endmacro %}
\ No newline at end of file
+{% macro add_columns_sql(relation, add_columns) %}
+ALTER TABLE {{ relation.render() }} ADD COLUMNS ({{ api.Column.format_add_column_list(add_columns) }})
+{% endmacro %}


Test patch:
diff --git a/tests/unit/macros/adapters/test_catalog_macros.py b/tests/unit/macros/adapters/test_catalog_macros.py
new file mode 100644
index 00000000..80fe92d4
--- /dev/null
+++ b/tests/unit/macros/adapters/test_catalog_macros.py
@@ -0,0 +1,272 @@
+from unittest.mock import MagicMock, Mock
+
+import pytest
+
+from tests.unit.macros.base import MacroTestBase
+
+
+class TestCatalogMacros(MacroTestBase):
+    @pytest.fixture(scope="class")
+    def template_name(self) -> str:
+        return "adapters/catalog.sql"
+
+    @pytest.fixture(scope="class")
+    def macro_folders_to_load(self) -> list:
+        return ["macros", "macros/adapters"]
+
+    def test_get_catalog_tables_sql(self, template_bundle):
+        """Test the SQL generation for catalog tables query"""
+        information_schema = {"database": "test_catalog"}
+
+        result = self.run_macro(
+            template_bundle.template, "databricks__get_catalog_tables_sql", information_schema
+        )
+
+        expected = """
+            select
+                table_catalog as table_database,
+                table_schema,
+                table_name,
+                lower(table_type) as table_type,
+                comment as table_comment,
+                table_owner,
+                'Last Modified' as `stats:last_modified:label`,
+                last_altered as `stats:last_modified:value`,
+                'The timestamp for last update/change' as `stats:last_modified:description`,
+                (last_altered is not null and table_type not ilike '%VIEW%') as
+                   `stats:last_modified:include`
+            from `system`.`information_schema`.`tables`
+        """
+
+        assert result == self.clean_sql(expected)
+
+    def test_get_catalog_columns_sql(self, template_bundle):
+        """Test the SQL generation for catalog columns query"""
+        information_schema = {"database": "test_catalog"}
+
+        result = self.run_macro(
+            template_bundle.template, "databricks__get_catalog_columns_sql", information_schema
+        )
+
+        expected = """
+            select
+                table_catalog as table_database,
+                table_schema,
+                table_name,
+                column_name,
+                ordinal_position as column_index,
+                lower(full_data_type) as column_type,
+                comment as column_comment
+            from `system`.`information_schema`.`columns`
+        """
+
+        assert result == self.clean_sql(expected)
+
+    def test_get_catalog_results_sql(self, template_bundle):
+        """Test the SQL generation for joining tables and columns"""
+        result = self.run_macro(template_bundle.template, "databricks__get_catalog_results_sql")
+
+        expected = """
+            select *
+            from tables
+            join columns using (table_database, table_schema, table_name)
+            order by column_index
+        """
+
+        assert result == self.clean_sql(expected)
+
+    def test_get_catalog_schemas_where_clause_sql(self, template_bundle):
+        """Test the SQL generation for schemas where clause"""
+        catalog = "test_catalog"
+        schemas = [["catalog_name", "schema1"], ["catalog_name", "schema2"]]
+
+        result = self.run_macro(
+            template_bundle.template,
+            "databricks__get_catalog_schemas_where_clause_sql",
+            catalog,
+            schemas,
+        )
+
+        expected = """
+            where table_catalog = 'test_catalog'
+            and (
+                table_schema = 'schema1' or
+                table_schema = 'schema2'
+            )
+        """
+
+        assert result == self.clean_sql(expected)
+
+    def test_get_catalog_relations_where_clause_sql(self, template_bundle):
+        """Test the SQL generation for relations where clause"""
+        # Create mock relations
+        relation1 = MagicMock()
+        relation1.schema = "test_schema1"
+        relation1.identifier = "test_table1"
+
+        relation2 = MagicMock()
+        relation2.schema = "test_schema2"
+        relation2.identifier = "test_table2"
+
+        relations = [relation1, relation2]
+        catalog = "test_catalog"
+
+        result = self.run_macro(
+            template_bundle.template,
+            "databricks__get_catalog_relations_where_clause_sql",
+            catalog,
+            relations,
+        )
+
+        expected = """
+            where table_catalog = 'test_catalog'
+            and (
+                (table_schema = 'test_schema1' and table_name = 'test_table1') or
+                (table_schema = 'test_schema2' and table_name = 'test_table2')
+            )
+        """
+
+        assert result == self.clean_sql(expected)
+
+    def test_get_catalog(self, template_bundle, context):
+        """Test the full get_catalog macro with mocked run_query"""
+        information_schema = {"database": "test_catalog"}
+        schemas = [["catalog_name", "schema1"], ["catalog_name", "schema2"]]
+
+        # Mock run_query to return a dummy result
+        mock_result = Mock()
+        context["run_query"] = Mock(return_value=mock_result)
+
+        self.run_macro_raw(
+            template_bundle.template, "databricks__get_catalog", information_schema, schemas
+        )
+
+        # Verify run_query was called once and capture the SQL
+        context["run_query"].assert_called_once()
+        sql_query = context["run_query"].call_args[0][0]
+
+        # Check complete SQL structure
+        expected_sql = """
+            with tables as (
+                select
+                    table_catalog as table_database,
+                    table_schema,
+                    table_name,
+                    lower(table_type) as table_type,
+                    comment as table_comment,
+                    table_owner,
+                    'Last Modified' as `stats:last_modified:label`,
+                    last_altered as `stats:last_modified:value`,
+                    'The timestamp for last update/change' as `stats:last_modified:description`,
+                    (last_altered is not null and table_type not ilike '%VIEW%') as
+                    `stats:last_modified:include`
+                from `system`.`information_schema`.`tables`
+                where table_catalog = 'test_catalog'
+                and (
+                    table_schema = 'schema1' or
+                    table_schema = 'schema2'
+                )
+            ),
+
+            columns as (
+                select
+                    table_catalog as table_database,
+                    table_schema,
+                    table_name,
+                    column_name,
+                    ordinal_position as column_index,
+                    lower(full_data_type) as column_type,
+                    comment as column_comment
+                from `system`.`information_schema`.`columns`
+                where table_catalog = 'test_catalog'
+                and (
+                    table_schema = 'schema1' or
+                    table_schema = 'schema2'
+                )
+            )
+
+            select *
+            from tables
+            join columns using (table_database, table_schema, table_name)
+            order by column_index
+        """
+
+        assert self.clean_sql(sql_query) == self.clean_sql(expected_sql)
+
+    def test_get_catalog_relations(self, template_bundle, context):
+        """Test the get_catalog_relations macro with mocked run_query"""
+        information_schema = {"database": "test_catalog"}
+
+        # Create mock relations
+        relation1 = MagicMock()
+        relation1.schema = "test_schema1"
+        relation1.identifier = "test_table1"
+
+        relation2 = MagicMock()
+        relation2.schema = "test_schema2"
+        relation2.identifier = "test_table2"
+
+        relations = [relation1, relation2]
+
+        # Mock run_query to return a dummy result
+        mock_result = Mock()
+        context["run_query"] = Mock(return_value=mock_result)
+
+        self.run_macro_raw(
+            template_bundle.template,
+            "databricks__get_catalog_relations",
+            information_schema,
+            relations,
+        )
+
+        # Verify run_query was called once and capture the SQL
+        context["run_query"].assert_called_once()
+        sql_query = context["run_query"].call_args[0][0]
+
+        # Check complete SQL structure
+        expected_sql = """
+            with tables as (
+                select
+                    table_catalog as table_database,
+                    table_schema,
+                    table_name,
+                    lower(table_type) as table_type,
+                    comment as table_comment,
+                    table_owner,
+                    'Last Modified' as `stats:last_modified:label`,
+                    last_altered as `stats:last_modified:value`,
+                    'The timestamp for last update/change' as `stats:last_modified:description`,
+                    (last_altered is not null and table_type not ilike '%VIEW%') as
+                    `stats:last_modified:include`
+                from `system`.`information_schema`.`tables`
+                where table_catalog = 'test_catalog'
+                and (
+                    (table_schema = 'test_schema1' and table_name = 'test_table1') or
+                    (table_schema = 'test_schema2' and table_name = 'test_table2')
+                )
+            ),
+
+            columns as (
+                select
+                    table_catalog as table_database,
+                    table_schema,
+                    table_name,
+                    column_name,
+                    ordinal_position as column_index,
+                    lower(full_data_type) as column_type,
+                    comment as column_comment
+                from `system`.`information_schema`.`columns`
+                where table_catalog = 'test_catalog'
+                and (
+                    (table_schema = 'test_schema1' and table_name = 'test_table1') or
+                    (table_schema = 'test_schema2' and table_name = 'test_table2')
+                )
+            )
+
+            select *
+            from tables
+            join columns using (table_database, table_schema, table_name)
+            order by column_index
+        """
+
+        assert self.clean_sql(sql_query) == self.clean_sql(expected_sql)
diff --git a/tests/unit/macros/adapters/test_columns_macros.py b/tests/unit/macros/adapters/test_columns_macros.py
new file mode 100644
index 00000000..f8fd9715
--- /dev/null
+++ b/tests/unit/macros/adapters/test_columns_macros.py
@@ -0,0 +1,91 @@
+from unittest.mock import MagicMock, Mock
+
+import pytest
+
+from tests.unit.macros.base import MacroTestBase
+
+
+class TestColumnsMacros(MacroTestBase):
+    @pytest.fixture(scope="class")
+    def template_name(self) -> str:
+        return "adapters/columns.sql"
+
+    @pytest.fixture(scope="class")
+    def macro_folders_to_load(self) -> list:
+        return ["macros", "macros/adapters"]
+
+    @pytest.fixture
+    def mock_relation(self):
+        relation = Mock()
+        relation.database = "test_db"
+        relation.schema = "test_schema"
+        relation.identifier = "test_table"
+        relation.render = Mock(return_value="`test_db`.`test_schema`.`test_table`")
+        relation.is_delta = True
+        return relation
+
+    def test_get_columns_comments_sql(self, template_bundle, mock_relation):
+        result = self.run_macro(template_bundle.template, "get_columns_comments_sql", mock_relation)
+
+        expected_sql = "DESCRIBE TABLE `test_db`.`test_schema`.`test_table`"
+        assert result == self.clean_sql(expected_sql)
+
+    def test_repair_table_sql(self, template_bundle, mock_relation):
+        result = self.run_macro(template_bundle.template, "repair_table_sql", mock_relation)
+
+        expected_sql = "REPAIR TABLE `test_db`.`test_schema`.`test_table` SYNC METADATA"
+        assert result == self.clean_sql(expected_sql)
+
+    def test_get_columns_comments_via_information_schema_sql(self, template_bundle, mock_relation):
+        result = self.run_macro(
+            template_bundle.template,
+            "get_columns_comments_via_information_schema_sql",
+            mock_relation,
+        )
+
+        # Note the lowercase in the WHERE clause due to |lower filters
+        expected_sql = """
+            SELECT
+              column_name,
+              full_data_type,
+              comment
+            FROM `system`.`information_schema`.`columns`
+            WHERE
+              table_catalog = 'test_db' and
+              table_schema = 'test_schema' and
+              table_name = 'test_table'
+        """
+
+        assert result == self.clean_sql(expected_sql)
+
+    def test_drop_columns_sql(self, template_bundle, context, mock_relation):
+        """Test drop_columns_sql macro"""
+        # Mock Column.format_remove_column_list
+        context["api"] = MagicMock()
+        context["api"].Column.format_remove_column_list = Mock(return_value="col1, col2")
+
+        remove_columns = ["col1", "col2"]
+
+        result = self.run_macro(
+            template_bundle.template, "drop_columns_sql", mock_relation, remove_columns
+        )
+
+        expected_sql = "ALTER TABLE `test_db`.`test_schema`.`test_table` DROP COLUMNS (col1, col2)"
+        assert self.clean_sql(result) == self.clean_sql(expected_sql)
+
+    def test_add_columns_sql(self, template_bundle, context, mock_relation):
+        """Test add_columns_sql macro"""
+        # Mock Column.format_add_column_list
+        context["api"] = MagicMock()
+        context["api"].Column.format_add_column_list = Mock(return_value="col1 INT, col2 STRING")
+
+        add_columns = ["col1", "col2"]
+
+        result = self.run_macro(
+            template_bundle.template, "add_columns_sql", mock_relation, add_columns
+        )
+
+        expected_sql = (
+            "ALTER TABLE `test_db`.`test_schema`.`test_table` ADD COLUMNS (col1 INT, col2 STRING)"
+        )
+        assert self.clean_sql(result) == self.clean_sql(expected_sql)
diff --git a/tests/unit/macros/base.py b/tests/unit/macros/base.py
index 11bf3383..6f78e94e 100644
--- a/tests/unit/macros/base.py
+++ b/tests/unit/macros/base.py
@@ -208,8 +208,19 @@ class MacroTestBase:
         Run the named macro from a template, and return the rendered value.
         This version strips off extra whitespace and newlines.
         """
-        value = self.run_macro_raw(template, name, *args)
-        return re.sub(r"\s\s+", " ", value).strip()
+        return self.clean_sql(self.run_macro_raw(template, name, *args))
+
+    def clean_sql(self, sql):
+        """Helper method to normalize SQL for comparison"""
+
+        sql = sql.lower()
+        # Remove extra whitespace, newlines, and standardize spacing
+        sql = re.sub(r"\s+", " ", sql).strip()
+        # Normalize multiple spaces to single spaces
+        sql = re.sub(r" +", " ", sql)
+        sql = sql.replace("( ", "(")
+        sql = sql.replace(" )", ")")
+        return sql
 
     def render_bundle(self, template_bundle, name, *args):
         """
diff --git a/tests/unit/macros/relations/test_constraint_macros.py b/tests/unit/macros/relations/test_constraint_macros.py
index 4ee67c4c..398d63b8 100644
--- a/tests/unit/macros/relations/test_constraint_macros.py
+++ b/tests/unit/macros/relations/test_constraint_macros.py
@@ -417,7 +417,7 @@ class TestConstraintMacros(MacroTestBase):
             "['alter table `some_database`.`some_schema`.`some_table` add constraint "
             "myconstraint PRIMARY KEY(valid_at TIMESERIES);']"
         )
-        assert expected in r
+        assert self.clean_sql(expected) in r
 
     def test_macros_get_constraint_sql_custom_noname_constraint(self, template_bundle, model):
         constraint = {
@@ -431,7 +431,7 @@ class TestConstraintMacros(MacroTestBase):
             "add constraint hash(some_table;PRIMARY KEY(valid_at TIMESERIES);) "
             "PRIMARY KEY(valid_at TIMESERIES);']"
         )  # noqa: E501
-        assert expected in r
+        assert self.clean_sql(expected) in r
 
     def test_macros_get_constraint_sql_custom_missing_expression(self, template_bundle, model):
         constraint = {
diff --git a/tests/unit/macros/relations/test_optimize_macros.py b/tests/unit/macros/relations/test_optimize_macros.py
index b64150d3..4e8e235f 100644
--- a/tests/unit/macros/relations/test_optimize_macros.py
+++ b/tests/unit/macros/relations/test_optimize_macros.py
@@ -22,7 +22,7 @@ class TestOptimizeMacros(MacroTestBase):
         config["zorder"] = ["foo", "bar"]
         sql = self.render_bundle(template_bundle, "get_optimize_sql")
 
-        assert sql == "optimize `some_database`.`some_schema`.`some_table` zorder by ( foo, bar )"
+        assert sql == "optimize `some_database`.`some_schema`.`some_table` zorder by (foo, bar)"
 
     def test_macros_optimize_with_extraneous_info(self, config, var, template_bundle):
         config["zorder"] = ["foo", "bar"]
diff --git a/tests/unit/macros/relations/test_query_macros.py b/tests/unit/macros/relations/test_query_macros.py
index dfda5ee4..34d698d8 100644
--- a/tests/unit/macros/relations/test_query_macros.py
+++ b/tests/unit/macros/relations/test_query_macros.py
@@ -22,7 +22,7 @@ class TestAlterQuery(MacroTestBase):
 
     def test_macros__alter_query(self, template_bundle):
         sql = self.render_alter_query(template_bundle)
-        expected = (
+        expected = self.clean_sql(
             f"ALTER {str(template_bundle.relation.type).upper()}"
             f" {template_bundle.relation.render()} AS ( select 1 )"
         )
diff --git a/tests/unit/macros/relations/test_table_macros.py b/tests/unit/macros/relations/test_table_macros.py
index f05dc9d4..8392cc08 100644
--- a/tests/unit/macros/relations/test_table_macros.py
+++ b/tests/unit/macros/relations/test_table_macros.py
@@ -49,10 +49,10 @@ class TestCreateTableAs(MacroTestBase):
             "delta.universalFormat.enabledFormats": "iceberg",
         }
         sql = self.render_create_table_as(template_bundle)
-        assert sql == (
+        assert sql == self.clean_sql(
             f"create or replace table {template_bundle.relation} using delta"
             " tblproperties ('delta.enableIcebergCompatV2' = 'true' , "
-            "'delta.universalFormat.enabledFormats' = 'iceberg' ) as select 1"
+            "'delta.universalFormat.enabledFormats' = 'iceberg') as select 1"
         )
 
     @pytest.mark.parametrize("format", ["parquet", "hudi"])
@@ -69,7 +69,7 @@ class TestCreateTableAs(MacroTestBase):
     def test_macros_create_table_as_options(self, config, template_bundle):
         config["options"] = {"compression": "gzip"}
         sql = self.render_create_table_as(template_bundle)
-        expected = (
+        expected = self.clean_sql(
             f"create or replace table {template_bundle.relation} "
             'using delta options (compression "gzip" ) as select 1'
         )
@@ -82,7 +82,7 @@ class TestCreateTableAs(MacroTestBase):
         config["location_root"] = "/mnt/root"
         sql = self.render_create_table_as(template_bundle, sql="select 1 as id")
 
-        expected = (
+        expected = self.clean_sql(
             f'create table {template_bundle.relation} using hudi options (primaryKey "id" ) '
             f"location '/mnt/root/{template_bundle.relation.identifier}'"
             " as select 1 as id"
@@ -99,7 +99,7 @@ class TestCreateTableAs(MacroTestBase):
         config["location_root"] = "/mnt/root"
         sql = self.render_create_table_as(template_bundle, sql="select 1 as id")
 
-        expected = (
+        expected = self.clean_sql(
             f'create table {template_bundle.relation} using hudi options (primaryKey "id" ) '
             f"location '/mnt/root/{template_bundle.relation.identifier}'"
             " as select 1 as id"
@@ -162,7 +162,7 @@ class TestCreateTableAs(MacroTestBase):
     def test_macros_create_table_as_liquid_cluster(self, config, template_bundle):
         config["liquid_clustered_by"] = "cluster_1"
         sql = self.render_create_table_as(template_bundle)
-        expected = (
+        expected = self.clean_sql(
             f"create or replace table {template_bundle.relation} using"
             " delta CLUSTER BY (cluster_1) as select 1"
         )
@@ -173,7 +173,7 @@ class TestCreateTableAs(MacroTestBase):
         config["liquid_clustered_by"] = ["cluster_1", "cluster_2"]
         config["buckets"] = "1"
         sql = self.render_create_table_as(template_bundle)
-        expected = (
+        expected = self.clean_sql(
             f"create or replace table {template_bundle.relation} "
             "using delta CLUSTER BY (cluster_1, cluster_2) as select 1"
         )
@@ -183,7 +183,7 @@ class TestCreateTableAs(MacroTestBase):
     def test_macros_create_table_as_liquid_cluster_auto(self, config, template_bundle):
         config["auto_liquid_cluster"] = True
         sql = self.render_create_table_as(template_bundle)
-        expected = (
+        expected = self.clean_sql(
             f"create or replace table {template_bundle.relation} using"
             " delta CLUSTER BY AUTO as select 1"
         )
@@ -196,7 +196,7 @@ class TestCreateTableAs(MacroTestBase):
 
         sql = self.render_create_table_as(template_bundle)
 
-        expected = (
+        expected = self.clean_sql(
             f"create or replace table {template_bundle.relation} "
             "using delta comment 'Description Test' as select 1"
         )
@@ -218,7 +218,7 @@ class TestCreateTableAs(MacroTestBase):
         config["file_format"] = "delta"
         sql = self.render_create_table_as(template_bundle)
 
-        expected = (
+        expected = self.clean_sql(
             f"create or replace table {template_bundle.relation} "
             "using delta "
             "partitioned by (partition_1,partition_2) "
@@ -246,7 +246,7 @@ class TestCreateTableAs(MacroTestBase):
         config["file_format"] = "hudi"
         sql = self.render_create_table_as(template_bundle)
 
-        expected = (
+        expected = self.clean_sql(
             f"create table {template_bundle.relation} "
             "using hudi "
             "partitioned by (partition_1,partition_2) "
diff --git a/tests/unit/macros/relations/test_tags_macros.py b/tests/unit/macros/relations/test_tags_macros.py
index 77035386..ea4aed06 100644
--- a/tests/unit/macros/relations/test_tags_macros.py
+++ b/tests/unit/macros/relations/test_tags_macros.py
@@ -14,7 +14,7 @@ class TestTagsMacros(MacroTestBase):
 
     def test_macros_fetch_tags_sql(self, template_bundle):
         sql = self.render_bundle(template_bundle, "fetch_tags_sql")
-        expected = (
+        expected = self.clean_sql(
             "SELECT tag_name, tag_value "
             "FROM `system`.`information_schema`.`table_tags` "
             "WHERE catalog_name = 'some_database'"
@@ -24,7 +24,7 @@ class TestTagsMacros(MacroTestBase):
 
     def test_macros_alter_set_tags(self, template_bundle):
         sql = self.render_bundle(template_bundle, "alter_set_tags", {"a": "valA", "b": "valB"})
-        expected = (
+        expected = self.clean_sql(
             "ALTER view `some_database`.`some_schema`.`some_table` "
             "SET TAGS ( 'a' = 'valA', 'b' = 'valB' )"
         )
@@ -33,6 +33,8 @@ class TestTagsMacros(MacroTestBase):
 
     def test_macros_alter_unset_tags(self, template_bundle):
         sql = self.render_bundle(template_bundle, "alter_unset_tags", ["a", "b"])
-        expected = "ALTER view `some_database`.`some_schema`.`some_table` UNSET TAGS ( 'a','b' )"
+        expected = self.clean_sql(
+            "ALTER view `some_database`.`some_schema`.`some_table` UNSET TAGS ( 'a','b' )"
+        )
 
         assert sql == expected
diff --git a/tests/unit/macros/relations/test_view_macros.py b/tests/unit/macros/relations/test_view_macros.py
index e206a819..8a279143 100644
--- a/tests/unit/macros/relations/test_view_macros.py
+++ b/tests/unit/macros/relations/test_view_macros.py
@@ -29,7 +29,7 @@ class TestCreateViewAs(MacroTestBase):
         sql = self.render_create_view_as(template_bundle)
         expected = (
             f"create or replace view {template_bundle.relation} "
-            "tblproperties ('tblproperties_to_view' = 'true' ) as ( select 1 )"
+            "tblproperties ('tblproperties_to_view' = 'true') as (select 1)"
         )
 
         assert sql == expected