Text fields behaving like Formula/Lookup fields

Question

We have found some fields in Salesforce that are defined as normal text fields, but behave like Formula/Lookup fields. What I mean by this is that these field's values change, but the systemmodstamp/lastmodifieddate do not change. Here are 2 examples:

1. Asset.ProductCode
   This field seems to be getting its value from Asset.Product2.ProductCode. But, the field is not defined as a formula field and is just a text field.

2. CampaignMember (City, State, Title, etc).
   These fields seem to be getting their value from the Lead/Contact object that is related to the CampaignMember. But again, the field are not setup as formula fields, so we are trying to understand how Salesforce updates these fields on the CampaignMember object without updating the SystemModStamp.

Question:

1. How does Salesforce update these field values without updating the SystemModStamp, as these are not formula/lookup fields?

2. How can we determine a field is a special field like these ones that display information from a related object using metadata?

Repro Steps

1. Open an Asset or CampaignMember record in SF. Note the SystemModStamp value.
2. Navigate to the associated Product (for an Asset) or Contac/Lead (for CampaignMember).
3. Edit the productCode on product or title on Contact/Lead.
4. Go back to the Asset or CampaignMember record.
5. Observe the ProductCode/Title show the latest value. SystemModStamp would not have changed.
6. Look at the metadata for these fields, they are not defined as lookups/formula.

Background:
We pull data from Salesforce to use in a DataLake. We tell our users to not use values in formula/lookup (relationship based) fields and instead to join to the other objects. But in this case, because the metadata does not have any information about these fields being a formula/relationship field, we dont know if the issue is being caused by a bug in our system or we need to give guidance to the users to use a formula/relationship. We are trying to figure out a reliable way to list all such fields on a Salesforce Object.

Answer 1

You can check this salesforce developer documentation link for campaign & asset object which will give you a type, properties & description on the various standard fields:

CampaignMember:

https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/sforce_api_objects_campaignmember.htm

Product:

https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/sforce_api_objects_asset.htm

For instance a quick search on the city field on CampaignMember object returned me the following:

Likewise you can list out other fields which might have internal salesforce mechanisms that reference related object fields without being explicitly defined as formula fields.