Currently the flow for deleting families in seqr is to attach all the partitions for the project, insert all the rows for the deleted families with a negative sign, and then call OPTIMIZE until the deduplicating merge completes. This works, but for large projects the OPTIZE is quite slow, and we have run into bugs where it does not properly handle cases where families have corrupted data with duplicated rows.
A simpler flow is to only insert rows for the project that do not have the impacted family guids. This means for large projects the initial INSERT query is much larger (all the rows EXCEPT the deleted families instead of all the rows in the families) but once that insert completes the table is in the correct state and will not rely on optimization
INSERT INTO {staging_table} SELECT
COLUMNS('.*') EXCEPT (sign, n_partitions, partition_id),
1 AS sign
FROM {production_table}
WHERE (family_guid NOT IN {family_guids} AND (project_guid = {project_guid})
Currently the flow for deleting families in seqr is to attach all the partitions for the project, insert all the rows for the deleted families with a negative sign, and then call OPTIMIZE until the deduplicating merge completes. This works, but for large projects the OPTIZE is quite slow, and we have run into bugs where it does not properly handle cases where families have corrupted data with duplicated rows.
A simpler flow is to only insert rows for the project that do not have the impacted family guids. This means for large projects the initial INSERT query is much larger (all the rows EXCEPT the deleted families instead of all the rows in the families) but once that insert completes the table is in the correct state and will not rely on optimization