Clean up unused _airbyte_tmp tables

[ChristopheDuong]

Tell us about the problem you're trying to solve

When syncs are canceled, encounter exceptions or something unexpected may happen, it is possible to get a "messy" state on destinations where "zombies" _airbyte_tmp tables are kept around...

See this user's question on slack:
https://airbytehq.slack.com/archives/C01MFR03D5W/p1627380195366300
with multiple _tmp tables for the same stream:

or this other user's worrying about it here:
https://airbytehq.slack.com/archives/C01MFR03D5W/p1632830821410100

I can't find any documentation on best practice for tidying these tables, is there any potential downside to deactivating the sync, deleting the old temp tables and then re-syncing? I’m at my wit’s end (details on issue #5435)!

Describe the solution you’d like

A way to clear up _tmp tables that may be confusing to users.

Describe the alternative you’ve considered or used

Ask people to safely delete them if no syncs is currently running

[lizdeika]

Hi,
We have 128 of such leftover failed sync tables in 1 (one) schema. We have more than one schema.

[segv]

We've had a few failed syncs from a farily big db; currenty looking at 600+ tmp tables. this makes refreshing the schema in datagrip/dbeaver painfully slow, and makes autocompletion much less useful.

[marcosmarxm]

@ChristopheDuong could we add a script in normalization to always delete tables with startwith _airbyte_tmp*? Let me know what do you think about it

[marcosmarxm]

Of course if normalization failed, the tmp tables will continue there. Adding to normalization can be a easy solution but don't solve the problem.

[ChristopheDuong]

@ChristopheDuong could we add a script in normalization to always delete tables with startwith _airbyte_tmp*? Let me know what do you think about it

It's not really normalization's job to clean up the _airbyte_tmp tables that are "internal" tables from destination connectors. But sure, you can do that.

[grishick]

This cleanup task seems to me like something that should be orchestrated by the platform, because this cleanup task can be invoked in these scenarios:

• destination connector failed and normalization was not run
• normalization started but failed
• normalization succeeded, but for some reason failed to delete the *_tmp tables

my suggestion is that we add a destination::cleanup task to the protocol and have the platform call it after each job. We can also allow users to manually trigger it.

[michel-tricot]

Is there a world where the destination tmp naming is a bit smarter and the next time the destination runs it looks at left over artifacts and delete what wasn't deleted in the previous run?

[grishick]

Is there a world where the destination tmp naming is a bit smarter and the next time the destination runs it looks at left over artifacts and delete what wasn't deleted in the previous run?

yes, however, if the sync is canceled, the messy state will persist until the next job runs.

[ChristopheDuong]

yes, however, if the sync is canceled, the messy state will persist until the next job runs.

That's still a lot better than accumulating 600 unused tables from 6 months ago :)

[michel-tricot]

Also it doesn't mean we shouldn't do our best to clean up on exit (cancel...)

[peter279k]

I also have the same problem about unused _airbyte_tmp_* tables.

I cancel the one of scheduled job because it runs for about whiles. And these above tables are presented since then.

Should I clean them safely with running the SQL?

I also found this discussion and I think it's okay to delete them manually.

[NAjustin]

For us, it was over 3,500 of these tables.

I do think that cleanup needs to happen here, but it also seems silly that _airbyte_tmp_* tables are completely ignored after a failed job. For example, I just had a HubSpot connector that synced several billion rows of data, but because an object was configured that wasn't enabled on this account (feedback_submissions), a 429 error was thrown and the whole job was aborted. So these temp tables were left, but could have been used to resume from a subsequent sync once the issue was corrected vs. restarting a large sync from scratch.

(I understand this is slightly out of scope for this issue, but resumability is worth considering before tables are purged wholesale on cancel/failure.)

