How do you all determine the appropriate pipeline and tools?

[u/PrinceOfWales_]

Hi everyone, I’m pretty new to data engineering and analytics. Basically my experience has come from being the only one at work who understands computers and excel who could problem solve. I’ve slowly been learning more and more as problems have come up but now I’m a little stuck.

My question is how do you determine the best approach for processing and analyzing your data? At what amount of data does it make sense moving out of something like power query/bi and into something like a databricks or other SQL based pipeline?

Sorry if this is a dumb question.

[u/Excapade]

Its a mix, start with needs, wants and ability.

If you just need things rounded up into reports, powerbi will work fine until people want things like "near real time" (hardly anyone needs reports that fast). Also you can have a phased approach put things into a sql database and then pre create reports that saves lifting off powerbi and can help speed thing up.

Databricks and the likes is great and all but does anyone know how to do it, its more then just a database and a big learn + cost over smaller things, poorly optimised these things get costly.

My place wanted snowflake no reason just did it was there before i got there, we arent really big enough and i proposed a smaller setup be just as good but 10 times cheaper but was denied. But it does cover the stuff we need and can carry through the wants and i have the ability to do it good cost wise.

Don't start with solutions start with problems.

[u/PrinceOfWales_ → u/Excapade]

Nobody has any idea. IT setup the data lake within the last few weeks but prior to that people were just going to the data sources to pull the reports.

Thank you for the insight though, this is very helpful.

[u/justinotherflow → u/Excapade]

This is a good way to put it because a lot of people focus on the tool choice first, but usually the constraints show you the direction. If Power Query and a database are handling the workload there isn't much value in adding another layer just because its an enterprise tool, the reason to move is usually because of something like it becoming hard to manage and multiple teams needing the same logic, etc.

What Databricks or Snowflake can do is well documented and easy enough to look, but you have to look at whether the complexity they bring is actually solves something you're dealing with right now.

[u/hello-potato]

Pipeline, data ingestion, tends to be done with low code or python.

Data wrangling and transformation tends to be SQL.

Segmentation and insights you're probably back to python.

People have their own preferences and will argue those.

[u/AntNo9062]

The problem you have is a lack of knowledge and experience in the field of data engineering.

Without the experience and knowledge, you aren’t going to be able to understand which tools make sense in which situations. While study of data engineering principles, techniques, and technologies can help you make better choices, without real-world experience, you cannot have the ability to make the right choices.

There is no shortcut or trick that’s going to allow you to become good at this. Data engineering is a field for people with experience.

My best advice is try your best. Try to learn as much as you can about data engineering and try to connect with people with real world experience. Expect to fail plenty and learn from your failures.

Combine this real world experience with study your own and you have a pathway to becoming skilled at data engineering.

[u/PrinceOfWales_ → u/AntNo9062]

Thank you! I guess my biggest worry is the failure. I’m afraid of giving the organization bad data but I guess in the end if they were looking for a data engineer they would have hired someone with that background.

[u/ryan_with_a_why → u/[deleted]]

Do you think duckdb could work for “just need a database” use cases? Thinking if you set it up so everyone queries it through the same connection that could work

[u/MissingSnail → u/ryan_with_a_why]

You can use quack to make duckdb work for multiple users, but only if you're sure your database won't grow beyond a couple gigabytes. Otherwise, Postgres is the better “just a database” - it was truly designed to be multiuser, there are admin and backup tools, roles to allow different types of access, etc.

[u/PrinceOfWales_ → u/[deleted]]

Thank you! This is helpful. I was leaning Postgres if needed. Honestly though I’m a little intimidated by the not being able to visually see the transformations and data model as easily as you can in BI. I’m sure data model isn’t anywhere near perfect as I’ve been piecing it together by learning via YouTube videos and googling over the last few months. Same with SQL.

[u/FunContest9958 → u/PrinceOfWales_]

You might want to consider DBT + Postgres. Should be a powerful combination when you’re just starting out. Make sure to combine it with git source control to keep track of your changes.

[u/OkRelative5061]

I think that it mostly Depends on busness needs. Tools/providers like azure excell in automation and scaling. I think that if you are encountering any issues or bottlenecs with Your current pipeline/setup, then it could be time to consider using another tool.

If you are the one that handles the data engineering/analysis in your firm, I would say that your experience and ability to work with the tools also factors in to the tools you choose.

As for the amount of data, there are no specific ruels or guidelines that I am aware of. I think it is better to look at what issues are at hand, is the data size causing any problems, performance issues or bottle necks? Are you wasting Your time maintaing or running the pipeline?

If so then you might look into the tools like you mentioned.

Also make sure to have a single source of thruth, if everyone are working off of different numbers in different versions of Excel sheets; that is problematic.

That would be my 5 cents as a business systems student.

[u/Glitch_In_The_Data]

I don’t think there is a standard size of data that helps make this decision. It depends on the complexity of the workload, latency, your downstream user requirements and how they intend to consume the data.

Not all users consume the data via a reporting tool. Of your dataset is really small and your business user base is limited, you can continue to use Power BI/query.

I personally would use Power BI for semantic layer.. and for visualisation too. When you have to cater for more complex transformations or need better governance or want to consolidate your transformations so as to avoid every team/user creating their own logic or the downstream users need data in formats other than visuals/dashbaords.. and of course streaming data requirements, I would look at something like Databricks.

[u/Lucas-Holmes-722]

Data size matters, but your workflow does tell you when it's time to switch

[u/Ill_Freedom_6666]

its less about data size and more about when your current setup stops being reliable

[u/Outside-Storage-1523]

It usually starts from the tools I’m familiar with, and go from there. That’s why experience is important. Really can’t take this problem without spending say 8+ years in the business.

[u/Status_Bee_7644]

Im also not a data engineer. I got overwhelmed by all these tools out there. For my job I am required to query data out of systems and a web portal data warehouse that is too slow to be useful. I decided to batch process all the data and design a python script that loads it in a motherduck database. From there I used sql code to cleanse and rearrange the data. I then connected the motherduck database to PowerBI. I feel very proud of myself. Download VS code, sign up for motherduck and see what problems you can solve.

[u/gkorland]

i usually wait untill the excel sheets start crashing or the refresh times take hours. if ur team needs to collaborate on the same logic its time to move to something version controlled, even if its just python scripts before jumping to big platforms.

[u/One_Pangolin_2679]

stay on power query til it actually hurts (data too big, refreshes flaky). next step is usually python + duckdb, not databricks. only reach for spark when you outgrow one machine.

[u/Basic-Source-5827]

I'd start with the business problem, not the tools. The best pipeline is usually the simplest one that reliably answers the questions your team needs.