Templates

Nightly ETL Pipeline

Every night, this workflow pulls from every API source, cleans and validates records before they touch the warehouse, loads them as one governed write, and sends a Slack alert the moment anything actually breaks. No cron job, no shell script, no dashboard-shaped surprise at 9am.

10 minutes setupUpdated 2026-09-16

What this is

Most nightly data pipelines are a cron job and a shell script held together by whoever set it up two years ago. When one API call fails partway through, the load goes in partial and silently wrong, and nobody finds out until someone notices the dashboard looks off. Debugging means SSHing into a box and reading through log files by hand.

This template replaces that with one governed workflow. It is fully deterministic, no model, no judgment call, just the same pull, clean, and load sequence applied every night. Every source is fetched in parallel so a slow API doesn't hold up the rest of the run. A failed fetch retries automatically before anyone has to look at it. Records that fail validation are flagged, not silently dropped or silently loaded bad. And if anything really does go wrong, your team hears about it in Slack that night, not from a bad dashboard the next morning.

How it runs

The workflow is invoked by an Unmeshed Scheduled Job, nightly, or whatever cron cadence fits your data, with no manual trigger required. Scheduling, retries at the run level, start/end dates, and pause/skip/restart controls all live in the Scheduled Jobs feature, not in the workflow body, so you get a timeline view and P50/P90/P95 execution metrics for free without building any of that yourself. Every run gets a run ID before anything else happens.

Three API sources are fetched in parallel rather than one after another. Each fetch retries automatically on a transient failure; if a source is still down after retries, that branch is marked failed but the other sources keep going; the run does not stop because one API had a bad night.

Once all three branches finish, a JavaScript step merges and normalizes the results into one shape, and validates each record. Records that fail validation are flagged with a reason instead of being dropped or loaded as-is. This step also produces the final counts: how many records came in clean, how many failed, and whether any source itself errored out.

Clean records are always loaded into the warehouse as a single batched upsert, one round trip, not one write per row, and idempotent, so a rerun of the same night's job updates existing rows instead of failing on a duplicate key. It runs regardless of whether some records failed validation, because clean records always deserve to land. A Switch then checks whether the run had any failures. If it did, the team gets a Slack alert with the run ID, the failure counts, and the source errors, sent that night. If the run was clean, nothing fires, no noise for a normal night. Either way, the run is marked complete and logged.

The steps

  • NOOP (init_run): Captures the run ID and start time before anything else happens. The trigger itself is an Unmeshed Scheduled Job (cron-based) invoking this workflow, there's no schedule logic inside the workflow.
  • PARALLEL (extract_parallel): Fetches from all three API sources at the same time. Lenient mode, one source failing doesn't fail the other branches.
  • HTTP (fetch_api_source_1 / 2 / 3): One GET per source, retried automatically on failure, marked optional so a source that's still down after retries doesn't take down the run.
  • JavaScript (merge_and_validate): Normalizes every source into one record shape, flags invalid records with a reason instead of dropping them, and produces the clean/failed counts and any source-level errors.
  • INTEGRATION (sql_postgres_1 / load_warehouse): A single batched upsert of every clean record into the warehouse via json_to_recordset, not a call per row. Runs unconditionally so good data always lands.
  • Switch (switch_alert): Checks whether the run had any failures, bad records, source errors, or both.
  • INTEGRATION (slack_alert_failure): Sends the team a Slack message with the run ID, failure counts, and source errors. Only fires when something actually went wrong.
  • NOOP (mark_run_complete): Marks the run complete either way and logs the final counts, so every night's run is readable after the fact with no SSH required.

Design notes

Nothing in this workflow makes a judgment call, there's no AI step and no discretionary branching. The only decision point is the Switch that checks whether the run had failures, and that check is a straightforward boolean read off merge_and_validate's output, not an inference.

Partial failure is visible, not silent. A source that fails after retries doesn't take the whole run down, and it doesn't get quietly absorbed either, it shows up in the run's failure count and, if the Slack alert fires, in the message itself.

The warehouse load always runs. It doesn't wait on the alert Switch and it isn't skipped when some records fail validation, the clean records still get written, and only the bad ones are held back. That's deliberate: a partial-but-flagged load is far better than a good night's data sitting in limbo because one record downstream was malformed. There's a single load step, not one per validation outcome, a clean run and a run-with-failures both load the same clean records the same way, so splitting the load into two identical steps would just be duplication with no behavioral difference.

The load is idempotent, not just batched. It's an upsert keyed on (source_name, record_id), so rerunning the same night's job, whether by hand or because the Scheduled Job retried, updates existing rows instead of throwing a duplicate-key error.

The load is one batched write, not a loop. Looping a single-row insert per record would mean one round trip per record and no way to reason about the load as a single unit of work. A batched json_to_recordset upsert keeps it to one call regardless of how many records came in that night.

Setup

  1. Connect your three (or however many) API sources, replace the placeholder fetch URLs with your real endpoints, and add authentication headers if the sources require them.
  2. Provision a Postgres-compatible warehouse table (etl_records by default) with columns for run ID, source name, record ID, title, raw record as JSON, and a loaded_at timestamp. Add a unique constraint on (source_name, record_id) so the upsert has something to key on. DDL is included with the template.
  3. Connect your Postgres/warehouse integration (the template ships configured for a Neon connection named neon-db, point it at your own instance and connection name).
  4. Configure a Slack connection and set the channel the failure alert should post to.
  5. Create an Unmeshed Scheduled Job pointing at this workflow, and set its cron cadence, nightly by default, but any cadence works. This also gives you retry policy, start/end dates, and pause/skip/restart controls at the run level, plus a timeline view and P50/P90/P95 metrics, all without touching the workflow itself.
  6. If your sources have different or larger response shapes than the demo, adjust the field mapping in merge_and_validate to match.
  7. If you've configured a named error policy in your Unmeshed org (e.g. for the HTTP fetch retries), confirm the retry-3x reference on the fetch steps matches a policy that actually exists, an unconfigured policy name fails the step with a policy-not-found error layered on top of whatever the real failure was.

Known limitations of this version

  • Validation only checks that each record has an ID; it doesn't yet check field types, ranges, or cross-source duplicates.
  • Fetch retries are capped by the configured error policy; a source that's down for the whole retry window is skipped for that run rather than escalated on its own.
  • Failed records aren't quarantined anywhere, they're counted and named in the Slack alert, but not written to a side table. The load step doesn't currently branch on the failure outcome; it writes the same clean records either way.

When to use it

  • You run the same pull-clean-load sequence every night (or on any fixed cadence) against a handful of API sources.
  • You want a slow or failing source to retry and get flagged, not silently take down the whole run.
  • You want bad records caught and reported, not silently dropped or silently loaded wrong.
  • You want to know the moment a run actually breaks, not the next time someone happens to check a report.

Build your own nightly pipeline

Start here, then point it at your real API sources, your warehouse, and your Slack channel. It runs every night without anyone babysitting a cron job.