# Pipeline Orchestration Analysis: Snowflake Tasks vs. Kestra **Prepared by:** Rajeev Roy **Date:** June 27, 2026 **Context:** Post-incident review of IN-17505 / Jun 26 timeout incident; SDLC improvement exploration --- ## Background The Insights team's priority playlist pipeline is one of our most critical data paths. It powers playlist placement data for all artists on the platform — ingesting CDC events from Chartmetric's Snowflake data share, staging them, and merging into the placements and metadata tables that feed artist-facing dashboards. Today this pipeline is implemented using **Snowflake native tasks and streams**. While this works, the Jun 26 2026 incident exposed a set of operational and SDLC limitations that we have been working around rather than solving. This document analyses those limitations, evaluates Kestra as an alternative orchestration layer, and makes a recommendation. --- ## 1. Current Architecture: Snowflake Tasks and Streams ### How it works ``` Chartmetric Data Share │ ▼ SPOTIFY_PRIORITY_PLAYLIST_EVENTS (Snowflake stream, CDC) │ ▼ [every 1 min — CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_EVENTS] SPOTIFY_PRIORITY_PLAYLIST_EVENT_STAGE (stage table) │ ▼ [child task — UPSERT_PLAYLISTS_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC] PLAYLISTS_PRIORITY_PLACEMENTS_BY_PARTICIPANT_ISRC_PLAYLIST_PUBLIC │ ▼ [child task — CAPTURE_SPOTIFY_PRIORITY_PLAYLIST_PROCESSING_LOG] SPOTIFY_PRIORITY_PLAYLIST_PROCESSING_LOG (Separate graph) PRIORITY_PLAYLIST_METADATA_UPDATE_STREAM │ ▼ [UPDATE_SPOTIFY_PRIORITY_PLAYLIST_METADATA] PRIORITY_PLAYLIST_METADATA ``` Tasks run on schedule (every 1 minute for the root CAPTURE task). Each child task is gated by `SYSTEM$STREAM_HAS_DATA()` — if the stream is empty the task no-ops. The entire graph is defined in Liquibase DDL changesets in the `database` repo and deployed through the standard PR process. --- ## 2. Problems with the Current Approach ### 2.1 Operational: No Observability **Problem:** Snowflake task execution history is a raw table query (`TASK_HISTORY()`). There is no dashboard, no timeline view, no easy way to see which tasks ran, when, how long they took, or how many rows they processed — without writing SQL. When the Jun 26 incident happened, diagnosing it required manually querying `INFORMATION_SCHEMA.TASK_HISTORY()`, cross-referencing timestamps, and calculating row counts from a backup table. There was no alert, no log, no automatic notification that 265 million rows had been staged. **Impact:** Incidents take longer to detect and diagnose. On-call engineers need Snowflake access and SQL knowledge just to answer "is the pipeline running?" --- ### 2.2 Operational: No Batch Size Guard **Problem:** Snowflake tasks are binary — they run or they don't. There is no mechanism to inspect a result mid-flight and make a decision (e.g. "if this batch is too large, stop and alert before running the expensive MERGE"). **What happened on Jun 26:** Chartmetric bulk-corrected timezone metadata on `L_SPOTIFY_PLAYLIST_SONY`, generating **265,244,349 CDC UPDATE events** in a single batch. The CAPTURE task staged all of them. The UPSERT MERGE then ran against 265M rows and hit the warehouse statement timeout after 60 minutes. The task auto-suspended with `SUSPENDED_DUE_TO_ERRORS`. No one was notified until a downstream report broke. **Impact:** A single external data quality event from a vendor (Chartmetric) can silently take down the pipeline for hours. --- ### 2.3 Operational: Error Recovery Is Manual and Order-Dependent **Problem:** When a child task in the Snowflake graph fails repeatedly, Snowflake auto-suspends it with `SUSPENDED_DUE_TO_ERRORS`. Recovery requires: 1. Suspending the root CAPTURE task (not the failed child — the root) 2. Resuming the failed child task 3. Resuming the root task The order matters and is not documented anywhere in the codebase. On Jun 26, the first recovery attempt failed because the tasks were resumed in the wrong order, adding 20+ minutes to the incident. **Impact:** Recovery is error-prone, undocumented, and requires institutional knowledge. --- ### 2.4 SDLC: SQL Lives Inside Snowflake DDL Files **Problem:** All task SQL (the CAPTURE INSERT, the MERGE CTEs, the processing log INSERT) is embedded inside Liquibase DDL changeset files in the `database` repo. This means: - You cannot run or test a SQL change without deploying it through Liquibase - There is no local development story — you must work against QA or PROD - Diffs in PRs show the entire DDL statement changing, not just the SQL that changed - There is no separation between "infrastructure" (the task definition) and "logic" (the SQL it runs) **Impact:** Even simple SQL fixes require a full PR/deploy cycle through the database repo, slowing iteration speed. --- ### 2.5 SDLC: No QA/PROD Environment Isolation at the Orchestration Level **Problem:** QA and PROD are separate Snowflake task graphs. They must be kept in sync manually. When a task definition changes, the Liquibase changeset runs against both environments — but the task graphs themselves are duplicated, not parameterised. If the QA task graph gets out of sync with PROD (which happens during incidents when hotfixes go straight to PROD), it is difficult to reconcile. **Impact:** QA is unreliable as a testing environment; hotfixes bypass QA entirely because it's too slow to redeploy both. --- ### 2.6 SDLC: No Per-Task Timeout **Problem:** The only timeout available is the Snowflake warehouse statement timeout, which applies globally. Setting it lower to protect the MERGE would also affect every other query running on that warehouse. On Jun 26, the timeout was 3,600 seconds (60 minutes). The MERGE ran for the full 60 minutes before failing. A per-task timeout of 30 minutes would have failed faster and triggered alerts sooner. **Impact:** Slow queries hold the warehouse for the full global timeout, delaying all other work scheduled on that warehouse. --- ## 3. Kestra as an Alternative ### What is Kestra? Kestra is an open-source, YAML-based workflow orchestrator. Flows are defined as YAML files, executed by a stateless server backed by PostgreSQL, and managed through a web UI. It supports hundreds of plugins including a native Snowflake JDBC plugin. It is conceptually similar to Airflow or Prefect but with a simpler YAML-first authoring model and a much lighter infrastructure footprint — a single Docker image + PostgreSQL is sufficient for local development and small production deployments. ### How Kestra maps to the current pipeline | Snowflake concept | Kestra equivalent | |---|---| | Snowflake task (scheduled, `SYSTEM$STREAM_HAS_DATA` gate) | Flow with Schedule trigger + `If` task checking stream | | Child task chain (CAPTURE → UPSERT → LOG) | Sequential tasks within a single flow | | `SUSPENDED_DUE_TO_ERRORS` | Flow moves to FAILED state; manual replay from UI or API | | Task resume order | Not required — flow retry is atomic, not graph-node-level | | Warehouse statement timeout | `timeout: PT30M` on the individual task | --- ## 4. How Kestra Addresses Each Problem ### 4.1 Observability: Full Execution Timeline Every execution is recorded with a per-task timeline showing start time, duration, status, logs, and output values (including row counts). No SQL required — visible in the Kestra UI at `http://localhost:8080`. An ops engineer can answer "did the pipeline run, and how many rows did it process?" by opening a browser tab. ### 4.2 Batch Size Guard Kestra flows can inspect the output of one task and branch before the next task runs. The `check_batch_size` task in this project reads the row count from the CAPTURE step. If it exceeds 1,000,000 rows, it: 1. Posts a Slack alert with the exact count and an execution link 2. Stops the flow before the MERGE runs This is exactly the guard that would have contained the Jun 26 incident at the CAPTURE step instead of letting it reach the MERGE. ### 4.3 Error Recovery: Replay, Not Graph Surgery When a Kestra flow fails, recovery is: 1. Fix the underlying issue (e.g. delete the bulk-refresh rows) 2. Click **Replay** in the UI (or `POST /api/v1/executions/{id}/replay`) There is no task graph resume order to remember. The flow re-runs from the failed task. ### 4.4 SQL as Plain Files In this project, SQL lives in `flows/sql/*.sql` — plain text files, version-controlled separately from the flow definition. The flow reads them at runtime using `{{ read('sql/filename.sql') }}`. Benefits: - SQL changes produce clean diffs (no DDL noise) - SQL files can be tested directly against Snowflake without deploying a flow change - Logic and infrastructure are decoupled ### 4.5 Single Flow, Environment-Switched by Variable `SNOWFLAKE_SCHEMA` in `.env` controls whether the flow runs against QA or PROD. No duplicate flow definitions, no separate task graphs to keep in sync. Promoting a tested QA flow to PROD is a one-line change in `.env` (or a CI environment variable). No Liquibase changeset required. ### 4.6 Per-Task Timeout ```yaml - id: upsert_placements type: io.kestra.plugin.jdbc.snowflake.Query timeout: PT30M # fails this task after 30 min, regardless of warehouse timeout ``` The MERGE gets a 30-minute ceiling. Other queries on the same warehouse are unaffected. --- ## 5. Trade-offs ### What we gain - Visibility and alertability without writing SQL - Batch size guard as a first-class pipeline feature - Simpler error recovery - Cleaner SDLC: SQL in files, single flow definition for all environments - Per-task timeouts as a safety circuit ### What we give up / what is harder | Concern | Detail | |---|---| | **New infrastructure to operate** | Kestra requires a PostgreSQL instance and a running server. In production this means a container (ECS/Kubernetes) and an RDS instance. Snowflake tasks have zero infrastructure overhead. | | **Snowflake stream ownership** | Snowflake streams are still defined in the `database` repo; Kestra only replaces the task layer. The stream → stage → merge pattern is unchanged. | | **Plugin maturity** | Kestra's Snowflake JDBC plugin is stable but less battle-tested than Airflow's Snowflake provider. Edge cases (e.g. multi-statement SQL, transaction handling) need validation. | | **Team familiarity** | The team knows Snowflake SQL deeply. Kestra YAML is a new syntax to learn, though it is straightforward. | | **Cost** | Kestra OSS is free. Production hosting adds infrastructure cost (~$50-100/month for a small RDS + ECS task). Snowflake tasks are included in the compute cost already being paid. | ### When Snowflake tasks are still the right choice - Pipelines that run entirely inside Snowflake with no need for external alerting or branching - Simple, stable, low-visibility pipelines where the overhead of a separate orchestrator isn't justified - Teams without the capacity to operate additional infrastructure --- ## 6. Recommendation **Adopt Kestra for the priority playlist pipeline as a pilot.** The Jun 26 incident was caused by the absence of a batch size guard — a feature that Snowflake tasks cannot implement natively. The same architectural gap exists today; another Chartmetric bulk refresh will cause the same timeout unless we add an external check. Kestra's batch guard, per-task timeout, and Slack alerting address the three root causes of the incident directly. The observability improvement alone (execution history, row counts, one-click replay) would significantly reduce mean time to detect and recover from future incidents. **Proposed pilot scope:** 1. Deploy Kestra to the Insights ECS cluster (alongside existing services) 2. Migrate the priority playlist pipeline (CAPTURE + UPSERT + METADATA) to Kestra flows 3. Retain the Snowflake task graph in a suspended state for 30 days as a rollback option 4. Evaluate after 30 days: if incident rate and MTTR improve, extend to other pipelines **Not proposed:** - Migrating all Snowflake tasks immediately (too broad, too risky) - Replacing the Snowflake stream/table architecture (orthogonal concern) --- ## 7. Reference: Local Kestra Project A working local replica of the pipeline is available at: ``` /Users/rajeev/projects/kestra-playlist-pipeline/ ``` To run it locally (requires Docker Desktop and Snowflake QA credentials): ```bash cp .env.example .env # fill in SNOWFLAKE_PASSWORD docker compose up -d open http://localhost:8080 ``` All three SQL statements (capture, upsert, metadata update) run against `FACTS.QA` by default. The batch guard and Slack alerting are wired in — set `SLACK_WEBHOOK_URL` in `.env` to receive alerts. --- *This analysis was prepared following the Jun 26 2026 pipeline incident (IN-17505). The local Kestra project was built as a proof-of-concept to validate the approach before proposing it to the team.*