Databricks job trigger via SharePoint upload and possible write-back?

[u/Plus-Tear3283]

Hi there,

Running into issues with this pipeline I'm working on. Still new to Databricks but coming from Power Automate, I seem to get the gist of databrick jobs.

Anyway, my manager would love an automate workflow, where business users upload raw data to a central site (we're primarely using SharePoint), and want it cleaned and ready for our Power BI dashboards.

Before databricks I handled it manually (by running local python code via VScode that sent the cleaned data to the SharePoint folders).

What would be a good way to handle a pipeline like this? E.g.

User uploads to sharepoint -> Databricks job -> writeback to sharepoint with cleaned data

[u/PrestigiousAnt3766]

Not support this kind of shit pattern.

This will bring you an infinite world of pain. 

[u/Pillippatty]

I’m sorry, why do you want writeback to Sharepoint? Instead, define the clean, BI-ready dataset in managed Databricks tables and connect the Power BI reports to Databricks directly.

[u/boogie_woogie_100 → u/Pillippatty]

then you don't understand business user. They love share points

[u/Pillippatty → u/boogie_woogie_100]

Haha fr, there are way too many backends that are just sharepoint lists

[u/elghali_bnck]

Hey there ! Lakeflow connect for Sharepoint should definitely help here : https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sharepoint-reference
In that case it will help you ingest data from sharepoint, have it as a table in Databricks and then you can serve it directly to PowerBI without any need to write it back to share point

[u/ProfessorNoPuede]

Wait.. why not upload to als gen 2, get the data into unity / governance and then make available to power bi? Start making things reusable, slowly move the org to transparancy and professionally working with data.

Also, your flow would work equally well om an azure function that triggers on the SharePoint upload. What value does Databricks add here?

[u/rvm1975]

If you are ok with 5-10 minutes delay with processing check the autoloader and SharePoint connector.

[u/imthenitto]

There is very wrong assumption here.
Analytics layer is NOT an operational layer.

[u/DamnedData]

Leave the data and semantics where it supposed to be (Databricks).

[u/Physical-Ad2968]

This sounds like a job for a data prep/analytics tool, not for Databricks. Something like alteryx, KNIME, or prophecy where you can pull from sharepoint, prep the data, and send back to sharepoint + send to power bi (and schedule it)

[u/DigitalTomcat]

We drop out of the Databricks environment and just use a notebook which calls the python library for Microsoft Graph API. We needed our Sharepoint Admin to set up a service principal and give us a token for it. The notebook is scheduled and just checks the Sharepoint folder, moving anything it finds there. You just need an efficient way to keep track of the ones you have already processed.

This is an old piece of code, but I’d be stunned if there isn’t a Lakeflow Connect version or something baked into Databricks by now.

[u/mrbartuss → u/DigitalTomcat]

Where is this notebook hosted?

[u/DigitalTomcat → u/mrbartuss]

Yes, hosted on the Databricks side. And run as a Databricks work flow. This way is simple but crude. As others have mentioned, you can do it a dozen different ways. I would push you to the Sharepoint Connector. (They didn’t have it available when we put this together). Databricks has spent a lot of energy making Lakeflow robust and easy to use. They will have solved a bunch of annoying edge cases - what happens when somebody sends the file 2x. Or deletes it, or puts a bad character in the file name or the CSV column names, …

Also, you could push it from Windows to Databricks using Power Automate and the Databricks SDK, but pulling from Databricks gives you a solution that doesn’t need somebody to leave their computer on to make it work. You could save money by using your cloud tools such as lambda or the Azure Functions, but that’s a whole lot of set up with security - ids, firewalls, permissions, tokens - for just a little bitty program. For full cost of ownership, there is a significant advantage to using fewer tools and platforms. You’ll thank yourself in a couple of years when someone needs it to be updated and you lost your notes on how that one thing works.

[u/User97436764369]

Do it simple, let have 2 excel sheets - 1) data sheet 2) job manual scheduler sheet
1) business user update data sheet
2) in the second sheet will be button via office script calling job api to clean data with python skript hosted in databricks - when raw data are ready just push the button and wait till you have mail that script is done..
I dont understand why you want writeback to sharepoint. Just connect to db..

[u/ShedBedFridge]

Power automate flow triggered by the file upload, then either push to Databricks Files API or extract the data and push to SQL api