lakehouse file shortcut from adlsgen2 how to refresh shortcut

[rxt]

I successfully created a shortcut from a lakehouse file to an ADLS Gen2 without any issues. However, I have a few questions:

1. When a new file is added to the ADLS Gen2, the shortcut doesn't automatically refresh. The new file doesn't appear in the shortcut folder until I manually click refresh. Is there a way to automate the refresh of the shortcut? Will the default sematic model refresh will refresh the shortcut ? 
   
2. We can manually load data into a table from the shortcut, but if the shortcut is refreshed, will the data automatically load into the table, or do we need to run a notebook to refresh the table?

I would greatly appreciate it if someone who is fabric guru could help me solve this Fabric mystery.

[rxt]

Thank you so much, Frithjof_v! This is the best plain English explanation I've come across for fabric beginners like me. I may need to experiment a bit, but regarding the last phrase you mentioned—that "the table should automatically reflect updated data from ADLS"—since it's a shortcut with ADLS Delta, the updated data will indeed be reflected. However, if I want to include historical data in a query for a dashboard using lakehouse table, would it make sense to create a file shortcut and use a notebook to load data into the table(append data into table), or should we directly connect to ADLS using copy activity or dataflow gen2 or even notebooks ? Then again, if we load the data into table that mean replication of the data into lakehouse table. I guess, shortcut is best used if you need to view the most latest data from the source? if adls is truncate and reload then shortcut could be use at its fullest ?

Once again many thanks for making my day !!!

[frithjof_v]

"However, if I want to include historical data in a query for a dashboard using lakehouse table, would it make sense to create a file shortcut and use a notebook to load data into the table(append data into table), or should we directly connect to ADLS using copy activity or dataflow gen2 or even notebooks ?"

I am guessing both of these methods will give identical results. You could probably also use the shortcut as a data source for your Data pipeline or Dataflow Gen2 (or Notebook, as you already mentioned).

As I always try to avoid unnecessary components, I would probably just connect directly to the ADLS storage if I was going to use Data pipeline or Dataflow Gen2. I would avoid creating a shortcut if I don't really need it. But I think that's up to you what you feel is easiest in the situation.

If I was going to use Notebook, I guess it could be easier to use the shortcut as a source. Anyway I think you could choose freely what to do and I guess it will be identical outcome (this is my understanding).

(There is also the option to use time travel, also with a shortcut table I guess, as long as the table is in delta table format.)

"I guess, shortcut is best used if you need to view the most latest data from the source?"

Yes. You also don't need to store the data in OneLake if you use shortcut, so you avoid duplicating data.

However, queries will need to go to ADLS every time someone queries the shortcut in Fabric, and then ADLS will provide the data. So if you use the shortcut table in a Direct Lake Power BI report, there will be queries sent to ADLS every time someone views the report or interacts with the report (click on a visual, make a slicer selection, enter a new page, etc.). 

So I guess there might be some network charges related to this, especially if the ADLS is in another region than your Fabric capacity. I'm not sure if ADLS charges anything for read operation transaction or if this is covered by the Fabric capacity. I haven't studied the details about it. This might be a reason to copy the data into Fabric in certain situations. I will try to learn more about this.

If you used Amazon S3 or Google Cloud Storage, you would get the option to cache the shortcut data, in order to save on egress fees. I haven't studied this so I can't say anything for certain regarding which charges may incur when using shortcuts. https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts#caching

In some cases, you can also write back data to the source by using a shortcut. 

And in some cases, you can also delete files and folders in the source by deleting files and folders in the shortcut. https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts#how-do-shortcuts-handle-deletions

[rxt]

Thank you so much, frithjof_v, for taking the time to share your knowledge with the world. The best instructors have a way of explaining things so clearly that anyone can understand, and you did exactly that! more power too you so that you can continue educating the world.

[frithjof_v · Accepted solution]

Based on my understanding, you never need to "refresh" a shortcut. A shortcut is only a pointer. The data doesn't actually get stored in the shortcut. The data is only stored in its source location (aka target path, in your case it's the folder in ADLS). Every time when someone needs to use the data in the shortcut, the queries go directly to the target path (in your case, ADLS) and then the data gets sent back from ADLS over the network and into Fabric. (With shortcut caching enabled, the behaviour is a bit different, but caching can only be enabled for GCS and S3 at the moment.)

1. I am guessing that this is just about what you see in the user interface, some kind of visual cache. I think the file is actually there, even if you don't click refresh. But you don't see it until you click refresh (or go to another page, and then return).

I think if you try to connect to the file from a Notebook, it will work, even if you cannot see the file in the user interface yet.

2. When clicking Load to Table, you create a copy of the current data, and the loaded table will no longer be connected to the origin. So this method will not reflect subsequent changes to the files in ADLS.

If you already have a delta table in ADLS, you could create the shortcut directly in the Tables section in the Lakehouse (not in the Files section). Then the table should reflect the updated data from ADLS automatically. I am guessing this is what you want.

Please refer to this link:

https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts#lakehouse