I'm sorry if this is a newbie question but I can't figure out how to update data in my persistence layer. I have an asset that uses

bigquery_pandas_io_manager

. It also receives a dataframe from an upstream asset. What I want to do is to persist the incoming dataframe so that only new data are added and existing rows updated based on a

datetime

index. Maybe I'm missing something elementary but I can't find out how to get access to the existing data in the database. The easiest thing would be, of course, to rely on the index and let BigQuery figure out which rows should be added which ones should be updated and which ones should be left untouched. But is something like that even possible? Thank in advance for any help on this.

Does your computation need to happen in-memory or within-the-database? If your upstream asset is also managed by the

bigquery_pandas_io_manager

, you can use the

dagster-gcp

resource to run a SQL query to do the upsert for you on BQ. You can define the dependencies using the

non_argument_deps

parameter on an asset definition. This assumes that you don't want to be loading (what I assume) is a huge table into memory.

Thanks for the advice! My upstream asset is actually coming from a different source, namely from a 3rd party API. I need to parse the data into a dataframe (that I validate against a Pandera schema using the

dagster_type

annotation) and then persist the result into my BigQuery table. The problem is that some of the new data entries may already exist in the database. Now that I think about it, I probably want to ignore the ones that are already saved (i.e. not update). It seems I can't run a query using the

dagster-gcp

resource in this case, can I?

Ah, I meant to send this link too on how to run a SQL query: https://docs.dagster.io/integrations/bigquery/reference#executing-custom-sql-commands-with-the-bigquery-resource

I don't know how I missed that section about SQL queries! I think I should be okay figuring it out with that help, thanks a lot!