The warranty resource that stores a copy of the email
A retailer's ACS org has 3.2 million profiles and 4.6 million warranty registrations. The contractor modelled Warranty as a custom resource with a 1:1 link to Profile and, because the registration form sometimes arrives before the profile is created, also stored the customer's email as a free-text field that workflows and personalization use instead of the link. Two problems surfaced. A weekly workflow that joins Warranty to Profile returns 680,000 rows where the business expects 4.6 million, and duplicate warranties appear whenever a registration is re-sent. The delivery team wants one message per warranty, and the analytics export takes seven hours and fills the work tables. The business now needs subscriptions where one profile can hold many warranties and one warranty can cover several products.
1. Propose the corrected data model: cardinalities, link direction, deduplication key and the treatment of the free-text email.
Read the worked answer
Cardinality first. The business needs Warranty N:1 Profile: many warranties per profile, each warranty belonging to exactly one profile. If a warranty can cover several products, add WarrantyLine N:1 Warranty, with the product as an option list if the catalogue is small, or as its own resource if it is not. Create the link from the child side (Warranty points to Profile, marked as a link to profiles) so it is exposed through the profile and services Ext API and can be traversed from a profile query.
ACS will not let you flip an existing link from 1:1 to N:1 in place. Add the new N:1 link alongside the old one, populate it, validate, then retire the old link from workflows, personalization and exports before deleting anything.
Key: the registration reference issued by the form is the natural dedup key, unique and stable. Do not use email as a key. Keep the email column during migration only, as a matching input: normalise it once (trim, lower-case), reconcile to Profile, then stop using it as a join. Warranties that arrive before the profile should land in staging, be matched in a second pass, and only then be linked. Do not create placeholder profiles.
2. Explain the 680,000-row join result. What is the model telling you, and how would you migrate and validate without a big-bang cutover?
Read the worked answer
The 680,000 is not a join failure; it is the model reporting what it can represent. With a 1:1 link, ACS holds one warranty pointer per profile: when a second warranty is linked, the previous pointer is overwritten, so a profile-based join can only return roughly as many rows as there are profiles with a live link. The remaining warranties are only findable through the free-text email, which matches inconsistently (case, spaces, plus-addressing, changed address) and has no index. The join is really a text match on a lucky subset.
Also check the direction and dimension. Joining Warranty to Profile on the link is one row per warranty; joining Profile to Warranty can return one row. The direction explains most surprises.
Migration: add the N:1 link, stage the 4.6 million rows, normalise email once, match in batches with a reconciliation activity, write the link, and count per-profile warranties before and after. Validate with two independent counts: distinct registration references in Warranty (about 4.6 million) and profiles with a non-null link (about 3.2 million if every profile has at least one). Run old and new exports in parallel for a week and reconcile totals before switching.
3. The analytics export takes seven hours for 4.6 million rows. Where is the time going, and what would you change in the workflow and the export design?
Read the worked answer
Seven hours for 4.6 million rows is about 180 rows per second. That is not a row-count problem; it is a per-row lookup problem. The export is resolving Profile through the text email rather than the link, so each row triggers a comparison or lookup that cannot use an index, and if the extract also pulls columns from a linked dimension it can multiply the lookups.
Fix the model first, then shape the extract:
- Join on the new link, not on email.
- Build a flat work table in a query activity with only the columns the analysts need.
- Offer an incremental extract on lastModified instead of a full snapshot, if the receiving side can consume it.
- Export in slices (month, or an integer modulus on profile ID) so each slice releases its work table and creates a restart point.
- Turn off interim-result persistence for this workflow: 30 days of retained work tables slows every temporary-table creation.
- Export compressed, and measure duration and row count per activity so you can prove where the time goes instead of moving it elsewhere.