AI Haberleri

“Data Extension” is the generic term, but SFMC supports several DE types with different behaviors. Knowing the distinctions saves you from trying to send email from a lookup DE or forgetting to refresh a Filtered DE before a campaign.

Here’s the reference we hand to every new engineer.

1. Sendable DE

The main type used for sending email. Requirements:

• Is Sendable = true in Properties
• A field of type EmailAddress
• A Send Relationship mapping a field (usually Primary Key) to the Subscriber Key

This is the DE you pick when you send a campaign or set up a Journey with Data Extension entry source. No send can happen without one.

2. Lookup DE (Non-sendable)

Reference data that AMPscript Lookup() pulls into email templates at render time. Common examples:

• Sales rep directory: SalesRep_DE with RepID, name, email, phone
• Product catalog: Product_DE with SKU, name, description, price
• Store locations: Store_DE with StoreID, address, hours

Not sendable, no EmailAddress field needed. Used by the email, not as the source of the send.

3. Filtered DE

A DE created by applying a Data Filter to another DE – point-and-click segmentation without SQL. Example: “subscribers from Master_DE where MemberTier = Gold” produces a Gold-only Filtered DE.

Important: Filtered DEs need to be refreshed to reflect the current state of the source DE. They don’t auto-update.

Refresh via:

• Filter Activity in Automation Studio (scheduled or ad hoc)
• Manually in Email Studio

If the source DE changes (new imports, updates) but the Filtered DE isn’t refreshed, the Filtered DE holds stale data. Campaigns targeting it send to outdated segments.

For anything more complex than single-attribute filtering (joins, calculations), use SQL Query Activity writing to a standard DE, not a Filtered DE.

4. Random DE

Splits a source DE into N equal random chunks. Use case: A/B/N testing where you need 10 equal groups to test 10 email variants.

No SQL needed. Configure the split percentages in the UI; SFMC assigns rows randomly.

Caveats:

• The “random” assignment happens at creation; refreshing re-randomizes, potentially putting the same subscriber into different buckets.
• For more sophisticated random assignment (e.g. Journey Builder’s Random Split), the Journey tool is usually the better fit.

5. Shared DE

A DE placed in the Shared Data Extensions folder in the parent Business Unit. Multiple child BUs in the Enterprise account can access the DE without copying it.

Use when:

• The same reference data (product catalog, store list) is needed across multiple brands or regions.
• You want a single source of truth rather than syncing copies between BUs.

Access permissions are set via Shared Data Extension Permissions – which BUs can read/write.

Watch for unintended cross-BU writes: if two BUs can write to the same Shared DE, coordinate schemas and import schedules.

6. Send Log DE

A special DE that logs every email send – who received what, when, subject line, etc. Useful for:

• Audit requirements beyond SFMC’s default 10-day retention
• Join with other data to build custom reporting
• Investigate specific send events

Created from the TriggeredSendDataExtension template when used with Triggered Sends.

Caveat: Test Sends don’t write to Send Log. Only production sends do. If you’re testing and expecting the Send Log to populate, it won’t.

7. DE with Data Retention Policy

Any DE can have a retention policy configured on creation:

• Delete records older than X days – SFMC auto-purges rows past the threshold
• Delete data and DE after X days – whole DE goes away

Use for:

• Temporary event signup DEs (auto-purge 30 days after event)
• PII-heavy DEs that shouldn’t retain data beyond a defined window
• Any Send Log or archive DE to prevent unbounded growth

Set retention at creation if possible. Adding it later works but doesn’t apply retroactively to existing rows until the next automation evaluation.

Picking the right type on a new DE

NeedDE TypeSend email from this listSendableReference data AMPscript will look upLookupSimple attribute-based segmentFilteredA/B test random splitsRandomCross-BU shared referenceSharedAudit / custom trackingSend LogAuto-purge old dataDE with Retention Policy

Most projects end up with a mix: one or two Sendable DEs, several Lookup DEs, possibly a Shared DE for multi-brand setups, and retention policies on anything transient.

Takeaway

Naming the DE type right in your head before creating it prevents architecture rebuilds. The decision takes seconds; fixing an incorrectly-typed DE after it’s been loaded with data can take a day.

Designing SFMC data architecture? Our Salesforce team ships Data Extension layouts, shared-BU patterns, and retention strategies on production engagements. Get in touch ->

See our full platform services for the stack we cover.

A deal desk dashboard showed the running total of opportunities per account. Total amount per account was a rollup column. Users opened the dashboard, saw a total, made a decision. Then someone added a new opportunity and checked the same account. The account’s total did not change.

Fifteen minutes later, refresh – still not changed. An hour later – changed. The rollup column was working exactly as documented, and the team had mistaken it for a real-time aggregate.

Here is the difference between calculated and rollup columns, and the pattern we now use when the dashboard needs to be current.

The two column types

Calculated column: computes its value every time the row is read. If the column adds two other columns, every time anything loads the row (form view, API call, view row), Dataverse computes the sum. Results are always current because they are re-derived on demand.

