Verification of source data

Database
Enthusiast

Verification of source data

Is there a way to validate source data within fastload or mload utilities.
4 REPLIES
Senior Apprentice

Re: Verification of source data

Could you please elaborate, what you're trying to achieve?

Dieter
Enthusiast

Re: Verification of source data

Thanks for responding. One of the tehnial questions I was asked in an interview. How do you validate and perform editing on source data in teradata utilities like MLOAD? My understanding is that load utilities are straight load to the teradata tables. I am not sure if INMOD routines is the answer.
Enthusiast

Re: Verification of source data

"Field-level" editing can take place with Multiload or Tpump(Fastload is very restrictive in what you're able to do). So, with Multiload, you can have CASE statements, which combined with functions and UDF's can perform some editing (example: UDF to validate that an incoming date is correct).

However, if you need to access other tables to perform validation (such as referential integrity), then you would need to use Tpump combined with the appropriate referential integrity constraints on the table. Of course, this has performance implications.

Another common approach is to load the source data into a "staging" table as is and then perform the edits and validations. This would be preferred in most cases since it makes use of the parallelism of the machine and tends to avoid some of the locking issues that you may encounter with Tpump.

In general, I would tend to put field-level editing in the load utility, while performing the other validations once the data is loaded into a staging table. You can then send the validated rows to your target table using the INSERT/SELECT.

Hope this helps.
Enthusiast

Re: Verification of source data

Thanks a lot.
This is great.
Pervez Ahmed