Smart Orchestration: Multi-Agent Systems and Workflows

Overview

What is Smart Orchestration, and how do we apply it to multi-agent systems? If you have built data flows or data pipelines, you already understand the purpose of orchestration: executing a series of connected steps with gates or signals that determine whether to continue or halt the process. When we bring AI into orchestration, we need to combine that same deterministic control with an agent's ability to reason and act.

As organizations adopt generative AI, however, many teams jump straight into building solutions without process definitions or software architecture principles, falling into two critical anti-patterns:

• The Monolithic Agent Trap: Putting everything into one single agent or tool—much like putting an entire software project into a single function or class. This causes prompt overload (mixing instructions for every possible task into one prompt), tool confusion (attaching dozens of database, API, and custom logic functions to one agent, which triggers hallucinations from picking the wrong tool), governance and auditing difficulties (making it hard to debug or audit what went wrong), and high token costs and slow execution from repeatedly sending massive context to the Large Language Model (LLM).
• The Spaghetti Code Trap: Realizing that a single monolithic agent does not scale and creating multiple agents, but stitching them together with brittle conditional logic (if/else statements) or uncontrolled chat loops. Hardcoding if this happens, do that strips away the agent's power to reason and react, while unstructured agent chat loops lose deterministic execution.

Smart Orchestration resolves both traps by pairing focused, single-purpose Worker Agents with structured Workflow Graphs—anchoring autonomous AI execution within deterministic enterprise controls.

🚀 Featured Open Source Projects

Explore these curated resources to level up your engineering skills. If you find them helpful, a ⭐️ is much appreciated!

🤖 AI Agents (ADK)

Focus: LLM Patterns, Multi-Agent Workflows, and Worker Agents

🏗️ Data Engineering

Focus: Real-world ETL & MTA Turnstile Data

📖 Data Engineering Process Fundamentals

Focus: Architectural patterns, hands-on data pipelines, and MTA turnstile case studies

---

💡 Contribute: Found a bug or have a suggestion? Open an issue and be part of the open-source project.

🔗 Related Repository: AI Engineering Workflows

Explore the full implementation of the worker agents and orchestration workflows used in this session:
https://github.com/ozkary/ai-engineering/tree/main/adk/workflow_agent

YouTube Video

👍 Subscribe to the channel to get notified on new events!

📋 Presentation Agenda

• Agent Anatomy: The Single-Purpose Worker — Applying the Single Responsibility Principle: scoping one focused agent to one bounded task, lean context, and specific toolset.
• The Multi-Worker Advantage: Why Decouple? — Eliminating monolithic bottlenecks through cognitive division of labor, isolated database permissions, and zero-trust security boundaries.
• Workflow Orchestration: Moving Beyond Agent Chat — Replacing open-ended conversational loops with deterministic state graphs that guarantee execution order, typed state handoffs, and circuit-breaker fault handling.
• Core Enterprise Workflow Patterns — Architectural breakdown of four production graph topologies: Sequential chains, Parallel fan-out/fan-in, Evaluator-Optimizer loops, and Dynamic intent routing.
• Live Walkthrough: Agent Workflows in Action — End-to-end execution of a multi-agent workflow querying a data warehouse and delivering analytical results.
• Key Takeaways — Summary of the core principles and Q&A points discussed in the session.

---

Now that we understand the problems with monolithic agents and spaghetti conditional logic, how do we start designing multi-agent orchestration the right way? Let's start by looking at how an individual Worker Agent works.

Agent Anatomy: The Single-Purpose Worker

In software development, it is a foundational best practice to build classes and worker tasks that focus on doing one thing well. The exact same principle—the Single Responsibility Principle (SRP)—applies to AI agents. Instead of building one agent that tries to do everything, we build a Worker Agent that has a single responsibility.

flowchart LR
    subgraph Worker["Worker Agent (Single Responsibility)"]
        I["Compact Instructions\n(Clear, concise prompt)"]
        S["Isolated Session State\n(Scoped memory for the task)"]
        T["Dedicated Tool\n(One focused function)"]
    end
    I & S & T --> G["Traceability, Governance, Cost Management & Hallucination Control"]

Scoping a Worker Agent to a single responsibility provides four major engineering benefits:

1. Clear, Compact Instructions: Instructions are the prompts we pass to the agent telling it what it does, what capabilities it has, and to be concise and exact without hallucinating. Because the worker focuses on one task, its instructions remain compact. Every time an agent runs, the LLM must read and process those instructions—so keeping prompts small directly lowers token usage and manages cost while making it easy to see where an agent fails and audit its behavior.
2. Isolated Session State: Session state is the working memory that enables an agent to understand what is happening and recall previous steps executed during the run. A single-purpose worker isolates its session state so it only holds the data and context relevant to its specific responsibility—not everything happening across the entire workflow. Once the one-time session finishes, that ephemeral memory is flushed out.
3. Dedicated Tools: Instead of equipping an agent with ten different tools to call APIs, query databases, and read data lakes, a worker agent is given a dedicated tool—just the one function it needs to perform its specific activity.
4. Traceability and Governance: By keeping the worker's footprint isolated and focused, we gain full traceability, cost management, easier testing, and tight control over hallucinations.

That is the foundation of our building block. Now that we understand what an individual Worker Agent is, what is the advantage of having multiple worker agents instead of a monolith? Let's look at why we decouple.

---

The Multi-Worker Advantage: Why Decouple?

When we decouple a monolithic agent and create multiple specialized workers, we unlock the Multi-Worker Advantage for agent orchestration:

• Domain Knowledge and Context Isolation: By creating specialized workers, we isolate both the domain knowledge and the execution context. If a worker's focus is to call a specific API based on its reasoning, that is all it knows. If another worker's job is database access—querying a table or a view—it is focused exclusively on that database task. Because it does not have a Google search tool attached to it, it is never going to wander off, run an internet search, and return unverified results. This isolation protects the pipeline from cross-tool confusion and hallucinations.
• Modularity and Easy Testing: By breaking a complex problem into smaller units of work, we can isolate each worker module, inject its specific instructions and tools, and test it independently—which is the end goal of clean software design.

How Multiple Workers Connect: The Orchestration Graph

Once we have multiple specialized workers, how do they fit together? They are coordinated through an Orchestration Graph:

flowchart LR
    OA([Orchestration Agent]) --> W1[Ingestion Agent]
    W1 --> W2[Aggregation Agent]
    W2 --> W3[Analysis Agent]
    W3 --> W4[Reporting Agent]

In this architecture, an Orchestration Agent is responsible for loading and coordinating the workflow. Based on the request and metadata, it loads the workflow graph—which is a connected series of Worker Agents. Every node in the graph is a single-responsibility Worker Agent that executes its specific task and hands off to the next node.

Now that we know how a workflow is structured conceptually using multiple workers, what does it look like when we build it in code with an SDK—and what makes it different from simply hardcoding if statements? Let's examine Workflow Orchestration.

---

Workflow Orchestration: Moving Beyond Agent Chat

In traditional procedural code, developers often write conditional statements like if result == True: call_next_function(). In agent development, we want to stay away from hardcoding brittle conditional ladders and instead use an orchestration engine that connects a series of Worker Agents using Composition.

Using a workflow graph gives us deterministic handoffs (gates):

• When one Worker Agent finishes executing, it writes its result to a defined output key in the session state.
• Depending on the workflow definition, the downstream agent reads that output and deterministically decides whether to execute its step or stop immediately if there was a failure.
• We achieve this by composing the workflow graph declaratively—defining the starting point and the edges that connect each agent node.

Defining a Worker Agent and Workflow Graph in Python (Google ADK)

Here is how this looks in Python using the Google Agent Development Kit (ADK) (workflow_agent/workflows/sequential.py):

from google.adk.workflow import Workflow, START
from worker_agent import WorkerAgent

# 1. Instantiate a single-responsibility WorkerAgent with instructions, tool, and output_key
ingestion_agent = WorkerAgent(
    name="IngestionAgent",
    instruction="Identify the target geographic zone and call query_zone_commuters once.",
    tools=[query_zone_commuters],
    output_key="raw_telemetry",
).agent

aggregation_agent = WorkerAgent(
    name="TimeSlotAggregatorAgent",
    instruction="Call calculate_time_slots once to aggregate observations into time slots.",
    tools=[calculate_time_slots],
    output_key="slotted_telemetry",
).agent

# 2. Compose the orchestration graph using edges
workflow = Workflow(
    name="mta_sequential_geo_pipeline",
    edges=[
        (START, ingestion_agent),
        (ingestion_agent, aggregation_agent),
        (aggregation_agent, peak_agent),
        (peak_agent, reporter_agent),
    ],
)

Notice how clean this composition is:

