Вопросы на собеседовании: Дата-инженер

Такую деградацию я начинаю разбирать с профиля выполнения, а не с переписывания SQL наугад.

• Сравниваю текущий профиль с последним быстрым запуском и ищу потерю отсечения партиций, размножение строк при соединении, перекос данных или сброс на диск из-за нехватки памяти.
• Выбираю исправление по результатам профилирования: фильтрую по исходной колонке партиции, предварительно агрегирую большую сторону соединения, меняю порядок соединений или устраняю дубли ключей.
• Дополнительно сверяю количество строк и последние загрузки, потому что бэкфилл или случайное дублирование данных могут объяснить замедление лучше любого изменения запроса.

Ключи партиционирования и кластеризации я выбираю по реальным шаблонам доступа, а результат проверяю по объёму сканирования.

• Большую таблицу фактов обычно партиционирую по дате события, потому что она чаще всего участвует в фильтрах и ретенции, позволяет отсекать старые данные и дёшево удалять целые периоды.
• Для кластеризации беру следующие по частоте колонки фильтров и соединений из истории запросов, например арендатора, клиента или продукт, а не полагаюсь на интуицию.
• Не допускаю избыточного дробления и кластеризации, поскольку мелкие файлы, метаданные и перекластеризация требуют ресурсов, и сравниваю объём прочитанных данных на типичных запросах до и после изменения.

Если один воркер сильно отстаёт от остальных, я в первую очередь проверяю перекос данных по ключу соединения.

• Считаю строки по каждому ключу с обеих сторон и ищу горячие значения, например NULL, технического арендатора по умолчанию или одного очень крупного клиента.
• Обрабатываю NULL и другие ненужные для соединения ключи отдельно, а небольшую таблицу при возможности рассылаю всем воркерам, чтобы убрать перераспределение данных.
• Для неизбежных горячих ключей применяю салтинг с обеих сторон, чтобы распределить нагрузку, а адаптивное выполнение считаю дополнительной защитой, но не заменой исправлению распределения данных.

Выбор всех колонок лишает колоночное хранилище одного из главных преимуществ производительности.

• Колоночный движок читает только указанные поля, поэтому звёздочка на широкой таблице событий может превратить небольшой запрос в терабайты сканирования и заметные расходы на вычисления.
• Она также ослабляет контракты данных: нижестоящие модели незаметно подхватывают новые колонки, а переименование может вызвать ошибку далеко от источника.
• В моделях пайплайна я требую явные списки колонок и проверяю их линтером или правилами dbt, оставляя звёздочку только для временного интерактивного исследования.

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

• Дубли в измерении заставляют каждую подходящую строку фактов повторяться, поэтому итоговые суммы могут удвоиться и при этом выглядеть правдоподобно.
• Я добавляю тесты уникальности ключей измерений и останавливаю сборку dbt до публикации данных, если ожидаемая кардинальность нарушена.
• Также явно фиксирую гранулярность модели и сравниваю число строк на входе и выходе там, где преобразование должно её сохранять.

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

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

Сначала я связываю рост расходов с конкретными нагрузками, а не ввожу общие ограничения вслепую.

• Группирую историю запросов по пользователю, сервисному аккаунту и отпечатку запроса, чтобы найти частые полные сканы, слишком частые пересборки dbt, забытые среды разработки и случайные перекрёстные соединения.
• Затем настраиваю автоматическую остановку, размеры вычислительных кластеров под разные нагрузки, выбор между оплатой по запросу и зарезервированными слотами BigQuery, а также лимиты сканирования с оповещениями.
• Помечаю запросы по пайплайну и команде, чтобы у постоянных расходов были владельцы, а новые отклонения обнаруживались быстро.

Полноту фактов я сохраняю с помощью специальных строк измерения для отсутствующих ссылок.

• NULL в ключе измерения выпадает при внутреннем соединении, поэтому метрики в разрезе могут потерять строки и перестать сходиться с итогами источника.
• В каждом измерении создаю зарезервированные суррогатные строки для состояний «неизвестно» и «не применимо», а загрузка направляет в них NULL и несопоставленные натуральные ключи.
• Это сохраняет полноту соединений и позволяет измерять долю проблемных записей, а также различать отсутствие данных в источнике и ошибку сопоставления, поскольку исправляются они по-разному.

Дубли я устраняю один раз в staging-слое, как можно ближе к загрузке.

• Вместе с владельцем источника определяю признаки дубля, затем использую row_number по бизнес-ключу с сортировкой по времени источника и стабильному дополнительному признаку, чтобы оставить самую свежую запись, а в поддерживающих движках применяю QUALIFY для ясной реализации.
• Окно обработки покрывает реальный срок повторной доставки источника, потому что поздний дубль может снова испортить уже очищенную партицию.
• Для каждой загрузки записываю долю дублей и оповещаю о скачках, которые часто раньше других признаков показывают массовые повторы запросов в источнике.

