question

The reason for it is quite simple -- data in a Cassandra cluster changes at a high velocity and partitions can be partially changed too which makes it difficult to reconcile the number without reading-before-writing.

Let me illustrate with some examples using this table:

CREATE TABLE community.users (
    name text PRIMARY KEY,
    address text,
    email text,
    mobile text
)

EXAMPLE 1 - INSERT over-count

If I add a new user to the table:

cqlsh:community> INSERT INTO users (name, address) VALUES ('alice', '1 Main Rd');

I end up with 1 partition in the table as expected:

cqlsh:community> SELECT * FROM users;

 name  | address   | email | mobile
-------+-----------+-------+--------
 alice | 1 Main Rd |  null |   null

$ nodetool tablestats community.users | grep partitions
Number of partitions (estimate): 1

But when I add Alice's email:

cqlsh:community> INSERT INTO users (name, email) VALUES ('alice', 'alice@mail.com');

there should still be the 1 partition in the table:

cqlsh:community> SELECT * FROM users ;

 name  | address   | email          | mobile
-------+-----------+----------------+--------
 alice | 1 Main Rd | alice@mail.com |   null

But the table metrics shows an estimate of 2 partitions:

$ nodetool tablestats community.users | grep partitions
Number of partitions (estimate): 2

This is because Cassandra doesn't "know" that the name = 'alice' partition already exists -- C* does not do a read-before-write. Therefore it estimates that there are 2 partitions based on the 2 INSERT statements.

EXAMPLE 2 - Updates

But what about updates? If we're updating a partition then we shouldn't be counting them as an update, right?

Updates in C* are inserts under the hood. And since C* doesn't do a read-before-write, C* doesn't know if the partition already exists so counts it as a new partition:

cqlsh:community> UPDATE users SET mobile = '61-456-789-123' WHERE name = 'bob';

And we end up with an estimate of 3 partitions:

$ nodetool tablestats community.users | grep partitions
Number of partitions (estimate): 3

when we know there are only 2 partitions in the table:

cqlsh:community> SELECT * FROM users ;

 name  | address   | email          | mobile
-------+-----------+----------------+----------------
   bob |      null |           null | 61-456-789-123
 alice | 1 Main Rd | alice@mail.com |           null

But if we flush the memtables to disk:

$ nodetool flush

the act of flushing forces the data to be coalesced between what's already on disk and what was in the memtable. All the fragments of the Alice partition get merged and the table count is now correct:

$ nodetool tablestats community.users | grep partitions
Number of partitions (estimate): 2

Conclusion

In a production environment, mutations (inserts, updates, deletes) are taking place at high velocity so the partition counts on a node will constantly get updated and reconciled.

Depending on when you look at the table metrics, the number of partitions (keys) will change. This is the reason it is labelled as an estimate.

In response to your other question, the DataStax Bulk Loader (DSBulk) is the correct tool to perform a count. But even then, it is a count at a point-in-time and only true at the time that you performed the count because in production environments, data is changing constantly. I've explained this in detail in Why COUNT() is bad in Cassandra. Cheers!