For anyone needing a temporary workaround, you can use a SQL script like this to delete them from within BigQuery (for folks who aren't comfortable in the CLI anyway):

DECLARE dry_run BOOL DEFAULT true; --CHANGE TO false TO ACTUALLY DROP!
DECLARE pattern STRING DEFAULT r"\_airbyte\_tmp\_%"; -- raw string only needs one backslash for _ character (so it isn't treated as a single char wildcard)
DECLARE project STRING DEFAULT "project-name";
DECLARE dataset STRING DEFAULT "DatasetName";
DECLARE retention_period STRING DEFAULT "7 DAY";
DECLARE tbls ARRAY<STRING> DEFAULT [];
DECLARE i INT64 DEFAULT 0;
EXECUTE IMMEDIATE """SELECT ARRAY_AGG(project_id||"."||dataset_id||"."||table_id ORDER BY table_id ASC) FROM `"""||project||"."||dataset||""".__TABLES__` WHERE type=1 AND table_id LIKE @pattern AND TIMESTAMP_MILLIS(last_modified_time) < TIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL """||retention_period||""")""" INTO tbls USING pattern AS pattern;
SELECT ARRAY_LENGTH(tbls); -- Returns the number of tables matched
IF
	NOT dry_run
THEN
	WHILE tbls[SAFE_OFFSET(i)] IS NOT NULL DO
		EXECUTE IMMEDIATE "DROP TABLE `"||tbls[OFFSET(i)]||"`";
		SET i = i + 1;
	END WHILE;
END IF;

Keep in mind this won't be as fast as deleting them through the bq CLI or an environment where you can call the drop asynchronously (this will only run at about 2 tables per second or so), but gives non-programmer types a way to see a list of tables matching a criteria by last change date (look at results of the first query result while in dry_run mode) or actually drop the matching tables (changing dry_run to false)

Either way, I think we should really consider ways to use synced data to minimize duplicate syncing where possible. and then clean up temp tables that are incomplete, unsafe, or blank.

[Zatte]

If i may suggest quick bandaid to this (requires dev work though):

1. Temporary tables can be created with a default table expiration of 2 days? (maybe even configurable).
2. Temporary tables can reside in their own dataset (where default table expiration can be configured in a case by case basis).

Neither solutions would block proper cleanup logic in case of exceptions but might be quicker and easier to implement and solves 90% of the pain experienced in this thread.

[BravoDeltaBD]

Any update on this issue? I just found a schema in our Snowflake warehouse with roughly 11'000 undeleted _airbyte_tmp tables. This resulted in the following DBT error during the Normalization step:

Too many schemas in schema  "FOO_DB"."BAR_SCHEMA"! dbt can only get
        information about schemas with fewer than 10000 objects.

[grishick]

The new version of Snowflake destination connector is not creating tmp tables anymore. If you update your Snowflake connector to the latest one, you can delete all those airbyte_tmp tables.

[TimothyZhang7]

We are also encountering this problem, we have over 6000 of such tables in a Bigquery destination.
Any update on this issue?

[grishick]

If you have upgraded to the latest BigQuery destination connector and you are using staging inserts loading method, you can delete _airbyte_tmp tables

[TimothyZhang7]

If you have upgraded to the latest BigQuery destination connector and you are using staging inserts loading method, you can delete _airbyte_tmp tables

Thanks for the clarification.

[ABitShift]

The same happens with MongoDB destination, we have zombie collections named airbyte_tmp_id3_Source_Stream.

Not sure if this happens after a failed job or after Airbyte server crash.
Would it be the "Airbyte responsability" to delete these zombie collections if the server crash (reconciliation)?

[lhvubtqn]

Same here! Our Github -> BigQuery connection creates a ton of _airbyte_tmp tables whenever it fails. Really need to fix this issue!

[grishick]

the latest version of BigQuery destination does not create _tmp tables anymore.

[grishick]

Same here! Our Github -> BigQuery connection creates a ton of _airbyte_tmp tables whenever it fails. Really need to fix this issue!

what version of BigQuery destination are you using?

[lhvubtqn]

Same here! Our Github -> BigQuery connection creates a ton of _airbyte_tmp tables whenever it fails. Really need to fix this issue!

what version of BigQuery destination are you using?

We are still using version 1.2.9. Will try upgrading to 1.2.17. Thank you!

[grishick]

• destination-bigquery stopped creating _airbyte_tmp tables starting version 1.2.13
• destination-snowflake stopped creating _airbyte_tmp tables starting version 0.4.46
• destination-redshift stopped creating _airbyte_tmp tables starting version 0.3.55

The newer versions do not delete airbyte_tmp tables created by previous versions, so if you still see any of these tables after the upgrade - feel free to drop them.

Other destinations will stop creating _airbyte_tmp tables before they are promoted to GA.

[cgardens]

This should be fixed.