Feed layout holding tables

Intent of feed layout holding tables

This topic explains why a record could be placed in a holding table for a feed layout definition (FLD).

FLD holding tables keep track of records that include information that doesn't match with the rest of the database. Holding table records are correctly formatted and valid but include information that is missing in an associated table. Effectively, the record doesn't fulfill the referential integrity requirements of the database (for example, a transaction record that specifies a product that doesn't exist). This often means that files are being sent/processed in the wrong order or that there's incomplete information.

Records that don’t comply with the standards/schema will end up getting filtered out of a load by the file validation step and be placed in a quarantine file.

Here are some other important things to know about FLD holding tables:

Redpoint CDP will not attempt to load records in a holding table for the current execution, but will attempt to load these records on the subsequent run of the specific FLD
FLD holding tables are cumulative and will continue to grow over time if you don't resolve all issues causing the records to be held
All FLDs have holding tables except for the following:
- Location (a core feed layout)
- Product (a retail feed layout)
The behavior of holding tables can be influenced by setting the holdingRetentionDays parameter in the API database settings. A value of -1 (the default) will allow unlimited duration; a positive value indicates the maximum number of days that the holding records will be retained. If you need to modify this value after your initial CDP setup, please contact Redpoint support.

Holding table reasons

An FLD holding table generates a report with the reason(s) that records are being held. The held_reason column is a comma-separated list of the reasons that a single record is being held. In the example below, the second column provides the number of records that are being held for a particular reason.

Example `held_reason`(s) from holding table with counts

The following table outlines each of the reasons that a record could get placed in the holding table. As mentioned previously and shown in the image above, a record may have more than one reason that it's being held.

In the table below...

References to multiple fields that include a "+" sign (for example, source_customer_account_id + business_unit_code) mean that the combination of the fields is used to perform a lookup, and not that the two fields are combined in any way.
The fields tie to the Field Name column in the feed layout definitions.
When business_unit_code is referenced, it is important to mention that business_unit_code is a field that uses a lookup table, and this field is not required to be populated. If it is not populated, then the default business_unit_code from the lookup table is used when loading the data. So, if you see a held reference including this field, but you are not providing a business_unit_code, then it implies that the combination of fields that includes the business_unit_code in it is using the default value from the lookup table when the combination of values is being used to identify a record in the database.
When address alone is referenced, it is a reference to the full address after it has gone through address standardization; each component is not listed out but implied as a part of the address.

Held reason	Held description	Ways to resolve
`ADDR_TYPE_CODE_1_INVAL` (this error code will only appear if a custom validation is being used)	The `address_type_code_1` referenced by this record does not exist in the database.	The `address_type_code_1` for this record should be updated to one of the allowed values documented in the core canonical layout document, reference data tab, `ADDRESS_TYPE_LOOKUP` table.
`ADDR_TYPE_CODE_2_INVAL` (this error code will only appear if a custom validation is being used)	The `address_type_code_2` `address` referenced by this record does not exist in the database.	The `address_type_code_2` for this record should be updated to one of the allowed values documented in the core canonical layout document, reference data tab, `ADDRESS_TYPE_LOOKUP` table.
`ADDRESS_INVAL`	The `address` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this address.
`CAMPAIGN_EVENT_INVAL`	The `client_campaign_event_id` referenced by this record does not exist in the database.	Send a record through the campaign event nonRPI FLD that contains this `client_campaign_event_id`.
`CUSTOMER_ACCOUNT_INVAL`	The `source_customer_account_id` + `business_unit_code` referenced by this record does not exist in the database.	Send a record through the customer account FLD that contains this `source_account_id` + `business_unit_code` combination.
`EMAIL_ID_INVAL`	The `email_id` referenced by this record does not exist in the database.	The `email_id` in this record needs to be adjusted to reflect a current `email_id`.
`EMAIL_INVAL`	The `email_address` referenced by this record does not exist in the database (`IR.EMAIL` table).	Send a record through the party profile FLD or transaction FLD that contains this email address so that it gets into the database (`IR.EMAIL` table).
`HOME_PHONE_INVAL`	The home phone number referenced by this record does not exist in the database (`IR.PHONE` table).	Send a record through the party profile FLD or transaction FLD that contains this home phone number so that it gets into the database (`IR.PHONE` table).
`HOUSEHOLD_ID_INVAL`	The `household_id` referenced by this record does not exist in the database.	The `household_id` in this record needs to be adjusted to reflect a current `household_id`. Could indicate a `household_id` was updated, check the `IR.INTERNAL_ID_XREF` table.
`INCOMPLETE_TRANS_DETAIL`	Only partial required transaction detail information for this record exists.	Make sure `line_item_num`, `source_product_id`, `transaction_detail_status_code`, and `global_net_sales_amount` fields are all populated.
`IND_ADDRESS_INVAL`	The `address` + `individual_id` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `address` + `individual` combination.
`IND_EMAIL_INVAL`	The `email_address` + `individual_id` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `email_address` + `individual_id` combination.
`IND_HOME_PHONE_INVAL`	The `home_phone_num` + `individual_id` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `home_phone_num` + `individual_id` combination.
`IND_MOBILE_PHONE_INVAL`	The `mobile_phone_num` + `individual_id` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `mobile_phone_num` + `individual_id` combination.
`IND_OTHER_PHONE_INVAL`	The (`other_phone_num_1` or `other_phone_num_2`) + `individual_id` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this (`other_phone_num_1` or `other_phone_num_2`) + `individual_id` combination.
`IND_WORK_PHONE_INVAL`	The `work_phone_num` + `individual_id` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `work_phone_num` + `individual_id` combination.
`INDIVIDUAL_ID_INVAL`	The `individual_id` referenced by this record does not exist in the database.	The `individual_id` in this record needs to be adjusted to reflect a current `individual_id`. Could indicate an `individual_id` was updated, check the `IR.INTERNAL_ID_XREF` table.
`LOCATION_INVAL`	The `source_location_id` + `business_unit_code` referenced by this record does not exist in the database.	Send a record through the location hierarchy FLD that contains this `source_location_id` + `business_unit_code` combination.
`LOYALTY_ACCOUNT_INVAL`	The `source_account_id` + `business_unit_code` referenced by this record does not exist in the database.	Send a record through the loyalty account FLD that contains this `source_account_id` + `business_unit_code` combination.
`MATCH_INSTANCE_INVAL` (should not appear)	The `match_instance_id` referenced in this record does not exist.	This indicates a mismatch between the match process and the transform job and some sort of system error. Contact support.
`MOBILE_PHONE_INVAL`	The `mobile_phone_num` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `mobile_phone_num`.
`ORIG_LOCATION_INVAL`	The `original_source_location_id` + `business_unit_code` referenced by this record does not exist in the database.	Send a record through the location hierarchy FLD that contains this `source_location_id` + `business_unit_code` combination.
`OTHER_EMAIL_1_INVAL`	The `other_email_address_1` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains `other_email_address_1`.
`OTHER_EMAIL_2_INVAL`	The `other_email_address_2` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains `other_email_address_2`.
`OTHER_EMAIL_3_INVAL`	The `other_email_address_3` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains `other_email_address_3`.
`OTHER_PHONE_1_INVAL`	The `other_phone_num_1` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains `other_phone_num_1`.
`OTHER_PHONE_2_INVAL`	The `other_phone_num_2` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains `other_phone_num_2`.
`PARTY_PROFILE_INVAL`	The `source_party_profile_id` + `business_unit_code` referenced by this record does not exist in the database.	Send a record through the party profile FLD that contains this `source_party_profile_id` + `business_unit_code` combination.
`PERSONAL_EMAIL_INVAL`	The `personal_email_address` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains `personal_email_address`.
`PHONE_INVAL`	The phone number referenced by this record does not exist in the database. This error is only generated for contact authorization records where phone is provided, and that is because there is no notion of phone type in the contact authorization feed layout.	Send a record through the party profile FLD or transaction FLD that contains this phone number (home, mobile, work, or other).
`PRODUCT_INVAL`	The `source_product_id` referenced by this record does not exist in the database.	Send a record through the product hierarchy FLD that contains this `source_product_id`.
`RESPONSE_EVENT_INVAL`	The `client_response_event_id` referenced by this record does not exist in the database.	Send a record through the response event FLD that contains this `client_response_event_id`.
`SOCIAL_INVAL`	The social login (`social_login_1`, `social_login_2`, `social_login_3`, `social_login_4`, `social_login_5`) referenced by this record does not exist in the database.	Send a record through the party profile FLD that contains this social login (`social_login_1`, `social_login_2`, `social_login_3`, `social_login_4`, `social_login_5`).
`TRANSACTION_DETAIL_INVAL`	The `source_transaction_id` + `line_item_num` referenced by this record does not exist in the database.	Send a record through the transaction FLD that contains this `source_transaction_id` + `line_item_num` combination.
`TRANSACTION_DISCOUNT_INVAL`	The `source_transaction_id` + `line_item_num` + `discount_sequence` referenced by this record does not exist in the database.	Send a record through the purchase discount FLD that contains this `source_transaction_id` + `line_item_num` + `discount_sequence` combination.
`TRANSACTION_INVAL`	The `source_transaction_id` referenced by this record does not exist in the database.	Send a record through the transaction FLD that contains this `source_transaction_id`.
`TRANSACTION_PAYMENT_INVAL`	The `source_transaction_id` + `payment_seq_num` referenced by this record does not exist in the database.	Send a record through the tender FLD that contains this `source_transaction_id` + `payment_seq_num` combination.
`WORK_EMAIL_INVAL`	The `work_email_address` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `work_email_address`.
`WORK_PHONE_INVAL`	The `work_phone_num` referenced by this record does not exist in the database.	Send a record through the party profile FLD or transaction FLD that contains this `work_phone_num`.