Datalake account_partitioned table row count is verly low

@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.

Thanks @PRADEEPCHEEKATLA 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

@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.