Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-08-2024 03:14 PM
Hi Dharinip. I've had similar conversations internally with developers asking very similar things. This is my general advice for this situation, but I think there are a lot of considerations to how you create your gold layer.
- How large is this dataset, and how much growth is expected? The larger the dataset gets, the slower view queries could become. If it's very large, I'd recommend materializing it as a table.
- Is the silver table a MANAGED table in Delta format, or is it in another format? If not in Delta, this could potentially slow down the performance a view.
- Are you able to add liquid cluster on your soft delete flag? This would allow Spark to more easily fetch just the live records in the event that you use a view instead of a table
- Are you able to further enhance the gold table with data from additional data sources? If so, you would likely want to materialize as a table, so that you don't have a large number of joins in a gold level view. Joins are a very expensive operation, as far as processing time.
Ultimately, I'd try it both ways and gauge performance, because there are potentially other factors that I didn't even list. The upside of a view is that this data is really live, once it gets into silver. There's no further `MERGE` operation that needs to happen to the files on disk.
If you have answers to the above questions, I can try and formulate a more specific recommendation to your scenario!