BQ pain points: feels more like an API than a data warehouse

[dataders]

background

our friends at BigQuery are very interested to hear how they can improve the product for users of dbt. They'd like to target specific pain points. The more context you can share on your request the better. Think about including things along the lines of:

• scenario -- a clear and concise description of what you do or don't want to happen.
• minimum reproducible example -- I know this is hard, but makes it so easy to have the problem fixed!
• workarounds-- A clear and concise description of any alternative solutions or features you've considered.
• benefit/impact -- what kind of use case would this improve? What dbt workflows get better?

focus area for this discussion: when is an API also a data warehouse?

If you're a data professional expecting BigQuery to be a traditional data warehouse, you're going to have a bad time.

We've heard of a number of opportunities in this area. Here's a few examples (feel free to grab one and flesh it out more!)

• docs are geared more towards developers than data warehouse engineers
• some of SQL API is still missing (can't tell from info_schema if table is sharded)
• If a BigQuery operation fails because of a quota limit, the API returns the HTTP 403 Forbidden status code.
• query timeout limit conflicting with num_retries (slack thread)

[github-christophe-oudar]

Part of the SQL API missing, we definitely need SQL for https://cloud.google.com/bigquery/docs/managing-table-schemas#add_a_nested_column_to_a_record_column because it prevents updating the schema automatically for incremental models when nested records are involved. (I reported it today to my Google rep)

I'm also hoping to see https://issuetracker.google.com/issues/228877713 happen someday to ease integration testing on BQ 🙏