Building Data Pipelines Using AI Prompt Workflows

A reliable data pipeline is more boring than a model and ten times as valuable. AI can scaffold an entire pipeline — ingestion, transformation, validation, orchestration — in minutes. The trick is briefing it like a senior data engineer: with the schema, the failure modes, the SLA, and the orchestrator in mind. This topic shows you how.

1. Introduction

Most data teams spend more time keeping pipelines healthy than building new ones. Every ingestion source has its own quirks, every transformation step has edge cases, and every downstream consumer has expectations that drift over time. AI is well suited to scaffolding the boilerplate — connectors, retry logic, schema validation, idempotent upserts, orchestration DAGs — and to upgrading existing pipelines with tests and observability. This tutorial covers the prompt patterns for the four pipeline stages and the major orchestration tools.

2. The Concept Explained

A modern data pipeline has four stages: ingest (pull from source systems and land in staging), transform (clean, model, and aggregate), validate (assert business rules and data contracts), and orchestrate (schedule, retry, alert). Each stage has its own prompt shape. Ingestion prompts focus on connectors and rate limits. Transformation prompts mirror the SQL and Pandas patterns from earlier topics. Validation prompts demand explicit assertions. Orchestration prompts demand the DAG shape, the schedule, and the failure behaviour.

A useful analogy: a pipeline is a relay race. Each runner (stage) has one job, hands off cleanly, and must not break when the previous runner slips. AI is a great coach for any one runner — but you, the data scientist, decide the baton-pass rules.

3. The Problem Without This Technique

Build me a data pipeline for our customer data.

Act as a senior data engineer designing a daily pipeline
in Airflow 2.8 with Python tasks and dbt models.

Sources:
- Stripe REST API (charges, customers, subscriptions),
  ~120k events/day, rate-limit 100 req/sec.
- Postgres replica `app_db` (users, plans tables).

Destination: BigQuery dataset `analytics_raw`
(landing) and `analytics_mart` (curated).

SLA: data fresh in mart by 07:00 UTC daily.
Failure behaviour: retry 3x with exponential backoff,
then alert #data-alerts on Slack.

Build:
1. An Airflow DAG (file: dags/customers_daily.py)
   with tasks: ingest_stripe -> ingest_postgres
   -> run_dbt -> assert_contracts -> notify.
2. Idempotent merge into BigQuery using
   MERGE on natural keys.
3. dbt models for users_dim, subscriptions_fact.
4. Contract tests in dbt: not_null on PKs,
   unique on PKs, accepted_values for plan_type.
5. Slack alert task using SlackAPIPostOperator.

Return: file tree, then code per file with brief
docstrings and inline comments explaining the
business choices.

4. The Solution

The pattern is: orchestrator + version → sources → destination → SLA + failure behaviour → DAG shape → file tree. The two pieces beginners forget are the SLA and the failure behaviour. Without an SLA, AI has no way to choose between cheap-and-slow versus expensive-and-fast. Without a failure behaviour, you get pipelines that fail silently.

For dbt-first teams, swap the Airflow scaffold for a dbt project structure (models/staging, models/marts, tests/) and ask AI to write the YAML schema files alongside the SQL models. For Prefect or Dagster, name the framework version and the deployment style — local script, work pool, Cloud — so the generated code matches your runtime.

5. Step-by-Step Breakdown

1. Name the orchestrator and version. "Airflow 2.8", "Prefect 2", "dbt 1.7". Version differences in task syntax are larger than they look.
2. List sources with shape and rate limits. Rows per day, payload size, API rate limits. The AI will pick async-vs-sync, batch sizes, and retry strategies accordingly.
3. State the destination and grain. Warehouse, dataset, table name, primary key, write mode (append, merge, replace).
4. Define the SLA. Time-of-day target, acceptable lateness, weekend behaviour. Each clause shapes the schedule and the retry policy.
5. Specify failure behaviour. Retries, backoff, alert channel, escalation. "Fail silently" is never the right default.
6. Demand contract tests. Not-null, unique, accepted-values, row-count bounds. Each test prevents a class of silent breakage.
7. Ask for the file tree first, then files. "List the files you will create. Then write each one in order." This avoids monolithic generated blobs that are hard to review.

Tip: After the AI generates a pipeline, prompt:

List every assumption you made about the source data or the destination warehouse. For each, suggest a test that would catch a violation.

You get a free QA checklist.

6. Practice Exercises

Design a dbt project for a SaaS analytics warehouse with stage / intermediate / mart layers. Provide the folder structure, sample SQL for a users_dim model with SCD Type 2 history, and the YAML schema with not_null, unique, and accepted_values tests.

7. Key Takeaways