Scheduled ETL Workflow Blueprint (n8n) â€” Template

Turn a requirements brief into a production-ready, node-by-node n8n ETL spec with mappings, validation, retries, migration handling, and tests.

What You'll Get

Full n8n node-by-node ordered workflow spec with exact node names, node types, credential placeholders, parameters, expressions, and sample I/O record JSON.

Field mapping tables: source â†’ intermediate â†’ target mappings with explicit transformation code or n8n expression syntax, data types, nullability, and validation rules.

Retry, timeout and backoff policies per node, global workflow timeout, idempotency key algorithm, dedupe/merge algorithm, dead-letter handling and retention policy.

Error-handling branches for validation failures, transient errors, auth failures and schema drift, with alert payloads to a failure channel.

Three representative test payloads (success, validation-fail, schema-migration) and expected outcomes plus instructions to run them in n8n manual tests.

Compact deployment checklist and minimal runbook entries for common failures (auth, rate-limit, schema drift, partial loads).

How to Use

1Provide a concise requirements brief with these critical inputs: source_app, warehouse_target, auth_method (how credentials are supplied), source_schema (JSON Schema or sample records), transform_rules (explicit functions or examples), unique key(s) for idempotency, failure_alert_channel, and optional cron (default: 0 2 * * * UTC).

2Run this template in an LLM or pass to an n8n implementer. If any critical inputs are missing, the template will prompt for them before producing the final spec.

3Replace credential placeholders and environment variables in the generated spec with your secure n8n credential names, import or paste node configs into n8n, then execute manual test runs using provided test payloads.

4Iterate: validate outputs against your target schema, confirm dedupe behavior on sample duplicates, and adjust batch sizing/pagination based on observed API rate limits.

Supply exact schemas and unique keys up front

Provide both the source JSON Schema (or representative sample records) and the target warehouse schema (column names/types). Explicitly list unique key fields used for idempotency (e.g., external_id, email). Example: {"source_schema": {"id": "string","email": "string","amount": "number"}, "unique_keys": ["id"]}. Without these the template must pause and request them, which delays delivery.

Works Best With

Best used for converting verbal or written ETL/integration briefs into production-ready n8n/Zapier/Make workflow blueprints when you can provide:

Source app name and API type (e.g., HubSpot CRM v3, Stripe REST v1) and credential pattern (OAuth2/API key).

Target warehouse details and required table(s) schema (e.g., Snowflake table DDL or BigQuery dataset/table).

Transform rules or example mappings (field-level rules, derived fields, currency/unit conversions).

Failure/alerting channel (Slack webhook, PagerDuty integration) and dead-letter storage path (S3/GCS).

Primary unique keys for idempotency/merge; if missing, the prompt will ask before producing final SQL/merge logic.

Prompt Template

# Automation & Workflow Blueprints (n8n/Zapier/Make) (use EXACT section order)

## Goal
- Daily scheduled ETL: export data from {{source_app}}, apply schema validation and transformations defined by {{transform_rules}}, handle schema migrations, enforce idempotency/dedupe, and load into {{warehouse_target}} with alerting to {{failure_alert_channel}} on failures. Provide a node-by-node n8n-ready workflow spec, mapping tables, retries/timeouts, dedupe strategy, and test payloads.

## Inputs
- Trigger: daily scheduled run (cron: daily at 02:00 UTC by default; prompt will accept a different cron if provided).
- Apps and integrations: {{source_app}} â†’ intermediate transforms â†’ {{warehouse_target}}.
- Auth assumptions: credentials for {{source_app}} and {{warehouse_target}} provided via {{auth_method}}-compatible credentials in n8n.
- Schemas/payloads: source schema = {{source_schema}}. Target schema must be inferred or provided; if missing, the prompt will request it.
- Failure policy: send alerts to {{failure_alert_channel}} and follow retry/dead-letter rules described below.
- Idempotency key: design uses a deterministic idempotency key per-record based on configured unique keys; dedupe rule described below.
- Dedupe rule: primary unique keys must be identified; if not provided, the prompt will request them before generating merge logic.

If any critical input above is missing, the prompt will ask for it before producing the workflow spec.

## Prompt
You are an expert n8n automation architect. Produce a complete, production-ready n8n workflow blueprint for a scheduled daily ETL that exports data from {{source_app}}, transforms and validates records according to {{transform_rules}} against the provided source schema {{source_schema}}, handles schema migrations, and loads into {{warehouse_target}} using credentials via {{auth_method}}. Send failure alerts to {{failure_alert_channel}}.

Requirements (must be satisfied in the output):
1. Provide a brief Overview summarizing intent, success criteria, and SLAs for the run.
2. Output an ordered Node list for n8n with exact node names and types. For each node include:
   - Node type (n8n node name), purpose, and placement in the flow.
   - Exact configuration settings (parameters), expressions where needed, credential names, and sample values.
   - Input/output data contract (example record JSON) and the precise field used as the idempotency key.
   - For API/webhook nodes, include the request method, full path, required headers, and example request/response payload schemas.
