From weekly exports to Snowflake + dbt feeding Eloqua CDOs with current shipment context

Overview

A national freight brokerage saw its account-based emails undercut by stale shipment details. Marketing pulled weekly exports to build outreach in Oracle Eloqua, while operations worked from constantly changing transportation data. We partnered with both teams to create a governed Snowflake data share from operations, transformed and validated with data build tool (dbt), and piped into Eloqua custom objects and dynamic fields through the platform’s bulk APIs. Reps began sending outreach with current milestones and context, which cut back on back-and-forth to confirm details, increased trust in the message, and made follow-ups faster and more relevant.

Client Profile

• Industry: Freight brokerage and logistics
• Company size (range): Mid-market
• Stage: Established, scaling across regions
• Department owner: Marketing & Customer Engagement
• Other stakeholders: Sales and Account Management, Operations, Data Engineering, IT/Security, Legal/Compliance

The Challenge

Account-based programs depended on knowing where a customer’s shipments stood: tender accepted, appointment set, in transit, exception flagged, delivered, and so on. Marketing was exporting a weekly file from operations to update Eloqua contact fields and a few custom objects. That worked when shipment volumes were predictable. It broke as lanes became more dynamic. Reps were emailing based on last week’s status, which created confusion when a delivery had moved forward, stalled, or changed altogether. Customers started asking to “check with ops” before responding. Every thread turned into a manual verification step.

Under the hood, the tooling wasn’t bad, just misaligned. Operations had a clean Snowflake environment tied to the transportation management system, and the data team already used dbt for transformations. Marketing had well-structured campaigns in Eloqua. But there was no timely bridge between them. Budgets favored reuse over a new platform. IT wanted governed data access and fewer CSVs flying around. Legal asked for better control of what freight details left the core system. Marketing needed updates in a cadence that matched shipping reality, without turning every campaign into a custom integration project.

Why It Was Happening

The workflow failed for familiar reasons: fragmentation and unclear ownership. Operations curated data to run the business; marketing copied a slice of it when convenient. The weekly export froze definitions at a moment in time and ignored the cadence of the network. Field names were reused across teams with different meanings. The Eloqua mapping didn’t reflect current business logic for milestones and exceptions. No validations ran before pushing content into campaigns, so a single bad value could personalize emails with misleading dates or statuses.

Handoffs were manual and brittle. When a load changed, operations had no trigger to inform marketing. When marketing scheduled a send, there was no automated confidence check tied to current shipment records. The result was predictable: reps became human routers, jumping between email, instant messages, and the TMS to confirm facts that should have been accurate in the first place.

The Solution

We aligned the data layer and the engagement layer without replacing either. Operations published a governed share in Snowflake with only the fields needed for outreach. The data team modeled and tested those fields in dbt, then we used Eloqua’s bulk APIs to populate custom objects and dynamic fields that power account-based personalization. Campaign logic used those fields to assemble timely context while including guardrails and fallbacks. Reps still reviewed high-stakes messages, but they no longer had to chase status updates before hitting send.

• Secure Snowflake data share from operations with read-only access to curated shipment views (Snowflake Secure Data Sharing)
• dbt models with tests for nulls, uniqueness, accepted values, and referential integrity (dbt tests)
• Derived fields for outreach, such as next milestone, aging exception summary, and shipment recency tier
• Privacy filters to exclude sensitive cargo details and customer notes
• Reverse data sync to Eloqua via bulk API, mapping to Custom Data Objects (CDOs) and contact fields (Oracle Eloqua)
• Dynamic content blocks that render different messaging based on milestone and exception flags
• Human-in-the-loop approval for high-priority accounts and flagged exceptions
• Alerting for schema changes or failed validations, with automatic fallbacks in Eloqua content
• Access controls and audit logs, aligned with IT and Legal policies
• Dashboard for marketing and operations to see sync status, last refresh, and test results

Implementation

