Forum Discussion
Runtime2.0 - appendOnly tableFeature + Copy Data activity incompatible
Hello,
Forgive me if any of the below is me incorrectly understanding things. We have just began to use Fabric as we start to migrate to it.
We recently switched our workspaces from runtime 1.3 to 2.0.
Since then it appears that when using Spark SQL to create a brand new delta table, it defaults to minReaderVersion 3 and minWriterVersion 7 which it did in 1.3 but also has "appendOnly" in tableFeatures which wasn't present in 1.3.
My understanding is that the writer SDK used to write to this new table must support the elements found in tableFeatures. But it seems that in our pipeline the Copy Data activity does not support this feature and so it fails when we try to use upserts.
There appears to be an issue around lookUp activity in the pipeline too. I run a notebook activity to generate the table, then I run RefreshSQLendPoint activity as previously we had issues where follow on activities using the endPoint didn't yet see changes done by the notebook. Now after this endpoint refresh comes our lookup activity which has started to complains about permissions on that new table. This is only since switching to Runtime 2.0. If I stop the pipeline and run it again a few minutes later, the lookup activity runs perfectly fine leading me to think there is something in the background that is taking place very slowly, perhaps implementing oneLake security or something?
Checking with AI, it advises me that I must create the tables with the following table properties in the DDL to support upsert in Copy Data activity. Probably showing my lack of knowledge here, it feels like to me that Runtime 2.0 was released but pipeline activities were not suitably updated to match the requirement changes and now lacking some compatibility?
TBLPROPERTIES (
'delta.minReaderVersion' = '1',
'delta.minWriterVersion' = '2',
'delta.enableDeletionVectors' = 'false'
);
I'd be really grateful for any guidance and corrections in my understand around this issue.
Hi RLWBG,
Your reading is mostly right. One clarification first: seeing appendOnly in tableFeatures does not necessarily mean the table is append-only. With writer version 7, Delta lists the legacy features explicitly, so appendOnly can appear in the list while the property itself is off. What matters for tools like the Copy activity is whether their Delta writer supports the protocol version and every feature in that list (deletion vectors are the usual blocker). You can check what you actually have:
DESCRIBE DETAIL my_table; -- minReaderVersion, minWriterVersion, tableFeatures SHOW TBLPROPERTIES my_table; -- delta.appendOnly should be false (or absent)Practical options, from least to most change:
# Option 1: create tables the Copy activity can write to (per table, in your DDL) spark.sql(""" CREATE TABLE my_table (id INT, name STRING) USING DELTA TBLPROPERTIES ( 'delta.minReaderVersion' = '1', 'delta.minWriterVersion' = '2', 'delta.enableDeletionVectors' = 'false' ) """) # Option 2: set the defaults for the session/environment instead of every DDL spark.conf.set("spark.databricks.delta.properties.defaults.enableDeletionVectors", "false") spark.conf.set("spark.databricks.delta.properties.defaults.minReaderVersion", "1") spark.conf.set("spark.databricks.delta.properties.defaults.minWriterVersion", "2")Option 3, which I usually prefer: keep the newer protocol and do the upsert in the notebook with MERGE, and use the Copy activity only to land raw data in a staging table (append/overwrite). That way you keep deletion vectors and other Runtime 2.0 benefits, and you are not tied to the Copy activity's writer support.
On the Lookup permission error that disappears after a few minutes: that pattern matches the SQL analytics endpoint metadata sync not having caught up with a brand-new table yet, rather than a real permission issue. Creating tables once up front (not on every run), or adding a short retry on the Lookup (Retry = 2-3, interval 60s) after the refresh call, usually removes it.
So my overall approach would be:
DESCRIBE DETAIL to see protocol + features -> Copy upsert target: create with writer v2 / no deletion vectors (per DDL or session defaults) -> Better: Copy -> staging, MERGE in notebook -> Lookup: create tables once + retry after SQL endpoint refreshAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
2 Replies
jubinsoniSkilled Sharer
Hi RLWBG,
Your reading is mostly right. One clarification first: seeing appendOnly in tableFeatures does not necessarily mean the table is append-only. With writer version 7, Delta lists the legacy features explicitly, so appendOnly can appear in the list while the property itself is off. What matters for tools like the Copy activity is whether their Delta writer supports the protocol version and every feature in that list (deletion vectors are the usual blocker). You can check what you actually have:
DESCRIBE DETAIL my_table; -- minReaderVersion, minWriterVersion, tableFeatures SHOW TBLPROPERTIES my_table; -- delta.appendOnly should be false (or absent)Practical options, from least to most change:
# Option 1: create tables the Copy activity can write to (per table, in your DDL) spark.sql(""" CREATE TABLE my_table (id INT, name STRING) USING DELTA TBLPROPERTIES ( 'delta.minReaderVersion' = '1', 'delta.minWriterVersion' = '2', 'delta.enableDeletionVectors' = 'false' ) """) # Option 2: set the defaults for the session/environment instead of every DDL spark.conf.set("spark.databricks.delta.properties.defaults.enableDeletionVectors", "false") spark.conf.set("spark.databricks.delta.properties.defaults.minReaderVersion", "1") spark.conf.set("spark.databricks.delta.properties.defaults.minWriterVersion", "2")Option 3, which I usually prefer: keep the newer protocol and do the upsert in the notebook with MERGE, and use the Copy activity only to land raw data in a staging table (append/overwrite). That way you keep deletion vectors and other Runtime 2.0 benefits, and you are not tied to the Copy activity's writer support.
On the Lookup permission error that disappears after a few minutes: that pattern matches the SQL analytics endpoint metadata sync not having caught up with a brand-new table yet, rather than a real permission issue. Creating tables once up front (not on every run), or adding a short retry on the Lookup (Retry = 2-3, interval 60s) after the refresh call, usually removes it.
So my overall approach would be:
DESCRIBE DETAIL to see protocol + features -> Copy upsert target: create with writer v2 / no deletion vectors (per DDL or session defaults) -> Better: Copy -> staging, MERGE in notebook -> Lookup: create tables once + retry after SQL endpoint refreshAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
v-saisrao-msftCommunity Support
HI RLWBG,
Have you had a chance to review the solution shared by jubinsoni ? If the issue persists, feel free to reply so we can help further.
Thank you.