Inconsistent count results on the same table in different columns

faq

#1

When I run a record count in a table, I get different results depending on which column I do the count on. But the number of records in each column should be the same.

First query:

SELECT COUNT("public"."epoch"."transaction_id") as c1, COUNT("public"."epoch"."id") as c2 FROM "public"."epoch";

Second query:

SELECT COUNT("public"."epoch_transactions"."transaction_id") as c1, COUNT("public"."epoch_transactions"."id") as c2 FROM "public"."epoch_transactions";

These should result in the same value, but they don’t.

The data from this table come from two tables in my MySQL source: EpochTransactionStats and MemberStats. The problem arises if I choose both of these tables in the Data Source to import.


#2

The most likely explanation for this outcome is that you configured your data source to send your data to one static destination table, and also did not define a Primary Key (PK).

The reason this would cause a problem is because the default PK is the id column. If an id column exists in the source, it will be used as the PK of the destination table. If you have data with the same id value in each table, the second ingested record with that id overrides the first (existing) record with that id.

In terms of solutions, one approach is to upload each of the tables into a different destination tables. Note that you can also configure dynamic destination tables.

Regardless of how many source tables you have, the important thing to ensure is that the records on the destination table(s) each have a unique primary key.

For more detail, see the documentation on these topics: