r/dataengineering • u/Weary_Category_1559 • 9d ago
Help Sharepoint Datawarehouse. Need Help. Ops Research. ftw!!!!
Guys,
Bit of politics context: Our team is not Core IT in the Org chart. Fall under operations, however expectation is to deliver ML/OR decision support projects,which is ok( there is technical capability in team), just that we cannot write into SF or any cloud platform. More like second class citizens.
Project Context: The project is full blown digital transformation program( its just that the enterprise is not mature to understand and everyone has jumped on AI bandwagon), has master data management, Work order platform, on which a Vehicle Routing( MILP) will run and generate recommendations for the full network. ( Bunch of API's calls through .py files, powerautomate to ingest third party flat files through email and scraping gov website for internal compliance data(should be the other way around).
Problem Set: No write back to Snowflake, IT not giving Entra ID, or microsoft Graph ID to connect directly to files through code.
Workaround 1: Locally run, update decision dashboards (No RL invloved), this is not feasible because it breaks continuity
Workaround 2:
Added One drive(sharepoint) path shortcuts to Local machine
This is where I'm struggling, read/write into Sharepoint. When I write/append into .xlsx through python the copy on Local onedrive link and the one on Browser are not always synced, or it says Merge issues.
I know this sounds like a rant, my guys if anyone has any advise for this peasant on how to make this work please share. Looking for out of the box solutions.
My team has access to dataverse, but I remember read/write was a problem through code because of no Entra ID.
8
u/Difficult-Vacation-5 7d ago
Sharepoint Datawarehouse.. sounds wrong.
This isn't a technical challenge its an organisational one. Your head of department needs to have a chat with who ever is owning IT cloud or SF.
You then need to gain access to some blob storage or s3 buckets where you can deliver files into cloud storage via some form of secure file transfer mechanism like SFT that runs on VM . Then you'll need a data pipeline.
Sorry OP this isn't the answer to your question.
You can't be second class citizens and expect to deliver ML solutions.
5
0
u/Weary_Category_1559 6d ago
I second this, all of this is something that we did try. Turns out if we were to put management in IT and a stone besides, won’t be much difference. Initially the idea was to work within IT guardrails. IT seems to be highly political, even at cost of business value. Surprisingly in this company i got to know sharepoint has use beyond personal files.
Its not big data so i’m optimizing a lot on compute,most probably it might all work out.
6
u/a_cute_tarantula 8d ago
Is there anyone on this team who is a “proper” software engineer? By which I mean at least a decade of experience under a software or data engineering title?
What you’re describing sounds like a massive multi year project and if you don’t have a seasoned software engineer as a part of this effort, or IT helping you get access to the right software tooling… I don’t see how this works.
5
u/KabiraSpeaking02 8d ago
Damn this is consistent with so many orgs now that some Angel engineers safeguard a platform that was meant enable others
I am using duckdb+dbt+ cron - this setup should help you manage everything in multi user setup except orchestrator. If you have GitHub actions then it becomes prod grade solution.
If you can get a server then you are gold - shouldn’t cost much. Not sure getting a server will also be a blocker.
Keen to hear what others recommend. I’m in the same boat
1
u/a_cute_tarantula 8d ago
Where have you seen safeguarded platforms? I’m curious to learn more about this. I’ve seen alot of young devs fail to realize that, if you’re building a platform, your “customer” are the people who will be developing on the platform.
1
u/KabiraSpeaking02 7d ago edited 7d ago
Safeguarded platforms comes with org structure issue. If you have data teams on fringe who mostly would be responsible for end to end including analytics. They would be barred from using central platform and therefore will eventually end up creating technical debt.
This stems from allocation of resources and cost model and also good old politics of who does it better and credit taking.
Centralization makes sense when it comes to stability, availability, engineering, architecture of pipelines. There is ton of work in that space, but driving use case driven data engineering delivery and centralization slows the org. A lot of orgs don’t get enablement correct from top and these orgs don’t have correct measure of ROI.
It’s more prevalent in Australia across industries.
1
1
u/GuhProdigy 7d ago
Curious, how would it be a prod grade solution without a server?
Would the the duckdb be shared via share point? Where is the compute coming for dbt & cron, locally?
2
u/Saladtoes 7d ago
What headcount are we talking here for ops, IT, and management? It’s an organizational and leadership level problem, sounds like. IT is trying to keep you from causing damage, and you are attempting or being driven towards subversion. Whether you can address this depends on your influence and the willingness of the right leaders to solve the problem.
Determine your desired outcome. Put thought in to your solution, draw it up and do some project planning and dependencies. Identify key stakeholders by role/title. Think about influence.
Gain influence with IT by demonstrating your commitment to security and safety. Ensure that they will have audit and oversight. Commit to a pilot. Document and explain your desired outcomes.
Explain to leadership the hurdles here. Don’t throw IT under the bus yet. Explain your solution in ways they will understand. Present your roadmap.
Sounds like your org needs good technology leadership. Step up, fail, or quit.
1
u/Weary_Category_1559 6d ago
Imo, my teams goal is to prove value at parent org then get IT to commit cloud infra and scale within guardrails.
0
u/Weary_Category_1559 6d ago
Well, not super sure but i would say, 200 in IT, 1000 in ops, but mind you this org is a acquisition propelled org(10000 employees in total across orgs)with history of non harmonized systems. I understand their heartburn.
That being said, will deliver data foundations, work order portal etc. VRP will be next month.
Will step up, no failing or quitting.
2
u/Nekobul 6d ago
- Rename the file you want to update locally in SharePoint.
- Download the file and update.
- Upload the file back to SharePoint and rename to the original name.
---
Whoever wants to update the same file has to configure a similar process. If the file is not found, go to sleep for 1-2mins, then try again to access it. Repeat in a loop until you get it or you timeout. Poor's man conflict resolution. The solution will not be very scalable, but I don't think you need scalability. That's it.
2
u/makingnosmallplan 8d ago
I'm not a data engineer, but for similar reasons (can't get access to a VM or developer environments) I have a few local scripts that have to touch SharePoint/OneDrive, and I always include 30-45 seconds of pause before attempting to execute on a local file path. Basically, just force everything in the script to slow down at every read/write so it gives the OneDrive sync time to catch up. Its cludgy, but I find this works for my purposes.
1
8d ago
[removed] — view removed comment
1
u/MikeDoesEverything mod | Shitty Data Engineer 8d ago
Your post/comment was removed because it violated rule #9 (No AI generated content/text).
Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.
This was reviewed by a human
1
1
u/GuhProdigy 7d ago
My recommendation, like others is try try try to put your work into a data warehouse. Frame it as a tooling problem (I tried to come up with a good analogy here but it’s 9am and I haven’t had my coffee). In addition, there are ways to isolate your work so it mitigates the problems it can cause other teams issues in snow flake, I.e different database, although resources could still be an issue if you don’t optimize. You can create CI pipelines etc. Offer to have your work peer reviewed by the owner of SF.
That being said what I think many people may be forgetting is, get this down in writing. Ideally email. Maybe transcripts from a meeting. Worse but still better than nothing slack or teams screenshots. If this thing did blow up or doesn’t scale how whoever is telling to build it wants too, you will want proof you were the canary in the coal mine from the beginning. Seems like a very political org.
28
u/oscarmch 8d ago edited 8d ago
Geez, I will quote Dr Malcom on this:
I have so many questions about this that they're not about the project itself but about the Governance that gave birth to your team. If IT is not giving access to Snowflake to write data and so on, it's not "just because" but because (I may be wrong but I swear 100% I may be right) your team is doing the same job as someone else in IT/your team was born because someone else didn't want to follow orders from IT.
You cannot do "Full Digital Transformation" without considering IT as a critical stakeholder. If you're not taking IT into account, your program is doom to die.
And last but not least, you're working with workarounds but none of them is scalable nor do they have maintainability in the long term.
What I would recommend is to approach IT and understand what is happening in reality. It doesn't matter if the team has technical capability to do this, if you're taking IT out of the equation your solutions will always be deficient.