• Each WorkerAgent declares its instruction, its dedicated tool (query_zone_commuters), and its output_key ("raw_telemetry"), which the next agent in the chain uses to continue the process.
• The Workflow object defines the edges—telling the orchestration engine to start at START, call ingestion_agent, then transition from ingestion_agent to aggregation_agent, and so on.

The Deterministic Gate Contract: {status, message, data}

In multi-agent systems, unhandled errors and unstructured responses cause upstream failures to cascade into downstream agents, leading to hallucinations and conversational drift. To prevent brittle if/else ladders while guaranteeing safe execution, every tool and worker task in the architecture adheres to a uniform envelope:

{
    "status": True,             # Boolean flag: True on success, False on failure/error
    "message": "Description",   # Human-readable status or diagnostic message
    "data": { ... }             # Structured payload written to the agent's output_key
}

This contract acts as an automated circuit breaker:

• On Success (status: True): The orchestration engine writes the data payload to session state under the designated output_key and signals the next agent node to run.
• On Failure (status: False): Downstream execution halts immediately, and the diagnostic message is logged for governance and debugging—preventing corrupt or missing data from triggering downstream hallucinations.

Now that we understand how to create a workflow graph in code, what types of workflows exist, and what is the purpose of each one? Let's review the four core enterprise workflow patterns.

---

Core Enterprise Workflow Patterns

There are four foundational architectural patterns for composing agents to execute enterprise business flows. These patterns apply across software engineering and map directly to multi-agent orchestration:

flowchart TD    
    subgraph P2["2. Parallel Workflow"]
        S2((START)) --> P_A[Agent A] & P_B[Agent B] & P_C[Agent C]
        P_A & P_B & P_C --> JN([JoinNode]) --> AGG[Aggregator Agent]
    end
    subgraph P1["1. Sequential Workflow"]
        S1((START)) --> A1[Agent A] --> B1[Agent B] --> C1[Agent C]
    end

flowchart TD
    
    subgraph P4["4. Custom Router Workflow"]
        S4((START)) --> RTR[Intent Router Agent]
        RTR -->|Intent A| T1[Target Worker 1]
        RTR -->|Intent B| T2[Target Worker 2 / Sub-Workflow]
    end

    subgraph P3["3. Loop Workflow"]
        S3((START)) --> DET[Detector Agent] --> COND{Threshold Met?}
        COND -->|No: Continue| IMP[Calibration Agent] --> DET
        COND -->|Yes: Exit| CERT[Certifier Agent]
    end

1. Sequential

Just as the name implies, a Sequential workflow is a linear series of steps: [A] → [B] → [C] → [D]. If A runs and succeeds, B runs; if B succeeds, C runs, and so on. Every node executes in strict linear order as long as each prior step completes cleanly.

2. Parallel

In a Parallel workflow, worker agents do not run in series—they run independently and simultaneously to execute tasks, and an aggregator puts their findings together for the final output. You use parallel execution when you need multiple agents to fetch or analyze data from different sources or regions at the same time (for example, querying an internal database, calling an API, or analyzing separate geographic zones concurrently) and then joining those results once all parallel branches finish.

3. Loops

A Loop workflow repeats worker activities until a threshold or condition (a marker) is met. The marker tells the workflow either that it has reached its maximum number of repetitions or that it has achieved the target value it was looking for—such as a calibration task that iteratively calibrates a process until it reaches a stable quality condition.

4. Custom Router

A Custom Router is a more advanced pattern that uses user intent to dispatch queries to a specific target. Based on what the user is asking for, the router agent determines which specialized worker agent (or which sub-workflow) to call—for example, deciding whether to route a request to an external search worker or an internal inventory system worker.

Furthermore, all of these patterns can be orchestrated together: a master orchestrator can route to a parallel workflow, which in turn can invoke a sequential workflow.

Now that we understand the four workflow patterns and how they work conceptually, let's see how this works live with a real data warehouse use case.

---

Review Agent Workflows Code (Live Walkthrough)

1. Background & Use Case: Data Warehouse Analytics Beyond Static Dashboards

In our live walkthrough, we execute a multi-agent workflow that performs Business Intelligence (BI) and commuter analytics against a Google BigQuery data warehouse. This use case is built on the NYC Metropolitan Transportation Authority (MTA) subway turnstile system from the book Data Engineering Process Fundamentals and the data-engineering-mta-turnstile repository.

In a traditional BI setup, we visualize turnstile traffic and total station entries/exits using dashboards in tools like Looker or PowerBI. Dashboards are great for viewing standard charts, but when you want to dig deeper into a specific station or geographic zone—for example, understanding peak commuter surges and resource needs for Midtown Manhattan or Lexington Station—you traditionally have to manually write SQL queries and Python scripts.

Our goal in this walkthrough is to use AI Agent Workflows to dig into the data warehouse and perform that deeper geo-fencing and commuter mobility analysis automatically on demand.

2. Reviewing the Code in Visual Studio Code

All of the code resides in the ai-engineering repository under the adk directory (worker_agent and workflow_agent). Walking through the implementation in VS Code reveals how the architecture is structured:

The Foundational Building Block: WorkerAgent (worker_agent/agent.py)

At the top of our hierarchy is WorkerAgent, which inherits from BaseAgent:

class WorkerAgent(BaseAgent):
    """
    Specialized worker agent derived from BaseAgent.
    Abstracts model resolution and framework primitives from workflow sub-agents.
    """
    def __init__(
        self,
        name: str,
        instruction: str = "",
        instructions: str = "",
        tools: Optional[List[Any]] = None,
        output_key: Optional[str] = None,
        **kwargs,
    ):
        resolved_instruction = instruction or instructions
        kwargs["name"] = name
        kwargs["instruction"] = resolved_instruction
        super().__init__(**kwargs)

        self.tools = tools or []
        self.output_key = output_key
        self.agent = self.build_agent()

Using object-oriented inheritance, BaseAgent handles centralized platform internals—such as which LLM model to use and platform-wide rules—while WorkerAgent isolates each worker's single instruction, dedicated tools, and output_key.

The Orchestrator: WorkflowAgent & WorkflowFactory (workflow_agent/agent.py)

Next, in the workflow_agent folder, WorkflowAgent acts as our orchestrator:

• Security via Inheritance (SecuredToolAgent): WorkflowAgent inherits from SecuredToolAgent, which reads instructions and applies cryptographic signature verification (.signed.md) to ensure prompt instructions have not been tampered with or exploited.
• Strategy Resolution via WorkflowFactory: Instead of using hardcoded if/else conditions to pick a workflow, WorkflowAgent calls WorkflowFactory (factory.py) to resolve the active strategy (sequential, parallel, loop, or custom) from metadata and build the workflow graph dynamically.

Inside the Workflow Definitions (workflow_agent/workflows/)

Inside the workflows/ folder, each pattern is cleanly encapsulated in its own module:

• Sequential (sequential.py): build_sequential_agent instantiates the four WorkerAgent instances in memory (IngestionAgent, TimeSlotAggregatorAgent, PeakWindowAnalyzerAgent, and GeoBriefingReporterAgent)—each with its own instruction, tool, and output_key—and chains them together in Workflow(edges=[...]):

  return Workflow(
      name="mta_sequential_geo_pipeline",
      description="Executes serial data operations from BigQuery turnstile telemetry to executive geo-zone mobility reports via ADK Workflow.",
      edges=[
          (START, ingestion_agent),
          (ingestion_agent, aggregation_agent),
          (aggregation_agent, peak_agent),
          (peak_agent, reporter_agent),
      ],
  )
  

• Parallel (parallel.py): build_parallel_agent instantiates regional worker agents (MidtownZoneAnalyst, QueensZoneAnalyst, DowntownFinancialZoneAnalyst) and wires them to run concurrently from START. Just like asynchronous promises spinning off in parallel, a JoinNode(name="merge_zones") waits for all three regional analysts to come back and joins their outputs before calling CrossZoneGeoSynthesizer:

  join_node = JoinNode(name="merge_zones")
  
  return Workflow(
      name="mta_parallel_workflow_suite",
      edges=[
          (START, (midtown_analyst, queens_analyst, downtown_analyst)),
          ((midtown_analyst, queens_analyst, downtown_analyst), join_node),
          (join_node, merger_agent),
      ],
  )
  

• Loop (loop.py) and Custom Router (custom.py): build_loop_agent connects the anomaly detector (ZScoreAnomalyDetector) to an evaluation condition (evaluate_quality_route) that checks whether the data quality confidence threshold has been met—continuing the calibration loop with SpatialImputerAgent or exiting to GeoQualityCertifierAgent once the condition is reached.

3. Running the Workflow Live

You can run the workflow agents using the ADK web UI, the standard ADK CLI (uv run adk run workflow_agent), or interactively using Google Antigravity (a CLI code assistant that can execute CLI commands and format the agent output cleanly for interactive evaluation). In a production environment, automated pipeline triggers invoke the CLI command directly with the target prompt.

