One of the great features of the Force.com platform is the ease at which you can meet auditing requirements such as field history tracking. With just a few clicks you can add history tracking to most standard objects, and any custom object. And because everything on the platform is metadata driven you can access this same field history information through the API:
For a custom object, you could use a query such as:
SELECT OldValue, NewValue, Parent.Id, Parent.name, Parent.customfield__c FROM foo__history
For a standard object, you could use a query such as:
SELECT OldValue, NewValue, Parent.Id, Parent.name, Parent.customfield__c FROM ContactHistory
I had a customer query this morning asking about Opportunity field history, and access through the API. Naturally, your first instinct is to look for an OpportunityHistory table, which certainly does exist; But Opportunities are a special case, it seems, when referring to field tracking.The OpportunityHistory table stores a record of how a particular Opportunity has progressed through the various stages of it's lifecycle.
I did a little digging, and if you want to access field history tracking for Opportunity you need to use the OpportunityFieldHistory table instead:
SELECT OldValue, NewValue, OpportunityId FROM OpportunityFieldHistory
It's a small tip, I know --- but sometimes these are the best ones.