Automatically delete _airbyte_tmp tables

Platform, Deploy & Infra Issues
,
1

Hi,

I have question about automatically deletion of tables with prefix _airbyte_tmp*. Currently we do a sync every night with our source and target db. After replication is complete, there are still _airbyte_tmp* tables. Every new sync will add another _airbyte_tmp tables, so the size of target db is increasing rapidly. So we want to delete these tables after replication is done. How it can be done? We use dbt and custom repository for some data transformations.

Of course I can use something like this:

BEGIN 
   FOR table_name IN (SELECT tablename FROM pg_tables WHERE tablename like '_airbyte_tmp%') 
   LOOP 
      EXECUTE 'DROP TABLE IF EXISTS ' || table_name; 
   END LOOP;

but I can’t determine CRON to run it with, I don’t know when the replication will finish.