When we load workflow_agent running the sequential strategy and pass the user intent prompt:

Analyze the peak commuter flow for Midtown Manhattan

the workflow uses its domain skills to understand the business concepts and executes each WorkerAgent step in sequence against BigQuery:

1. Ingestion (IngestionAgent): Queries BigQuery to fetch the turnstile observations for the Midtown Manhattan stations (59 ST, 34 ST-HERALD SQ, GRD CNTRL-42 ST, 34 ST-PENN STA).
2. Slot Aggregation (TimeSlotAggregatorAgent): Aggregates the station traffic across the canonical time slots (morning_rush, afternoon_midday, evening_rush).
3. Peak Window Analysis (PeakWindowAnalyzerAgent): Evaluates the morning rush and evening rush surges and determines the zone's directional mobility profile.
4. Executive Bulletin (GeoBriefingReporterAgent): Produces the final Geo-Zone Commuter Mobility Bulletin with operational insights:

================================================================================
NYC TRANSIT GEO-ZONE COMMUTER MOBILITY BULLETIN
================================================================================
Target Zone          : Midtown Manhattan (ZONE_MIDTOWN_COMMERCIAL)
Monitored Stations   : 59 ST, 34 ST-HERALD SQ, GRD CNTRL-42 ST, 34 ST-PENN STA
Spatial Profile      : Commercial / Employment Destination (Inflow Heavy)
Net Direction Ratio  : 1.84 (Morning Inbound / Outbound)
--------------------------------------------------------------------------------
[Peak Mobility Windows]
  • Peak Arrival Influx:    Morning Rush (08:00 - 11:00)
  • Peak Departure Outflow: Evening Rush (16:00 - 20:00)

[Operational Dispatch Directive]
  • Allocate maximum turnstile entry-gate throughput during 'evening rush'.
  • Schedule location-based dynamic messaging near station exits during 'morning rush'.
================================================================================

Instead of manually slicing charts in a dashboard, the interactive multi-agent workflow queries the data warehouse and immediately delivers operational insights—helping teams manage resources, anticipate bottlenecks, and schedule geofenced activities.

[!TIP] Bonus VS Code Plugin — Code Lineage: When analyzing your files and class inheritance structures in Visual Studio Code, check out the Code Lineage by ozkary.com extension in the VS Code Marketplace (written and published by Oscar Garcia). It generates a visual view of how your classes and code files connect.

Now that we have seen the code and live execution in action, let's recap our key takeaways and review the questions answered during the session.

---

Key Takeaways

Core Principles to Remember

• Use Worker Agents to Isolate Context, Tooling, and Instructions: Keep each worker focused on a single responsibility with compact instructions, isolated session state, and dedicated tools. This makes your agents easy to manage, easy to test, easy to audit, and cost-effective to run.
• Monitor and Continuously Refactor: As you build out your worker agents and orchestration workflows, monitor how they grow. If you notice you have overbuilt a Worker Agent and it is doing more than it needs to do, refactor it—break it down into another worker agent to keep each agent's footprint small. Likewise, if a workflow grows too complex or needs a different execution path, refactor it into separate, composable workflows.

Session Q&A Highlights

1. How do you control the process of Worker Agents via a Loop?
   Think of a Loop workflow just like a loop in standard code: it relies on a matching exit condition (for example, iterating from 1 to 10, or repeating until a specific condition is true). In our code (loop.py), the workflow calls an evaluation function after each pass that checks those exit conditions (confidence_score >= 0.95 or maximum iterations) so it knows deterministically whether to continue looping or exit.
2. Is Orchestration the same as Federation?
   No, Orchestration and Federation are different concepts. Federation is an enterprise-wide governance directive—for example, federated security and Single Sign-On (SSO), where you log in once to access multiple applications. Orchestration is the mechanism for coordinating and executing a connected series of workflow steps deterministically.
3. Are the agents in our workflow talking and collaborating with independent external agents?
   When you build a workflow, your Orchestration Agent only invokes the Worker Agents defined within that workflow. However, if you build a Worker Agent whose specific tool performs an Agent-to-Agent (A2A) integration or calls a Model Context Protocol (MCP) server for discovery and integration, then it will communicate with those external agents through that governed tool. What you must never do is allow an agent to freely search the web and call arbitrary endpoints it finds on its own, as that leads to hallucinations and security issues.
4. What is a workflow under the hood—is it a script, and how does it compare to no-code tools?
   A workflow definition is orchestration metadata that specifies which agents to use and how their steps connect based on conditions (sequential, parallel, loop, etc.). When you use a visual no-code tool where you drag and drop agent nodes on a canvas, that interface is simply generating a YAML or metadata script behind the scenes to execute that sequence. In our code-first ADK repository, we define that exact same workflow graph directly in Python (Workflow(edges=[...])).

---

🌟 Let's Connect & Build Together

Thanks for reading! 😊 If you found these architectural patterns helpful, let's connect and continue the conversation:

• GDG Broward: Join our local dev community for workshops and live tech sessions.
• Global AI Events: Connect with the community across global AI events.
• LinkedIn: Follow along for regular deep dives into software architecture and AI engineering.
• GitHub: Explore open-source projects, agents, and samples.
• YouTube: Watch recordings and step-by-step video tutorials.
• BlueSky / X / Twitter: Daily tech updates and architectural tips.

👉 Originally published at ozkary.com

Overview What is Smart Orchestration , and how do we apply it to multi-agent systems? If you have built data flows or data pipelines, you a...

Read Article →

Master Prompting for Data Analysis: Practical Patterns

Overview

Large Language Models (LLMs) have become a foundational technology in modern data engineering and analytics. By leveraging their natural language understanding and generative capabilities, data professionals can accelerate how they profile datasets, design data models, and write SQL queries against a Data Warehouse (DW). However, an LLM is only as effective as the instructions and context it receives. Without a structured approach, asking an AI model to analyze data often produces inconsistent queries, incorrect aggregations, or references to columns that do not exist.

In this post, we explore the technical specifications and architectural patterns of prompting and prompt engineering as they apply to data analysis. Our main objective is to understand how these prompting techniques work under the hood and how to use them to connect to a data warehouse and drive analytical tasks using SQL. As we cover each concept, we will illustrate the pattern with a concise prompt snippet using a consistent analytical context—querying station dimensions, commuter entry/exit measures, and time-slot distributions—so we can clearly see how each technique builds upon the last.

🚀 Featured Open Source Projects

Explore these curated resources to level up your engineering skills. If you find them helpful, a ⭐️ is much appreciated!

🏗️ Data Engineering

Focus: Real-world ETL, Data Discovery, Data Warehousing & MTA Turnstile Data Analysis

🤖 AI Agents (ADK)

Focus: LLM Patterns, Skills & Commands, and Agentic Workflows

📖 Data Engineering Process Fundamentals

Focus: Architectural patterns, hands-on data pipelines, and MTA turnstile case studies

---

Foundations of Prompting

Before we can apply advanced analytical frameworks, we need to understand the core mechanics of how foundation models process instructions and what makes a prompt effective for data analysis.

What is a Prompt

A prompt is the structured input text sent to a foundation model to initiate a task, guide its reasoning, and define the expected output. In a technical sense, a prompt acts as the interface specification between the user and the LLM. Because foundation models are stateless by default, the prompt must carry all the necessary information required to complete the request within a single execution context (known as the context window).

When applying prompting to data analysis and SQL generation, a well-formed prompt specification typically consists of four components:

• Instruction: A clear directive stating the analytical task to perform (e.g., profiling data, identifying dimensions, or generating an aggregation query).
• Context: Background information that constrains the model, such as the target database system (e.g., Google BigQuery or SQL Server), table names, and business rules.
• Input Data or Parameters: The specific fields, schema definitions, filter criteria, or data samples to be evaluated.
• Output Indicator: The format and structure expected in the response, such as a SQL query, a Markdown table, or a list of observations.

👍 When interacting with LLMs for data analysis, using a low temperature setting (e.g., 0.0 to 0.2) is recommended so the model produces deterministic, focused SQL rather than creative variations.

Let's look at a basic prompt snippet that illustrates these four components for a data discovery task:

Instruction: Identify the dimensions, measures, and composite key from the provided dataset fields.
Context: We are performing data discovery on a transit turnstile dataset to prepare it for a data warehouse.
Input Fields: CA (Booth), UNIT (Remote), SCP (Device Address), STATION, DATE, TIME, ENTRIES, EXITS.
Output: Provide a bulleted list classifying each field as a Key, Dimension, or Measure.

While knowing the basic components of a prompt allows us to communicate with an LLM, writing a simple prompt is often not enough when working with large datasets and complex database schemas. To consistently generate accurate SQL and reliable analytical insights, we need to apply the discipline of prompt engineering.

---

What is Prompt Engineering

