The online community for software testing & quality assurance professionals
 
 
Calendar   Today's Topics
Sponsors:
Lost Password?

Home
BetaSoft
Blogs
Jobs
Training
News
Links
Downloads



Software Testing >> Functional Testing

Pages: 1 | 2 | >> (show all)
gopster1283
Member


Reged: 09/05/08
Posts: 26
ETL testing and the role of a QA
      #574666 - 06/15/09 02:36 PM

Fellow QAs:

I am not sure if this is the right section to post this question, apologies if its not.

I would like to know what exactly is ETL testing? I know the concepts of extracting from source data, transfering to staging tables and then loading them into your final tables.

However, what will a QA be involved in? What tools will he use? How will he use them?

Thank you.


Post Extras: Print Post   Remind Me!   Notify Moderator  
JCTreb
Active Member


Reged: 08/16/01
Posts: 789
Loc: Minneapolis, MN
Re: ETL testing and the role of a QA [Re: gopster1283]
      #574675 - 06/15/09 06:21 PM

ETL testing really isn't all that complicated. Though, it requires a lot of attention to detail.

What does a tester do in an ETL environment? This is what I do:

1. Review the requirements
2. Review the source data - data profiling.
3. Modify the source data to meet any requirements that weren't already met with the original source data.
4. Execute the ETL job(s)/transformation(s).
5. Validate the results.
6. Validate that the data is usable. If the data isn't usable, then you really haven't gained anything.

As to what tools are used, it depends on the individual and the environment that they're working in. Personally, I want/need access to two applications at a bare minimum:

1. The ETL tool itself
2. Some sort of db gui tool (i.e., Toad, etc.)

Regardless of how the ETL code is exercised - directly through the ETL tool or through some command line script, I want to be able to go into the code so I can understand how the developer is meeting a specific requirement. If a set of data is to be grouped together, I like to be able to go in and understand how the code is grouping that data. Ultimately, I want to be an asset to the developers (and the rest of the project) and not a line item to be checked off prior to implementation. (Besides, sometime down the road I may turn into an ETL developer...and it's good to understand what is involved.)

Lastly, there is one test that I usually start off with before really diving in-depth to what I'm working on. Formally, it's a smoke test. Personally, it's the "surprise me" test. If I can execute the ETL job and it completes successfully, that's half the battle right there. What this tells me is that all of the pieces are in place to execute the job...be it database structure, file system structure, and/or data itself. It's surprising how often that something is missed. The plus side of this is that, regardless of whether data has been set up for specific tests, I do have a starting point to start validating data.

--------------------
Jason Trebilcock

"The single biggest problem in communication is the illusion that it has taken place."

-George Bernard Shaw, Irish playwright and Nobel Prize winner, 1856-1950


Post Extras: Print Post   Remind Me!   Notify Moderator  
gopster1283
Member


Reged: 09/05/08
Posts: 26
Re: ETL testing and the role of a QA [Re: JCTreb]
      #574678 - 06/15/09 06:49 PM

JCTreb:

Thank you for your response. I have some questions for you.

1) What is an ETL job? Can you provide me with some examples?

2) Is a tool at all necessary? I worked in a situation where I feel it was an ETL process, so please enlighten me.

A. We extracted source data from remote source system out of our network into some flat files.

B. We loaded this data into our staging tables. I verified data integrity at this point.

C. From staging tables, we again loaded this data into our test environment. We validation the data transformation rules into our test environment. Data was then migrated across to prod environment after validation.

D. We then again extracted data from test into final tables which would seen by only certain stakeholders. Again we verfied the PL/SQL queries behind this transformation.

From the numbers you listed in your response, I performed 1,2,3,5,and 6. So in your opinion have we performed ETL? or was this just regular backend testing. Hence my dilemna what the need for a tool is.


Post Extras: Print Post   Remind Me!   Notify Moderator  
brentpaineModerator
Veteran


Reged: 03/09/07
Posts: 3755
Loc: Waterloo, Ontario, Canada
Re: ETL testing and the role of a QA [Re: gopster1283]
      #574770 - 06/16/09 06:35 AM

