ANSI MODE does not seem to work.

Help
,
1

The problem I’m having

Enabling the ANSI MODE by either running SET ANSI_MODE=true or SET spark.sql.ansi.enabled=true does not enable it.
The mode that should work is described here:

And hence should be configurable, but setting it does not change it’s behavior…

For anyone interested, the docs on the ANSI MODE itself can be found here:

The context of why I’m trying to do this

I want databricks to fail on incorrect casts and other stuff (see link above). This allows us to be fully in control instead of receiving unexpected NULL values after casting.

What I’ve already tried

  • Adding in dbt_project.yml:

    on-run-start:
  - SET spark.sql.ansi.enabled=true

  • Adding the setting to the profiles.yml as explained here (did I try this correctly?)

    databricks:
  outputs:
    a:
        type: databricks
        catalog:
        ...
        threads: 4
        session_properties:
            ansi_mode: true

  • Manually running the SET spark.sql.ansi.enabled=true and manually running the compiled query in databricks. This fails the sql casting as expected (and as desired).

Could it be databrick’s fault?

Somehow, this doesn’t behave as expected either:

SET ansi_mode = true;

SELECT cast(12345 AS TINYINT);

It simply returns 57, instead of throwing an error.

The following does work:

SET spark.sql.ansi.enabled = true;

SELECT cast(12345 AS TINYINT);

Casting 12345 to tinyint causes overflow here.

I will try with spark.sql.ansi.enabled: true in the profiles.yml…

2

Don’t use SET ANSI_MODE=true (also not in the profiles.yml) which is suggested by databricks

Instead set the following in your profiles.yml:

databricks:
  outputs:
    a:
        type: databricks
        catalog: abfp02_a
        host: https://adb-7629108220744896.16.azuredatabricks.net/
...
        session_properties:
            spark.sql.ansi.enabled: true