[Bug] Wrong generated SQL in enforced contract models that have different column order between yml and sql files

### Is this a new bug?

- [x] I believe this is a new bug
- [x] I have searched the existing issues, and I could not find an existing issue for this bug

### Which packages are affected?

- [ ] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [ ] dbt-postgres
- [ ] dbt-redshift
- [x] dbt-snowflake
- [ ] dbt-spark

### Current Behavior

With Snowflake adapter, column order is not always respected in the generated SQL queries, compared to model yml and sql files, in the context of incremental model with contract enforced at least.

### Expected Behavior

Having different ordering of columns between model.yml and model.sql should not be an issue, or should results in an error, depending on your vision.

### Steps To Reproduce

foo.yml

```yaml
version: 2

models:
- name: foo
config:
contract:
enforced: true
columns:
- name: column1
data_type: date
- name: column2
data_type: varchar
- name: column3
data_type: number
- name: column4
data_type: number
```

foo.sql

```sql
{{ config(
materialized="incremental",
incremental_strategy="insert_overwrite",
on_schema_change="append_new_columns"
) }}

with raw as (
select *
from {{ ref("bar") }}
)

select
column1,
column2,
column4,
column3,
from raw
```

As you can see, the order of columns 3 and 4 are swapped between the yml file and the sql final query.

When our production job got triggered by the merge of the PR that introduced the model, the following query get generated in Snowflake :

```sql
create or replace table database.schema.foo
(
column1 date,
column2 varchar,
column3 number,
column4 number
)
copy grants
as (
select column1, column2, column3, column4
from (
with raw as (
select *
from database.schema.bar
)
select
column1,
column2,
column4,
column3
from raw
) as model_subq
)
```

The query inserts column4 into column3, and vice-versa. This SQL is valid and won't throw any error in Snowflake as column3 and column4 have the same data type. So this results in having the model materialized without any error but inserting wrong data in those columns.

Also, once the model get materialized for the first time, here is the SQL generated by the job run that updates it :

```sql
create or replace temporary view database.schema.foo__dbt_tmp
(
"COLUMN1",
"COLUMN2",
"COLUMN4",
"COLUMN3",
)
copy grants
as (
with raw as (
select *
from database.schema.bar
)
select
column1
column2
column4
column3
from raw
)
```

So, the view that dbt creates that will be used for the insert overwrite statement later on follows the column order of the model SQL file, while, as seen above, the deploy initial job follows the column order of the yml file.

Finally, and as a side note as it is not directly related, the insert overwrite final SQL query uses a select * instead of explicit columns selection ; I am not 100% convinced it is totally safe.

### Relevant log output

```shell

```

### Environment

```markdown
dbt Cloud latest version
```

### Additional Context

Originally posted as a support issue through my cloud subscription, copy pasting the answer :

> Based in the [docs](https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.getdbt.com%2Fdocs%2Fmesh%2Fgovern%2Fmodel-contracts%23how-to-define-a-contract&data=05%7C02%7Chugo.epicier%40rolex.com%7C95e5f8f10072458667cc08de3e600a78%7Cf2460eca756e4a3fbd14d2a84590fc31%7C0%7C0%7C639016779408213801%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=DHtReHbjcDzf5Ht6UaYfqrYCeA4lIPJoAFADWNLNIZk%3D&reserved=0) mentioned below, you've identified a gap between the documented behavior and the implementation. The docs state that preflight is "order agnostic" and that DDL will follow contract order—which sets an expectation that order shouldn't matter. But the data insertion step doesn't account for this reordering, resulting in the mismatch you observed.
> When building a model with a defined contract, dbt will do two things differently:
>
> 1. dbt will run a "preflight" check to ensure that the model's query will return a set of columns with names and data types matching the ones you have defined. This check is agnostic to the order of columns specified in your model (SQL) or YAML spec.
> 2. dbt will include the column names, data types, and constraints in the DDL statements it submits to the data platform, which will be enforced while building or updating the model's table, and order the columns per the contract instead of your dbt model.