29 копий одного документа на проде — как удалить лишние и не потерять нужную

Меня зовут Светлана и я немного (или много) «душный» аналитик. В своей первой «пробе пера» я постаралась рассказать, каким же ветром и откуда меня забросило в мир IT и чем дышится в царстве бизнес- и системного анализа. В этот раз я решила поделиться одним реальным рабочим кейсом.

В моей практике есть такие сервисы, разработкой которых я занимаюсь «с нуля». В них настолько погружаешься, что со временем лучше самих стейкхолдеров понимаешь, как именно (с какого ракурса и в каком темпе) его необходимо дорабатывать и развивать. Один такой сервис у меня в особых фаворитах: мы с ним вместе уже два с половиной года, и я частенько грешу тем, что по сей день мониторю его работу и делаю рандомные проверки, «нянчу», холю и лелею его больше, чем хотелось бы моему ПМу.

Одной из последних доработок сервиса была реализация интеграции с новым модулем внешней системы. На ежедневной основе сервис должен направлять запросы на получение «пачки» документов, которые затем он должен связать по заданным параметрам с записями из базы данных заказчика и отобразить на интерфейсе сервиса.

Согласно предоставленной документации и информации от ответственного за сопровождение и настройку внешней системы, после отправки внешней системой очередной «пачки» документов к нам в сервис всем направленным документам должен проставляться статус о выгрузке, что позволяет избежать повторного получения одного и того же документа.

После доставки доработки прошло около двух месяцев: доработка давно принята стейкхолдерами, замечаний по работе сервиса не поступало, обсудили только ожидания стейкхолдеров по будущим доработкам. Казалось бы, всё хорошо. Но просто наслаждаться «тишиной» – это слишком скучно, а душа (пусть будет она, а не другое место) просит драйва и приключений, так что в какой-то из дней я по своему обыкновению решила проверить, что же там «под капотом» происходит.

Что-то не так…

По результатам изучения таблиц, в которые складываются данные о получаемых документах, и сравнения данных сервиса с первоисточником, я обнаружила, что в таблице сервиса записей больше, чем в первоисточнике (тут стоит пояснить, что у меня есть доступ к исходной базе данных внешней системы).

Приняв стойку озадаченного суриката, я пыталась понять, что же пошло не так. Первой мыслью, когда я это обнаружила, было «А где же Я накосячила?!»

Проанализировав данные, я обнаружила, что хоть количество записей в БД сервиса и превышало количество документов в первоисточнике, часть документов внешней системы всё ещё отсутствовала в БД сервиса. Моментально пронёсшимся в голове вопросом, естественно, был «почему?». Следующее открытие давало мне ответ на этот самый вопрос: в БД сервиса были дубли – по 15, 17 и даже 29 копий одного и того же документа. И это ещё не все документы были получены нашим сервисом.

Изучив даты получения дублирующихся документов, стало понятно, что внешняя система сама отдавала нашему сервису одни и те же документы. Не было ни ошибки интеграции, ни накладок и упущений реализации. Как я впоследствии выяснила, на стороне веб-сервиса внешней системы не была проставлена та самая настройка, которая должна была переводить переданные документы в статус выгрузки.

Что с этим делать?

На стороне внешней системы ошибку исправили довольно быстро – новые дубли больше не появлялись. Но исторические записи в БД на проде никуда не делись, а значит – нужно было привести в порядок собственную БД.

Первое решение, которое может прийти на ум, так как самое очевидное – найти все группы с одинаковым document_id, оставить одну запись, остальные удалить.

И вот на этом самом месте простая, казалось бы, задача перестала быть простой: одна (а может, и больше) из копий могла уже быть связана с бизнес-сущностями из базы данных заказчика. Если оставить просто самую новую запись, можно удалить ту, которая уже реально участвует в бизнес-процессе.

А какие действительно дубли?

В задачах очистки данных важно отделять техническую идентичность от бизнес-идентичности. Полностью одинаковых строк в БД сервиса не было: у них отличались внутренний id, даты создания и изменения, зачастую статус и наличие связей.

Но все эти строки описывали один и тот же документ внешней системы. Надёжным признаком дубля был document_id — идентификатор исходного документа, который нам передавался и по которому можно было найти документ в базе данных внешней системы.

Для пользователя и внешней системы это один документ. Для базы — множество разных строк. Проблема для всех – в интерфейсе сервиса отображается множество клонов одного документа.

Ищем «горца»

Для таких кейсов удобно сначала определить «горца» — единственную запись группы, которую необходимо гарантированно оставить (в конце концов, «остаться должен только один!»). И только после этого уже строить набор на удаление.

Очевидные решения далеко не всегда подходят

Для понимания дальнейших рассуждений поясню статусную модель документа после того, как он получен сервисом от внешней системы:

получен → привязан (установлена связь документа с бизнес-сущностью) → выгружен (данные документа выгружены в систему заказчика).

Статусов по факту больше, но для дальнейших разъяснений я немного упростила статусную модель.

Итак, какую же запись нужно оставить?

Оставить запись с максимальным id: внутренний идентификатор удобен как технический критерий, но сам по себе он ничего не говорит о ценности записи. Самая новая по id строка может оказаться «пустой» копией.

Оставить самую позднюю по дате изменения – это уже выглядит разумнее, но это не гарантирует того, что останется запись, которая привязана к бизнес-сущности. Т.е. необходимо также учитывать приоритет статуса: оставить строку в статусе «получен» неправильно, если есть строка с таким же document_id в статусе «привязан».

