To your next point ("When a NULL value is provided for the target Id column, the record will be inserted."), I am a little bit confused. If I already have the INSERT task activated/scheduled which essentially imports all new records that have not been subscribed yet, do I still need have the UPSERT task activated for new subscriptions?
You have one package with the UPSERT operation that is scheduled to run every 3 hours. Most likely, 3000 records were inserted since the last package run (during 3 hours). As we can see, this behaviour is non-persistent.
Also, you have one package with the INSERT operation and state filter = Inserted. If the package with UPSERT operation runs first, it will insert all new records. Then, the package with the INSERT operation will also insert these new records which may lead to duplicates, errors, etc.
Please note, UPSERT operation always performs two operations - INSERT or UPDATE. In your scenario, you should use one package with the UPSERT operation or two packages with the INSERT and UPDATE operations.
The only issue I am encountering is only having records that have been modified in Salesforce to feed over to MailChimp and update existing subscribers. Would ("if a non-null value is provided for your target Id column, the update operation is erformed") be the way to do it?
The UPSERT operation updates a record if it exists or inserts a new record. This allows you to avoid inserting duplicate data. You need to map the target ID/Primary key columns for performing UPSERT.
In Skyvia, UPSERT determines what action to perform in the following way: if a Null value is specified for the ID or primary key, UPSERT operation inserts the record, and if a non-null value is specified, UPSERT operation tries to update the record with the specified ID or primary key. Skyvia does not actually checks if such record exists, and providing invalid ID/PK values results in failed records.
Lastly to your final point, If the UPDATE/UPSERT - ACCOUNT is set up incorrectly, What needs to be changed to only target modified records from salesforce and to have imported into MailChimp and update existing subscribers?
The first two tasks in your package (importing data from Contact and Lead) have correct lookup mapping for the id field. But the third task, for the Account object, uses just column mapping for the Id field. This is not correct. Id values in Salesforce Account are always different from Id values in MailChimp ListMembers. Since a non-null Id is provided for each account, the update operation is performed. But since there is no such Id value in MailChimp, each record will fail. Thus, you need to setup mapping for the Id field like it is made for the first two tasks.