Prompt Engineering Tutorial for Data Engineers

What Prompt Engineering Actually Is

Prompt engineering is the practice of designing input instructions that reliably guide an AI model toward a specific output — the right structure, reasoning depth, format, and accuracy — rather than hoping the model infers your intent from vague or incomplete instructions.

For data engineers specifically, this matters because the same underlying model produces radically different SQL quality depending on whether you tell it to “write a query” versus providing the table schema, a worked example, a reasoning instruction, and a JSON output constraint. The model’s knowledge of SQL is constant — what changes is how much of that knowledge is directed toward your actual problem.

Role Assignment — Set the Expertise Level First

Role assignment is the cheapest technique with the highest baseline improvement: one sentence at the start of a prompt that tells the model what kind of expert it is operating as. This shifts the model’s response distribution toward expert-level vocabulary, reasoning depth, and awareness of edge cases in that domain.

You are a senior data engineer with 10 years of experience building
production pipelines on Microsoft Fabric and Azure Synapse.
You write T-SQL that is efficient, well-commented, and handles
NULL values and edge cases explicitly.

Task: Write a query that calculates 30-day rolling average revenue
per customer, excluding customers with fewer than 5 transactions
in the window.

Without the role preamble, the same task produces working SQL that often misses NULL handling, uses suboptimal window function patterns, and skips edge case coverage. With it, the model defaults to production-grade habits.

Chain-of-Thought — Force Reasoning Before the Answer

Chain-of-thought (CoT) prompting instructs the model to reason step by step before producing a final answer. Without this instruction, language models tend to jump directly to an output — which works for simple tasks but fails on anything requiring multi-step logic. With it, the model decomposes the problem first, and the final answer reflects that decomposition.

The simplest CoT trigger is three words appended to any instruction: “Think step by step.” For data engineering tasks, a more specific reasoning scaffold produces more reliable results:

You are a senior data engineer specializing in Power BI DAX.

Task: Write a DAX measure for year-over-year revenue growth percentage.

Before writing the DAX, reason through these steps:
1. What base measure does this depend on?
2. What date intelligence function is needed?
3. Is DIVIDE safer than the division operator here? Why?
4. What should this return when there is no prior year data?

Then write the final measure.

The model’s reasoning output is itself useful — it surfaces assumptions that would otherwise be hidden inside the final expression. If step 4 produces a wrong assumption (“return 0 when prior year is blank”) you can correct it before the DAX is written, rather than debugging a silently wrong measure later.

Few-Shot Prompting — Teach by Example

Few-shot prompting provides two to five worked examples inside the prompt before asking the model to handle a new case. The examples establish the expected input-output pattern — format, vocabulary, naming conventions, reasoning structure — and the model replicates that pattern for the new input without needing explicit rules about every constraint.

This is particularly effective for tasks with a consistent structure: SQL transformations following a house style, dbt model naming conventions, data quality rule generation, or Power BI measure patterns.

Convert each source column description into a dbt staging model
column entry with name, description, and data_tests.

Example 1:
Source: customer_id — unique integer identifier for each customer
Output:
  - name: customer_id
    description: "Surrogate primary key for the customer."
    data_tests:
      - unique
      - not_null

Example 2:
Source: order_status — varchar, one of: placed, shipped, delivered, cancelled
Output:
  - name: order_status
    description: "Current fulfilment status of the order."
    data_tests:
      - not_null
      - accepted_values:
          values: ['placed', 'shipped', 'delivered', 'cancelled']

Now convert this:
Source: total_amount_usd — decimal(10,2), revenue value, must be non-negative

Schema Grounding — Give the Model the Actual Table

Schema grounding means injecting the actual table and column definitions from your data model into the prompt. Without it, the model generates SQL against imagined column names — it will hallucinate plausible-sounding names that do not exist in your schema. With it, the model is constrained to columns that actually exist.

Use only the columns defined in the schema below. Do not reference
any column not explicitly listed.

Schema:
Table: fct_orders
  - order_id         INT       NOT NULL  -- Primary key
  - customer_id      INT       NOT NULL  -- FK to dim_customers
  - order_placed_at  TIMESTAMP NOT NULL  -- UTC order submission time
  - status           VARCHAR   NOT NULL  -- placed|shipped|delivered|cancelled
  - gross_revenue    DECIMAL(12,2)       -- Pre-refund order value USD
  - net_revenue      DECIMAL(12,2)       -- Post-refund order value USD

Table: dim_customers
  - customer_id      INT       NOT NULL  -- Primary key
  - customer_email   VARCHAR   NOT NULL
  - acquisition_date DATE      NOT NULL
  - country_code     CHAR(2)   NOT NULL

