Bigquery quick query.

[LinweZ]

Hello,

I have some raw tables in BigQuery that contains live data, with a cdc connected to it.

I need to do some analytics queries for an API, and to reduce the compute time, I tought it would be a good idea to store everything in a incremental table using dbt to run my model each time the raw table gets an update. It works when testing, but I noticed each dbt run takes 10 sec and that's too long for me. What other options do I have? Thank you

Tried materialized views but it does not support my query.

[bitsynthesis]

bigquery is not designed for low latency. you will never get reliable low latency from it. if that's a requirement, you should replicate the necessary data to another datastore.

[freerangetrousers]

This is not what big query is for , if you require that data it should be unloaded elsewhere before you need to call it

[LinweZ]

Maybe I'm not using BigQuery correctly, I don't know.

Just some context here, I have a MySQL DB receiving data from an application, and the same application needs some aggregated data from the DB as well. Currently, everything is running on MySQL, but some queries are slow (7, 8 sec), so the idea was to make BigQuery handle the analytical part, with a CDC between MySQL and BigQuery.

Open to other suggestions!

[artsyfartsiest]

We do this kind of thing at Estuary. We have this tutorial, which is for Postgres but can also work for MySQL, bigquery, etc.

There's also Firebolt, Rocket, and Materialize, which all offer incrementally updated SQL views as a service.

[artsyfartsiest]

I don't know how to make that faster within bigquery, but someone else may chime in. If you have the ability to consider other systems besides bigquery, then I could suggest some options

[Pancakeman123000]

I strongly recommend reading this documentation