S3 Shortcuts for delta table vs partitioned delta table

[akkibuddy7]

I'm trying to understand the below scenario in terms on optimization, time complexity or costing wise -

Creating shortcut TABLE for Delta table

Creating shortcut TABLEfor Partitioned Delta table

Note: I'm planning to convert parquert table to delta table and another use case is for partitioned parqet table to paritioned delta table

[v-sgandrathi]

Helloi @akkibuddy7,

Thanks for reaching out to the Microsoft Fabric Community forum.

Thank you, Nilendra, for your prompt response to the query.

I wanted to check if you had the opportunity to review the information provided by @nilendraFabric. Please feel free to reachout to us if you have any further questions.

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Regards,
Sahasra.

[nilendraFabric · Accepted solution]

Hello @akkibuddy7 

Delta Table Shortcut

Creating a shortcut to a Delta table allows you to reference the table without duplicating data. This approach is efficient for non-partitioned Delta tables but comes with certain considerations:

• Performance: Non-partitioned Delta tables are optimized for general use cases and can leverage features like data skipping, caching, and compaction. However, as the data grows, performance may degrade due to the accumulation of small files, which increases I/O overhead during queries. Regular maintenance using commands like OPTIMIZE and VACUUM can mitigate this issue
• Cost: Non-partitioned Delta tables are generally less expensive to maintain since they avoid the overhead associated with managing partitions. However, frequent writes or updates can lead to fragmentation, requiring compaction operations that incur additional compute costs.

Partitioned Delta Table Shortcut

Partitioning a Delta table involves organizing data into subsets based on one or more columns (e.g., date or region). Creating a shortcut for a partitioned Delta table has distinct advantages and trade-offs:

• Performance: Partitioning improves query performance when filtering on partition keys because only relevant partitions are scanned. This reduces the amount of data read during queries. However, over-partitioning (e.g., using high-cardinality columns) can lead to excessive small files and degrade performance.
• Write Complexity: Writing to partitioned tables involves distributing data across partitions, which can slow down writes if the data is unevenly distributed or if there are too many partitions. Maintenance operations like OPTIMIZE are crucial to address fragmentation and ensure efficient file sizes
• Cost: Partitioning can increase storage costs due to additional metadata and file management overhead. However, it can reduce query costs by minimizing the data scanned during reads.

Aspect	Non-Partitioned Delta Table	Partitioned Delta Table
Query Performance	General-purpose; slower for large datasets	Optimized for filtered queries on partition keys
Write Performance	Simpler; no partition overhead	Slower due to partition management
Maintenance	Requires compaction for small files	Requires both compaction and partition management
Cost Efficiency	Lower maintenance cost; higher query cost for large datasets	Higher storage cost; lower query cost when optimized
Best Use Case	Small/medium datasets or no filtering needs	Large datasets with frequent filtering on partition keys

Please accept the answer if this is helpful

[akkibuddy7]

Can we create Shortcut table based on Paritioned Delta tables from s3? 

[nilendraFabric]

Yes, you can create a shortcut table in Microsoft Fabric from partitioned Delta tables stored in an Amazon S3 bucket..the data in your S3 bucket must be in Delta format.