Yes, there is a 1:1 relationship between AccountID and ESFA_ID in Accounts. Sorry for the lack of clarity. Essentially, ESFA_History is a decommissioned CRM system table and GolfDataSnapShot is a new, similar (probably not exact, but that's not anybodies problem but mine) table from SalesForce CRM. Some of the fields in ESFA_History, upon renaming, would contain values found in the other two tables. But, ESFA_History should be a historical version of GolfDataSnapShot.
what I would do is exactly the same as you suggested yourself initially: masssage all the data so that it fits in a single table. You can do this by distilling a Mapping Table from Accounts that translates ESFA_ID into a single AccountID. Since AccountID is an index into the currently used CRM system, we'll keep that one and eliminate ESFA_ID (or turn it into a non-key field, just for reference).
Upon loading the ESFA_History table, translate the ESFA_ID into an AccountID using the mapping table and concatenate the rows to the GolfDataSnapShot, thereby renaming all corresponding ESFA_History fields.
The few exceptions (having two AccountID's) should be analyzed manually; you can then decide what to do about them: drop one (again using a mapping table that contains a list of "illegal" ESFA_ID's), merge information into a single row or something else. This is just a data quality phase and should be manageable.
Does this sound useful to you?
This is a good solution and i think the best way to handle this situation properly. Upon further investigation, the data is in a great need of cleansing; more so than i expected. I'm dropping the duplicates and bad account names with WHERE clauses (where not wildmatch(Name, '*USE ACCOUNT BLAH*') or something like not wildmatch(Name, '*DELETED ACCOUNT*') etc. <-- this is the kind stuff i'm battling with) . There are too many to pick individual accounts out for a mapping table. By the way, would you please go further into the mapping table containing "illegal" ESFA_ID's? I can't envision how to script for that. It sounds useful for future endeavors if you wouldn't mind sharing with us.
For example, imagine that an ESFA_ID should take the form ABCD1234, then something like this could do the job.
MAPPING LOAD *, 1 AS Flag INLINE [
FROM ... (qvd)
WHERE Not applymap('MapIDsToEliminate', ESFA_ID, false());
The main advantage of Mapping Table vs (wild)match is that an INLINE source is easier to maintain and can be easily converted into an external source (if the illegal-ID-table keeps growing)
I recently used something similar to correct absurd transaction dates that had obviously been the result of typing errors (like Jun 6, 3012). This trick can be used when there aren't too many corrections to make.