Hi,I want to create a table to get the raw count of my tables and also want to add the today's record count when the scheduled job is executed daily.

Hello, I think the issue with your current query might be that you are grouping by both t.table_name and ps.row_count. I think you should try to group only by t.table_name to get the total row count per table, as you are only displaying the sum of the values, I don't think you need to group by it.

Additionally, if you want to distinguish between raw tables and tables created through transformations, you might need a way to filter or classify those tables ( I am not sure if you have a condition as such but for instance, raw tables usually don't have any prefix and edited tables might start by "P2P_" for instance, so you will need to take that into account as a filter .

Here is a revised version of your query that you might need to adapt.

DROP TABLE IF EXISTS TABLE_COUNT_TC;

CREATE TABLE TABLE_COUNT_TC AS(

SELECT t.table_name AS table_name,

SUM(ps.row_count) AS row_count,

  CURRENT_DATE AS Record_Date

  FROM tables t

  JOIN projections p ON t.table_id = p.anchor_table_id

  JOIN projection_storage ps on p.projection_name = ps.projection_name

  GROUP BY t.table_name

WHERE table_name NOT LIKE ''P2P%'

  );

Please tell me if there is any problem at all with the query or you need further clarification.

Thank you!

Hope it helps

Rodrigo

Thanks for the response.

I tried the above logic and the count was not matching with my raw count. If I remove SUM for row_count , it is showing multiple lines.

Is there any other way to get count of raw data.

Thanks,

Supriya