What causes "STRUCT field order drift should be ignored" in dbt-labs/dbt-core?

Inspect the Err output of compare_sql to find which STRUCT field pair failed to match after canonicalization

STRUCT field order drift should be ignored

Error message

STRUCT field order drift should be ignored

What it means

Test panic from .expect in crates/dbt-adapter/src/sql/diff.rs:3501: compare_sql between two Snowflake CTE queries whose only difference is the field ordering inside a STRUCT projection returned Err, so the differ treated reordered STRUCT fields as a semantic difference. The differ is supposed to make projection/STRUCT field order independent so recorded SQL and Fusion-generated SQL compare equal.

Source

Thrown at crates/dbt-adapter/src/sql/diff.rs:3501

"#;

        let sql_fusion = r#"
create or replace table `db`.`sch`.`opportunity_product_entity_stream_base` as (
  with hashed_rows as (
    select
      to_hex(md5(to_json_string(
        struct( -- noqa: PRS
          a , b , c
        )
      ))) as row_hash_id
    from `db`.`sch`.`t`
  )
  select * from hashed_rows
);
"#;

        compare_sql(sql_fusion, sql_recorded, AdapterType::Snowflake)
            .expect("STRUCT field order drift should be ignored");
    }

    #[test]
    fn test_bigquery_struct_projection_order_drift_should_be_ignorable() {
        // Same as the Snowplow ordering drift case, but without comments.
        let expected_alias_order = [
            "service_configuration_context",
            "modal_entity",
            "experiment_entity",
            "kafka_connector_entity",
            "selected_item_context",
            "service_integration_context",
            "workflow_entity",
            "video_entity",
            "value_calculator_context",
            "kafka_plan_finder_context",
            "feature_flag_context",
            "posthog_session_context",

View on GitHub (pinned to 0267ce9170)

Solutions

Inspect the Err output of compare_sql to find which STRUCT field pair failed to match after canonicalization
Extend the projection-order canonicalization so STRUCT fields are compared as unordered name->expression maps (matched by alias) for Snowflake
Check whether a recent change to alias/projection extraction regressed handling of nested STRUCT expressions in CTEs
If field order is actually semantically meaningful in the new SQL, fix the generator, not the differ

Example fix

// before
compare_sql(sql_fusion, sql_recorded, AdapterType::Snowflake)
    .expect("STRUCT field order drift should be ignored");
// after (library-side: compare struct fields by alias, unordered)
let norm = |s: &str| sort_struct_fields_by_alias(canonicalize(s, AdapterType::Snowflake));
assert_eq!(norm(sql_fusion), norm(sql_recorded));

Defensive patterns

Strategy: validation

Validate before calling

// verify only projection order differs before invoking the diff
fn projections_equal_ignoring_order(a: &str, b: &str) -> bool {
    let mut pa: Vec<_> = extract_projections(a); let mut pb: Vec<_> = extract_projections(b);
    pa.sort(); pb.sort(); pa == pb
}

Try / catch

if let Err(e) = compare_sql(sql_fusion, sql_recorded, AdapterType::Snowflake) {
    eprintln!("STRUCT projection diff: {e}"); // inspect canonical forms, don't panic
}

Prevention

Treat SELECT projections as alias-keyed maps, not ordered lists, in comparisons
Test STRUCT-heavy queries in both argument orders
Keep generated column order stable, or normalize before diffing
Cover CTE pipelines (with final `select * from cte`) in canonicalizer tests

When it happens

Trigger: cargo test -p dbt-adapter with test at diff.rs:3501: two otherwise identical Snowflake queries (Snowplow-style `hashed_rows` CTE ending in `select * from hashed_rows`) differ only in the order of STRUCT fields in the projection, and compare_sql(sql_fusion, sql_recorded, AdapterType::Snowflake) reports a mismatch.

Common situations: Replaying recorded warehouse SQL against newly generated SQL after a compiler change reordered SELECT items; canonicalizer changes that removed order-insensitive matching for struct literals/projections; a regression in alias-aware projection matching inside CTEs.

Related errors

AI-assisted analysis of dbt-labs/dbt-core@0267ce9170 (2026-09-07). Data as JSON: /api/errors/96b87b4f7c871738. Report an issue: GitHub.