Datalake account_partitioned table row count is verly low

Musarrat Aziz 5

We have azure datalake with builtin pool
In that we have builtin account_Partitioned table which use to have similar no or rows as per non partitioned table but now from today only showing 10% of total rows

We are in UK West region and something happened today to effect this

IS there anything we can do to fix this. if this is not appropriate forum can you guide us to appropriate plz

account_partitioned

PRADEEPCHEEKATLA-MSFT 77,336 Reputation points Microsoft Employee

2023-11-07T08:52:33.2666667+00:00
@Musarrat Aziz - Thanks for the question and using MS Q&A platform.

It seems like you are experiencing an issue with the row count of your Azure Data Lake account_partitioned table. I'll do my best to help you with this.

Firstly, can you please confirm if you have made any changes to the table schema or data recently? Also, have you checked if there are any errors or warnings in the logs related to this issue?

If there are no changes made to the table schema or data and there are no errors or warnings in the logs, then it is possible that the issue is related to partitioning. In dedicated SQL pools, partitioned tables store and execute operations on the table rows according to data ranges. If the data is not distributed properly, it can cause issues with the row count.

To resolve this issue, you can try the following steps:

Check if the table is partitioned correctly. You can use the following query to check the partition distribution:

SELECT $PARTITION.<partition_function_name>(<partition_column>) AS Partition, COUNT(*) AS Rows FROM <table_name> GROUP BY $PARTITION.<partition_function_name>(<partition_column>) ORDER BY Partition;

Replace <partition_function_name>, <partition_column>, and <table_name> with the appropriate values for your table.

If the partition distribution is incorrect, you can try to repartition the table using the ALTER TABLE statement. Here is an example:

ALTER TABLE <table_name> REBUILD PARTITION = ALL WITH (DISTRIBUTION = HASH(<partition_column>));

Replace <table_name> and <partition_column> with the appropriate values for your table.

If the issue persists, I recommend open a support ticket for further assistance. They will be able to provide you with more specific guidance based on your environment and configuration.

I hope this helps! Let me know if you have any further questions.
Musarrat Aziz 5 Reputation points

2023-11-07T09:19:33.87+00:00

Thanks @PRADEEPCHEEKATLA-MSFT for your reply
Actually I didnt create this partition its a snapshot of data partitioned monthly automatically

When I am accessing actual table i.e
select count(*) from [dbo].[account] it gives me 85K rows of data
but when I use snapshot table with query below

select count(*) from [dbo].[account_partitioned]
this gives me only 4K rows

usually difference is few records but since yesterday we are getting only few thousands records
PRADEEPCHEEKATLA-MSFT 77,336 Reputation points Microsoft Employee

2023-11-08T04:27:07.4066667+00:00
@Musarrat Aziz - Thank you for the additional information. It sounds like the issue is specific to the account_partitioned table and not related to partitioning.

One possible cause of this issue could be that the snapshot of the data was taken at a point in time when the account_partitioned table had fewer rows than the actual account table. This could happen if the snapshot was taken before the data was fully loaded into the account_partitioned table.

To resolve this issue, you can try taking a new snapshot of the data and see if the row count is correct. You can also try to refresh the existing snapshot to see if it resolves the issue.

Here is an example of how to refresh a snapshot:

ALTER TABLE REFRESH SNAPSHOT;

Replace <table_name> with the appropriate name of your table.

I hope this helps! Let me know if you have any further questions.
PRADEEPCHEEKATLA-MSFT 77,336 Reputation points Microsoft Employee

2023-11-10T08:00:54.8033333+00:00

@Musarrat Aziz - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.