We use a subset of real data as a basis to ensure that linkages are all going to work, but it's then all anonymised and reviewed by a second person to ensure that it really is going to be okay. This way, the system will work in the same way as live (rejecting records that are invalid or orphaned for example) and loading data that has the full referential integrity as expected. Due to the fact that we're testing batch loads into data warehouses most of the time, we have rafts of test data (separate from the development samples that we build for the developers) with a myriad of scenarios that will ensure that we cover most bases - new scenarios are added all the time. We build processes to build the test data so that it can be regenerated on demand. We're not talking huge volumes of data here, as this is essentially functional testing at this stage.

All of our emails are sent via a separate SQL / SSIS application (we've had issues with email servers going offline just as we were about to send out a "success" email which then caused the job to keel over). This means that the email requests are dropped into a SQL table, and then batch sent later. The test version of the email system doesn't actually send emails until you ask it to (the live version is a 24x7 job, test isn't) and so we can review what should be sent before it is. The test system doesn't have authority to relay outside the organisation either as a separate safety feature. The other safety feature which helps prevent us sending out emails to the wrong people is that all email distribution lists are managed within the email app, rather than in the SSIS / SQL process. This also means that we can easily change the recipients of emails when job changes happen without having to re-deploy the system. The system just requests an email to be sent to "Client1" of type "File Loaded Successfully" and the distribution list is retrieved from the email system at send time. Given that we have a separate test system, we have a separate test email table so we have different email lists pointing to the same email type.

Ben