Суррогатные ключи отделяют идентичность сущности в хранилище от нестабильных идентификаторов источников и поддерживают несколько исторических версий.

• Натуральные ключи могут переиспользоваться, различаться между системами и не способны однозначно различить несколько строк SCD2 одной бизнес-сущности.
• В современном ELT-пайплайне я часто использую детерминированный хеш системы-источника, исходного идентификатора и, для строк SCD2, даты начала действия версии: такой ключ стабилен при параллельных повторных запусках и не требует обращения к последовательности.
• Документирую состав ключа и выбираю надёжный хеш, поскольку такие ключи сложнее проверять вручную, а вероятность коллизии остаётся ненулевой.

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

• Транзакционные факты хранят одну строку на событие на самом детальном полезном уровне, например заказ, платёж или клик, и служат вариантом по умолчанию, поскольку из них часто можно вывести остальные представления.
• Периодические снимки фиксируют состояние через равные интервалы, например ежедневные остатки или баланс на конец месяца, когда прошлые уровни дорого или невозможно восстановить из событий.
• Накопительные снимки обновляют одну строку процесса по мере прохождения этапов заказа, оплаты, отгрузки и доставки, упрощая анализ длительности, но создавая неудобную нагрузку для хранилищ, рассчитанных на добавление строк.

Историю я сохраняю, но отделяю дорогой доступ к ней от обычных запросов текущего состояния.

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

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

• Поля из фильтров, соединений и метрик становятся колонками staging-слоя, чтобы движок использовал статистику и отсечение, а ошибки типов обнаруживались заранее.
• Редкие или действительно вариативные атрибуты остаются в JSON, поскольку их разворачивание создало бы сотни разреженных колонок и постоянные миграции схемы.
• Поддерживаю контракт извлечения со списком вынесенных путей, типов и правил NULL, а витрины используют готовые колонки вместо многократного разбора исходного JSON.

Правила часовых поясов и календаря я фиксирую в модели данных, а не повторяю в запросах отчётов.

• Временные метки храню в UTC, а бизнес-дату вычисляю по заданному отчётному часовому поясу при загрузке или в staging-слое, чтобы избежать разных трактовок границы суток.
• Поддерживаемое измерение дат содержит финансовые периоды, границы недель и праздники, поэтому моделям не приходится дублировать хрупкую календарную арифметику.
• Заранее определяю обработку дней перевода часов длиной 23 и 25 часов, отклоняю или исправляю локальное время без смещения при загрузке и моделирую отдельную локальную бизнес-дату для каждого рынка, если она различается.

Звёздную схему я строю вокруг бизнес-процессов и явно заданной гранулярности, а не прямого переноса всех исходных таблиц.

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

Согласованные измерения задают витринам общую идентичность и единые определения ключевых сущностей.

• Факты продаж и поддержки используют одинаковые ключи клиента и даты, поэтому межвитринные показатели описывают одну и ту же сущность и период.
• Отдельные копии команд быстро расходятся в правилах дедупликации, свежести и определениях атрибутов, что приводит к разным итогам и ненадёжным соединениям между областями.
• Я отношусь к основным измерениям как к версионируемым продуктам с владельцем и единым путём сборки, а витрины ссылаются на них вместо копирования логики.

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

• Мост содержит по одной строке на каждую промоакцию, применённую к заказу, а при необходимости ещё и коэффициент для распределения скидки или другого показателя.
• Соединение фактов через мост размножает строки заказа, поэтому невзвешенная выручка по промоакциям завысит итог, если показатель не обозначен как неаддитивный между акциями.
• Я документирую это размножение и предоставляю заранее распределённую витрину для типового анализа, чтобы пользователи не повторяли рискованное соединение самостоятельно.

Слои разделяют техническую обработку источника, общую бизнес-семантику и представление для конкретного потребителя.

• Staging-слой стандартизирует имена и типы и устраняет исходные дубли, обычно в отдельной модели для каждой таблицы источника.
• Core-слой один раз применяет бизнес-правила и формирует согласованные сущности и факты, а витрины подготавливают проверенный результат под конкретные сценарии.
• Такая структура локализует изменения схемы источника, не даёт копировать бизнес-логику по дашбордам и позволяет отлаживать небольшие понятные шаги вместо одного огромного преобразования.

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

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

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

• Предпочтителен CDC на основе журнала транзакций: он передаёт события удаления, после чего хранилище помечает записи удалёнными с сохранением аудита или физически удаляет их.
• Если CDC нет, я периодически выгружаю все первичные ключи и ищу отсутствующие через антисоединение либо договариваюсь о признаке мягкого удаления в источнике, принимая стоимость и ограниченную расписанием свежесть.
• Отдельно фиксирую, что одного updated_at недостаточно, потому что у удалённой записи не появляется новая временная метка для попадания в дельту.