Удалить все непривязанные записи? Тоже не работает: если в группе вообще нет связанных записей, одна копия документа всё равно должна сохраниться. Иначе дедупликация превращается в полное удаление документа.

Использовать DISTINCT? DISTINCT решает задачу одинаковых строк, но не одинаковых бизнес-объектов, ведь он убирает повторяющиеся строки из результата, оставляя лишь уникальные комбинации значений по указанным столбцам. Но в моём случае были разные id, временные метки и ссылки, которые делают записи различными на уровне SQL, а DISTINCT не применимым.

То есть проблема заключалась не в том, как найти дубли, а в выборе записи, которая должна пережить очистку.

После разбора данных правило у меня получилось таким.

Для каждого document_id:

1.      найти все записи;

2.      если среди дублей есть записи, связанные с бизнес-сущностью, оставить запись в наиболее приоритетном статусе. Если таких записей несколько — оставить последнюю по дате изменения;

3.      если связанных нет, оставить последнюю запись группы;

4.      все остальные записи — это кандидаты на удаление.

Понятие «связанная» тоже пришлось формализовать. В реальной системе оно определялось наличием ссылки на бизнес-сущность в базе данных заказчика. Это принципиальная часть решения: связанность здесь не просто технический флаг. Она показывает, что запись уже стала частью бизнес-процесса и её удаление может нарушить целостность данных и что-то сломать.

Другими словами, если доставка пиццы не работает, а ужин смогут приготовить либо Микеланджело, либо Донателло, выбирать Рафаэля только потому, что он последним был на кухне, или Леонардо, потому что он лидер команды, может показаться хорошей и как будто бы технически оправданной идеей, но явно не принесёт ценного результата. А если Донателло сможет приготовить только бутерброды, в то время как Микеланджело – полноценный ужин, то выбор явно в пользу Микеланджело. С дублями происходит то же самое. Сначала нужно определить, какая характеристика делает запись ценной для бизнес-процесса, и только потом использовать технические признаки.

Первый результат — не DELETE

Самая опасная часть подобных задач — это желание сразу получить финальный DELETE и забыть о дублях. Но для данных на проде «с ноги» перейти к удалению будет далеко не самым правильным решением.

Первый результат очистки — контрольная выборка: что именно алгоритм собирается оставить и удалить.

До изменения данных стоит сформировать предварительную выборку по каждой группе дублей, по которой можно явно отследить document_id, сколько было записей, которая запись является «горцем» и сколько записей подлежит удалению.

Такая выборка позволит проверить, что:

·      для каждой группы выбран ровно один и именно «горец»;

·      если в группе есть связанные записи, «горец» тоже связан;

·      если связанных записей нет, «горец» всё равно существует;

·      для каждой группы выполняется проверка, что количество записей на удаление на единицу меньше общего количества записей в группе дублей;

·      после очистки id оставшейся записи совпадает с id заранее определённого «горца».

Особенно важен последний пункт. Недостаточно убедиться, что после очистки дублей больше нет и количество удалённых строк совпало с ожидаемым. Нужно проверить, что в каждой группе осталась именно та запись, которую алгоритм заранее определил как «горца». Иначе можно получить формально идеальный результат — количество групп дублей равно нулю, — но при этом сохранить не ту запись и потерять нужную бизнес-связь.

И вот после проверки контрольной выборки уже можно формировать набор для DELETE.

Пограничные сценарии, или «что ещё могло пойти не так»

Основной алгоритм занимает несколько строк. Но чтобы проверить, что выбранное решение действительно надежно, нельзя забывать о пограничных сценариях, которые важно заранее описать при постановке задачи.

Вполне наглядно такие сценарии можно описать парой «сценарий – ожидаемое поведение». На примере моего кейса можно выделить следующие пары:

Вроде бы все возможные сценарии предусмотрели. Но не стоит забывать про весьма полезный процесс – откат.

Можно качественно описать алгоритм, можно продумать все возможные пограничные сценарии, но неспроста же ещё в школе нас приучали к «страховке», когда на физре перед прыжком даже с самой небольшой высоты стелили маты для приземления. Вот и с любыми шаманствами с данными на проде также: если очистка физически удаляет данные, до запуска стоит сохранить резервную копию, достаточную для восстановления, если что-то пойдет не так.

Вот теперь можно «пускаться во все тяжкие» с данными на проде. Удачи нам!

Так каков же в итоге «путь самурая»?

В аналогичных ситуациях системный аналитик может самостоятельно, если обладает достаточными знаниями, написать алгоритм для очистки от дублей данных на проде. Но главной его задачей всё же является качественное описание алгоритма действий, прочитав который, ответственный за «чистку» сможет однозначно сформировать понимание того, что же необходимо сделать.

Другими словами,

хорошее требование к очистке данных отвечает прежде всего не на вопрос «что удалить?», а на вопрос «что система не имеет права потерять?».

SQL-запрос в моей истории — всего лишь инструмент реализации и писала его не я (к моему огромному облегчению). Моей основной задачей было определить правило целостности данных, которое должно было выполняться после очистки, а команда разработки уже навела порядок в данных.

Этот кейс не претендует на универсальный рецепт для похожих случаев. Это просто один из реальных случаев из моей практики, с которым я столкнулась буквально месяц назад. Возможно, для кого-то он покажется очевидным, но кому-то (хотелось бы верить) поможет вовремя задать правильный вопрос: не «что удалить?», а «что нельзя потерять?».