Thinking of BigQuery for data warehousing

[u/Nickeddd]

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?

[u/Why_Engineer_In_Data]

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.

[u/Nickeddd → u/Why_Engineer_In_Data]

Which ETL tool would you recommend?

[u/Why_Engineer_In_Data → u/Nickeddd]

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.

[u/monkeyinnamonkeysuit → u/Why_Engineer_In_Data]

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.

[u/Why_Engineer_In_Data → u/monkeyinnamonkeysuit]

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.

[u/Nickeddd → u/Why_Engineer_In_Data]

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.

[u/Why_Engineer_In_Data → u/Nickeddd]

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)

[u/greenleaf187 → u/Why_Engineer_In_Data]

Hey there! I’m trying to conduct a Google hackathon, and I’m struggling with the small details with my use. Can I dm you to throw some ideas?

[u/monkeyinnamonkeysuit]

To answer this question you need to tell us so much more about your data and environment, but the most telling one for the bigquery question is how much data you actually have.

In the nicest way possible, if you are new enough to data engineering that you are evaluating this question using the criteria you have provided, you are too new to data engineering to make this decision alone if the outcome matters at all. The criteria you have provided are trivial to do in any modern data platform.

I'm going to guess from your "apis and a few excel files" comment that you are much too low a volume of data to justify bigquery. And the success or failure of a data warehouse lives and dies on a lot more than platform choice.

That said, if your requirements are simple then you can probably make it work on more or less any platform, it just wont be the right tool for the job and will make things harder/top expensive/worse user experience etc.

[u/Nickeddd → u/monkeyinnamonkeysuit]

Getting into the details, I have thought of a medallion architecture, keeping it serverless, implementing SCD2 in a couple of tables. We have one fundamental source with 1-2 millions of rows and +20 columns, where most of the business logics and others analysis comes from. Besides that, there are 3 or 4 more sources with around 50k rows each.

[u/monkeyinnamonkeysuit → u/Nickeddd]

All of your data would fit in RAM on a modern laptop. The shape and nature of your data does not drive this conversation at all, there is no platform out there that can't do what you are asking. Bigquery might still be a reasonable choice. What tech platforms does your business have already? Who is going to be building and maintaining this, what skills do they have and who will be the end users consuming it? What BI tooling is available to you? What cadence does the data arrive in? Whats your aspiration for data growth, both in terms of number of sources and total rowcount?

[u/Nickeddd → u/monkeyinnamonkeysuit]

The data comes in hourly, so constant refreshes will be needed, so thats why we use tools that allows scheduled updates like PowerBI. Also, I'll be the one building and maintaining this data warehouse

[u/Odd-String29 → u/Nickeddd]

That little data is no problem for BigQuery. You can argue you can run your own database locally or in the cloud somewhere, but I think all of those might actually be more expensive than doing it in BigQuery.

[u/bayouski]

BigQuery sounds like a pretty reasonable choice here, especially if the main use is analytics/BI. it's fully managed, so there's basically no database infra to babysit, and for a smaller workload the on-demand pricing can stay pretty cheap. just learn partitioning + how query costs work early, because badly written queries can scan way more data than expected.

the bigger thing i'd think about is ingestion. since most of your sources are APIs + Excel, i'd avoid building a bunch of custom scripts unless you actually need to. Coupler.io is one option for getting both into BigQuery on a schedule, including generic JSON APIs.

i'd start small: load a couple of real sources, build one BI use case, watch the query costs for a month, then decide if it makes sense as the company-wide warehouse.

[u/Nickeddd → u/bayouski]

Hey, thanks for the suggestion! Any YouTube channel you recommend for learning more about BigQuery and this kind of tools?

[u/captain_obvious_here]

I don't know a single person who regretted choosing BQ.

It's a great tool, no matter how much data you have. And it stays pretty cheap if you know what you are doing.

[u/sois]