Task: Write a query showing monthly net revenue by country for the
last 12 complete months, excluding test orders (status = 'test'),
ordered by month descending then revenue descending.

The inline comments on each column carry the same information a good data dictionary would — business meaning, not just data type. This is the schema pattern that produces the most accurate SQL because the model can distinguish gross_revenue from net_revenue correctly based on the inline descriptions, rather than guessing from column names alone.

Structured Output — Constrain to Machine-Readable Format

Structured output prompting instructs the model to return its response in a specific machine-readable format — JSON, YAML, CSV, or XML — rather than natural language prose. This is required for any workflow where the model’s output feeds into another system: a data pipeline, an API call, a configuration generator, or an agent tool.

The most reliable pattern is to provide a JSON schema directly in the prompt alongside an instruction to return valid JSON only, with no additional text or markdown fences:

Extract the following fields from the pipeline log excerpt below
and return them as a valid JSON object matching this schema exactly.
Do not include any explanation, markdown, or text outside the JSON.

Schema:
{
  "pipeline_name": "string",
  "run_id": "string",
  "status": "success | failed | skipped",
  "duration_seconds": number,
  "rows_processed": number | null,
  "error_message": "string | null"
}

Log excerpt:
[2026-06-10 14:22:01] Pipeline: ingest_orders_daily | RunId: r-4892
Duration: 142s | Rows: 84,391 | Result: SUCCESS

Negative Constraints — Rule Out Unwanted Approaches

Negative constraints explicitly tell the model what not to do. This is distinct from just describing what you want — it rules out the specific failure modes the model defaults to when given a data task without constraints.

Negative constraints are most powerful when combined with schema grounding and role assignment — the role sets baseline habits, the schema constrains the data layer, and negative constraints close the specific gaps between model defaults and your team’s standards.

Prompting for Agent Tool Use

When building AI agents that use tools — querying a semantic model via the Power BI MCP server, executing dbt commands, or reading OneLake files — the prompt design shifts from “produce a good answer” to “decide which tool to call, with what arguments, in what order.”

The critical differences in agent tool prompts are boundary clarity and error handling:

You are a data analyst agent with access to three tools:
- execute_query(model_id, dax_query): Runs DAX against a semantic model
- get_model_info(model_id): Returns available tables and measures
- summarize_results(data, question): Summarizes query results in plain English

Rules:
1. Always call get_model_info before execute_query for any new model_id.
2. Never construct a DAX query that references a table or measure not
   returned by get_model_info.
3. If execute_query returns an error, report the error to the user
   and stop — do not retry with a modified query.
4. Return only the summarize_results output to the user, never raw JSON.

User question: What was total net revenue for Q1 2026 by country?
Semantic model ID: 7f4a2b91-...

The Combined Pattern — All Five Techniques in One Prompt

Each technique adds independent value. Combining them into a single prompt produces results that are qualitatively better than any single technique alone — the role sets habits, CoT forces reasoning, the example demonstrates the pattern, schema grounding prevents hallucination, and structured output makes the result usable downstream.

You are a senior analytics engineer at a retail company building
production dbt models on Microsoft Fabric Warehouse. You follow
the staging → intermediate → marts layer pattern and write
well-documented YAML with specific business-level descriptions.

Think step by step:
1. Identify the data type and business meaning of each column.
2. Determine which generic tests apply (unique, not_null, accepted_values, relationships).
3. Write a specific business description — not a placeholder.
4. Return valid YAML only. No markdown. No explanation outside the YAML.

Example:
Source: product_id INT NOT NULL (unique product identifier, FK to dim_products)
Output:
  - name: product_id
    description: "Surrogate key for the product. Foreign key to dim_products.product_id."
    data_tests:
      - not_null
      - relationships:
          to: ref('dim_products')
          field: product_id

Now generate the column YAML for all columns in this staging model:

Table: stg_returns
  - return_id         INT          NOT NULL  -- Unique return transaction identifier
  - order_id          INT          NOT NULL  -- FK to stg_orders
  - returned_at       TIMESTAMP    NOT NULL  -- UTC timestamp of return approval
  - reason_code       VARCHAR(20)  NOT NULL  -- One of: defective, wrong_item, changed_mind, other
  - refund_amount_usd DECIMAL(10,2)          -- Approved refund value, may be null if pending

This single prompt uses all five techniques: role (senior analytics engineer with specific patterns), CoT (4 reasoning steps), few-shot (one worked example), schema grounding (actual column definitions with inline comments), and structured output (YAML only, no markdown). The result is production-ready dbt YAML that requires minimal editing.

Common Prompt Engineering Mistakes in Data Work

FAQ — ↑ AI & Agents · Data Engineering Prompt Engineering Tutorial

Resources — Prompt Engineering for Data Engineers