Wow! Awesome explanation Jason.

In response to these new questions:

1) The ETL job is generally going to be whatever transformation or manipulation is being placed on the data.

2) No, a tool isn't always necessary. I worked at a company with some rather large databases, and we never implemented any ETL tools. It does mean a substantial amount of work, though. Plus, I find there are many more possibilities for error. I mean, generally, if there is an error in the tool, then you can pick up on it easily enough. When it's human error, there can be one-offs and really weird errors that you might not see immediately.

Based on what you're describing, you've also executed the ETL jobs, no?

--------------------
Brent
--------------------
9 out of 10 people I prove wrong agree that I'm right. The other person is my wife.
--------------------


Post Extras: Print Post   Remind Me!   Notify Moderator  
gopster1283
Member


Reged: 09/05/08
Posts: 26
Re: ETL testing and the role of a QA [Re: brentpaine]
      #574791 - 06/16/09 07:39 AM

Thanks brentpaine for your input.

So in essence an ETL tool verifies the business rules.

For example, in a staging environment, if we weed column X in Table Y to have all even numbers. Then, an ETL tool will execute the code that would perform this transformation?


Post Extras: Print Post   Remind Me!   Notify Moderator  
brentpaineModerator
Veteran


Reged: 03/09/07
Posts: 3755
Loc: Waterloo, Ontario, Canada
Re: ETL testing and the role of a QA [Re: gopster1283]
      #574803 - 06/16/09 08:30 AM

Lol, ok, my brain is obviosuly fried today. I'm sitting here looking at this and thinking, "Yeah, that's what I said." Lol.

Ummm, yes and no. Essentially an ETL Tool will act like a framework, right? So it's going to give you the basic tools that you need to map out your data migration. Some tools can be very simple and others are full-blown graphical mappers which allow you to visually plot every single move you make and what transformations are happening at what point in the process.

So this should give us, in QA, the ability to see what is actually being executed, the timing on the executions and where the biggest risk areas might be.

--------------------
Brent
--------------------
9 out of 10 people I prove wrong agree that I'm right. The other person is my wife.
--------------------


Post Extras: Print Post   Remind Me!   Notify Moderator  
JCTreb
Active Member


Reged: 08/16/01
Posts: 789
Loc: Minneapolis, MN
Re: ETL testing and the role of a QA [Re: brentpaine]
      #574834 - 06/16/09 10:46 AM

And Brent just hit on something else near and dear to my heart. If, for example, you're testing an ETL job/process/whatever with a minimal set of records (define that however you like)...and the 'real' ETL job/process/whatever is going to be expected to handle a significantly larger set of data, you may want to think about generating additional data to get to the size of what you will be seeing in your production environment.

Additionally, you may want to think about what the volume of data pre-existing in your production environment is going to be when you do the above-mentioned test (in your test environment.)

We're looking at two different overall tests above:
1. How long will it take to process a production size file?
2. A variation of 1. How long will it take to process a production size file with a production size database?

Part of the question that you will want to think about is how long does it take for the ETL job/process to complete. The source of this question is that you may be negatively impacting the user experience (that is, if you have some sort of user application that uses the data that you just loaded.) If you have an ETL job that runs for hours and gobbles up resources, that's not good.

"You see, ETL's my thing."

--------------------
Jason Trebilcock

"The single biggest problem in communication is the illusion that it has taken place."

-George Bernard Shaw, Irish playwright and Nobel Prize winner, 1856-1950


Post Extras: Print Post   Remind Me!   Notify Moderator  
gopster1283
Member


Reged: 09/05/08
Posts: 26
Re: ETL testing and the role of a QA [Re: JCTreb]
      #574845 - 06/16/09 12:31 PM

