Skip to main content

Quoted Identifiers in Coalesce

When you create or reference objects in Coalesce, the platform wraps identifiers to match your connected data platform. Snowflake Projects use double quotes. Databricks and BigQuery Projects use backticks. Understanding these differences ensures your SQL runs correctly in Transforms, the Join tab, and Pre-SQL or Post-SQL.

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.

Quoted Identifiers in BigQuery​

BigQuery Projects use backticks for identifiers, the same quoting style as Databricks Projects in Coalesce.

While BigQuery SQL can use double-quoted identifiers in some settings, Coalesce generates backtick-quoted identifiers for BigQuery Projects. Use backticks when you add column or table names by hand in Transforms or the Join tab.

Example generated pattern:

`my_project`.`my_dataset`.`stg_customer`.`c_custkey`

If you copy Snowflake examples that use double quotes, replace those identifiers with backticks before you run the Node on BigQuery.

Formatting Data Transformations​

When writing transformations, use the syntax for your Project platform:

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

Platform-Specific Code Generation​

When you create a Databricks or BigQuery Project in Coalesce, generated SQL uses backticks for identifiers. Helper tokens such as {{SRC}} and ref functions resolve using that Project's quoting style.