[CT-2199] Generalize constraint compatibility warnings

[MichelleArk]

Refinement discussion: #6750

Currently, constraints that are set but unenforceable or unsupported raise warnings in adapter-specific jinja macros. For example: snowflake__get_columns_spec_ddl in dbt-snowflake.

To generalize this warning mechanism, we'll need a new adapter method that can tell dbt whether a constraint is:

1. supported & enforced
2. supported but not enforced
3. not supported

Here's what that might look like for dbt-snowflake, in pseudo code:

@staticmethod
def constraint_support:
    return {
        "not_null": ConstraintSupport.ENFORCED,
        "primary_key": ConstraintSupport.NOT_ENFORCED,
        "foreign_key": ConstraintSupport.NOT_ENFORCED,
        "unique": ConstraintSupport.NOT_ENFORCED,
        "check": ConstraintSupport.NOT_SUPPORTED,
    }

This mapping could be a static method, or a property, or maybe just a constant class attribute. As long as subclasses of BaseAdapters are able to overwrite it. This mapping should also not be available in the jinja context as it's intended for use within the python adapter implementation and not by end users in user space (jinja).

This method would be called in order to determine whether to raise warnings for unenforced/unsupported constraints (unless the user has disabled via warn_unsupported or warn_unenforced (#7066)). Ideally, warnings would be raised at runtime - during materialization, for legible user-facing logs. The actual method that raises warnings could also be implemented in python, on the adapter, calling warn_or_error and firing an event under the hood. It should be used to replace the existing adapter-specific warning mechanisms.

The constraint_support method should be implemented for each adapter, but the warning implementation should be generalized and only implemented in dbt-core.

---

Is blocked by:

• [CT-2198] Unify constraints and constraints_check configs #7066

Blocks:

• [CT-2197] [Feature] Detect breaking changes to constraints in state:modified check #7065

[jtcohen6]

Adapter-specific support

The default implementation of this method should be the one shown in the description above. This should hold true for dbt-redshift and dbt-snowflake, and seems to be the standard pattern for most analytical data platforms.

constraint type	support
not_null	enforced
primary_key	not enforced
foreign_key	not enforced
unique	not enforced
check	not supported

For dbt-bigquery:
Updated given recent BQ support for primary_key + foreign_key

constraint type	support
not_null	enforced
primary_key	not supported supported
foreign_key	not supported supported
unique	not supported
check	not supported

For dbt-postgres:

constraint type	support
not_null	enforced
primary_key	enforced
foreign_key	enforced
unique	enforced
check	enforced

For dbt-spark/dbt-databricks: not_null and check constraints are supported and enforced. In the current implementation, however, none of these constraints is enforced before a model builds. I see this more as a platform limitation that we should document, and work with the Databricks team to improve. Still, we shouldn't include these as part of the "model contract" while they aren't things we can actually enforce at build time. So, for now, we should consider them "supported, not enforced." In a future version where we can supply column specs within atomic create table as statements, we can flip them on as enforced.

constraint type	support
not_null	not enforced
primary_key	not enforced
foreign_key	not enforced
unique	not enforced
check	not enforced

---

This method doesn't include anything for the type: custom of ConstraintOption, which we're including in the spec (#7066). Whatever that might be, it's bound to be specific to someone's very special data platform, and the adapter for that data platform should include it in this method.

[peterallenwebb]

Is there any need to distinguish "supported/enforced on a column" from "supported/enforced on a model" for the purposes of this case, or is that reliably a none-or-both proposition across databases?

[jtcohen6]

Is there any need to distinguish "supported on a column" from "supported on a model" for the purposes of this case, or is that reliably a none-or-both proposition across databases?

Good thought! It's reliably a none-or-both proposition.

(When I first read your question, I thought it was asking something slightly different: Are there any constraints that cannot be defined at both column & model level? The answer there is "yes," not_null constraints can only be defined at the column level.)

[jtcohen6]

If specified, a custom-type constraint should always be presumed to be supported and not enforced