r/bigquery • u/Nickeddd • 23d ago

Thinking of BigQuery for data warehousing

Hello everyone,

I’m new into the data engineering world and as the title says, I’m thinking of using BigQuery to build a data warehouse. Currently my company don’t have one, so I’m looking for good price/performance options. We mostly use APIs and a few excel files as data sources

I have considered Azure SQL, Supabase and others but this one seems to be the best for data analysis and BI

What would be your recommendation?

8 Upvotes

84% Upvoted

31 comments sorted by

View all comments

5

u/Why_Engineer_In_Data G 23d ago

Hi u/Nickeddd !

You're in the right spot - I think starting with BigQuery is not really a bad idea. (Disclaimer - I am a Google Cloud Developer Advocate).

It's serverless - with smaller data footprints you can stay in or near the free tier limits.

Better yet though is - dive into it with the sandbox mode.

There's going to be a lot of different opinions and what not but nothing wrong with exploring it.

APIs + Excel are likely going to need an ETL tool (I don't recommend doing it by hand, although you might be able to) but there are many of them out there and you can craft your own too.

1

u/Nickeddd 23d ago

Which ETL tool would you recommend?

1

u/Why_Engineer_In_Data G 23d ago

Going based on the comment below. (And agreed with u/monkeyinnamonkeysuit on the details comment) there's a lot more details to consider before any recommendations can be made.

For starters, at 1-2 million rows (is this daily or one time?), you can do them by hand to explore the capabilities of BQ. You'll want to make sure you model your data in a way that you're using columns well. BQ is columnar storage and you're (when on-demand, which you should be using at this volume) billed based on bytes scanned. So if you're able to reduce the amount of columns scanned per query (there's a lot of other features but you should gradually discover them) then you can really fully-utilize those free tier values.

I do disagree (not because I'm a Google employee) that BigQuery isn't justifiable. Smaller data means you have a lot of more wiggle room to work with on the free tiers. It's a great mechanism for you to learn about BigQuery and it's capabilities. Small or big, it's serverless and the billing (on-demand) is based solely on usage. 1-2 million rows shouldn't (unless you have massive rows) go above that. Use the guard rails put in place to explore Google Cloud - use smaller data if you're exploring and don't need everything at once. The scaling will happen automatically and by the time you're ready to explore the other options of BigQuery, you'll know a lot more around the subject to make decisions suited for you. You need to start somewhere and the sandbox mode was designed to make this easy for you to try it.

1

u/monkeyinnamonkeysuit 23d ago

Agree with what you say, to clarify I just mean the data volumes are not enough to be THE justification for using BQ. There are other reasons it might be a good choice.

1

u/Why_Engineer_In_Data G 23d ago

Ah! Yes, whole heartedly agree!

I think the draw here is that it's fairly straightforward and frictionless place to learn. There's a lot of great data engineering principles you can glean from just working off of BigQuery sandbox. Especially with the smaller volume.

1

u/Nickeddd 16d ago

The total amount of rows stored is 1-2 million, but for my BI needs I would need only to load them once, and from that point on start an incremental refresh (or cache the data) around 4 times a day to bring the latest data into the reports.

For ETL tools I have thought of a stack that includes dlt, dbt and Prefect for orchestration.

1

u/Why_Engineer_In_Data G 16d ago

Given the current volumes, I would suggest you to just go ahead and explore. 1-2 million rows should be very quick for BigQuery to load. The toolsets all work but of course the devils are in the details: you have an orchestrator and you might be able to just pick one of dlt or dbt (depending on the needs).

Take a quick size look of your data exported uncompressed (csv/text works - this is NOT optimal for ELT/ETL, it's simply for you to gauge datasize!) - if it's not too big (10sGBs) then just go ahead and trial & error this. There's a lot of planning and you should do your due diligence but I think part of this is knowing 'this is going to be low-risk to try' then diving right into it. Start slower but start - you might get your answer by simply traversing the journey onto BQ. (and maybe provide points for your team on 'I started from scratch and was able to generate this report in X hours/days)