Cannot run merge statement in the notebook

[Dimitry]

Hi all

I'm trialing Databricks for running complex python integration scripts. It will be different data sources (MS SQL, CSV files etc.) that I need to push to a target system via GraphQL. So I selected Databricks vs MS Fabric as it can handle complex Python scripting with lots of utility files.

With that in mind, I created tables and transformations, but in one case I ran into a problem that I'm unable to solve.

I have an SQL cell, that runs successfully on serverless starter warehouse but fails with error on serverless compute. I've simplified the script to still reproduce the issue:

The error is: [UNRESOLVED_COLUMN.WITHOUT_SUGGESTION] A column, variable, or function parameter with name `_metadata` cannot be resolved. SQLSTATE: 42703

I found this because of the enableRowTracking on the table.

The script works just fine via SQL starter warehouse.

Please help me to find what needs to be setup for this to work.

[SP_6721 · Accepted solution]

Hi @Dimitry ,

The issue you're seeing is due to delta.enableRowTracking = true. This feature adds hidden _metadata columns, which serverless compute doesn't support, that's why the MERGE fails there.

Try this out:

• You can disable row tracking with:
  ALTER TABLE shopify.stock.location2
  SET TBLPROPERTIES (delta.enableRowTracking = false);

Alternatives:

• Keep delta.enableChangeDataFeed = true, it works on both compute types.
• Use a SQL warehouse (non-serverless) if you need row tracking for that operation.
• Or, consider using Change Data Feed instead of row tracking for audit/logging needs.