Scott Lu on 21 Aug 2023 09:54:44
The partition is very important for maintaining data and improving query performance on large Fact table in DW.
ATM, we have a 2 TB table, and backend logic is complicated, and it will take 8 mins for querying a complicated logic on that table by using partition view strategy on a synapse on-demand pool. But in Fabric DW, same query will take 21 mins due to lack of partition (I guess it will scan the whole delta).
I know I can use lake-house and spark to have a partition table, but I do want to have the partition strategy in DW because it is the essential for large DW.
- Comments (2)
RE: Support partitioning table in Fabric data warehousing
HiThis would allow Transform processes to work on an unexposed table partition and then switch out once completed.I don't know how you would do this without partitions - time is money and no-data or incomplete/inconsistent data cannot be used/analysed.Transform processes can take from seconds to hours and table data cannot be used during that time as it's in a incomplete/inconsistent state until he transform build has completed .Partition Switch would give a consistent switch-over timing between old and new data (and lock the data for the duration). Would enable a "publish" pattern based on partition switching.Thanks
RE: Support partitioning table in Fabric data warehousing
100% agree. I am creating tables with dbt and it has to be done in a warehouse, but then it's unpartitioned and slow.