Platforms

Solutions

Products

Services

Resources

Company

About Us

Clientele

Events

Careers

Disclosures

Media Kit

Select Language

SELECT LANGUAGE

Platforms

Solutions

Products

Services

Resource

Company

Support

Select Language

SELECT LANGUAGE

Resource

/ Blogs

From Working to Trustworthy: Building Resilience and Observability into an Enterprise Snowflake Data Pipeline (AEP + Marketo)

Every Pipeline Works on Day One

Most data pipelines work on launch day. Demos are clean. Stakeholders are happy. Data flows from source to destination on schedule.

What separates a pipeline that works from a pipeline you can trust is what happens on every other day — when a deployment goes wrong, when a client needs a historical rebuild, when a task silently succeeds and loads nothing, or when alerts fire so often that the team stops reading them.

We recently built a complete Snowflake data pipeline observability and resilience layer for a global B2B marketing data mart, serving Adobe Experience Platform (AEP) and Marketo. The pipeline was functionally sound when we engaged. What it lacked was the operational depth that enterprise clients need before they commit real revenue to it.

Here is what we built, what broke before we fixed it, and what changed.

The Problem with Stream-Based Pipelines When Things Go Wrong

The pipeline processed daily snapshot merges on Snowflake streams — a sound design for incremental workloads. Streams are efficient. They track only what changed, advance on commit, and prevent duplicate processing.

They are also fragile in the wrong hands.

When a client needed a historical rebuild or a bad deployment wiped a snapshot, the team had two options: use the stream and risk consuming offsets that the live pipeline still depended on, or run manual SQL and hope nothing got corrupted. Either way, a senior engineer spent 30 to 60 minutes on the call per entity. There was no safe way to re-run. Every backfill carried real production risk.

Building Safe Bootstrap Procedures

We built 20+ dedicated bootstrap procedures — covering AEP person, account, entitlement, preference, the full Marketo entity set, Desktop, DxGy, Capital Offer, and Workforce entities.

The critical design decision: they read directly from audit consumption streams and leave stream offsets completely untouched. Production continues uninterrupted. Operations can trigger a full entity rebuild from a runbook without involving a data engineer.

The outcome was straightforward: backfill turnaround dropped from hours to minutes. Re-runs became safe because the underlying MERGE operations are idempotent. The class of incident where a manual stream read consumed offsets the incremental pipeline still needed went to zero after bootstrap procedures were in place.

Validation That Actually Gets Used

Recovery tooling is only useful if the team can verify the recovery worked — quickly, consistently, and without requiring a specialist.

Post-load verification previously took approximately 20 minutes of manual SQL across three to four scripts per entity. Under pressure, teams skip that step. When it gets skipped, bad data reaches AEP and Marketo, and client-facing systems carry the cost.

We built a primary key validation dashboard that runs automated PK checks across all bootstrap tables for AEP and Marketo. Verification time dropped from 20 minutes to under 2 minutes in a single view.

We also built a task-monitoring dashboard covering all 20 scheduled snapshot tasks — 8 parent merges and 12 child attribute-count jobs — with 7 days of run history, duration, and error status in one screen. Daily health checks that previously required clicking through Snowsight task by task (15 to 20 minutes every morning) now take under 60 seconds.

One practical lesson from this build: the first version of the dashboard fired 20-plus queries on page load and never finished rendering. A dashboard nobody opens isn't a dashboard — it is a liability disguised as a deliverable. We rebuilt it to load instantly with on-demand data fetching. On a normal week, over 95% of parent tasks succeed on their scheduled cron slot. The team can see that in seconds.

A second validation workstream ran alongside this. The pipeline supports two ways of handling new source attributes. In V1, this is handled by hand: when new attributes appear in the source, an engineer hardcodes them into the stored procedure before the next run. V2 does the same job dynamically — at runtime, the script compares source against target, identifies any attributes that have appeared since the last run, and adds them automatically.

V2 removes the manual step, but it is not universally safer. When business names don't map cleanly between source and target, the dynamic script fails. So, the two approaches coexist, and the choice is made per entity based on how predictable the source schema is. Before we could trust V2 on an entity, we validated its output against V1 column by column—hundreds of schema columns, full key coverage, and every value difference accounted for.

When Timeouts Say Success and Load Nothing

