Изменение сбора данных с объединением таблиц в ETL

Question

В моем процессе ETL я использую Change Data Capture (CDC) для обнаружения только тех строк, которые были изменены в исходных таблицах с момента последнего извлечения. Тогда я делаю преобразование только для этой строки. Проблема в том, что у меня есть, например, 2 таблицы, которые я хочу объединить в одном измерении, и только одна из них изменилась. Например, у меня есть таблица "Страны и города":

Страны:

ID Name
1  France

Города:

ID Name  Country_ID
1  Lyon  1

Теперь допустим, что в таблицу Towns добавлена ​​новая строка:

ID Name  Country_ID
1  Lyon  1
2  Paris 2

Таблица стран не была изменена, поэтому CDC для этих таблиц показывает мне только строку из таблицы городов. Проблема заключается в том, что когда я выполняю объединение стран и городов, в наборе изменений стран нет строки, поэтому объединение приведет к пустому набору.

У вас есть идея, как это решить? Конечно, могут быть более сложные случаи, включающие 3 и более таблиц и последующие объединения.

Answer 1

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

Есть несколько способов решить эту проблему.

Одним из способов было бы сделать ваши объединения по естественным ключам в таблице измерений или сопоставления, чтобы получить связанную страну (ВЫБЕРИТЕ отдельное название страны, [..другие атрибуты..] из dim_table где country_id = X).

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

Answer 2

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

1st Pass is where everything matches via the join... 
Union All 
2nd Pass Gets all towns where there isn't a country
(left outer join with a where condition that 
requires the ID in the countries table to be null/missing).

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