Prompt engineering is the systematic process of designing, structuring, and refining prompts to optimize the accuracy, completeness, and performance of an LLM's output. In data analysis, prompt engineering moves us away from trial-and-error questions and toward a repeatable specification process.

When our goal is to connect to a Data Warehouse (an OLAP system) and perform data analysis with SQL, prompt engineering focuses on providing precise technical specifications so the model understands both the structure and the meaning of the data:

• Schema and Relationship Specification: Explicitly defining fact tables (which hold quantitative measures) and dimension tables (which hold descriptive attributes), along with the foreign keys used to join them.
• Measure Behavior and Granularity: Clarifying whether numeric fields are transactional values that can be summed directly or cumulative register values that require delta calculations across time intervals.
• Query Performance Constraints: Instructing the model to filter on partitioned columns (such as date/time fields) and group by clustered columns (such as station identifiers) to avoid costly full-table scans.
• Data Quality Rules: Specifying how the query should handle NULL values, duplicate records, or statistical outliers.

👍 Well-engineered prompts reduce token usage and prevent common SQL generation errors by giving the model explicit boundaries before it writes a single line of code.

The following prompt snippet shows how prompt engineering refines a general request into a precise technical specification for a Data Warehouse query:

Generate a SQL query for our BigQuery Data Warehouse using the following technical specifications:
- Fact Table: fact_turnstile (partitioned by created_dt, clustered by station_id)
- Dimension Table: dim_station (station_id, station_name)
- Join Criteria: Inner join fact_turnstile and dim_station on station_id
- Measures: Calculate total entries (departures) and exits (arrivals)
- Constraints: Filter created_dt between '2023-03-01' and '2023-03-31', exclude rows where station_id is null, and group the results by station_name.

Providing clear technical specifications is the foundation of prompt engineering, yet there are many situations where describing a rule in plain text leaves room for ambiguity. When we want the model to follow a specific pattern or classification rule, we can guide its behavior by controlling how many examples we include in the prompt.

---

In-Context Learning: Shot Prompting Patterns

In-context learning refers to a foundation model's ability to learn how to perform a task simply by observing examples provided within the prompt itself, without requiring any retraining or fine-tuning. Depending on the complexity of the analytical task, we use Zero-Shot, One-Shot, or Few-Shot prompting.

flowchart LR
    A["Zero-Shot Prompting \n0 Examples\n(Relies on pre-trained knowledge)"] --> B["One-Shot Prompting\n1 Example\n(Establishes format and structure)"]
    B --> C["Few-Shot Prompting\nMultiple Examples \n(Teaches rules and edge cases)"]

Zero-Shot Prompting

Zero-shot prompting is asking a foundation model to complete a task with no prior examples, relying entirely on its pre-existing knowledge of programming languages, SQL syntax, and analytical concepts.

In data analysis, zero-shot prompting is most effective for standard, well-defined tasks where the model already understands the underlying pattern—such as writing basic SQL aggregations, explaining statistical concepts, or performing initial exploratory data profiling on familiar data types.

Task: Write a SQL query to profile the `ext_turnstile` table during our discovery phase.
Requirements:
- Merge the separate `DATE` and `TIME` string columns into a single timestamp column named `created_dt`.
- Return the minimum date, maximum date, total row count, and the count of distinct `STATION` values.

Zero-shot prompting is fast and concise, making it a great starting point for standard SQL queries. However, when our analysis requires custom business formatting, such as grouping timestamps into specific multi-hour time slots with custom labels, a zero-shot prompt may guess the wrong format. To enforce a specific structure, we transition to one-shot prompting.

---

One-Shot Prompting

One-shot prompting involves showing the foundation model just one example alongside the instructions, allowing it to learn the expected pattern and apply that knowledge to the remaining items in the task.

In data analysis, one-shot prompting is ideal when we need consistency in how data is categorized, labeled, or transformed in SQL. By supplying a single input-to-output mapping, we remove ambiguity about naming conventions, data casting, or interval logic without writing lengthy explanations.

Task: Write a SQL CASE statement to categorize the hour of `created_dt` from `fact_turnstile` into 4-hour time slots for our analysis dashboard.

Use this single example as the pattern for all six 4-hour blocks in a 24-hour day:
Example Input: Hours 8 through 11 (8:00 AM to 11:59 AM)
Example Output: WHEN EXTRACT(HOUR FROM created_dt) BETWEEN 8 AND 11 THEN '08:00-11:59am'

Complete the SQL CASE expression for all six 4-hour time slots (starting at 12:00 AM) and group total `entries` and `exits` by `time_slot`.

While one-shot prompting is great for establishing a uniform structural pattern like a CASE statement, a single example cannot represent datasets that have multiple distinct variations or data quality exceptions. When the model needs to distinguish between several different data conditions, we expand to few-shot prompting.

---

Few-Shot Prompting

Few-shot prompting provides the foundation model with multiple examples to learn from, helping it understand complex tasks, edge cases, and conditional rules to improve its performance.

During data cleaning and preprocessing, raw datasets often contain anomalies, missing audit events, or outliers that skew aggregations. Few-shot prompting allows us to show the model examples of valid records alongside specific types of bad data so it can generate SQL logic that filters or transforms each scenario accurately.

Task: Write a SQL query that classifies turnstile audit records and calculates net `entries` deltas using the following examples:

Example 1 (Normal Scheduled Audit):
Input: DESC = 'REGULAR', prev_entries = 1000, current_entries = 1250
Expected Logic: Valid 4-hour increment -> return `current_entries - prev_entries` (250)

Example 2 (Recovered Missed Audit):
Input: DESC = 'RECOVR AUD', prev_entries = 1250, current_entries = 1250
Expected Logic: Duplicate recovery timestamp -> exclude record to prevent double-counting

Example 3 (Counter Reset Outlier):
Input: DESC = 'REGULAR', prev_entries = 50000, current_entries = 10
Expected Logic: Negative delta caused by hardware reset -> return NULL

Generate the SQL query for `fact_turnstile` that applies these rules across each device (`booth_id`, `scp`) ordered by `created_dt`.

Shot prompting patterns give us fine-grained control over how the model learns from examples, whether we need zero examples for quick profiling or several examples for complex data cleaning. Beyond showing the model examples of what to output, we can also improve analytical quality by shaping who the model acts as and grounding it in our schema specifications.

---

Contextual and Behavioral Prompting Patterns

Role Prompting

Role prompting is a technique used to guide the behavior, tone, and domain focus of an LLM by assigning it a specific role or persona.

In data analysis, different roles look at the exact same dataset through different lenses. For example, a Data Engineer focuses on table partitioning, incremental loads, and surrogate keys, whereas a Business Analyst (BA) focuses on summations, distributions across dimensions (like station or day of the week), and actionable KPIs for stakeholders. Assigning a role tells the model which analytical patterns and terminology to prioritize.

Role: Act as a Senior Business Intelligence Analyst specializing in commuter traffic patterns and location-based marketing.
Context: In our data warehouse (`fact_turnstile` joined with `dim_station`), `exits` represent commuters arriving at a station area, and `entries` represent commuters departing from that location.
Task: Write a SQL query to identify the top 10 stations with the highest commuter arrivals during morning peak hours (08:00-11:59am), and explain how local businesses can use this distribution for geofencing advertisements.

Assigning a role aligns the model's analytical perspective with our business goals, but a persona alone does not give the model knowledge of our specific database tables. To ensure our SQL queries execute without schema errors, we pair role prompting with schema grounding.

---

Schema Grounding and Metadata Specification

Schema grounding (often referred to as context injection) is the practice of embedding explicit data dictionary definitions, table relationships, and data types directly into the prompt so the model's responses are strictly anchored to your physical Data Warehouse schema.

When an LLM is asked to write SQL without schema grounding, it guesses table and column names based on generic conventions. By injecting the Star Schema metadata—extracted from our discovery and data modeling documentation—into the prompt, we constrain the model to use only verified entities and attributes.

Schema Specification:
- Table `dim_station`: `station_id` (STRING, PK), `station_name` (STRING)
- Table `dim_booth`: `booth_id` (STRING, PK), `remote` (STRING), `booth_name` (STRING), `station_id` (STRING, FK)
- Table `fact_turnstile`: `log_id` (STRING, PK), `station_id` (STRING, FK), `booth_id` (STRING, FK), `scp` (STRING), `created_dt` (TIMESTAMP), `entries` (INTEGER), `exits` (INTEGER)

Rule: Use ONLY the tables and column names defined in the Schema Specification above.
Task: Write a SQL query that joins all three tables to return daily `entries` and `exits` by `station_name` and `booth_name`.

With role prompting and schema grounding in place, the model has both the right analytical perspective and accurate metadata for single queries. However, a complete data analysis process rarely happens in a single query; it progresses through multiple stages from discovery to final reporting. To manage multi-step workflows effectively, we use modular prompting patterns.

