Fabric Shortcut ADLSG2 SQL endpoint displaying only most latest data

[rxt]

I have created a fabric shortcut to ADLSG2 folder. when I run a sql endpoint it only displays the most latest data even though we have more data in adlsg2.  here is an example, calling a fabric SQL endpoint 

syanpse SQL call to adlsg2 with delta format 

but we know we do have alot more data in the adlsg2 and when you do a parquet format

how can we get all the data(historic data) in fabric instead of just the most latest one ? instead of creating shortcut do I need to import data in to fabric table ? it is obious that fabric table are call fabric delta tables, but I need to have all the data for the analytics purposes. Any guidance or though would be really appriciated.

[Anonymous]

HI @rxt,

For you scenario, I'd like to suggest you to use dataflow/data pipeline to copy data from ADLS Gen2 to fabric lakehouse instead of use shortcut. (shortcut work as its name, it is point to the file path instead of getting all data to the lake house side)

Regards,

Xiaoxin Sheng

[rxt · Accepted solution]

Thank you guys for quick responses. Being new guy in fabric space, my question may not have made sense. But I did figure out the solution. Just incase somebody have similar issue, how I solved my issue.

It appears that when you create shortcut from azure adlsg2

Instead of creating shortcut directly into table, it should first create shortcut from the files only then it allows to select the option type parquet or delta.

and change the option type to parquet.

Only then you load the table from file.

That did the trick for me. now all my historical data are displayed from sql endpoint when I access the lakehouse table. I hope it will be helpful to someone in the future.

[SachinNandanwar]

You are doing a select count(*) which would return the count from the table which is 10111 or am I missing something ?

[frithjof_v]

Could you explain a bit more what you mean by get all the historic data?

Do you mean time travel functionality? (e.g. %%sql SELECT * FROM tableName VERSION AS OF 1)

In that case, you need to use Notebook instead of SQL Analytics Endpoint.

In a Notebook, you could also use this code %%sql DESCRIBE HISTORY tableName; in order to verify the version history of the table.

Can you confirm that you have a delta table in ADLS Gen2, and that you created the shortcut in the Table section of a Fabric Lakehouse?

Can you share a screenshot of the table name inside the Fabric Lakehouse? I'm interested to see if it has the proper Delta triangle icon and Shortcut icon. (Remember to not show any sensitive information).

Can you also confirm whether your Fabric Lakehouse has schema (preview) enabled? Because I see you have [dbo] in your query. SQL Analytics Endpoint doesn't work with Fabric Lakehouse which has schema (preview) enabled.