Testing in a Data Warehouse

[RCC13]

I work in a Data Warehouse as a Quality Control Analyst. Development uses Abinitio as the ETL tool and we use SQL for writing our test scripts. I am trying to find out how other shops test their existing Data Warehouse maintenance projects. We use the functional and technical specifications to create our Test Strategy, Test Plans and Test Cases. We use a subset of production data for our test bed. We test all of the transformation rules stated in the specifications (some have very complex logic), creating ‘seeded’ test data to meet the criteria of the transformation rule if the data does not already exist. My Questions: How are other shops meeting the need to test all the transformation rules? Are other shops seeding data or only using what exists in production? If all transformation rules are not being tested is there a risk factor associated with the untested rules? In your environment does the Developer run the ETL graphs for loading test target tables or does your Testers run the graphs?
Thanks.

 

[johnherman]

Testing can be broken into at least 4 components:

At the lowest level is Unit Testing which is based on the Detail Design Specs or Work Breakdown Structure. Basic field mapping and transformation testing starts with unit testing.

Integration Testing comes next. The new code or modules is merged into the existing system and tested.

System Testing comes next. This is based on the Functional Requirements or Specifications. Each functional requirement should have one or more tests.

Acceptance Testing or UAT comes next. This tests the user requirements. If the users have no requirements, this step validates that no measurement gains or loses size during the loading of the Data Warehouse. Money and count fields should be balanced.

Whenever possible, the tester should be different from the developer.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright