topic Re: Data Pipeline from Ingest to Lakehouse to other Lakehouse in Pipelines

Data Pipeline from Ingest to Lakehouse to other Lakehouse

ContractRick — Fri, 28 Jun 2024 20:22:32 GMT

I currently have 1 workspace with 2 lakehouses in it: Bronze and Silver. The Bronze lakehouse contains files and tables from ingestion, while the Silver lakehouse contains tables that have been transformed from the Bronze lakehouse as a source. These transformations are done through a mixture of Notebooks and Dataflow Gen2. I then have 2 other workspaces, each with its own Gold lakehouse. These lakehouses contain a subset of what is in the Silver lakehouse, only what is pertinent for the semantic models attached to it. These are in different workspaces to facilitate different Viewer and RLS permissions.

I am looking for a way to use a Pipeline the coordinate these transforms instead of manually scheduling them and hoping for the timing to work out... The idea is to have one Pipeline that runs any ingestion routines, and upon success, runs any Silver transforms, and upon success copys/transforms again to Gold. The problem I am running into is that even though the Pipeline shows all steps successful, the data is not in the final location. The first ingestion steps work, but then the transform to silver steps aren't seeing the new data in the Bronze lakehouse from the previous steps (same for Silver to Gold).

Is there a certain period of time I have to wait for the lakehouse to return the newly ingested data after a notebook or dataflow has run? Is it some sort of session limitation? Right now it seems like I may have to go back to scheduling the steps individually...

Re: Data Pipeline from Ingest to Lakehouse to other Lakehouse

spencer_sa — Tue, 22 Oct 2024 07:56:25 GMT

If you're using the SQL Endpoint to view data in the lakehouse (rather than say using SparkSQL in a notebook), you might find there is a lag in the data appearing. See this Community post;

https://community.fabric.microsoft.com/t5/Data-Engineering/Lakehouse-tables-not-showing-up-in-SQL-Endpoint/m-p/4062734#:~:text=The%20issue%20you%20are%20experiencing,few%20minutes%20and%20check%20again.

Re: Data Pipeline from Ingest to Lakehouse to other Lakehouse

frithjof_v — Tue, 22 Oct 2024 09:39:57 GMT

If you are using Dataflow Gen2 to read from a Lakehouse, then the Dataflow Gen2 will read from the SQL Analytics Endpoint.

As mentioned by spencer_sa , there can be a lag with the SQL Analytics Endpoint. It can take x number of seconds (or minutes) for the Lakehouse data to become available in the SQL Analytics Endpoint.

I'd just use Notebook all the way, instead of Dataflow Gen2, if that is possible for you. This way, you avoid using the SQL Analytics Endpoint. Notebook reads directly from the Lakehouse.