It's a really good choice if you're not going to have a super gigantic warehouse. There's also a very generous free tier, and it also comes with data form, which will help you build your models. Let me know if you need some help. I work at a Google partner.

[u/lars_jeppesen → u/sois]

What do you mean by "not super gigantic warehouse"? It's basically limitless regarding storage.

[u/sois → u/lars_jeppesen]

Thinking of cost. Querys get expensive, but work well. I just like to remind clients of potential costs. they can decide between speed or $$$

[u/PolicyDecent]

I'd definitely go with bigquery, since it's almost free for the small data, and requires no maintenance.
You just need a ELT solution to ingest data and transform it.
To ingest you can use fivetran or open source alternatives like ingestr, dlt, airbyte.

To transform you can use bigquery builtin dataform, dbt or bruin.

Disclaimer: I'm the developer of bruin and ingestr. It's open-source and exactly built for these scenarios. Feel free to reachout if you need any help to use it. Easiest in the market to use.

[u/Turbulent_Egg_6292]

Curious? Which is your goal? Data warehouse? Processing and consuming through apis? Batch processing/real time processing? I'd only recommend bq if you dont need to consume the data for dashboards/apis and if you are very sure you will be batch processing

[u/lars_jeppesen → u/Turbulent_Egg_6292]

How so? It's amazing for dashboards

[u/Turbulent_Egg_6292 → u/lars_jeppesen]

Unless you are hitting very small datasets, dashboards with high latency constraints force you to use use BI engine and general costs are very high in comparison to Clickhouse. For small data and non latency requirements bq is okey (i assume you'd run on demand), but anything past a certain level clickhouse is king for consumkng thw data

[u/Content-Parking-621]

Went through the same decision last year, BigQuery won for us too. Solid pricing and query speed. Since you're on APIs and Excel, Windsor.ai made syncing those into BigQuery painless, saved me weeks of pipeline work honestly.

[u/BTA_SR]

For context, our team at Beyond the Arc assesses AI visibility for banks and fintechs. We have web analytics data (Google GA4) and other sources flowing into BigQuery. With the native connectors for Claude Code (and now there’s a new MCP server), natural language queries of the data are amazing. We’ve iteratively built up complex filters, run ad hoc reports, built views and tables. These capabilities aren’t exclusive to BigQuery, but is something you should consider whatever direction you decide to go.

[u/boeingb17]

I use BQ for my data warehouse as a small business, and it's fantastic. Since so much of the pricing is process-based, I can let my data sit for storage-only fees and pay to pull when needed. All of my customer-facing databases are on dedicated MySQL or PG servers since I don't want to be surprised. But for internal data warehousing, BQ gives me the most powerful SQL option at minimal cost. When I use it, I pay for it, but it's power is in how it scales down so elegantly.

[u/lars_jeppesen]

We use BigQuery and love it. You need to be aware of how to optimize your searches to avoid being robbed but other than that it's great.
Materialized views are fantastic for automatically grouping data on all kinds of ways and use those for analytics queries. Amazing imho

[u/sydneysweeney69]

I suggest go with snowflake , it’s not tied to any cloud ecosystem. It’s got great support and it’s not that expensive. Bigquery is dogshit and so is google . They don’t care about building enterprise warehouses . That’s not their business model or moat .
Infact they are so lost , they don’t even have proper ai or frontier models .

[u/PepSakdoek]

For the size of your data, maybe start with Ms Access.

Bigquery is amazing for speed of querying big datasets with billions of rows. If you've ever run a query on a prod environment that took 1h+ to execute only to remember you forgot one column, will help understand getting the the bliss of that same query done in less than 10s.

But as the data sizes rise so do the costs! 

[u/PolicyDecent → u/PepSakdoek]

Please, noone should use MS Access as the DWH. It should be removed from the world.

[u/PepSakdoek → u/PolicyDecent]

Yes... It's about experiencing the pain of slow queries. In their data-size space.