Align on T-SQL Model DDL Changes

[cmgoffena13]

Creating this issue to huddle on the issues regarding T-SQL and model DDL. @fresioAS @mday-io @albertosuman-1k5 @StuffbyYuki

Diagnosis

Initial Issue: #5773
PR that was merged for this issue: #5864

The initial fix touched sqlmesh/core/dialect.py and modified global behavior for model DDL. This fix did not have enough tests for what it actually modified. Blast radius of this change is too big.

This change introduces a myriad of issues once format is applied and plan is triggered:

1. DATETIME2 / SMALLDATETIME now gets converted to ROWVERSION on load for SQL Server, which directly impacts data loads due to schema change. This introduces bugs into SCD_TYPE_2 models. Errors on inserts/comparisons.
2. NVARCHAR / NCHAR now gets converted to VARCHAR / CHAR on load for SQL Server, causing data corruption with missing Unicode data.
3. TINYINT now gets converted to UTINYINT on load for SQL Server, which is incompatible. Will break plan.
4. BigQuery models can now be corrupted. Issue: sqlmesh format corrupts MODEL meta expressions (ignored_rules + DATE_TRUNC for BigQuery) #6035
5. Snowflake can have backfills triggered due to DATE_TRUNC and fingerprints changing.

A fix for T-SQL expanded into affecting two other engines (and possibly more given the reach of the change).

Proposal

1. The initial fix has too much blast radius. Instead of trying to move forward and work around this change, we should revert the change that makes model DDL dialect=None. A fix for T-SQL should not affect other engines.
2. We work to address the original issue, booleans in T-SQL. There is already code in the codebase that works with the gross (1 = 1) in some places. We should make a precision change to address T-SQL specifically. Microsoft / T-SQL is not very great at playing with others and is a special case that should be isolated.

Something to consider here: @fresioAS has already upgraded to this initial fix, changing all of his models to align to the recent fix. I don't know all of the syntax changes in the files, but we need to come up with "migration" steps to ensure that anyone that has upgraded and formatted in T-SQL can have their models return to a good state.

Ideally, we need to get the framework back into a solid state and address the boolean issue so all parties involved can move forward and upgrade.

Related Issues / PRs / work

• PR fix(format): preserve model column dialect #5926 -- Work from @fresioAS to try and address the change. PR closed.
• PR feat(format): add transpile_meta opt-in for dialect-aware MODEL headers #5944 -- Work from @albertosuman-1k5 to add in a flag to opt in to previous behavior. Still open.
• Issue sqlmesh format corrupts MODEL meta expressions (ignored_rules + DATE_TRUNC for BigQuery) #6035 -- unexpected impact / bug.
• PR fix(format): preserve dialect-specific SQL in MODEL/AUDIT/METRIC headers #5949 -- Work from @mday-io to address this. Currently is patching the initial fix, but does not address sqlmesh format corrupts MODEL meta expressions (ignored_rules + DATE_TRUNC for BigQuery) #6035

The initial fix has impacted too much. We should revert and find a better approach for the initial issue. Just need to coordinate so nobody is surprised here.

[mday-io]

I feel confident #5949 is the right path forward here - I don't think we need to revert.

[cmgoffena13]

@mday-io sounds good - lets just make sure scope for tests and regression includes the BigQuery fallout as well so this can get wrapped up