For an extension, to modify database data, is it better to use SQL or API
Generally, it is wisest to always use the API.  Many necessary updates actually touch multiple tables and the API takes care of it nicely.  This is especially true with financial updates. The API also encapsulates the database structure so as to effectively hide any database structure changes that may happen over time.

IMHO, pretty much the only time that SQL might be called for is if you are doing a huge bulk update wherein using the API would take excessively long.