topic Re: Best Practice storage when using both sql and notebooks - warehouse or parquet in Data Warehouse

Best Practice storage when using both sql and notebooks - warehouse or parquet

AbiNyk — Fri, 18 Oct 2024 12:21:04 GMT

Hi,

I need to do some logic using stored procedures and afterwards notebooks consuming the resultSets.

Since it is not easy to call stored procedures from a notebook (Fabric Data Warehouse connection) i thought of making my stored procedure logic directly in the Lakehouse using notebooks as a kind of stored procedures (Spark SQL), and then invoking these whenever needed.

But i am unsure if this is a good idea? What is best practice when you want to use SQL as much as possible but also need notebooks?

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

FabianSchut — Sat, 19 Oct 2024 10:12:23 GMT

Hi AbiNyk,

Another option is to write all SQL logic in a warehouse and use a shortcut in the lakehouse to the output tables from the warehouse. Then you are able to pick up the results from the stored procedures in the warehouse and use it for more transformations in the lakehouse.

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

tharunkumarRTK — Sun, 20 Oct 2024 06:58:24 GMT

AbiNyk

You can now run T-SQL from fabric notebooks

https://learn.microsoft.com/en-us/fabric/data-engineering/author-tsql-notebook

you can attach your warehouse to notebook and run t-sql
I guess you can run stored procedures as well, you can give it a try

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

Anonymous — Mon, 21 Oct 2024 01:23:59 GMT

Hi AbiNyk ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

AbiNyk — Mon, 21 Oct 2024 08:52:06 GMT

Thanks for the reply and link.

It only shows how to make a simple query - not to execute a stored procedure. Do you know the syntax for calling a SP?

Further, i get a message on the DW: "This data source is not fully supported under the current global language." - Do you know what it means?

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

AbiNyk — Mon, 21 Oct 2024 08:53:45 GMT

The results are not to be written to tables but only consumed by the notebook.

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

Anonymous — Tue, 22 Oct 2024 07:45:04 GMT

HI AbiNyk,

>>Further, i get a message on the DW: "This data source is not fully supported under the current global language."

I think this mean current you can not in the correspond language mode, so that not all type of function and command supported when you use notebook to execute with sql query.

Have you switch the notebook language mode to the t-sql ? After this operation, you can use exec command to invoke SP.

Data warehouse tutorial - transform data using a stored procedure - Microsoft Fabric | Microsoft Learn

Regards,

Xiaoxin Sheng

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

AbiNyk — Tue, 22 Oct 2024 08:21:21 GMT

Its in the connection/path area and not in the notebook:

Re: Best Practice storage when using both sql and notebooks - warehouse or parquet

frithjof_v — Tue, 22 Oct 2024 09:26:44 GMT

"i thought of making my stored procedure logic directly in the Lakehouse using notebooks as a kind of stored procedures (Spark SQL), and then invoking these whenever needed.

But i am unsure if this is a good idea?"

I think this is a good idea. Use Spark SQL and Lakehouse with Delta Table. Just try and see if it can solve your need. If it does, then I think this is the most efficient technology and a recommended way.

Please share if you stumble upon some specific roadblockers.

Remember to run OPTIMIZE and VACUUM at suitable intervals. In many cases, running OPTIMIZE and VACUUM once per day is a general okay solution. Depending on how often you make updates to the data tables.