Lazy evaluation shouldn't be a problem, it just delay where the error will occur. You can check each step by forcing the execution, typically run a nrow to count the number of rows and it will force the execution of the underlying steps, and will eventually show if there is an error.

For exemple you can do a area_of_interest |> nrow() to see if everything is ok with the copy between databases. For information, to build area_of_interest dbplyr technically download the first table from the first database on your machine and copy it to the second one in a temporary table. You can do it yourself it once for all instead of of copy each time by using collect(), then copy_to, eventually you can create a definitive table there. Otherwise, you have to accept that there will be a delay each time for the download and upload of the data.

Additionnaly, you can note that you can connect the first database to the second directly between themself using a Foreign Data Wrapper. It appears like a normal table in postgres, but it technically is remote, so you can filter, join, etc... with it directly in postgres, and postgres is the one handling the eventual download of data. Typically in your case you could create a FDW on the second database that links to the first one, because the second one is the one with the bigger tables.

For the join, it should work with dbplyr between 2 tables. It's just a little different that what you did. 'area_of_interest' is the local name of the table, so it doesn't exist in SQL that execute on your database. Fortunatly, 'join' in dbplyr automatically attribute alias (RHS and LHS for right and left sides) on your tables that you can use in SQL, and if you want to directly use SQL you need to use the sql_on argument:

all_obs |>
    dbplyr::inner_join(
        y = area_of_interest,
        sql_on = "ST_Intersects(RHS.the_geom, LHS.epsg28992_centroid)"
    )

Lazy evaluation shouldn't be a problem, it just delay where the error will occur. You can check each step by forcing the execution, typically run a nrow to count the number of rows and it will force the execution of the underlying steps, and will eventually show if there is an error.

For exemple you can do a area_of_interest |> nrow() to see if everything is ok with the copy between databases. For information, to build area_of_interest dbplyr technically download the first table from the first database on your machine and copy it to the second one in a temporary table. You can do it yourself it once for all instead of of copy each time by using copy_to (I don't think you have to 'collect' before, and maybe dbplyr is smart enough to download and upload chunk by chunk if the data is big...). Eventually you can create a definitive table there from this temporary table. Otherwise, you have to accept that there will be a delay each time for the download and upload of the data.

Additionnaly, you can note that you can connect the first database to the second directly between themself using a Foreign Data Wrapper. It appears like a normal table in postgres, but it technically is remote, so you can filter, join, etc... with it directly in postgres, and postgres is the one handling the eventual download of data. Typically in your case you could create a FDW on the second database that links to the first one, because the second one is the one with the bigger tables.

For the join, it should work with dbplyr between 2 tables. It's just a little different that what you did. 'area_of_interest' is the local name of the table, so it doesn't exist in SQL that execute on your database. Fortunatly, 'join' in dbplyr automatically attribute alias (RHS and LHS for right and left sides) on your tables that you can use in SQL, and if you want to directly use SQL you need to use the sql_on argument:

all_obs |>
    dbplyr::inner_join(
        y = area_of_interest,
        sql_on = "ST_Intersects(RHS.the_geom, LHS.epsg28992_centroid)"
    )