Rollup column: computes its value on a schedule. The default schedule is every 12 hours, configurable down to one hour per column. A rollup that sums child record amounts reads the current state of children when the schedule fires, stores the result, and serves that stored value until the next schedule.

Both store their definition (the formula) in the solution. Both look identical to consuming code. The runtime behavior is radically different.

When each one is correct

Calculated columns are correct when:

• The formula depends only on fields on the same row, or on a parent’s fields (via lookup)
• The computation is cheap (arithmetic, string concatenation, conditionals)
• You need the value to be current every time it is read

Rollup columns are correct when:

• The formula aggregates across many child records (SUM, COUNT, AVG, MIN, MAX)
• The underlying data changes frequently but the aggregate does not need to be current within seconds
• Scheduling async recomputation is acceptable

A calculated column that aggregates children is not allowed. Dataverse blocks it – the reason is performance, reading an account would trigger a query across all its opportunities every load. Rollup is the platform’s answer: compute the aggregate ahead of time, serve it cached.

Why the async nature bites

Teams who want a rolling total of child records are usually asked for it by a business user who sees the number on a dashboard and expects it to match reality. When a user tells the team “the total on the Account form should show all linked Opportunity amounts,” the team builds a rollup column. The dashboard shows the rollup.

The user then creates an opportunity, looks at the account, and the total is stale. From the user’s perspective, the number is wrong. From the platform’s perspective, the number is accurate for the last time the schedule fired.

The fix is either to lower the user’s expectations (the dashboard updates on a schedule) or to change the implementation.

Real-time totals: three alternatives

When the aggregate genuinely needs to be current:

Alternative 1: plugin that updates a stored total on every child change.

A post-operation plugin on Opportunity (Create, Update on amount, Delete) recalculates acme_total_opportunities on the parent Account and writes it. Works for modest volumes – an account with 200 opportunities, each updating rarely, is fine. Fails for accounts with 20,000 opportunities updating frequently – every child change becomes a parent write, and the Dataverse execution pipeline starts throttling.

Good for: master-detail relationships with medium fanout.

Alternative 2: calculated column summing via the lookup.

You cannot aggregate children from a parent via a calculated column, but you can compute the child’s contribution per row using a calculated column on the child. acme_attributable_amount = IF(acme_is_counted, amount, 0) on Opportunity, then use that in any consumer query. The consumer does the aggregation at read time.

Works for: dashboards that can run FetchXML with aggregate operators (SUM the acme_attributable_amount across children in a single query). Fails if the consumer is a Dataverse form or view that cannot run aggregate queries.

Good for: Power BI reports, custom dashboards, anywhere you control the read query.

Alternative 3: rollup with one-hour schedule plus “Refresh” button.

Keep the rollup for the baseline case. Add a button on the form that explicitly triggers a rollup recalculation via the CalculateRollupField SDK request. User experiences it as “I just added a child, now I hit Refresh, the total updates.”

Works for: dashboards with bursty usage patterns – the baseline is acceptable, explicit refresh covers the “just changed it” case.

Good for: sales management screens, deal desk reviews, scenarios where users understand the refresh semantics.

The audit we run on rollups

On any project with more than three rollup columns, we audit quarterly:

1. Is the rollup still in use? Rollups accumulate – someone adds one for a requirement that later gets dropped, the column stays. Deprecated rollups still consume schedule slots.
2. Is the schedule frequency right? A rollup set to 12 hours when the underlying data changes every hour is both wrong (stale) and wasteful (the 12-hour window sees many state changes).
3. Is the formula still correct? Filters in rollup definitions reference specific statecode/statuscode values. When those values change semantics in later releases, rollups silently compute the wrong thing.

The audit takes 20 minutes per project. Half the time, at least one rollup can be deleted; a third of the time, at least one schedule needs adjusting.

Gotcha: deleting a child does not trigger rollup

A rollup of child-record amounts updates when a child is created or its amount changes. It does not update when a child is deleted, until the scheduled refresh fires.

The window between “child deleted” and “rollup refreshed” shows an overstated total. This is especially painful in audit scenarios – a user deletes a duplicate, the total still shows the duplicate’s contribution for up to twelve hours.

The fix is either a plugin on the child Delete event (post-operation, trigger a rollup recalculation explicitly via CalculateRollupFieldRequest) or a tighter schedule. We usually go with the plugin when the table supports deletions, because the time-lag is user-visible.

The rule we enforce

Rollup columns ship with a form tooltip: “This total refreshes every N hours. Click Refresh after adding new records to see updated totals immediately.”

The tooltip is one field-description line, it costs nothing to add, and it inoculates the “why is the number stale” ticket that would otherwise land every other week. Every rollup we ship has it. Every one we inherit gets it added on the first pass.

