Вопрос

основной вопрос

Как я могу эфемерно материализовать медленно изменяющийся тип измерения 2 из папки ежедневных извлечений, где каждый csv представляет собой одно полное извлечение таблицы из исходной системы?

обоснование

Мы разрабатываем эфемерные хранилища данных как витрины для конечных пользователей, которые можно развернуть и сжечь без последствий. Для этого у нас должны быть все данные в озере / большом двоичном объекте / корзине.

Мы ежедневно копируем полные выдержки, потому что:

1. мы не смогли надежно извлечь только набор изменений (по независящим от нас причинам), и
2. мы хотели бы поддерживать озеро данных с «наименьшими» возможными данными.

проблемный вопрос

Есть ли решение, которое могло бы предоставить мне состояние на определенную дату, а не только «новейшее» состояние?

экзистенциальный вопрос

Я думаю об этом полностью в обратном направлении, и есть гораздо более простой способ сделать это?

Возможные подходы

пользовательская dbt материализация

В insert_by_period пакете есть dbt.utils материализация dbt, которая, я думаю, может быть именно тем, что я ищу? Но я в замешательстве, поскольку это dbt snapshot , но:

1. запуск dbt snapshot для каждого файла постепенно, все сразу; и,
2. создается непосредственно из внешней таблицы?

Дельта-озеро

Я мало что знаю о дельта-озере Databricks, но, похоже, это должно быть возможно с дельта-таблицами?

Исправьте задание извлечения

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

Пример

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

2020-09-14_CRM_extract.csv

 | OppId | CustId | Stage       | Won | LastModified | Extracted |
|-------|--------|-------------|-----|--------------|-----------|
| 1     | A      | 2 - Qualify |     | 9/1          | 9/14      |
| 2     | B      | 3 - Propose |     | 9/12         | 9/14      |
  

2020-09-15_CRM_extract.csv

 | OppId | CustId | Stage       | Won | LastModified | Extracted |
|-------|--------|-------------|-----|--------------|-----------|
| 1     | A      | 2 - Qualify |     | 9/1          | 9/15      |
| 2     | B      | 4 - Closed  | Y   | 9/14         | 9/15      |
| 3     | C      | 1 - Lead    |     | 9/14         | 9/15      |
  

2020-09-16_CRM_extract.csv

 | OppId | CustId | Stage       | Won | LastModified | Extracted |
|-------|--------|-------------|-----|--------------|-----------|
| 1     | A      | 2 - Qualify |     | 9/1          | 9/16      |
| 2     | B      | 4 - Closed  | Y   | 9/14         | 9/16      |
| 3     | C      | 2 - Qualify |     | 9/15         | 9/16      |
  

Конечный результат

Ниже приведен SCD-II для трех файлов по состоянию на 9/16. SCD-II по состоянию на 9/15 будет таким же, но OppId=3 имеет только один из valid_from=9/15 и valid_to=null

 | OppId | CustId | Stage       | Won | LastModified | valid_from | valid_to |
|-------|--------|-------------|-----|--------------|------------|----------|
| 1     | A      | 2 - Qualify |     | 9/1          | 9/14       | null     |
| 2     | B      | 3 - Propose |     | 9/12         | 9/14       | 9/15     |
| 2     | B      | 4 - Closed  | Y   | 9/14         | 9/15       | null     |
| 3     | C      | 1 - Lead    |     | 9/14         | 9/15       | 9/16     |
| 3     | C      | 2 - Qualify |     | 9/15         | 9/16       | null     |
  

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

Мои опасения были бы:

1. Если ваши исходные или целевые требования изменятся, как вы собираетесь с этим справиться? Вам нужно будет развернуть вашу таблицу данных, выполнить полное регрессионное тестирование на ней, применить ваши изменения, а затем протестировать их. Если вы делаете это по мере того, как / когда изменения известны, то это требует больших усилий для неиспользуемой таблицы данных, особенно если вам нужно делать это несколько раз между использованиями; если вы делаете это, когда нужна таблица данных, то вы не достигаете своей цели по предоставлению карты данных для «мгновенного» использования.

Я не уверен, что ваше утверждение «у нас есть DW как код, который можно удалять, обновлять и воссоздавать без сложностей, связанных с традиционным управлением изменениями DW», верно. Как вы собираетесь тестировать обновления своего кода без запуска datamart (ов) и прохождения стандартного цикла тестирования с данными — и чем это отличается от традиционного управления изменениями DW?

2. Что произойдет, если в ваших исходных системах есть поврежденные / неожиданные данные? В «обычном» DW, где вы загружаете данные ежедневно, это обычно было бы замечено и исправлено в течение дня. В вашем решении сомнительные данные могли появиться несколько дней / недель назад, и, предполагая, что они загружены в вашу таблицу данных, а не с ошибкой при загрузке, вам понадобятся процессы, чтобы определить это, а затем, возможно, придется распутывать записи SCD за несколько дней, чтобы устранить проблему
3. (Актуально только при наличии значительного объема данных) Учитывая низкую стоимость хранилища, я не уверен, что вижу преимущество в развертывании datamart, когда это необходимо, в отличие от простого хранения данных, чтобы они были готовы к использованию. Загрузка больших объемов данных при каждом запуске datamart займет много времени и будет дорогостоящей. Возможный гибридный подход может заключаться в том, чтобы запускать инкрементные загрузки только тогда, когда требуется datamart, а не запускать их каждый день — таким образом, у вас всегда есть данные о том, когда datamart использовался в последний раз, и вы просто добавляете записи, созданные / обновленные с момента последней загрузки

Я не знаю, лучшее это решение или нет, но я видел, как это делается. При создании исходной таблицы SCD-II добавьте столбец, который является сохраненным HASH() значением всех значений записи (вы можете исключить первичный ключ). Затем вы можете создавать внешнюю таблицу поверх вашего ежедневного полного набора данных, который включает ту же HASH() функцию. Теперь вы можете выполнить MERGE или INSERT/UPDATE для вашего SCD-II на основе первичного ключа и того, изменилось ли значение ХЭША.

Ваше главное преимущество при выполнении таких действий заключается в том, что вы избегаете ежедневной загрузки всех данных в Snowflake для сравнения, но выполнение этого способа будет медленнее. Вы также можете загрузить временную таблицу с HASH() функцией, включенной в вашу COPY INTO инструкцию, а затем обновить SCD-II и затем удалить временную таблицу, что на самом деле может быть быстрее.