Answering your questions:
1 - the sourcetype have to be created on the HF where the DB Connect is installed, because it will be used to identify your data for the indexing pipeline.
2 - As far as I know, if the source DB table is cleaned it should not cleanup the splunk index because once the data is indexed you cannot change the data, although the retention/max volume parameters from the particular index is reached or someone cleanup all the events to the this particular index, thus the user have to have admin rights and splunk service must to be stopped to run the clean command. Per my knowledge, only dblookup function run the queries on the source DB tables, but did not index any data into splunk, it is being used to enrich data into splunk.
for further information about dblookup, check this document -> https://docs.splunk.com/Documentation/DBX/3.2.0/DeployDBX/Createandmanagedatabaselookups