SEL createtimestamp, lastaltertimestamp FROM dbc.tables WHERE tablename LIKE '%TABLE%'
Cheers!
d3V1L
Experience is what we get, when we don't get what we want!
You might also ask the developers to add a load_date or load_timestamp field to the table which contains the required information.
In case of load_date the impact of space usage can be limited by using MVC.
If this is not an option you can ask to maintain a log table where the load process which maintains this tables is adding log infos for each run. So you could check there.
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
D3V1L - In our environment, the timestamps (createtimestamp, lastaltertimestamp) in dbc.tables refer to when the table was created, or altered, not when the data was refreshed.
ulrich - appreciate your feedback. I will ask our folks about a log file.
the table is a forward looking table that is dropped and reloaded daily.
No history is kept.
our current process is to run
select min(sched_date) from table
if the output is today's date, the table has been loaded.
we can continue to do this but it is a frustrating and inefficient process.
if the table is not loaded, we have to wait, run the query again.
repeat until we get today's date.
You mentioned in your post "dropped" and in this case it has to be created afterwards. In this scenario D3V1L solution would work.
I seems you delete and insert / select afterwards.
Ask for a log table, not for a log file.
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Are there multiple tables you are working with or is it just one table?
-- If you are stuck at something .... consider it an opportunity to think anew.
Sorry if my terminology is lacking. I am only a user.
Here is what happens. Every day, the previous day's data is no longer on the table.
Only the current day's data is on the table. Whether that means purged, dropped, deleted, erased, replaced, or ???, do not know or care.
Because of inconsistent load times, I need to know is when the table has the current day's data, not the previous day's data.
I have a query, select min(order_date) from databasename.tablename
If result is today, the table is current.
Otherwise the table has stale data.
It seems there should be a better way to determine if the table has the current day's data.
Adeel,
There may be other tables that are on a daily refresh but this table is the one that gives the most heartburn.
Thanks
As stated before you should ask IT that they maintain a log table wich contains information when this table was last time refreshed.
In this szenario you would query your log table to check if todays run ended successfully.
Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles
Tables that are dropped and reloaded daily, how can I determine if the table has been today's load, not yesterday's?