feat(redshift): add profile option to drop relations without CASCADE

[colin-rogers-dbt]

Summary

Add a profile credentials option (drop_without_cascade or similar) to omit CASCADE from DROP TABLE/VIEW/MATERIALIZED VIEW statements in dbt-redshift.

Background

All three dbt-redshift drop macros unconditionally append CASCADE:

macros/relations/table/drop.sql (line 2):

drop table if exists {{ relation }} cascade

macros/relations/view/drop.sql (line 2):

drop view if exists {{ relation }} cascade

macros/relations/materialized_view/drop.sql (line 2):

drop materialized view if exists {{ relation }} cascade

CASCADE instructs Redshift to automatically drop all dependent objects (views, etc.). On clusters with many objects, Redshift must walk the full dependency graph to resolve each DROP ... CASCADE, which is expensive. For users who exclusively use unbound views (or have no downstream views at all), there are no dependents to cascade to — the overhead is pure waste.

Additionally, CASCADE is the root cause of the concurrent-transaction race condition that requires the global thread lock (see related issue). Removing CASCADE also opens the door to removing the lock.

Proposed Change

Add a boolean profile credential field drop_without_cascade (default False) that, when True, emits DROP ... RESTRICT (or simply omits CASCADE) instead.

Files to change

dbt-redshift/src/dbt/adapters/redshift/connections.py

Add to RedshiftCredentials dataclass (~line 190):

# Drop behavior
drop_without_cascade: bool = False

Add to _connection_keys() (~line 228):

"drop_without_cascade",

macros/relations/table/drop.sql:

{%- macro redshift__drop_table(relation) -%}
    drop table if exists {{ relation }} {% if not adapter.config.credentials.drop_without_cascade %}cascade{% endif %}
{%- endmacro -%}

macros/relations/view/drop.sql:

{%- macro redshift__drop_view(relation) -%}
    drop view if exists {{ relation }} {% if not adapter.config.credentials.drop_without_cascade %}cascade{% endif %}
{%- endmacro -%}

macros/relations/materialized_view/drop.sql:

{% macro redshift__drop_materialized_view(relation) -%}
    drop materialized view if exists {{ relation }} {% if not adapter.config.credentials.drop_without_cascade %}cascade{% endif %}
{%- endmacro %}

Note: The exact mechanism for passing credentials into Jinja macros should follow the existing pattern used by redshift__use_show_apis() in macros/metadata/helpers.sql. Alternatively this could be implemented as a Python-level override of the drop macros using execute() directly in impl.py.

Safety

• Default is False — no change in behavior for existing users.
• Safe only for projects that don't have dependent views downstream of dropped relations (e.g., projects using only unbound views, or where dbt manages all dependencies).
• Must be documented with a clear warning: if a dependent object exists and CASCADE is omitted, Redshift will raise an error rather than silently dropping it.
• Pairs naturally with drop_without_lock (see related issue): users who set both get maximum DROP performance with minimal risk when their project has no downstream view dependencies.

Related

• Related to the drop_without_lock feature request — the thread lock exists specifically because of CASCADE race conditions.