A police detective works for the municipality, whether it be a city, township or county (though in my experience, counties are usually under the auspices of a sheriff & have deputies rather than detectives). A detective might also work for the local DA's office.
A federal investigator is someone who works for the Justice Department (incl the FBI), Homeland Security, CIA, Treasury Dep't etc. Their duties/areas of investigation are strictly defined by their charter (what Congress has authorized them to do.) They may coordinate with the local police; for instance, the FBI is normally called in for kidnappings. But the crime is usually first investigated by the local police, who make the determination of whether or not to call in the Feds.
If a detective disobeys a direct order from his superiors, it is likely he'd be terminated for dereliction of duty or insubordination, especially if he refuses more than one order. Most PDs would have a procedure that needs to be followed (eg: verbal notice, written notice, firing), so that an officer has a chance to make good. The more times he refuses the order, the more likely it is he'll be fired.
Sorry it took so long to respond, but I'm here about once a month, as you see. I've set up a "watch" on this thread -- if you have any more questions, I'd be happy to help as much as I can. My father-in-law was a police lieutenant, so I'm speaking from a small-town experience.
Hope this helps!