---

Modular Workflow Patterns: Chaining and Reusing Prompts

Prompt Chaining

Prompt chaining is a technique that breaks a complex analytical process into a sequence of smaller, interconnected prompts, where the output of one prompt serves as the input for the next prompt.

Attempting to perform data profiling, data cleaning, and multi-dimensional distribution analysis inside one massive prompt often leads to incomplete SQL or logic errors. By chaining prompts together, we mirror the natural stages of the data analysis process, validating each intermediate query before moving to the next step:

• Step 1 (Discovery & Profiling Prompt): Generate a SQL query to inspect date ranges, data types, and null values in the raw source.
• Step 2 (Transformation & Cleaning Prompt): Pass the validated findings from Step 1 into a second prompt to build a clean SQL view or Common Table Expression (CTE).
• Step 3 (Distribution Analysis Prompt): Use the CTE from Step 2 as the input to generate the final distribution queries by station, day of the week, and time slot.

-- Prompt 1 (Base CTE Generation) --
Write a SQL CTE named `station_time_metrics` that joins `fact_turnstile` and `dim_station`, maps `created_dt` into the day of the week (`Sun` through `Sat`) and 4-hour `time_slot`, and aggregates `entries` and `exits`.

-- Prompt 2 (Chained Analysis Using Prompt 1 Output) --
Using the `station_time_metrics` CTE generated in the previous step, write two analytical SELECT queries:
1. A station distribution query returning the top 10 stations by total `exits`.
2. A day-of-week distribution query comparing total `entries` and `exits` across each `weekday`.

Prompt chaining gives us a structured, step-by-step pipeline for building complex SQL analyses. However, to execute a chain reliably in an automated or agentic workflow, we need a standardized mechanism to carry context from one step to the next—which brings us to reusing prompts with templates.

---

Reusing Prompts (Prompt Templates)

Reusing prompts is the practice of abstracting proven prompts into parameterized prompt templates that can be stored under source control, shared across teams, and executed repeatedly by injecting dynamic variables and prior execution context at runtime.

In a simple reporting scenario, a template replaces hardcoded parameters (such as dates or station names) much like a Python function or a dbt Jinja macro. In a chained or agentic data analysis workflow, prompt templates play an even more critical architectural role: they act as state containers that automatically inject the output and observations from previous steps into the current prompt so the model maintains full continuity across the analytical process.

To keep injected context organized and prevent the LLM from confusing prior outputs with new instructions, templates rely on two types of template tags:

• Variable Placeholder Tags ({variable_name}): Dynamic tokens replaced at runtime by the application or orchestrator before the prompt is sent to the LLM. These carry both user parameters (e.g., {dimension_column}, {measure_column}, {date_range}) and state carried over from earlier steps (e.g., {schema_metadata}, {previous_context}, {last_query_observation}).
• Structural Boundary Tags (<tag>...</tag>): XML-style delimiter tags (such as <schema_context>, <previous_context>, and <task>) that wrap the injected variables. These boundary tags explicitly tell the model where historical context ends and where the current analytical instruction begins.

Template Name: agentic_sql_analysis_step

Template Prompt:
Role: Senior Data Analyst connected to our BigQuery Data Warehouse.

<schema_context>
{schema_metadata}
</schema_context>

<previous_context>
Prior SQL CTE / Discovery Output:
{previous_context}

Latest Data Warehouse Observation:
{last_query_observation}
</previous_context>

<task>
Using the validated CTE in <previous_context>, write a SQL query to analyze `{measure_column}`
grouped by `{dimension_column}` for `created_dt` between '{start_date}' and '{end_date}'.
Current Objective: {current_analysis_goal}
</task>

Let's review how the template tags work together in this specification:

• {schema_metadata} inside <schema_context>: Injects the verified table definitions (fact_turnstile, dim_station, dim_booth) discovered at the start of the workflow so every step stays grounded.
• {previous_context} inside <previous_context>: Carries forward the SQL CTE or profiling summary generated by the previous link in the prompt chain.
• {last_query_observation}: Injects the sample rows, row counts, or error messages returned by the Data Warehouse from the prior query execution.
• {measure_column}, {dimension_column}, {start_date}, {end_date}, and {current_analysis_goal} inside <task>: Inject the specific analytical parameters for the current step (for example, analyzing exits across time_slot to identify peak commuter arrival windows).

By combining structural boundary tags with dynamic variable placeholders, reusable prompt templates provide the backbone for multi-step agentic workflows. Once our templates can carry state from one step to the next, we can pair them with reasoning and execution frameworks that allow the model to think and query the database autonomously.

---

Reasoning and Agentic Frameworks

When an analytical task requires multi-step logic, statistical validation, or interactive querying against a live Data Warehouse, standard prompting patterns may jump to conclusions too quickly. Frameworks like Chain-of-Thought (CoT) and ReAct structure how the model thinks and acts before delivering an answer.

Chain-of-Thought (CoT) Prompting

Chain-of-Thought (CoT) prompting is a technique that instructs the foundation model to break down a complex problem into explicit, sequential reasoning steps before generating the final output or SQL code.

In data analysis, writing a query that combines window functions (LAG), statistical metrics (like Z-scores or correlation preparations), and multi-level groupings is error-prone if the model writes the SQL immediately. By asking the model to explain its analytical logic step-by-step first, each reasoning step conditions the next, resulting in much more accurate SQL.

Task: Write a SQL query to identify outlier stations where morning arrivals (`08:00-11:59am` exits) differ significantly from evening departures (`04:00-7:59pm` entries) using a Z-score threshold of 1.95.

Let's think step-by-step before writing the SQL query:
1. First, explain how to filter and aggregate mean morning `exits` (arrivals) and mean evening `entries` (departures) per `station_name`.
2. Second, define the mathematical formula for the difference and standard deviation across stations to compute the `z_score`.
3. Third, explain how to filter for outlier stations where `ABS(z_score) > 1.95`.
4. Finally, generate the complete SQL query using CTEs that correspond to steps 1 through 3.

Chain-of-Thought is highly effective for planning complex SQL and statistical logic in a single response. However, CoT relies solely on internal reasoning without inspecting live data in the database. When we need the model to test a hypothesis by running a query, observing the actual results from the Data Warehouse, and deciding the next step dynamically, we use the ReAct framework.

---

ReAct (Reason + Act) Prompting

ReAct (Reason and Act) is a framework where the foundation model interleaves verbal reasoning (Thought) with external tool execution (Action) and data inspection (Observation) in a continuous loop until it solves the analytical problem.

In an AI-assisted data warehouse environment, a ReAct workflow connects the LLM to a database execution tool. Instead of guessing why a metric looks unusual, the model reasons about what to check, runs a diagnostic SQL query, reads the returned rows, and refines its analysis based on real evidence:

• Thought: The model formulates a hypothesis or identifies what data it needs to inspect next.
• Action: The model invokes a tool (e.g., executing a SELECT query against the Data Warehouse).
• Observation: The system returns the query results from the database back to the model.

Instruction: You have access to a `run_sql_query` tool connected to our Data Warehouse. Use the Thought -> Action -> Observation loop to answer the analytical question.

Question: Is there a statistically significant difference between weekday and weekend commuter arrivals (`exits`) at top stations in July?

Thought 1: First, I need to query `fact_turnstile` and `dim_station` for July to inspect the top 10 stations by total `exits` and verify there are no missing dates or null values.
Action 1: run_sql_query("SELECT s.station_name, COUNT(DISTINCT DATE(f.created_dt)) AS days_recorded, SUM(f.exits) AS total_exits FROM fact_turnstile f JOIN dim_station s ON f.station_id = s.station_id WHERE EXTRACT(MONTH FROM f.created_dt) = 7 GROUP BY s.station_name ORDER BY total_exits DESC LIMIT 10")
Observation 1: [Returns 10 stations, all with 31 days recorded].
Thought 2: Now that data completeness is verified, I will write a query comparing average daily `exits` on weekdays (Mon-Fri) versus weekends (Sat-Sun) for these top 10 stations.
Action 2: run_sql_query("... [Weekday vs Weekend Aggregation SQL] ...")

Frameworks like Chain-of-Thought and ReAct elevate LLMs from simple text generators into analytical reasoning engines. To complete our workflow and ensure that the SQL queries and insights produced by these frameworks are ready for production dashboards, we apply one final set of verification and output patterns.

---

Self-Critique and Structured Output Prompting

When integrating AI-generated analysis into automated pipelines or Business Intelligence (BI) dashboards, two additional prompting techniques ensure reliability:

• Self-Critique (Verification) Prompting: Instructing the model to review and validate its own generated SQL query against a checklist of best practices—such as verifying partition filters, checking join keys, and confirming division-by-zero protection—before finalizing the output.
• Structured Output Prompting: Constraining the model to return its analytical results in a strict, machine-readable format (such as JSON, CSV, or a standardized Markdown table) so visualization tools and applications can consume the output directly.

