Add support model contracts

[tglunde]

No description provided.

[marvinfromblueforte]

With the introduction of model contracts, there are now two places to define the structure (especially column order) for a model: YAML and SQL. Here's a quick analysis on what that means:

If model contracts are not enforced, the database handles the DDL according to its interpretation of the model SQL. That has been the default behavior.

If model contracts are enforced, dbt-core completely relies on the YAML config when generating the model DDL (see code comment here).

I don't find that very intuitive because dbt could just as easily read the column order by parsing the model.sql. It needs to do that anyway to check whether SQL columns are equivalent to YAML columns. But since the goal is to release 1.5 quickly, I would prefer to keep it the way dbt-core intended it for now.

Additional considerations:

dbt's contract enforcement also is very strict right now and requires YAML and SQL to be perfectly aligned. If the column order differs, if a column is missing or differently named in the SQL, the model run will fail + roll back. Even if a dbt user only wants to enforce some simple constraints (e.g. a single primary key on a wide table), they'll have no choice but to maintain their entire model structure both in YAML and SQL.

Apart from that, I foresee some frustration due to Exasol's internal casting behavior (CHAR vs. VARCHAR), because many contracts will fail if string and numerical columns aren't explicitly casted everywhere in SQL. I hope this can be addressed in a future core release to allow some kind of mapping between accepted data types.