[CT-1355] Add column type constraints as dbt native configs

[sungchun12]

Problem:

• Data types are created after the fact(assumed by the database) and/or a dbt user has to explicitly cast data types in their SQL. Because of this, there isn't much incentive to assure dbt models have expected data types for consumers to depend on(it's tedious work :/). This may not be a problem for 50 dbt models, but becomes a big problem when you have 2000+ dbt models. There should be an easier way to configure data type constraints for any dbt models and provide options to enable/disable data type enforcement(think: what mypy does for the python static typing experience).

Research:

• Postgres Constraints: https://www.postgresql.org/docs/current/ddl-constraints.html#id-1.5.4.6.6
• Snowflake ONLY enforces not null constraints, the rest are nice to have metadata: https://docs.snowflake.com/en/sql-reference/constraints-overview.html#supported-constraint-types
• BigQuery Macro Demo: https://www.loom.com/share/1f1f190e66254d12984962c613e8082d

Considerations:

• have the configs embedded in dbt-core and then each adapter can take stable configs to adjust table materialization macros
• Enforce column positions based on schema config
• dbt-core contains the hub of native configs while adapters own specific implementations as each database has nuances to which enforceable constraints are valid.
• think about not null and default values for a table
• Work with Jon Neo from Canva
• Include a check constraint as pgsql enables it?

Team:

• @jonathanneo
• Need one more person

[sungchun12]

Related discussion: #5244 (reply in thread)

[sungchun12]

Example BigQuery Constraints Macro:https://gist.github.com/sungchun12/f7ea081773ae824a83294649530d6e41

[sungchun12]

What if constraints could be defined once within a subfolder path in dbt_project.yml and then in automatically creates not null constraints in the DDL?

[sungchun12]

Closing in favor of this: #6079