Task: Review the SQL query we generated for station time-slot distributions and return the final validated result in JSON format.

Verification Checklist:
1. Ensure `created_dt` is used in the WHERE clause to utilize table partitioning.
2. Confirm all 6 four-hour time slots are ordered chronologically from 12:00am to 11:59pm.

Output Format: Return a JSON object with keys `is_valid` (boolean), `optimization_notes` (string), `validated_sql` (string), and `recommended_chart_type` (string).

By combining verification checks with structured outputs, we close the loop between exploratory prompting and production-ready data analysis.

---

Summary

Prompt engineering is a core competency for modern data analysis, providing a structured methodology to translate business questions into accurate, performant Data Warehouse queries. Rather than relying on ad-hoc questions, selecting the right prompting pattern allows us to control how foundation models interpret schemas, handle data quality nuances, and reason through complex SQL logic:

Prompting Pattern	Technical Purpose in Data Analysis
Prompt & Prompt Engineering	Defines the core instruction, schema context, constraints, and expected output format for Data Warehouse queries.
Zero-Shot Prompting	Leverages pre-trained model knowledge for standard SQL queries and initial data discovery without examples.
One-Shot Prompting	Uses a single example to enforce consistent formatting, labeling, or SQL CASE categorization (such as time slots).
Few-Shot Prompting	Provides multiple examples to teach pattern recognition, data cleaning rules, and outlier handling.
Role Prompting	Assigns a domain persona (e.g., Business Analyst or Data Engineer) to guide analytical focus and terminology.
Schema Grounding	Injects verified table and column metadata from the Data Warehouse to prevent schema hallucinations.
Prompt Chaining	Connects sequential prompts so discovery outputs feed transformation CTEs and final distribution analyses.
Reusing Prompts (Templates)	Uses structural boundary tags (<previous_context>) and variable placeholders ({previous_context}, {dimension_column}) to carry state across agentic steps and parameterize queries.
Chain-of-Thought (CoT)	Breaks down multi-step SQL logic and statistical calculations sequentially before generating code.
ReAct Framework	Combines reasoning (Thought) with live database queries (Action) and result inspection (Observation).
Self-Critique & Structured Output	Validates SQL against performance/quality rules and formats results into JSON or tables for BI tools.

Hands-on Resources

To see the data engineering and data analysis concepts referenced in the prompt snippets above in action, explore these hands-on project folders:

• 👉 Data Engineering Process - Step 1: Discovery
• 👉 Data Engineering Process - Step 5: Data Analysis and Visualization

---

🌟 Let's Connect & Build Together

Thanks for reading! 😊 If you found these architectural patterns helpful, let's connect and continue the conversation:

• GDG Broward: Join our local dev community for workshops and live tech sessions.
• Global AI Events: Connect with the community across global AI events.
• LinkedIn: Follow along for regular deep dives into software architecture and AI engineering.
• GitHub: Explore open-source projects, agents, and samples.
• YouTube: Watch recordings and step-by-step video tutorials.
• BlueSky / X / Twitter: Daily tech updates and architectural tips.

👉 Originally published at ozkary.com

Overview Large Language Models (LLMs) have become a foundational technology in modern data engineering and analytics. By leveraging their n...

Read Article →

Beyond the Persona: Mastering Agent Skills and Commands

Overview

Your agent's persona was never meant to carry your entire domain knowledge. As teams bring AI into real data engineering workflows—handling shifting ingestion feeds, inferring schemas, and standardizing lakehouse partitions—a single system instructions file quickly becomes a tangled mess of identity, procedure, and governance. It becomes difficult to maintain, costly to run, and impossible to trust in production pipelines.

The solution lies in classic software architecture principles: skills teach an agent what it knows, while commands give it something it can do. By decoupling domain judgment from deterministic execution, we build modular, testable, and cost-effective AI systems. A deterministic data pipeline command executes predictable steps automatically, invoking an AI skill only when genuine reasoning is required—such as when an unfamiliar payload arrives with an unmapped schema that threatens downstream tables.

In this post, we explore the architectural patterns behind modular agents, moving from an overburdened monolithic persona to a decoupled system that integrates human-in-the-loop security gates, Model Context Protocol (MCP) tools, and automated schema drift resolution.

🚀 Featured Open Source Projects

Explore these curated resources to level up your engineering skills. If you find them helpful, a ⭐️ is much appreciated!

🤖 AI Agents (ADK)

Focus: LLM Patterns, Skills & Commands, and Agentic Workflows

🏗️ Data Engineering

Focus: Real-world ETL & MTA Turnstile Data

📖 Data Engineering Process Fundamentals

Focus: Architectural patterns, hands-on data pipelines, and MTA turnstile case studies

---

💡 Contribute: Found a bug or have a suggestion? Open an issue and be part of the open source project.

🔗 Related Repository: AI Engineering

Explore the full implementation of the skills, commands, and agent definitions used in this workflow:
https://github.com/ozkary/ai-engineering/tree/main/adk

YouTube Video

👍 Subscribe to the channel to get notified on new events!

📅 Agenda

• The Monolith Agent — The Real-World Ingestion Problem: Why cramming prompts, credentials, and business logic into a single persona leads to context bloat, token burn, and fragility.
• Demystifying Agent Skills — Teaching Domain Judgment (What It Knows): Encapsulating bounded domain knowledge into modular markdown specifications and injecting them dynamically.
• Demystifying Agent Commands — Deterministic Execution (What It Does): Eliminating hallucinations by replacing open-ended LLM generations with reusable, strategy-backed executable commands.
• Managing Schema Drift — When Skills and Commands Collaborate: Orchestrating an event-driven Zero-ETL data lakehouse pipeline that catches schema deviations and enforces human-in-the-loop guardrails.
• Live Demonstration — From Schema Drift to Data Warehouse Provisioning: A hands-on walkthrough using Google Antigravity, ADK CLI, and BigQuery to detect drift, draft DDL, and provision tables safely.
• Key Takeaways & The Road Ahead: Core principles for architecting enterprise-grade agents and scaling them across distributed data platforms.

---

The Monolith Agent: The Anti-Pattern of Context Bloat

When engineers first build AI agents, the standard approach is straightforward: open a prompt file or system message configuration and draft a comprehensive persona. This single file frequently includes:

1. Agent Identity: Persona definition, voice, and conversational directives.
2. Domain Business Logic: Field definitions, schema contracts, validation rules, and business nuances.
3. Target System Rules: Naming conventions, SQL dialect specifics, partitioning strategies, and target warehouse instructions.
4. Tool Definitions & Credentials: Instructions on which APIs to invoke and how to format payloads.

While this works for simple prototypes, in an enterprise data pipeline it becomes a severe anti-pattern known as the Monolithic Agent.

graph TD
    subgraph Monolithic Agent Anti-Pattern
        A[Single System Prompt] --> B[Domain Knowledge: MTA / Telemetry]
        A --> C[Dialect Rules: BigQuery / Snowflake]
        A --> D[Execution Logic: Table DDL / Storage]
        A --> E[Governance & Security Constraints]
    end
    B & C & D & E --> F[Context Bloat & Token Burn]
    F --> G[Hallucinations & Pipeline Crashes]

The Pitfalls of the Monolith

• Context Bloat & Token Burn: Every single interaction transmits the entire instruction set. If your agent is processing hundreds of file events, resending thousands of prompt tokens per run incurs massive latency and unnecessary cost.
• Tight Vendor Coupling: Hardcoding BigQuery, Snowflake, and SQL Server conventions into the same instructions creates cross-system contamination and makes switching platforms difficult.
• Uncontrolled Hallucinations: When an agent is asked to write and execute code end-to-end within an expansive prompt, field names drift, data types mutate unpredictably, and schema consistency collapses.
• Violation of Established Software Principles: Software engineering long ago adopted Single Responsibility (SRP), Separation of Concerns (SoC), and Domain-Driven Design (DDD). Building AI agents requires the exact same discipline.

To solve this, we decouple the agent into Skills (declarative knowledge) and Commands (deterministic actions).

---

Demystifying Agent Skills: Teaching Domain Judgment (What It Knows)

A Skill represents domain expertise and semantic judgment. It answers the question: What does the agent know?

In practice, a skill is a structured specification (typically a modular Markdown manifest) that defines the rules, schema specifications, and analytical judgment required for a specific business domain.

graph TD
    subgraph Skills Architecture
        A[Agent Kernel]
        A -->|Injected via DI| B[MTA Transit Skill]
        A -->|Injected via DI| C[Factory Telemetry Skill]
        A -->|Injected via DI| D[BigQuery Governance Skill]
        B & C & D --> E[Domain Judgment & DDL Generation]
        E -.->|No Direct Execution| F[Structured Metadata Artifact]
    end

