Working patterns in the migration database

Over many engagements, we have built up a library of reusable routines. A few common examples are described here. Each one shows how routine questions are answered reproducibly, with little manual work.

SQL preparation before data migration

Preliminary profiling. Before any mapping is written, every table in the staged source is profiled. For each column, the profile records its type, length, row count, null count, distinct count and most frequent values. Questions such as which columns contain email addresses, or which are mostly empty, can then be answered in a single query. Profiling output is the first evidence in an assessment and the basis of the completeness baseline.

Identifier conversion. Salesforce record IDs come in two forms: a 15-character form that is case-sensitive, and an 18-character form that is case-safe. Before any join or load, every Salesforce identifier is converted to the 18-character form. That way, IDs that pass through case-insensitive tools such as spreadsheets or databases with case-insensitive collation still match the right record.

Fuzzy matching. Candidate duplicates and cross-system matches are scored by Levenshtein distance, applied to normalized strings: names with legal suffixes, punctuation and case removed; email domains; and street addresses with unit designators standardized. The function, which descends from published open-source code, returns the number of single-character edits between two strings, and a threshold relative to string length yields candidates for review. The normalization and thresholds are customized for each client and recorded with the matching criteria the business signed. Where needs are more granular, such as weighted field scoring or field-level survivorship (keeping the best value of each field from any losing duplicate), our consultants may recommend a dedicated deduplication tool.

Validation

Validation output. Every load stores its results for each record, along with any error returned. Validation reports are generated from those stored results, rather than assembled by hand. They fall into two high-level buckets: success and exception. Reports are produced for each object and each test cycle, so the results of one event are kept apart from another's.

The value of these routines is consistency. The foundation is built once and kept in a source-controlled repository; the details are tailored to each client. Run on the same load, they produce the same results, whoever runs them. Salesforce's own data import guidance covers the platform tools the loads themselves use.