We are parsing s3 path in snowfalke query to fetch the partition_id for each table.
Wrote a query its working fine if the part-*.parquet files present under the partitions.
Giving a wrong results if the parquet files having with different names.

Aim is single query should return partition_id=20240101 in both the use cases.

use case1 working fine as expected:

select split(split(split_part(‘1234/local/table_name1/partition_id=20240101/part-0001.parquet’,’table_name1′),’part-‘)[0],’/’)c

output :
partition_id=20240101

use case2 giving output – partition_id and parquet file also
select split(split(split_part(‘1234/local/table_name2/partition_id=20240101/table.name2.parquet’,’table_name2′),’part-‘)[0],’/’)c

output:
partition_id=20240101
table.name2.parquet

I have to tweak the query so it should work in both cases and returns only partition_id=20240101. Please share your inputs.

Thanks in advance