Core Characteristics of an Agent Skill

• Single-Responsibility Boundaries: A skill encapsulates one specific domain model (e.g., MTA subway transit feeds) or platform governance standard (e.g., BigQuery naming and partitioning standards).
• Declarative Knowledge, Not Execution: A skill never executes code, connects to databases, or alters infrastructure directly. It produces structured artifacts—such as an inferred schema, validation summary, or proposed Data Definition Language (DDL) statement.
• Agnostic Dynamic Loading: Rather than loading every skill simultaneously, the agent kernel remains lightweight. Using Dependency Injection (DI), the runtime injects only the skills relevant to the active workflow.

# Example: Injecting targeted skills into a lightweight SkillAgent
agent = SkillAgent(
    skills=[
        MtaTransitSkill(),        # Domain-specific schema & validation rules
        BigQueryGovernanceSkill() # Target warehouse conventions & partitioning rules
    ]
)

Domain Skills vs. Platform Governance Skills: A domain skill (like MtaTransitSkill) defines business entities, field requirements, and expected schema contracts. A platform governance skill (like BigQueryGovernanceSkill) defines engine-specific formatting standards—such as snake_case column conventions, timestamp parsing constraints, and lakehouse partitioning rules. Neither skill executes database queries; they purely supply the declarative rules the agent needs to synthesize compliant DDL.

By isolating domain and platform rules into distinct skills, teams can refine schema requirements and governance guidelines without modifying the underlying agent execution engine.

---

Demystifying Agent Commands: Deterministic Execution (What It Does)

A Command represents deterministic execution. It answers the question: What can the agent do?

In production data engineering, data warehouse modifications, bucket operations, and network transactions must never be left to stochastic LLM text generation. If you ask an LLM to generate and run an ALTER TABLE statement directly five times, you risk getting five subtly different variations—or worse, a hallucinated column that breaks downstream dashboards.

Commands provide absolute predictability by wrapping operations in testable, deterministic code that implements the Strategy Pattern.

graph TD
    subgraph Commands Architecture
        CMD[Agent Command] --> STRAT[WarehouseTableStrategy]
        STRAT --> BQ[BigQuery Strategy]
        STRAT --> SF[Snowflake Strategy]
        STRAT --> SQL[SQL Server Strategy]
        BQ --> MCP[Model Context Protocol MCP]
        SF --> MCP
        SQL --> MCP
    end

Why Commands Matter

1. Guaranteed Determinism: The command implements fixed logic (e.g., GetFileSampleCommand, CreateExternalTableCommand). The LLM does not improvise the execution path.
2. Strategy Pattern for Portability: The agent interacts with an abstract WarehouseTableStrategy. Whether the target is BigQuery, Snowflake, or Databricks, the agent invokes the same interface while the underlying strategy handles engine-specific semantics.
3. Integration with Model Context Protocol (MCP): Commands communicate with target resources through standardized MCP servers, ensuring authentication, audit logging, and connection pooling are maintained centrally.
4. Deterministic Routing via Configuration: Decisions about which data warehouse receives which feed are governed by deterministic configuration files (e.g., routing.yaml), rather than probabilistic prompt suggestions:

# routing.yaml: Deterministic ingestion targets
domains:
  mta_transit:
    target_warehouse: "bigquery"
    dataset: "transit_staging"
    strategy: "external_table_v2"
  factory_telemetry:
    target_warehouse: "snowflake"
    database: "iot_telemetry"

---

Managing Schema Drift: When Skills and Commands Collaborate

To understand how skills and commands interact in production, consider a real-world scenario from the Metropolitan Transportation Authority (MTA) New York City transit turnstile data—the canonical dataset explored throughout my book, Data Engineering Process Fundamentals.

The Problem: Incoming Schema Drift

In a modern Zero-ETL lakehouse, raw transit feeds land in cloud storage buckets as CSV or Parquet files. BigQuery queries these files directly via external tables, avoiding heavy ETL ingestion jobs.

When a new file format arrives (Version 2) containing altered column names, restructured timestamps, or additional device counters, traditional automated pipelines fail catastrophically. Unmapped columns crash downstream queries, while naive auto-ingestion can pollute warehouse tables with broken types.

sequenceDiagram
    autonumber
    actor Lake as Data Lake (Storage Trigger)
    participant Agent as SkillAgent Core (Skills & Commands)
    participant Gate as Security Gate (Pre-Hook & HITL)
    participant BQ as Data Warehouse (BigQuery via MCP)

    Lake->>Agent: Event: New File Dropped (turnstile_v2.csv)
    Note over Agent: 1. GetFileSampleCommand fetches 5-row sample<br/>2. MTA Skill detects schema drift<br/>3. Skill synthesizes normalized V2 DDL
    Agent->>Gate: Dispatch CreateExternalTableCommand(DDL)
    Note over Gate: Pre-Use Hook intercepts tool call<br/>Agent placed on hold (Suspended)
    Gate->>Gate: Security Prompt: Approve DDL Mutation?
    alt Human Approves (yes)
        Gate->>BQ: Execute DDL via BigQuery MCP Tool
        BQ-->>Agent: External Table Created (ext_mta_turnstile_v2 live)
        Note over Agent,BQ: Lakehouse reads V2 immediately at wire speed
    else Human Rejects (no)
        Gate-->>Agent: Mutation Aborted (Workflow Ends Safely)
        Note over BQ: Fail-Closed: Table is Never Created
    end

Deterministic Inspection & Schema Normalization

A critical design requirement of this architecture is deterministic inspection:

• Bounded Sampling, Not Full Payloads: Passing massive multi-gigabyte raw files directly into an LLM prompt burns enormous tokens, risks context truncation, and introduces latency. Instead, the agent invokes a deterministic command (GetFileSampleCommand) to extract a bounded slice of records (the header plus the first several rows).
• Domain-Driven Schema Normalization: The raw sample is evaluated by the domain skill (MtaTransitSkill). The skill contains canonical schema rules, column mappings, and enterprise formatting conventions. When schema drift is detected, the skill does not simply panic or halt the pipeline. Instead, it normalizes the new external table schema—mapping renamed raw columns (control_area, remote_unit, sub_device_id) to standard types and generating a compliant DDL statement for BigQuery.
• Continuous Ingestion Without Pipeline Interruption: Because the skill normalizes the new format into a clean, parallel external table (ext_mta_turnstile_v2), the data lakehouse can consume the new feed immediately without breaking existing V1 analytics or crashing scheduled batch jobs.

The Human-in-the-Loop (HITL) Gate

While the skill has the domain intelligence to normalize schemas, autonomous AI must never possess unchecked authority to modify production database structures.

To enforce security:

1. Pre-Use Hooks Intercept Mutations: When the agent attempts to run CreateExternalTableCommand, the command dispatches an MCP tool call. The SecureToolAgent layer evaluates this call against registered Pre-Use Hooks. Read-only queries pass freely, but state-mutating operations (DDL/DML) are intercepted before they ever reach the database server.
2. Agent Put On Hold: The pre-use hook suspends the agent's execution. The agent is placed on hold in a waiting state while a structured alert is surfaced to the human operator with the proposed DDL and target metadata.
3. Approval (yes): If the human verifies the DDL and approves, the hook unblocks execution. The command forwards the query through the BigQuery MCP tool, provisioning the new table seamlessly.
4. Rejection (no): If the human rejects the change or detects an anomaly, the process halts immediately and the table is never created. This fail-closed model guarantees that hallucinations, schema corruption, or unauthorized alterations cannot reach the analytical warehouse.

---

Live Demonstration: From Schema Drift to Warehouse Provisioning

During the live session, we walked through this pattern using Google Antigravity and the Agent Development Kit (ADK) CLI, operating on the open-source repository.

1. The SkillAgent Implementation

The agent class inherits from SecureToolAgent, which equips it with standard MCP tool connectivity and security controls. We then inject domain skills and executable commands:

class SkillAgent(SecureToolAgent):
    """
    Modular Agent demonstrating dynamic skill injection
    and deterministic command execution.
    """
    def __init__(self, skills=None, commands=None, routing_config="routing.yaml"):
        super().__init__()
        self.skills = skills or []
        self.commands = commands or []
        self.routing_config = load_routing(routing_config)

    def register_skill(self, skill):
        self.skills.append(skill)
        # Mounts domain specifications into agent knowledge boundary
        self.mount_knowledge(skill.get_manifest())

    def register_command(self, command):
        self.commands.append(command)
        # Binds executable tool signatures to the agent runtime
        self.bind_tool(command.get_tool_signature())

2. Simulating Schema Ingestion & The ADK CLI

To trigger the agent workflow, we invoke the Agent Development Kit (ADK) CLI:

