Hi all. Probably it was answered many times already. What would be the best way to combine frequent key based replication (PostgreSQL in GCP thus no CDC, and Mongo say every hour) with nightly full syncs? It’s possible of course to setup 2 different sources and destinations for the same database, configure them differently and combine them with DBT. I am wondering if there is a better way out of the box?

Just curious, what's the reason for such setup? Is it desire to capture deletes in destination snapshot without having WAL?

Yes, it’s one of the reasons. WAL is not available in GCP managed Postgres unfortunately.

Perhaps you could create logical IS_DELETED column in the source. That would require modification of service that writes to PSQL.

one of the options for sure.

i think the safest for the moment is to setup two different connections between the same source/destination pair but with different frequency and sync modes as described here: https://docs.airbyte.io/architecture/connections Then handle in DBT a compare between your hourly incremental vs nightly full refresh streams to flag deleted rows Note that you can leverage the “prefix” field in the connection page to make sure both connections are not writing to the same table in the destination

Thank you

For combining two sources in dbt, see: https://discourse.getdbt.com/t/how-to-create-near-real-time-models-with-just-dbt-sql/1457