# ANSI MODE does not seem to work.

**URL:** <https://discourse.getdbt.com/t/ansi-mode-does-not-seem-to-work/19223>\
**Category:** Help\
**Tags:** databricks, dbt-core\
**Created:** [April 30, 2025, 6:03pm UTC](https://discourse.getdbt.com/t/ansi-mode-does-not-seem-to-work/19223 "2025-04-30T18:03:07Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![scfrouws](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/scfrouws/32/4860_2.png) [@scfrouws](https://discourse.getdbt.com/u/scfrouws)\
**Post date:** [April 30, 2025, 6:03pm UTC](https://discourse.getdbt.com/t/ansi-mode-does-not-seem-to-work/19223/1 "2025-04-30T18:03:07Z")

</div>

## 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:

> **[Databricks setup | dbt Developer Hub](https://docs.getdbt.com/docs/core/connect-data-platform/databricks-setup)**
>
> Read this guide to learn about the Databricks warehouse setup in dbt.

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:

> **[ANSI\_MODE | Databricks on AWS](https://docs.databricks.com/aws/en/sql/language-manual/parameters/ansi_mode)**
>
> Learn about the ANSI\\\_MODE configuration parameter in Databricks SQL.

## 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`:

- Adding the setting to the `profiles.yml` as [explained here](https://docs.getdbt.com/docs/core/connect-data-platform/databricks-setup) (did I try this correctly?)

- 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:

```auto
SET ansi_mode = true;

SELECT cast(12345 AS TINYINT);

```

It simply returns 57, instead of throwing an error.

The following does work:

```auto
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`…**

---

<div class="post-metadata">

**Author:** ![scfrouws](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/scfrouws/32/4860_2.png) [@scfrouws](https://discourse.getdbt.com/u/scfrouws)\
**Post date:** [April 30, 2025, 7:25pm UTC](https://discourse.getdbt.com/t/ansi-mode-does-not-seem-to-work/19223/2 "2025-04-30T19:25:56Z")

</div>

Don’t use `SET ANSI_MODE=true` (also not in the profiles.yml) which is [suggested by databricks](https://docs.getdbt.com/docs/core/connect-data-platform/databricks-setup)

Instead set the following in your `profiles.yml`:

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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [May 7, 2025, 7:26pm UTC](https://discourse.getdbt.com/t/ansi-mode-does-not-seem-to-work/19223/3 "2025-05-07T19:26:50Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.