adk agent run skill-agent \
  --event "storage.object.created" \
  --bucket "mta-turnstile-lake" \
  --path "feeds/v2/turnstile_2026_09.csv"

Understanding the ADK CLI Parameters

• skill-agent: The target agent profile to instantiate. The runtime configures the agent kernel and injects its specified skills (MtaTransitSkill, BigQueryGovernanceSkill) and commands (GetFileSampleCommand, CreateExternalTableCommand).
• --event "storage.object.created": Specifies the incoming event type. This matches standard cloud lifecycle event definitions (e.g., Google Cloud Storage Object Finalize, AWS S3 s3:ObjectCreated, or Azure Event Grid).
• --bucket "mta-turnstile-lake": The target storage bucket in the data lake where the raw payload arrived.
• --path "feeds/v2/turnstile_2026_09.csv": The relative URI key of the newly dropped file.

Production vs. Local Emulation: In a live enterprise deployment, developers do not manually trigger terminal commands. Instead, automated cloud infrastructure listens for file drops: a Cloud Storage bucket event publishes to Google Cloud Pub/Sub or Eventarc, which invokes a serverless container (such as Google Cloud Run or a Cloud Function) running the ADK agent runtime. The CLI command faithfully replicates this exact production event-driven behavior for local testing, CI/CD verification, and interactive playbook orchestration with Google Antigravity.

Deterministic Inspection in Action

Upon receiving the event, the agent executes GetFileSampleCommand. Rather than streaming entire megabytes into the prompt, it fetches a bounded 5-row sample. The MTA domain skill evaluates the sample against known schemas, identifies the drift, and synthesizes a normalized DDL statement:

[INFO] Executing: GetFileSampleCommand(uri="gs://mta-turnstile-lake/feeds/v2/turnstile_2026_09.csv")
[INFO] Sample loaded: 5 rows retrieved.
[WARN] Schema Drift Detected!
       Missing Baseline Columns: [C/A, UNIT, SCP]
       Detected V2 Columns:      [control_area, remote_unit, sub_device_id, net_entries, net_exits]
[INFO] Domain Skill generated BigQuery External Table DDL:
       CREATE OR REPLACE EXTERNAL TABLE `transit_staging.ext_mta_turnstile_v2`
       (
           control_area STRING,
           remote_unit STRING,
           sub_device_id STRING,
           event_timestamp TIMESTAMP,
           net_entries INT64,
           net_exits INT64
       )
       OPTIONS (
           format = 'CSV',
           uris = ['gs://mta-turnstile-lake/feeds/v2/*.csv'],
           skip_leading_rows = 1
       );

By normalizing the new external table schema to match lakehouse partitioning and naming conventions, the skill guarantees that the pipeline can consume the new file format without failing or interrupting existing workloads.

3. Human Approval (HITL) and Execution

Before mutating the data warehouse, the agent initiates table provisioning via CreateExternalTableCommand. However, because the agent inherits from SecureToolAgent, the tool call is intercepted by a Pre-Use Hook.

The Agent is Placed on Hold

The hook determines that creating or replacing an external table constitutes an infrastructure mutation requiring human authorization. The agent's execution is immediately suspended (put on hold), and an interactive prompt is rendered:

[SECURITY GATE] Action requires administrative authorization:
Mutation: Create BigQuery External Table 'ext_mta_turnstile_v2'
Do you approve the execution of this DDL? (yes/no): yes

Two Distinct Security Outcomes:

• Approval Path (yes): The operator reviews the proposed DDL and grants approval. The agent resumes execution, and the pre-use hook releases the intercepted command. The command invokes the BigQuery MCP tool, executes the DDL, and provisions ext_mta_turnstile_v2. A quick refresh of the BigQuery console confirms that the V2 table is live and queryable at wire speed.
• Rejection Path (no): If the operator detects an irregularity or rejects the change, the pre-use hook terminates the workflow immediately. The process ends, and the table is never created. This fail-closed architecture ensures that no unauthorized or broken schemas can ever compromise analytical pipelines.

4. Beyond the Demo: Multi-Version Coexistence & Skill Evolution

While the live demonstration successfully created the V2 external table, provisioning a new schema is not the end of the architectural story—it represents the beginning of an ongoing, production-grade data lifecycle:

Multi-Version Coexistence in Production

With ext_mta_turnstile_v2 successfully deployed, the data warehouse operates in a live multi-version coexistence state. Downstream analytics, scheduled dashboards, and legacy ETL pipelines reading the original V1 table continue running without downtime. New applications and modernized reports can simultaneously begin querying the V2 schema. Over time, as data producers migrate and incoming V1 storage events cease, the legacy V1 external table can be gracefully deprecated and retired without risking breaking changes.

Continuous Evolution: The Inevitable Arrival of V3

Data feeds continuously evolve. Over time, an upstream vendor or sensor system might drop an unannounced Version 3 (v3) payload into the lakehouse. This arrival triggers the exact same event-driven loop:

1. The storage trigger detects turnstile_2026_v3.csv.
2. The agent executes GetFileSampleCommand to inspect the sample deterministically.
3. The domain skill attempts to normalize the schema and synthesize an external table DDL.

When Complex Drift Causes the Agent to Fail

Not all schema changes are simple column additions. In real-world enterprise environments, a V3 payload might introduce complex edge cases—such as deeply nested JSON structures, conflicting timestamp formats, or ambiguous domain concepts that defy existing heuristic rules.

In these scenarios, the LLM-driven skill may fail to produce an acceptable or compliant schema proposal. It might hallucinate a data type, mishandle a nested field, or violate lakehouse partitioning rules.

The HITL Gate as a Circuit Breaker for Skill Updates

This failure mode is precisely why the Human-in-the-Loop (HITL) gate is indispensable:

• Execution Halted: When the human operator inspects the proposed V3 DDL in the security prompt and recognizes that the schema proposal is flawed or unacceptable, they enter no.
• Zero Impact on Production: The pre-use hook aborts the operation immediately. The flawed table is never created, and the data warehouse remains protected.
• Skill Evolution: Rather than modifying the agent's core source code or hacking together manual database scripts, the engineering team updates the domain skill manifest (MtaTransitSkill). By adding explicit domain heuristics, specifying parsing patterns for the new nested fields, or clarifying type conversion rules in the skill's Markdown specification, they close the knowledge gap.
• Re-running with Enhanced Intelligence: Once the skill is updated, re-running the agent enables it to correctly normalize the V3 schema, generate flawless DDL, pass the human inspection gate, and provision the new table cleanly.

Skills and Commands as First-Class SDLC Artifacts: Creating new skills or updating existing skills and commands is not a sign of pipeline failure—it is the natural, expected lifecycle of an agentic data system. Treating skills as version-controlled specifications ensures that as data domains evolve, agent intelligence grows incrementally alongside the business.

---

Key Takeaways & The Road Ahead

Refactoring from a monolithic agent to modular skills and commands bridges the gap between fragile AI experiments and reliable production data systems.

Concept	Monolithic Agent	Decoupled Skills & Commands
Domain Knowledge	Crammed into one prompt	Modular Markdown manifests (Skills)
Execution	Stochastic LLM query generation	Reusable, strategy-backed classes (Commands)
Token Cost	High (full prompt on every call)	Low (load only active domain and tools)
Portability	Hardcoded to one database	Abstracted across engines (BigQuery, Snowflake)
Safety & Governance	Uncontrolled side-effects	Pre-Tool Hooks & Human-in-the-Loop gates

Guiding Principles for Your Next Agent

1. Skills Define What the Agent Knows: Keep them declarative. Use skills to codify domain heuristics, validation rules, and schema contracts.
2. Commands Define What the Agent Does: Keep them deterministic. Use proven design patterns (Strategy, Factory) and route execution through Model Context Protocol (MCP) tools.
3. Keep the Kernel Agnostic: Build a lightweight core engine and inject capabilities dynamically using Dependency Injection.
4. Guard Critical Operations: Never allow an autonomous agent to execute destructive DDL or DML without human review. Use pre-tool hooks to enforce governance.

By establishing these boundaries, data teams can confidently harness generative AI to handle real-world data entropy while keeping pipelines robust, secure, and predictable.

---

🌟 Let's Connect & Build Together

Thanks for reading! 😊 If you found these architectural patterns helpful, let's connect and continue the conversation:

• GDG Broward: Join our local dev community for workshops and live tech sessions.
• Global AI Events: Connect with the community across global AI events.
• LinkedIn: Follow along for regular deep dives into software architecture and AI engineering.
• GitHub: Explore open-source projects, agents, and samples.
• YouTube: Watch recordings and step-by-step video tutorials.
• BlueSky / X / Twitter: Daily tech updates and architectural tips.

👉 Originally published at ozkary.com

Overview Your agent's persona was never meant to carry your entire domain knowledge. As teams bring AI into real data engineering work...

Read Article →