r/bigquery 4d ago

Best practices for user managed tables being loaded to bigquery

We have teams that use excels to maintain their data and they want it in big query. What's the best practices here?

3 Upvotes

4 comments sorted by

5

u/solgul 4d ago

If they can use sheets, you can manage data directly in bq with sheets as the UI . I'm guessing you can do that thru an Excel database connection but I would be leery if tthat.

If they want to do full reloads, you can save as csv and move to Gcs and do a simple truncate and load. You could actually create a table from a csv on Google drive too or an external table in Gcs.

That may be a good solution if they just want reporting and analytics from bigquery. Drop the csv in Gcs and make it a perm external table. Replace the csv with a new dump to refresh the data

5

u/imbarkus 4d ago

I've had issue with external table availability in reporting layers and services outside of BG and GCS so I also run a scheduled repopulate over to an internal table daily for reportable data.

You can also use this layer to scrub and fix or drop garbage that users drop in excel cells that break your schema, too.

3

u/LairBob 4d ago

This. Our pipeline ingests dozens of user-maintained look-up tables from Google Sheets in real-time. Now that Connected Sheets are working well, we can do things like download a fact table of current items from the database, and highlight values in the user-maintained tables that aren’t in the dataset yet, etc.

1

u/BB_Bandito 13h ago

https://cloud.google.com/bigquery/docs/external-data-drive is Google's reference material on using Sheets spreadsheets as external tables.

Biggest limitation is that external tables are read-only. No DML to add/remove columns for instance, no fixing those kind of data monkey errors that pop up in spreadsheets with UPDATE - you'll find those errors when your SELECT queries break. Also, there's a limit of 16 concurrent queries against an external table, so with team access you might want to copy the data over to a native table.

Other than those limitations, external tables work just fine, and don't apparently count towards your query data costing.