3. Provide Field mapping tables:
   - Source field â†’ intermediate field â†’ target field.
   - Data types, nullable rules, transformation logic (explicit functions or expression syntax).
   - Validation rules per field and actions on validation failure.
4. Include detailed Retries/timeouts + dedupe strategy:
   - Retry policy per node (max attempts, backoff strategy, intervals).
   - Overall workflow timeout and cancellation rules.
   - Idempotency key generation algorithm and dedupe merge algorithm with conflict resolution examples.
   - Dead-letter path: how failed records are stored and retried or archived.
5. Include Error handling rules and branching logic:
   - Branches for validation failures, transient upstream errors, authentication failures, and schema drift (migration path).
   - Fallback steps for partial failures (e.g., load partial batch, mark records for manual review).
   - Alerting rules to {{failure_alert_channel}} including minimal alert payload.
6. Provide Test payload(s):
   - At least three representative JSON test payloads: clean success case, validation-failure case, schema-migration case.
   - Expected result for each test and how to run them in unit tests or n8n manual executions.
7. Include Deployment & Run notes:
   - Required n8n credentials, recommended concurrency (batch) size, pagination handling, and monitoring metrics to surface.
8. Ask for any missing critical inputs (e.g., target schema, unique keys, rate limits) before producing the final spec if they were not provided.

Output formatting rules (must follow exactly):
- The output must be structured into the sections in this order: Overview; Node list (ordered); Field mapping table(s); Retries/timeouts + dedupe strategy; Test payload(s).
- Use concrete node configs and avoid abstract/generic nodes; do not use â€œmagic nodes.â€
- Show webhook schemas or API payloads when relevant (request/response examples).
- All transformations must be explicit (show code snippets or n8n expression syntax).
- End with a compact deploy checklist and minimal runbook entries for common failures.

Generate the full workflow now. If any critical input is missing from {{source_app}}, {{warehouse_target}}, {{auth_method}}, {{source_schema}}, {{transform_rules}}, or {{failure_alert_channel}}, list the missing items and prompt for them before producing the full spec.

## Output Format
Output:
1) Overview
2) Node list (ordered)
3) Field mapping table(s)
4) Retries/timeouts + dedupe strategy
5) Test payload(s)

Produce the workflow as n8n operator-grade instructions and configurations ready to be pasted into an LLM or used directly by an n8n implementer.

## Guardrails
- No â€œmagic nodesâ€; every transformation must show explicit expression code, JavaScript snippets, or built-in node parameters.
- Enforce idempotency: include deterministic idempotency key per record and a dedupe merge algorithm.
- Validate schemas before load; on schema drift propose a migration handler node that checkpoints and pauses ingestion.
- All network calls must include timeouts and retry rules with exponential backoff; transient failures must route to a retry branch.
- Provide a dead-letter storage location (e.g., S3/bucket or database table) and retention policy.
- Alerting must contain minimal reproducible context (workflow id, node name, sample failing record idempotency key, error message, timestamp).
- Log every batch run with run_id and metrics: processed, failed, retried, loaded_count.
- Do not assume infinite API quotas; include rate-limit handling and backoff logic.

## Examples
- Mini node list excerpt:
  1) Scheduler (n8n Cron) â€” cron: 0 2 * * * â€” outputs run_date.
  2) Source Fetch (HTTP Request or specific node for {{source_app}}) â€” paginated GET with page token handling; parameters: pageSize=1000; headers: Authorization from {{auth_method}} credentials.
  3) Validator (Function or JSON Schema node) â€” validate against {{source_schema}}; invalid records â†’ Validation Failure branch that writes to dead-letter + alert to {{failure_alert_channel}}.

Variables

Usage Notes

Not for

â€¢ Real-time streaming or event-driven architectures with sub-minute latency requirements.

â€¢ Very high-volume distributed ETL where a distributed job system (Spark/Beam) is required instead of n8n.

â€¢ Replacing formal legal/medical compliance assessmentsâ€”this template helps implement controls but is not a compliance audit.

Do not include

â€¢ Do not include raw credentials, API keys, or secrets in the requirements brief; use placeholder names (e.g., CRED_SALESFORCE_API).

â€¢ Avoid vague transformation descriptions like 'normalize' or 'clean' without examplesâ€”provide exact transform rules or sample inputs/expected outputs.

â€¢ Do not assume target schema when not provided; the template will request it or mark required fields as assumptions.

Scheduled ETL Workflow Blueprint (n8n) â€” Template

Generate a complete, implementable n8n-ready daily scheduled ETL blueprint: ordered nodes with exact configs, field mapping tables, auth and retry policies, idempotency/dedupe algorithm, schema-migration handling, alerting, and test payloads.

Turn this into a working asset in minutes.

Import into Workspace to auto-fill variables, adapt output style, and iterate with OctiAI's prompt editor.

Auto-fill & personalize from your inputs

AI-assisted edits: tone, structure, constraints

Attach context so outputs stay consistent

Iterate and save versions

What you'll get

Output format

Markdown

Best used when

When working on template or detailed.

Avoid when

Real-time streaming or event-driven architectures with sub-minute latency requirements.

Made for

What to fill in

Use the guidance below to complete each placeholder with the right level of detail.

Source App