Forum Discussion
QUESTION::Databricks delta files Spark SQL DML not supported in Fabric
Ok, but then, if this default behavior you mention is active, why do I see NO snappy file show up in the folders, and the total number of small files is not reduced if I do not run the following commands?
And lately, also small files is not reduced after I run the following commands from a Notebook? (the below sequence is the same cell sequence as in the Notebook):
spark.conf.set("spark.databricks.delta.optimize.maxFileSize", "10GB") spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true") spark.conf.set("spark.databricks.delta.optimizeWrite.binSize", "5GB")%%sql OPTIMIZE RAW_SUMMARY ZORDER BY ([YEAR], [MONTH]); OPTIMIZE RAW_TRANSACTION ZORDER BY ([YEAR], [MONTH], Direction, VehicleClass, Lane); OPTIMIZE FACT_SUMMARY ZORDER BY ([YEAR], [MONTH], [DATE]); OPTIMIZE FACT_TRANSACTION ZORDER BY ([YEAR], [MONTH], [DATE], [HOUR]);spark.conf.set("spark.databricks.delta.retentionDurationCheck.enabled", "false")%%sql VACUUM RAW_SUMMARY RETAIN 0 HOURS; VACUUM RAW_TRANSACTION RETAIN 0 HOURS; VACUUM FACT_SUMMARY RETAIN 0 HOURS; VACUUM FACT_TRANSACTION RETAIN 0 HOURS;
Hi Element115,
Thank you for using Microoft Fabric Community Forum.
Thank you nilendraFabric for the insightful points, completely agree with your points.
As mentioned, Microsoft Fabric currently does not support DML operations like ALTER TABLE ... SET TBLPROPERTIES in Spark SQL Notebooks, and there is no official documentation or public roadmap yet indicating when this might be enabled.
The recommended workaround is to use spark.conf.set(...) within PySpark, which allows session-level configuration for Delta Lake optimizations.
Fabric already applies several optimizations by default, including:
Optimized Write – enabled automatically to manage small file sizes during writes
V-Order – applied behind the scenes to improve data compression and query performance
So while direct DML is not yet supported, Fabric does apply intelligent defaults for performance tuning, reducing the need for manual configurations in many cases.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Regards,
Sahasra
Community Support Team.
Ok, but then, if this default behavior you mention is active, why do I see NO snappy file show up in the folders, and the total number of small files is not reduced if I do not run the following commands?
And lately, also small files is not reduced after I run the following commands from a Notebook? (the below sequence is the same cell sequence as in the Notebook):
spark.conf.set("spark.databricks.delta.optimize.maxFileSize", "10GB")
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")
spark.conf.set("spark.databricks.delta.optimizeWrite.binSize", "5GB")
%%sql
OPTIMIZE RAW_SUMMARY ZORDER BY ([YEAR], [MONTH]);
OPTIMIZE RAW_TRANSACTION ZORDER BY ([YEAR], [MONTH], Direction, VehicleClass, Lane);
OPTIMIZE FACT_SUMMARY ZORDER BY ([YEAR], [MONTH], [DATE]);
OPTIMIZE FACT_TRANSACTION ZORDER BY ([YEAR], [MONTH], [DATE], [HOUR]);
spark.conf.set("spark.databricks.delta.retentionDurationCheck.enabled", "false")
%%sql
VACUUM RAW_SUMMARY RETAIN 0 HOURS;
VACUUM RAW_TRANSACTION RETAIN 0 HOURS;
VACUUM FACT_SUMMARY RETAIN 0 HOURS;
VACUUM FACT_TRANSACTION RETAIN 0 HOURS;
Hi Element115,
As currently, Fabric Spark Notebooks do not fully support Delta Lake DML operations like OPTIMIZE, ZORDER, or manual Spark config settings for file compaction. These settings are either ignored or have limited impact in Fabric.
Fabric handles file optimization automatically using Optimized Write and V-Order, but these are triggered only under specific conditions, and manual control is not available yet. That’s why small files may still appear, and .snappy.parquet files may not be generated.
We suggest submitting this request on the official Microsoft Fabric Ideas forum to help prioritize support for Delta DML and manual optimization controls:
Fabric Ideas - Microsoft Fabric Community
If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!
Regards,
Sahasra.
v-sgandrathi That is strange because after the whole notebook ran again yesterday, I got the snappy files in all folders and the folders got cleared of all the small files, except the current folder for May since the I/O process is running against this folder every hour. So the OPTIMIZE and VACUUM commands do seem to work after all.
Or am I missing something here?