ETL or Extract-Transform-Load defines the mechanism of data flow from a system to the data warehouse.Here is the checklist for ETL / Data Warehouse Testing:

1. Validate schemas of both source and targets of data warehouse2. Ensure key constraints for targets are in sync with specifications given to you
3. query on source and targets and try to break transformation logic written in informatica. This is key and important since data sits in target based on transformation logic written and also check for Mapplets if any.3. try to break/check update rules attached to transformation
4. check for surrogate keys
5. Write SQL queries for sources and check weather they are meaningful to targets and vice-versa
6. check for scheduling jobs.
7. Create SQL queries with different logics and check for data consistencies for different conditions“Given a scenario where a single file is parsed into more than say 1000 columns in different tables say more than 10.If the person testing this would need to be able to code some scripts quickly to run a comparison of values between source file and relational staging table columns.Can it be done say by using macros in Excel??”

Here are a couple possible solutions:
1) Depending on your query tool, you could write a looping construct to query both the spreadsheet using one connection, and the staging database using another connection. Compare each value upon retrieval.

2) You could replicate the model in above using MS-Access and some VBA.

3) You could use VBScript or other language to accomplish above.

Steps
Need to validate that data from our data warehouse is properly loaded to our datamart. Would like to create baseline files using ‘db_execute_query’ and then ‘db_write_records’, and then compare the datamart result set with the baseline using file_compare (or a separate diff utility).