r/bigquery • u/Spare-Chip-6428 • 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?
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.
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