I download an employee's email into a table, I then take that collection of emails I just downloaded and run a bunch of rules on them.
e.g. if email from [email protected], route the email to folder1.
How could I write a unit test for this process?
Would I have to create a dummy collection of emails, and then right a rule for each one?
I have a hard time breaking the unit test out, I always want to include database procedures into the test which I know is wrong.
The exact shape of your unit tests will depend on the architecture of your system. Generally, you would create a set of mock emails. Use a mocked Email object. They don't have to actually exist in a database. You should have the emails represented as classes anyway.
Write your unit tests to Act on the mocked emails, and assert that the rule behaved the way you expect.
How you can test a piece of software largely depends how you've broken it down into different components. There's really no testing of a "process". You should be focusing on individual components instead. For example, with a process like the one you described, the software could be broken down into different components as follows:
You would essentially be testing each component individually, running tests that exercise every aspect (as much as possible) of each component.
I'm being a purist here, but a unit test should test the smallest unit of code. Which is normally a class method. So you should look at each public method your class or classes has and write test which test only the logic in that method. That may mean mocking out other classes that interact, or refactoring your code to reduce the dependencies. Often you will find that you have a class that has multiple responsibilities which should be broken down into multiple classes which will make it possible to write unit tests for. This is how unit testing is a appraisal activity rather than a testing activity. It should increase the quality of your design.
You might also look at customer focused tests for testing it all works together.