Long-running merges are expected at enterprise data volumes. What is not acceptable is a task that reports success while loading zero rows.

We found a failure class where task-level and session-level timeouts were misaligned. A task would reach the session limit, stop processing, and report success. The target table had no new rows. A green checkmark that meant nothing.

The fix was to set timeouts deliberately by workload class rather than inheriting defaults. Daily merge tasks now run with a 24-hour task timeout. Bootstrap/backfill tasks, which process far larger volumes in a single pass, are set to seven days — the maximum Snowflake permits. A tighter ten-hour ceiling applies mainly to ALTER and schema-evolution tasks, where a long run signals a problem rather than a heavy load.

Setting the task timeout alone is not enough. Snowflake enforces whichever is lower — the task timeout or the session statement timeout — so a generous task timeout gets silently overridden by a tighter session limit inside the procedure. That was exactly our misalignment. We aligned the session timeouts inside the procedures, then added row-count validation after long runs instead of relying on the status flag alone. That step catches the silent failures before they propagate to AEP and Marketo.

For one task failing against a backlog of hundreds of millions of stream records, the structural fix (batch processing) is on the roadmap. The immediate fix was a carefully staged timeout extension that kept production running while we designed the proper solution. Pipeline uptime went from failing daily to running clean for multiple consecutive weeks.

Alerts the Team Will Read

Alert fatigue is a solved problem in theory and an unsolved one in practice. The pattern is consistent: alerts fire too frequently, go to a channel nobody monitors, and describe conditions that are within normal tolerance. The team mutes the channel. The pipeline can degrade, and nobody notices.

We have specified and built nine targeted alerts covering stopped and suspended tasks, failing tasks, long-running jobs, stored procedure failures, and audit stream backlog above 2 million events. That backlog threshold is deliberately set at 50% of the consumption table limit — meaning the team is warned on the audit side before the problem reaches the dispatch path that clients experience.

The target state: suspended tasks caught within 10 minutes, backlog flagged before it affects outbound data to AEP and Marketo, failing merges surfaced before the morning standup has to ask whether the load ran.

What Snowflake Data Pipeline Observability Delivers in Practice

The pipeline was functionally built before we engaged. What we added was the operational layer that makes it enterprise-grade:

20+ bootstrap procedures covering core, Desktop, Workforce, DxGy, and Capital Offer snapshot entities — full rebuild from audit tables without touching stream offsets
Zero stream-offset incidents after bootstrap procedures landed
Post-load verification in under 2 minutes — down from 20 minutes of manual SQL
Centralised task health view for core snapshot merge DAG (8 parent + 12 child tasks), replacing manual Snowsight checks
95%+ parent task success rate on scheduled slots across a normal week
V1 to V2 parity validated across hundreds of schema columns, 100% key coverage, with value differences under 5% and each one explained
9 operational alerts specified and built — covering stopped, suspended, and failing tasks, long-running jobs, stored procedure failures, and audit-stream backlog (staleness-based, with per-system volume ceilings derived from 30-day production analysis); rollout to a dedicated channel pending

That is what Snowflake data pipeline observability looks like in production — the difference between a pipeline that passes a demo and one that holds up under real operational conditions.

Build a Pipeline Your Clients Can Trust

This engagement was delivered by our Digital Business Services team, which brings together the disciplines that turn a working pipeline into an operationally dependable one:

Integrated Engineering — pipeline design, stored procedures, bootstrap and backfill tooling across enterprise data platforms
Infrastructure & Cloud — Snowflake and cloud platform operations, task orchestration, performance and cost optimisation
QE and Automation — automated validation, parity testing, monitoring dashboards and alerting
Enterprise Services — martech and customer data platform integration, including Adobe Experience Platform and Marketo
AI/ML Engineering — anomaly detection, predictive alerting and intelligent data quality on top of the observability layer

If your pipeline works but you would not yet stake a client relationship on it, we should talk. Connect with our experts at reachus@covalensedigital.com or fill out a quick contact form, and we will get back to you.

Author

$Anju Gulati-Author$

Balaji Vijaykumar, Data Engineer
Balaji specialises in Snowflake and modern cloud data platforms. He focuses on building scalable, reliable, and production-ready data solutions with a strong emphasis on data engineering, automation, and observability.

Related Blogs

Digital BSS: The Cornerstone of Telecom Evolution in the 5G Era

20 May 2025