• Discovery: Shadowed reps and campaign managers to map how shipment context influenced outreach. Cataloged the exact fields needed and sensitivity levels. Clarified owners for definitions like “delivered” and “exception.”
• Design: Defined a shared data contract in Snowflake with names, types, and business logic for each outreach field. Designed dbt models and tests. Mapped CDOs and contact fields in Eloqua. Planned fallbacks for missing or questionable data.
• Build: Published a read-only data share from operations. Built dbt models for shipment milestones, exceptions, and contact-to-account joins. Implemented filters to drop sensitive details. Set up bulk API jobs to upsert Eloqua CDOs and contact fields.
• Testing/QA: Ran dbt tests on every refresh. Compared Eloqua previews against Snowflake views in a sandbox. Introduced a human-in-the-loop review queue for top accounts and flagged records before opening the spigot to production campaigns.
• Rollout: Started with a small subset of accounts and a single campaign. Monitored alerting and dashboards, then expanded to more segments. Kept the weekly file as a temporary fallback until confidence was established.
• Training/hand-off: Trained marketing on dynamic content rules, fallback behavior, and the approval queue. Trained operations on the data contract and how changes flow through. Documented governance, access, and incident response.

Results

Reps began emailing with shipment details that matched what customers saw on their end. Milestone shifts showed up in outreach without last-minute scrambles. Exceptions were summarized clearly, and when data looked questionable, fallbacks kept messages accurate rather than empty or misleading. The back-and-forth to confirm basics dropped, and conversations moved faster to scheduling, accessorials, and next loads.

Marketing gained dependable personalization without ballooning effort. Scheduling became more predictable because waves no longer paused for manual status checks. Operations saw fewer ad hoc pings from reps. IT and Legal were more comfortable because data left the core system through a governed path with auditability, and CSV exports faded into the background. Campaign performance improved steadily as trust in the content returned.

What Changed for the Team

• Before: Reps checked shipment status in multiple systems before emailing. After: Outreach templates pulled current milestones automatically with a quick approval for sensitive cases.
• Before: Marketing built lists from weekly files. After: Dynamic content used a governed data share with transformations and validations.
• Before: Conflicting definitions caused inconsistent messaging. After: A shared data contract aligned operations, marketing, and sales on field meanings.
• Before: CSVs were passed around through inboxes. After: Secure, read-only access fed only the fields needed into Eloqua.
• Before: Campaigns stalled when data looked wrong. After: Fallback content and alerts kept schedules steady while issues were resolved.

Key Takeaways

• Integrate marketing with the operational source of truth instead of duplicating it; curate what is needed and nothing more.
• Validate marketing data at the warehouse layer so content renders consistently and safely.
• Use dynamic content and custom objects in the marketing platform to avoid rebuilding journeys for each account.
• Keep humans in the loop where nuance matters; automate the rest with clear fallbacks.
• Establish a data contract across teams to stabilize definitions as business logic evolves.
• Favor governed data sharing over ad hoc exports to meet both performance and compliance needs.

FAQ

What tools did this integrate with?
Operations exposed a curated Snowflake share, transformations and tests ran in dbt, and Oracle Eloqua received updates through bulk API jobs that populated Custom Data Objects and contact fields. The transportation management system remained the upstream source, connected to Snowflake as before.

How did you handle quality control and governance?
We introduced a shared data contract, dbt tests for critical fields, and alerting on failures or schema changes. The Snowflake share was read-only with field-level filtering to exclude sensitive data. In Eloqua, dynamic content included fallbacks when validations flagged an issue, and high-impact sends required a human-in-the-loop approval for flagged accounts.

How did you roll this out without disruption?
We piloted with a narrow segment and a single campaign, kept the weekly file as a temporary fallback, and monitored dashboards and alerts. Only after the new pipeline proved stable did we expand segments and retire the manual export.

What data did you bring into Eloqua, and what stayed in operations?
Only fields directly needed for outreach were synced: shipment milestone, exception summary, shipment recency tier, and account-to-contact linkage. Sensitive cargo details, internal notes, and operational metrics with limited messaging value stayed in Snowflake.

What happens when definitions or schemas change upstream?
Schema changes are detected through tests and metadata checks. Alerts notify owners, and Eloqua renders fallback content until the contract and mappings are updated. This keeps campaigns accurate while allowing operations to evolve their models responsibly.