Maintenance on Lakehouse Tables

Thanks for the idea, seems like I'm just going to have to copy data as you suggested -- likely to a warehouse or to another lakehouse and then optimize for queries from there.  Thanks!

Yes there is and it contains approximately 130+ .json files with some .checkpoint.parquet files in it.

Crazy idea, 

Find the location of the Delta Table in OneLake and create a new table, from that path, then try to vacuum and optimize.

The Create table should not duplicate the data,

spark.sql(f"""create table {table_name}
            using delta
            location '{delta_folder_path}'"""
        )

as it is pointing to an existing delta location, should simply create a metadata entry into the Fabric Lakehouse Catalog, could you humor me and show me the properties of one of the tables, would look something like

Here are the properties for this table -- definitely similar.

It is a Shortcut, would be the only difference

Yup makes sense that this is a short cut into an ADLS Gen 2 storage facility that the Fabric instance doesn't have access to.  So impossible to maintain, but I didn't realize that shortcuts would cause you to also increase your OneLake storage...seems like I'm doing exactly what was told us was the benefit, no data duplication but it appears that I'm consuming Dataverse storage and OneLake storage as well.

The Docs here, https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-view-in-fabric#direct-access-to-your-data-in-microsoft-onelake, say

When you link to Fabric from Power Apps, the system creates an optimized replica of your data in delta parquet format, the native format of Fabric and OneLake, using Dataverse storage such that your operational workloads aren't impacted. Is it possible that onelake was chosen as the destination?

I'm pretty sure external shortcuts don't consume OneLake storage.

Do you see any indications that it consumes OneLake storage?

Edit: Sorry, just became aware of your screenshot from the capacity metrics app.

That is very surprising.

Can there be some other delta tables in this workspace (not managed by Dataverse)?

E.g. are you running some notebooks, pipelines or dataflows which create a copy of some of the Dataverse tables inside Fabric? 

try to download, onelale file explorer to see your onelake files

https://learn.microsoft.com/en-us/fabric/onelake/onelake-file-explorer

It would be interesting to try.

But I'm pretty sure OPTIMIZE would need to optimize (i.e. rewrite) the physical, underlying parquet files. 

In order to create files better optimized for querying (if the Dataverse managed files are not optimized for querying), my best bet is to create a physical copy in a Lakehouse (OneLake) and run optimize on the copy.

5 ways to get your Dataverse Data into Microsoft Fabric / OneLake - DEV Community

This blog describes different ways of syncing Dataverse data into Fabric. 

Just to be sure, which option in the blog article (link above) are you using?

There are two main options (besides traditional ETL using dataflows, pipelines or notebooks):

• Link to Fabric
• Azure Synapse Link (this is also an option for syncing Dataverse data for consumption in Fabric)

Link your Dataverse environment to Microsoft Fabric and unlock deep insights - Power Apps | Microsoft Learn 

Could you confirm which option you are using?

Anyway, I'm surprised that you're seeing OneLake storage being consumed, although I don't have extensive personal experience with the Link to Fabric or Azure Synapse Link options. Can you confirm that you don't have set up any notebooks/dataflows/pipelines which copies some data physically in OneLake?

I notice that your data storage in OneLake was first empty, and then there was some data from October 15, before it started increasing rapidly from October 24th. Is October 24th the day when you activated the link? Or did some other process starting to generate data on this date, some process which physically stores data in OneLake, in the workspace called Fabric?

Ref. your screenshot which shows that the OneLake data is stored in a workspace called Fabric. Is this the name of the workspace which contains the Dataverse Lakehouse? Or is this another workspace?

There is also a yammer forum for the Dataverse Link functionality, it covers both the Link to Fabric and Azure Synapse Link option. I think this is the link: Viva Engage : Dynamics 365 and Power Platform Preview Programs (although I cannot confirm 100% that this is the real link to that forum, but I think it is).

All the tables are directly related to the Link to Fabric configuration.  However, this data has only been exported since 7 days ago.  Its quite possible that an optimize and vacuum job hasn't been run yet on Microsoft's underlying storage facility.

With Fabric Link (a.k.a. "Link to Fabric") the OneLake is the only option available which is always created when tables are exported from D365 Sales or from D365 Finance and Operations.

Really appreciate all the suggestions!  I think I have a pretty good idea how to move forward.

Thanks for the references! I'll do some more digging into the actual data, but yes, the inventrans table as an example is sitting at 3GB of delta lake files.  I'm part of the D365 preview community groups on Yammer, so I may post my questions there and discuss with the Fabric Link product team.

Thanks for all your help as well!

I think the shortcuts can show with "xGB" (for example 3GB) in Fabric and the files can show in the "view files", but the data should actually be stored in Dataverse, if you are indeed using the Link to Fabric option.

But the shortcuts should not show as storage in the Fabric Capacity Metrics app (I think). Because the data should be stored in Dataverse, if using the Link to Fabric option.

Is there some other (non-shortcut) data in the workspace called Fabric? I.e. some data which is actually stored physically in that workspace.

I notice that your data storage in OneLake was first empty, and then there was some data from October 15, before it started increasing rapidly from October 24th. Is October 24th the day when you activated the link?

Or did some other process starting to generate data on October 24th, some process which physically stores data in OneLake, in the workspace called Fabric?

Best of luck in getting to the root of it! 😀

As mentioned by richbenmintz , OneLake File Explorer can perhaps provide some insights into this case.

Azure Storage Explorer is another option which provides a different view into the OneLake storage: Integrate OneLake with Azure Storage Explorer - Microsoft Fabric | Microsoft Learn

Hey richbenmintz,

I'm fighting this same issue and wanted to share the results of creating a new table that points to the existing files. 

I created the table using this:

create table mserp_taxtransbientitydhs
using delta
location 'abfss://[email protected]/dataverse_operationstm_cds2_workspace_xxxxxxxxxxxxxxxxxxxxxxxxxxx.Lakehouse/Tables/mserp_taxtransbientity'

Unfortunately, it still gives the error "

MethodNotAllowed, "This operation is not supported through shortcuts of account type Dataverse.""

Was worth a shot! Thanks for all your sugegstions on this one.

Darin