Can any of you point me in the direction of some ETL literature/articles ( more along the lines of a QA's role ).

Thanks for the response, this is really assisting me.


Post Extras: Print Post   Remind Me!   Notify Moderator  
JCTreb
Active Member


Reged: 08/16/01
Posts: 789
Loc: Minneapolis, MN
Re: ETL testing and the role of a QA [Re: gopster1283]
      #574850 - 06/16/09 12:52 PM

Last time I looked, there really wasn't a whole lot in the way of what you are looking for. The following are a sampling of some of the articles, or, in the case of the last link, book, that I've come across in the past.

Where are the article on data warehouse testing...

Multiple executions

ETL Testing

Agile Data Warehousing - While this book isn't testing-specific, it does contain a number of pages that discuss testing within the confines of a data warehouse...which can readily be extrapolated to general ETL-related testing activities.

--------------------
Jason Trebilcock

"The single biggest problem in communication is the illusion that it has taken place."

-George Bernard Shaw, Irish playwright and Nobel Prize winner, 1856-1950


Post Extras: Print Post   Remind Me!   Notify Moderator  
wyaddow
Junior Member


Reged: 12/28/01
Posts: 6
Loc: Rhinebeck, NY, USA
Re: ETL testing and the role of a QA [Re: gopster1283]
      #610718 - 01/23/10 12:14 PM Attachment (3880 downloads)

I have prepared a paper on the subject and would be happy to send to anyone (it has been attached as well) Also, I have a large number of papers and presentations on the topic. Please feel free to contact me.

Post Extras: Print Post   Remind Me!   Notify Moderator  
Satya04
Newbie


Reged: 09/11/08
Posts: 6
Re: ETL testing and the role of a QA [Re: wyaddow]
      #616424 - 03/04/10 01:02 PM

Hi, I would be interested to get the documents related to DWH testing. As you have mentioned that you have collected few documents on the subject, Can you please send them to me at

Thanks for your help.

Edited by Administrator (05/30/10 04:15 PM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
KishoreAutomation
Active Member


Reged: 10/08/06
Posts: 925
Re: ETL testing and the role of a QA [Re: Satya04]
      #616457 - 03/04/10 06:17 PM

Hi Wyaddow,

Could you please send me doucment to my id:
[Edited by Mod]

Thanks for your support.

Edited by martinh (06/21/10 06:29 PM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
JCTreb
Active Member


Reged: 08/16/01
Posts: 789
Loc: Minneapolis, MN
Re: ETL testing and the role of a QA [Re: KishoreAutomation]
      #616459 - 03/04/10 07:35 PM

Um, the document was attached to wyaddow's post. You shouldn't need to have it emailed (despite the offer).

--------------------
Jason Trebilcock

"The single biggest problem in communication is the illusion that it has taken place."

-George Bernard Shaw, Irish playwright and Nobel Prize winner, 1856-1950


Post Extras: Print Post   Remind Me!   Notify Moderator  
Satya04
Newbie


Reged: 09/11/08
Posts: 6
Re: ETL testing and the role of a QA [Re: JCTreb]
      #616568 - 03/05/10 09:39 AM

Yep, I have already downloaded the attached document but I was actually responding to this comment "Also, I have a large number of papers and presentations on the topic."


Thanks

Edited by Satya04 (03/05/10 09:39 AM)

Post Extras: Print Post   Remind Me!   Notify Moderator  
subbarao123
Newbie


Reged: 05/30/10
Posts: 6
Re: ETL testing and the role of a QA [Re: Satya04]
      #628591 - 05/30/10 01:01 AM

ETL means :Extraction Transformation loading. We will take the source data , manipulate the source data, transform the sourece data in to staging table finally transfered to main table.

Post Extras: Print Post   Remind Me!   Notify Moderator  
Peter Ruscoe
Veteran


Reged: 03/18/02
Posts: 7686
Loc: Tampa Bay
Re: ETL testing and the role of a QA [Re: subbarao123]
      #628605 - 05/30/10 04:53 PM

subbarao123, how did that add anything to the lengthy and detailed explanation given by JCTreb above?

Post Extras: Print Post   Remind Me!   Notify Moderator  
JCTreb
Active Member


Reged: 08/16/01
Posts: 789
Loc: Minneapolis, MN
Re: ETL testing and the role of a QA [Re: JCTreb]
      #628606 - 05/30/10 05:13 PM

A little less than a year ago, I stated:

Quote:

Besides, sometime down the road I may turn into an ETL developer...and it's good to understand what is involved.





And that was pretty prophetic on my part. Crossed over to the world of ETL development on a full-time basis two weeks ago. Had a month-long 'trial period' from mid-March to mid-April...and then made the permanent switch two weeks ago.

Does that mean I'm leaving? Ohheckno. I'm still fascinated by the world of testing. But I'm more fascinated by the world of data.

--------------------
Jason Trebilcock

"The single biggest problem in communication is the illusion that it has taken place."

-George Bernard Shaw, Irish playwright and Nobel Prize winner, 1856-1950


Post Extras: Print Post   Remind Me!   Notify Moderator  
venky510
Member


Reged: 01/21/10
Posts: 34
Loc: Gujarat,India
Re: ETL testing and the role of a QA [Re: JCTreb]
      #629883 - 06/09/10 04:02 AM

ETL stands for extract, transform, and load. It can consolidate the scattered data for any
organization while working with different departments. It can very well handle the data
coming from different departments.
For example, a health insurance organization might have information on a customer in
several departments and each department might have that customer's information listed in
a different way. The membership department might list the customer by name, whereas
the claims department might list the customer by number. ETL can bundle all this data
and consolidate it into a uniform presentation, such as for storing in a database or data
warehouse.
ETL can transform not only data from different departments but also data from different
sources altogether. For example, any organization is running its business on different
environments like SAP and Oracle Apps for their businesses. If the higher management
wants to take discussion on their business, they want to make the data integrated and used
it for their reporting purposes. ETL can take these two source system data and make it
integrated in to single format and load it into the tables.


Generally the normal testing steps are:
Requirements Analysis
Testing Methodologies
Test Plans and approach
Test Cases
Test Execution
Verification and Validation
Reviews and Walkthroughs

The main difference in testing a ETL is that we basically involve the
SQL queries in our test case documents. It is vital to test both the initial loads of the Data
Warehouse from the source i.e. when it gets extracted and then updating it on the target
table i.e. the loading step. In specific cases, where trouble shooting is required, we verify
intermediate steps as well.

A defect or bug detection can be appreciated if and only if it is detected early and is fixed
at the right time without leading to a high cost. So to achieve it, it is very important to set
some basic testing rules. They are:
No Data losses
Correct transformation rules
Data validation
Regression Testing
Oneshot/ retrospective testing
Prospective testing
View testing
Sampling
Post implementation

hope this help...

--------------------
regards,
venkatesan
http://venkatesan-iyer.blogspot.com


Post Extras: Print Post   Remind Me!   Notify Moderator  
new2etl
Newbie


Reged: 06/21/10
Posts: 1
Re: ETL testing and the role of a QA [Re: gopster1283]
      #631595 - 06/21/10 04:14 PM

Hi wyaddow,
Could you send me the docs and materials relating to ETL [Edited by Mod] I would really appreciate your help.

Thank You

Edited by martinh (06/21/10 06:30 PM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
KishoreAutomation
Active Member


Reged: 10/08/06
Posts: 925
Re: ETL testing and the role of a QA [Re: new2etl]
      #631603 - 06/21/10 06:33 PM


I asked so long back, could you please send me the doucment

Regards,
Kishore


Post Extras: Print Post   Remind Me!   Notify Moderator  
Pages: 1 | 2 | >> (show all)



Extra information
0 registered and 11 anonymous users are browsing this forum.

Moderator:  blueinatl, AJ, Jeanj, corklad, martinh, brentpaine 

Print Topic

Forum Permissions
      You cannot start new topics
      You cannot reply to topics
      HTML is disabled
      UBBCode is enabled

Rating:
Topic views: 68303

Rate this topic

Jump to

Contact Us | Privacy statement SQAForums

Powered by UBB.threads™ 6.5.5