Skip to main content

Quoted Identifiers in Coalesce

When you create or reference objects in Coalesce, the platform automatically wraps identifiers in a way that matches your connected data platform. Snowflake uses double quotes by default, while Databricks uses backticks. Understanding these differences ensures your SQL code runs correctly across environments.

Quoted Identifiers in Snowflake​

Snowflake treats identifiers differently depending on how they are quoted and cased:

  • Creating an object in uppercase with double quotes allows case-insensitive references later:

    CREATE TABLE "EDW"."DIM"."DIM_CUSTOMER";

You can then reference it as DIM_CUSTOMER, dim_customer, or DiM_cuSToMeR.

  • Creating an object in lowercase or mixed-case with double quotes requires exact matching:

    CREATE TABLE "EDW"."DIM"."dim_customer";
    CREATE TABLE "EDW"."DIM"."Dim_Customer";

    These must be referenced as "dim_customer" or "Dim_Customer".

Current Limitations​

Coalesce always applies double quotes to preserve case sensitivity. There is currently no way to disable this behavior. If you use lowercase or mixed-case names, they will override Snowflake’s case-insensitive defaults.

Snowflake Parameter Solution​

You can make quoted identifiers case-insensitive with:

ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE;

At the workspace level in Coalesce, you can configure:

{
  "QUOTED_IDENTIFIERS_IGNORE_CASE": true,
  "QUERY_TAG": "Coalesce"
}

Quoted Identifiers in Databricks​

Databricks uses backticks for identifiers. While Databricks itself can support double-quoted identifiers with ANSI settings, Coalesce does not support generating double-quoted identifiers for Databricks projects. Instead, Coalesce generates SQL with backticks to match Databricks standards.

Formatting Data Transformations​

When writing transformations, use the correct syntax for your data platform:

  • Snowflake: Use single or double quotes. Example:

    REPLACE({{SRC}}, 'Supplier#', '')

  • Databricks: Use backticks. Example:

    ROUND(`forecast_hourly_metric`.`temperature`, 1)

Platform-Specific Code Generation​

When you create a Databricks project in Coalesce, the system automatically generates Databricks-compatible SQL. Identifiers are wrapped in backticks instead of double quotes, and table/column references adapt to Databricks conventions.