Table Load Timestamp

Database
Enthusiast

Table Load Timestamp

Tables that are dropped and reloaded daily, how can I determine if the table has been today's load, not yesterday's?

Tags (2)
7 REPLIES
Enthusiast

Re: Table Load Timestamp

SEL createtimestamp, lastaltertimestamp FROM dbc.tables WHERE tablename LIKE '%TABLE%'

Supporter

Re: Table Load Timestamp

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.

Enthusiast

Re: Table Load Timestamp

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.

Supporter

Re: Table Load Timestamp

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.

Teradata Employee

Re: Table Load Timestamp

Are there multiple tables you are working with or is it just one table?

Enthusiast

Re: Table Load Timestamp

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

Supporter

Re: Table Load Timestamp

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.