Manufacturers running Dynamics 365 Supply Chain Management almost always also run a dedicated Manufacturing Execution System (MES) on the shop floor. Production order updates, inventory movements, quality tests, and traceability data flow between them continuously. The integration has to be low-latency (shop floor runs on seconds, not hours), high-throughput (hundreds of events per minute at peak), and reliable (lost messages mean lost traceability).

Three integration patterns come up in evaluations. Two have documented failure modes.

The options that don’t fit manufacturing

Nightly batch jobs via Data Management Framework. Designed for bulk data movement, not real-time signaling. Production orders complete hours before D365 knows about it. Real-time inventory view is always lagging. Traceability data arrives after the batches have shipped.

Custom OData polling with a loop that queries MES every few seconds. Introduces polling overhead for no latency benefit, and MES systems aren’t typically designed to handle heavy poll loads. Also creates a custom code dependency that needs maintenance.

Database-level triggers on the MES database pushing directly to F&O’s database. Breaks supportability completely. D365 F&O is a managed platform – direct database writes aren’t supported, aren’t upgrade-safe, and will break the next time Microsoft changes schema. Also creates a security nightmare (MES has privileged write access to F&O’s database?).

The only answer that fits the requirements is Azure middleware between the two systems.

The Azure-native pattern

Logic Apps or Service Bus as middleware between MES and D365, with F&O Business Events on the D365 side.

What each piece does:

Azure Service Bus for the guaranteed-delivery, ordered messaging. Production-order status updates, inventory moves, quality-test results flow through Service Bus queues with FIFO ordering per production order.

Azure Logic Apps for the orchestration where branching and transformation happen. A pick-complete event from MES fires a Logic App that transforms the payload, updates inventory in D365, and triggers the next production-flow message back to MES.

F&O Business Events for the D365-side publishing. When a production order is created, released, or completed in F&O, a business event fires to Service Bus or Event Grid. MES subscribers pick it up.

Custom Services on F&O for the inbound – when MES has a state change D365 needs to record, the Logic App (or Function) calls a custom service endpoint on F&O. Custom services are designed for low-latency targeted writes, unlike data entities which are bulk-optimized.

Traceability architecture

Traceability is specific in manufacturing – regulators and customers need to know which raw materials went into which finished-goods batch. D365’s batch tracking combines with MES’s shop-floor batch recording to produce the full lineage. The integration ensures:

• MES tracks the physical movement (machine X processed batch Y at time Z)
• D365 records the ERP-level batch (raw material batch A consumed in production order B producing finished batch C)
• Integration correlates the two via batch numbers and production order references
• Recall scenarios can trace backward from sold finished goods to source materials, or forward from suspect materials to affected finished goods

The integration isn’t just about moving data – it’s about keeping the correlation intact under all failure modes.

High-throughput considerations

At manufacturing scale (large plants with multiple lines, each firing events per minute), throughput planning matters:

• Service Bus sizing – standard tier suffices for most deployments; Premium only when message volumes exceed the standard tier’s throughput units
• Logic Apps concurrency – configured per workflow, default is 20 concurrent runs; high-throughput flows need higher
• F&O write capacity – custom services are faster than data entities for single-record writes; batching is appropriate when MES aggregates multiple updates
• Dead-letter monitoring – alerts when Service Bus DLQ gets non-zero entries; usually indicates transformation errors that need human review

Reliability patterns

Manufacturing can’t afford lost messages. The architecture carries:

• Retry with exponential backoff in Logic Apps for transient failures
• Dead-letter queues for poison messages
• Idempotency keys on custom service calls so retry doesn’t double-record
• Correlation IDs flowing end-to-end for cross-system debugging
• Monitoring dashboards on message throughput and latency per queue

Direction-specific patterns

Flows in each direction have different shapes:

MES → D365 (updates ERP from shop floor):

• MES publishes to Service Bus topic
• Logic App subscribes, transforms, calls F&O custom service
• F&O updates inventory, production order status, quality records

D365 → MES (issues work to shop floor):

• F&O business event on production order release
• Event flows to Service Bus
• MES subscriber picks up the work package, distributes to lines

Bidirectional correlation:

• Correlation IDs in headers
• Handshake patterns for critical state transitions (e.g., “order ready” → “order accepted by line”)

Where custom code fits

Not everything is Logic Apps. Sometimes:

• Azure Functions for complex transformations Logic Apps can’t express cleanly
• Custom services in F&O for writes standard entities don’t cover
• Durable Functions for long-running orchestrations (multi-day production runs)

Each has a clear use case. Reach for them when the declarative tool hits a limit, not as default.

What ships with the architecture

A working MES-to-D365 integration has:

• Service Bus queues and topics partitioned by production-order or line
• Logic Apps for each directional flow with transformation logic
• Business events published on production order lifecycle
• Custom services on F&O for inbound writes from MES
• Dead-letter queue monitoring with alerting
• Traceability validation – spot-check recall scenarios quarterly
• Runbook for extending the integration when a new MES module is added

The pattern is architect-grade because manufacturing systems won’t tolerate the simpler options. Azure middleware is the supported, scalable, maintainable middle.