In the end it seemed that the deduplication using materialized view was the most performant approach because the ingestion latency was starting to get really high using a custom deduplication mechanism without materialized views. The only option was to upscale the SKU but that itself also has a great impact on cost.

However, deduplication using the materialized view approach also comes with a certain load on the ingestion process when working with billions of rows.