- It is the best way to pull data into MOSS from an external data source
Yes and no. It's very much depending on what your business requirement is. Do you want to just bring in say a list of master data for a particular column you'll be reusing? Then custom site columns are the way to go as I learned. Is it to bring in full records from business systems into your sharepoint site to get KPIs, include them in lists etc. etc. then yes it is.
- If I pull the data into MOSS lists and columns - is this data cached in MOSS?
As far as my experience goes - yes it is.
- If I change the raw data in the LOB data source, when will these changes show in my MOSS lists/web parts? Immediately?
Web part yes I believe, as custom columns in a list - no. There's a refresh button you can use to requery the BDC to get teh new data back.
- Apparently there is a BDC API to code against? Is this right? Has anyone used this?
There's objects as part of the Sharepoint namespace for the BDC and a webservice too. I've used both and they're just as straightforward as the rest of SharePoint for coding.