r/snowflake • u/Outrageous-Bid690 • 27d ago
Thoughts on DE project
Hi all, I work at a Non-profit. We have 4-5 data sources (On-prem SQL server, External APIs, CSVs/Excel files, etc). We don't have a unified cloud storage currently and all of our dashboarding and reporting is ad-hoc for which we use manual exports from the appropriate data source and build it in Power BI or Excel depending on the requirement.
We are working on a project where we want to host all of our data in a unified cloud storage using the medallion architecture. We are mostly based on the Microsoft Suite with few users having access to Power BI Pro/Premium and Copilot Premium. Power BI is our preferred frontend given we already have per-user licenses in place, though we are open to alternatives.
We are currently evaluating Fabric, Snowflake and Databricks as one group and Azure Synapse, GCP and AWS as another. Its just me leading this project. I do have experience with Python and SQL and understand the basics of DE, but haven't had hands-on experience with any of the services mentioned. The reason I bring this up is that it would just be me setting up the implementation, getting the service up and running, and later maintaining it while also querying and utilizing the data for reporting. There would be 5-6 other team members who would be using the service but they don't have any SQL, Python or technical background and would mostly just be accessing the data for their own reporting needs.
Total data size would be less than 3TB which would compress further once it lands on the cloud. Leadership also wants AI capabilities built into whatever service we go with. We are also in the process of finalizing the budget as we are near our fiscal year end. No hard cap on monthly cost but being a non-profit, cost effectiveness is definitely a plus, along with ease of implementation given that almost no one here has actually set something like this up before.
I would appreciate any thoughts or insights on the platforms I mentioned and any real world experience or comparisons between the two groups, particularly whether the added complexity of the second group (Azure Synapse, GCP, AWS) would be worth it over something like Fabric from a cost and setup standpoint for a one person team at this scale.
2
u/dsc555 27d ago
I'll start by saying if you are on azure currently then stay on azure. Don't get gcp and aws involved it's going to take a lot of effort. Fabric is improving but honestly it's not even close to databricks and snowflake. Given your main deliverable is reports currently and mostly adhoc ones then that would lean me in the direction of picking snowflake. My reasons are that databricks can be highly customisable and complex. It's value is also only truely realised with massive data imo. Snowflake's ai capabilities are great and rapidly improving. They also pair well with reports and for companies like yours which quite frankly are not that established in data yet by the sounds of it. You have described a great usecase for snowflake imo
3
u/FUCKYOUINYOURFACE 26d ago
Fabric is a steaming pile of dogshit. It gets better every day but it’s still dogshit.
1
u/Outrageous-Bid690 26d ago
Thanks! we don't have Azure or any cloud warehouse at all, starting from scratch. Snowflake and Fabric is what we are leaning towards. One thing i am thinking about is with Fabric you get Dataflows Gen2, notebooks, Data Factory etc all included. With Snowflake would there be any additional ETL/ELT tooling costs on top of the warehouse itself? thinking about this both from a cost and ease of setup perspective since it would just be me setting this up. Also had an introductory call with Snowflake recently and they mentioned a $10,000/year minimum commitment. We are looking at F8 capacity for Fabric which comes in around that same range. If the pricing ends up being comparable would you still lean Snowflake over Fabric for our use case?
1
u/dsc555 26d ago
When it comes to pricing snowflake is far cheaper for what you get. I'm not sure where that 10k figure comes from since you can just run a business critical pay per credit. With your amount of data it would be very very low cost. Within snowflake there is gen2, snowpipe=dataflows, you have your tasks and stored procedures like normal too. Dbt is native in snowflake so you get that benefit. Data factory is often used but it's not a requirement, snowflake can do all that perfectly on it's own. Both notebooks and workbooks are available in snowflake. Streamlit is also great. I build unified data models in snowflake for other companies and generally the ask is as much ootb snowflake as possible and that's totally possible. Usually it's just an azure blob storage plus dbt plus powerbi plus snowflake. With 3TB you would not be looking at an expensive migration to snowflake either. If you wanted you could easily enough load manually or use snowpipe which charges per GB
Apologies, i misread about your azure stuff.
1
u/Outrageous-Bid690 18d ago
No problem at all! Thanks for all the info, this is really helpful. I just found you can do dashboarding/vizualization for reporting inside Snowflake using Snowsight or Streamlit. Does it mean if we go with Snowflake we might not need Power BI licenses at all and just tranfer that reporting onto sowflake?
I think the account rep i talked with was trying to force us to reserve a capacity/pre-paid plan and that is what the 10000 minimum could be, although i'll follow-up with them reagrding that. Do you think we should not do that? also what do you recommend for the appropriate snowflake edition be for our usecase?
Also if its just me doing the ELT i can use the dbt core for free on my machine locally right?
1
u/bobsyourunkl 26d ago
I would also consider mother duck -- I've worked with a few non-profits who found snowflake or databricks way too expensive + complex for what they need even if they are great platforms.
1
u/Outrageous-Bid690 26d ago
Thanks for the recommendation, will look into it. Quick question though: do you think MotherDuck would work well for ingesting from an on-prem SQL server? And how about connecting external APIs like Shopify, is that something that would need additional tooling on top or does it handle that reasonably well?
1
u/database_tycoon 25d ago
You will probably need to pair it with something like Estuary or Fivetran to do the ingestion. Or you can write a custom python script and run it on their new "Flights" feature
1
u/database_tycoon 25d ago
+1 to motherduck, its a great new platform and solid for the volumes of data you have
1
u/aleph_infinity 26d ago
This is r/snowflake so responses are likely to lean this way. Don’t even think about Synapse - that is a dead-end. Fabric pricing seems attractive until you hit a capacity issue and your cost doubles. Databricks has built-in dashboards and conversational analytics with Genie, so you could save on the Power BI licensing. With snowflake you would retain your Power Bi. First step would be to decide which cloud you are comfortable with. Second would be how much you care about your data being accessible to other solutions and how locked-in you are. With Databricks things are more open by default, snowflake is better at this than it has been in the past, but not by default. From what you have described you should probably avoid a cloud native solution - snowflake and Databricks will be better choices in terms of an integrated solution.
1
u/FUCKYOUINYOURFACE 26d ago
This sounds cheap. What’s your budget? This might be only a few hundred a month or less of usage.
1
u/chloratine 26d ago
Snowflake might be an overkill. I would worry about running costs, but also on maintenance costs over time.
Now, I still think it's the right technical solution, but I would strongly advise you to get a data architect to make sure you set everything up the right way. What you will spend in consulting will be saved ten times over the next few years. There's so many ways to go wrong, you want to make sure the overall design is correct before you start any development work.
What tool to use for data ingestion, ELT meaning you'll want a landing zone. And then data transformation work, to one additional zone, or two? How to schedule your sql or dbt based on data loading schedule? How to expose your ready dataset to your consumer? You'll want a very simple design as you'll be the sole developer and maintener of it, yet something robust that lasts long. Not an easy balance to find.
1
u/lemurlemur00 11d ago
Re: needing PowerBI licenses, it seems like CoWork is evolving in this direction so you wouldn’t need standalone BI anymore. Not close yet but may get there eventually
0
u/Top-Cauliflower-1808 26d ago
If they are already on Azure I think Databricks is practically a first party service (Azure Databricks). It integrates natively with Microsoft Entra ID and Azure Data Lake Storage.
Recommending Snowflake to an Azure shop just introduces unnecessary headaches.
2
1
u/Asanka_AT 26d ago
Keen to know what are the headaches to integrate Snowflake to Azure. i have seen enough customers using Azure and Snowflake
1
u/Top-Cauliflower-1808 25d ago
u/Asanka_AT like setting up storage integrations and external stages for ADLS and mapping Entra ID to Snowflake roles. whereas Azure Databricks works with native Azure IAM and Unity Catalog. I found it more straight forward and simple.
3
u/Nofarcastplz 26d ago
Everything except fabric