Data Engineering

Reprocess a small overlap window so late-arriving records are not silently lost.

Normalize inconsistent metadata keys into a stable schema before reporting/export.

Choose a surviving customer row by source trust first, recency second.

Choose the first non-null value from multiple candidate fields.

Translate explicit business rules into a readable segment label.

Normalize multiple source populations into one staging shape without deduplicating too early.

Compare normalized object dictionaries to identify keys that were added, removed or changed between exports.

Use ROW_NUMBER() when the business question is “which single row should survive inside each logical group?” The hard part is not the function — it is defining the partition and ordering rules correctly.

A Golden Record rule often needs explicit source trust, status priority or permission precedence before recency. Put those rules in the window ORDER BY so the surviving row reflects the business contract.

On large Data Extensions and Data Views, where the function is applied can decide whether the database can use an index efficiently. Keep searchable columns “bare” on the left side of predicates whenever possible.

Most “my counts exploded” problems are grain problems. Define what one row represents in every source before writing the JOIN.

When one Query Activity has too many joins, scans and transformations, split the work into deterministic stages. You gain observability, smaller scans and easier failure isolation.

The SQL text is only half the Query Activity. The target action changes how the platform writes data and can become the bottleneck as the target grows.

Use NTILE() when you need balanced random buckets. Use _CustomObjectKey modulo when you need a very cheap, approximately even split on large standard Data Extensions and exact balance is not required.

A Data Extension is a relational table, not an ordered list. ORDER BY is useful when paired with TOP or window functions to select which rows win, but downstream consumers should never rely on physical row order.

These are the defaults I want to remember before promoting a query from exploration into a production automation.

Name the columns you intend to move instead of depending on SELECT *.

A pragmatic consolidation layer inside SFMC when enterprise CRM/MDM capabilities are unavailable.

Convenient during exploration, fragile as a durable data contract.

Normalize external metadata into stable typed records before building reports, graphs or drift logic.

Turn duplicated source records into a repeatable winner-selection flow with explicit identity, source priority and tie-break rules.

Consolidate identity inside SFMC when the organization lacks a dedicated CRM/MDM layer, while keeping the workaround explicit.

Normalize two environment exports, compare stable object fingerprints and review dependency drift before a migration or structural change.