%sql
-- Collect only the latest version for each country
CREATE OR REPLACE TEMPORARY VIEW silverTable_latest_version as
SELECT *
FROM
(SELECT *, rank() over (partition by Country order by _commit_version desc) as rank
FROM table_changes('silverTable', 2, 5)
WHERE _change_type !='update_preimage')
WHERE rank=1
OK
%sql
-- Merge the changes to gold
MERGE INTO goldTable t USING silverTable_latest_version s ON s.Country = t.Country
WHEN MATCHED AND s._change_type='update_postimage' THEN UPDATE SET VaccinationRate = s.NumVaccinated/s.AvailableDoses
WHEN NOT MATCHED THEN INSERT (Country, VaccinationRate) VALUES (s.Country, s.NumVaccinated/s.AvailableDoses)
Demo of Delta Lake change data feed