Agents & Reasoning: When LLMs Learn to Think Before They Speak

9 min read
#AI-Agents #ReasoningEngine #LangGraph #CrewAI #MultiAgent #LLM

Agents & Reasoning: When LLMs Learn to Think Before They Speak

Your LLM can write code, answer questions, and generate content. But can it plan a complex project, break it down, delegate tasks, and coordinate results? That's where agents come in — and things get REALLY interesting.

The Reality Check

Single LLM calls are like having one genius locked in a room. Agents? They're like having an entire team with specialized skills, memory, and the ability to use tools.

Remember how we talked about prompt engineering and user intent in Part 1? That's your foundation. But when problems get complex — like analyzing a dataset, writing code, debugging it, and then generating a report — you need something more powerful.

Here's the thing: Agent vs Workflow, they are essentially the same. Someone renamed the 'workflow' and became rich. Therefore, we will use 'agent' wording from now on.

From Chatbots to Agents: The Evolution

Traditional LLMs are reactive. You ask, they answer. Done. But real work is never that simple.

Take migrating from T-SQL stored procedures to DuckDB dbt. You need to:

  1. Parse complex T-SQL logic with CTEs, temp tables, and cursors
  2. Convert procedural code to dbt's declarative SQL models
  3. Transform SQL Server functions to DuckDB equivalents
  4. Restructure dependencies into dbt's DAG pattern
  5. Validate output data matches the original procedures

Each step depends on the previous one's results. That's where agents come in — they can loop back, self-correct, and adapt their approach based on what they discover.

The reasoning gap is real. Ask GPT-4 to "convert this stored procedure to dbt," and you'll get a generic SELECT statement. Ask an agent? It'll first parse your T-SQL logic, identify cursor patterns that need window functions, map SQL Server functions to DuckDB equivalents, create modular staging and mart models, and validate the outputs match.

Where agents absolutely crush it for database migrations:

  • Parsing complex stored procedures with nested logic and dependencies
  • Converting procedural patterns (cursors, loops) to set-based SQL
  • Mapping hundreds of SQL Server functions to DuckDB equivalents
  • Iteratively validating that migrated models produce identical results

Agent Architecture Patterns (Single Agent)

ReAct: Reasoning + Acting

The ReAct pattern is beautifully simple. Instead of thinking OR acting, your agent alternates between reasoning about what to do next and actually doing it:

  1. Reasoning (thought) - The agent thinks about what to do next
  2. Acting (action) - The agent takes a concrete action
  3. Observing (observation) - The agent sees the results and uses them for the next reasoning step
# ReAct loop for T-SQL to dbt conversion
def convert_stored_procedure(tsql_proc):
    # INITIAL REASONING
    thought = llm.think(f"Analyzing T-SQL procedure: {tsql_proc.name}")
    # Agent thinks: "This proc has 3 CTEs, 2 temp tables, and a cursor - needs staging models"

    # INITIAL ACTION  
    action = parse_tsql_structure(tsql_proc)
    observation = {"ctes": 3, "temp_tables": 2, "cursors": 1}

    # ITERATIVE LOOP
    while not conversion_complete(observation):
        # REASONING: What should I do next based on what I observed?
        thought = llm.think(f"Given {observation}, what's the next conversion step?")
        # "Convert cursor to window functions, temp tables to CTEs"

        # ACTING: Take the action identified in reasoning
        action = convert_next_component(thought)
        observation = execute_conversion(action)
        # "Successfully converted cursor to ROW_NUMBER() OVER()"
    
    return generate_dbt_model(observation)

Plan-and-Execute

The Plan-and-Execute pattern, formalized in the research (Plan-and-Solve Prompting), separates strategic planning from tactical execution. This two-phase approach excels when tasks have complex dependencies or require resource coordination.

T-SQL procedures with dependencies need upfront planning. The agent creates a migration roadmap, identifies dependencies, then executes systematically.

# Planning T-SQL to dbt migration strategy
def plan_migration_strategy(tsql_database):
    # Step 1: Analyze all stored procedures and dependencies
    migration_plan = llm.create_migration_plan(tsql_database)
    # Plan: "Convert base tables first, then staging procs, then reporting procs"
    
    # Step 2: Execute migration in dependency order
    migrated_models = []
    for proc in migration_plan.ordered_procedures:
        result = migrate_procedure(proc, dependencies=migrated_models)
        migrated_models.append(result)
        
        # Adapt plan if complex logic discovered
        if result.has_dynamic_sql or result.has_nested_cursors:
            migration_plan = llm.revise_plan(migration_plan, result)
            # "Found dynamic SQL - need intermediate Python model"
    
    return create_dbt_project(migrated_models)

Self-Reflection

Or Reflexion, enables iterative improvement through self-critique. Unlike traditional one-shot generation, reflective agents evaluate their own outputs, identify flaws, and refine their approach across multiple iterations.

Agents that critique their own dbt models. They generate, validate against original T-SQL, and iterate until the logic matches perfectly.

# Self-reflection for dbt model validation
def validate_dbt_conversion(tsql_proc, initial_dbt_model):
    current_model = initial_dbt_model
    
    while True:
        # Compare outputs between T-SQL and dbt
        validation = llm.compare_outputs(tsql_proc, current_model)
        
        if validation.data_match_percentage > 99.5:
            break  # Near-perfect match achieved
            
        # Identify specific logic mismatches
        issues = validation.find_logic_differences()
        # "DATEADD function converted incorrectly"
        # "GROUP BY rollup not preserved"
        
        # Self-correct the dbt model
        current_model = llm.fix_dbt_model(current_model, issues)
    
    return current_model
Run this example yourself 🔧

Script: 1_tsql_to_dbt_agent.py

Command:

uv run 1_tsql_to_dbt_agent.py

Expected Output:

View markdown file in new tab 

🚀 T-SQL to dbt Migration Agent Demo

📊 Step 1: Analyzing T-SQL procedure...
{
  "error": "Failed to parse analysis",
  "raw": "```json\n{\n  \"procedure_name\": \"sp_customer_sales_analysis\",\n  \"components\": {\n    \"ctes\": [\n      \"CustomerOrders\",\n      \"CustomerSegments\"\n    ],\n    \"temp_tables\": [\n      \"#CustomerMetrics\"\n    ],\n    \"cursors\": [\n      {\n        \"name\": \"customer_cursor\",\n        \"query\": \"SELECT customer_id FROM #CustomerMetrics WHERE total_sales > 1000\"\n      }\n    ],\n    \"updates\": [\n      \"UPDATE #CustomerMetrics SET total_sales = total_sales * 1.1 WHERE customer_id = @customer_id\"\n    ],\n    \"dynamic_sql\": false\n  },\n  \"business_logic\": \"Aggregates completed orders per customer (total sales, order count, last order date), classifies customers into segments (though the segment CTE is unused), stores the aggregates in a temp table, then boosts total sales by 10% for customers whose sales exceed $1,000 using a cursor-based loop, and finally returns the enriched metrics.\",\n  \"data_flow\": [\n    \"1. CTE `CustomerOrders` aggregates orders by customer_id where order_status = 'completed'.\",\n    \"2. CTE `CustomerSegments` derives a segment label based on total_sales but is not referenced later.\",\n    \"3. Insert aggregated rows from `CustomerOrders` into temp table `#CustomerMetrics`.\",\n    \"4. Open cursor `customer_cursor` to iterate over rows in `#CustomerMetrics` where total_sales > 1000.\",\n    \"5. For each cursor row, update that row\u2019s total_sales by multiplying by 1.1.\",\n    \"6. Close and deallocate the cursor.\",\n    \"7. Return all rows from `#CustomerMetrics`.\"\n  ],\n  \"complexity\": \"moderate\",\n  \"migration_challenges\": [\n    \"Replacing the cursor with a set\u2011based UPDATE (e.g., UPDATE #CustomerMetrics SET total_sales = total_sales * 1.1 WHERE total_sales > 1000).\",\n    \"Eliminating the unused `CustomerSegments` CTE or integrating its logic into the final model.\",\n    \"Translating the temp table into a dbt staging or intermediate model, ensuring the schema matches the original temp table.\",\n    \"Ensuring the final SELECT is represented as a dbt model that can be materialized (view or table).\",\n    \"Handling the business rule that boosts sales only for customers above a threshold without cursor overhead.\",\n    \"Testing that the segment classification logic (if needed later) is preserved in the dbt transformation.\"\n  ]\n}\n```"
}

🔄 Step 2: Converting to dbt models...
{
  "error": "Failed to generate dbt models",
  "raw": "```json\n{\n  \"models\": [\n    {\n      \"name\": \"intermediate_customer_orders\",\n      \"type\": \"intermediate\",\n      \"materialization\": \"view\",\n      \"sql\": \"SELECT\\n  customer_id,\\n  SUM(order_amount) AS total_sales,\\n  COUNT(*) AS order_count,\\n  MAX(order_date) AS last_order_date\\nFROM {{ ref('orders') }}\\nWHERE order_status = 'completed'\\nGROUP BY customer_id\",\n      \"tests\": [\n        \"unique(customer_id)\",\n        \"not_null(customer_id)\",\n        \"not_null(total_sales)\",\n        \"not_null(order_count)\",\n        \"not_null(last_order_date)\",\n        \"accepted_values(order_status, ['completed'])\"\n      ],\n      \"documentation\": \"Aggregates completed orders per customer: total sales, order count, and last order date.\"\n    },\n    {\n      \"name\": \"mart_customer_metrics\",\n      \"type\": \"mart\",\n      \"materialization\": \"view\",\n      \"sql\": \"WITH base AS (\\n  SELECT\\n    customer_id,\\n    total_sales,\\n    order_count,\\n    last_order_date\\n  FROM {{ ref('intermediate_customer_orders') }}\\n)\\nSELECT\\n  customer_id,\\n  CASE WHEN total_sales > 1000 THEN total_sales * 1.1 ELSE total_sales END AS total_sales,\\n  order_count,\\n  last_order_date\\nFROM base\",\n      \"tests\": [\n        \"unique(customer_id)\",\n        \"not_null(customer_id)\",\n        \"not_null(total_sales)\",\n        \"not_null(order_count)\",\n        \"not_null(last_order_date)\",\n        \"accepted_range(total_sales, 0, null)\"\n      ],\n      \"documentation\": \"Final customer metrics with a 10% boost applied to total sales for customers exceeding $1,000. Mirrors the cursor\u2011based update logic from the original T\u2011SQL procedure.\"\n    }\n  ],\n  \"conversion_notes\": [\n    \"Replaced the cursor and temp table with a single set\u2011based UPDATE logic inside the final view.\",\n    \"The unused CustomerSegments CTE was omitted; its logic can be added later if needed.\",\n    \"All staging and intermediate logic is expressed as declarative SQL views, enabling dbt\u2019s dependency graph.\",\n    \"The final mart is materialized as a view; change to `table` or `incremental` if persistence or performance is required.\",\n    \"Assumes a source model named `orders` exists; adjust the `ref('orders')` call if the source name differs.\"\n  ],\n  \"duckdb_optimizations\": [\n    \"Store the final mart as a Parquet table for efficient columnar reads: `{{ config(materialized='table', file_format='parquet') }}` if persistence is desired.\",\n    \"Enable DuckDB's `SET memory_limit='4GB';` to allow larger in\u2011memory operations during compilation.\",\n    \"Use `SET enable_bloom_filter = true;` to speed up joins on `customer_id` if the dataset grows large.\",\n    \"Leverage DuckDB's `SET enable_vectorized_execution = true;` for faster aggregation.\"\n  ],\n  \"validation_queries\": [\n    \"/* Validate row counts match original procedure */\\nSELECT COUNT(*) AS proc_rows FROM {{ ref('mart_customer_metrics') }};\\n\\n/* Validate total sales sum */\\nSELECT SUM(total_sales) AS proc_total_sales FROM {{ ref('mart_customer_metrics') }};\\n\\n/* Compare with a direct T\u2011SQL execution result set (run separately) */\\nSELECT\\n  COUNT(*) AS expected_rows,\\n  SUM(total_sales) AS expected_total_sales\\nFROM (\\n  /* Replace with the original procedure's output table or temp table */\\n  SELECT * FROM #CustomerMetrics\\n) AS expected;\",\n    \"/* Spot\u2011check a few customers */\\nSELECT\\n  customer_id,\\n  total_sales\\nFROM {{ ref('mart_customer_metrics') }}\\nWHERE customer_id IN (1, 2, 3);\"\n  ]\n}\n```"
}

✅ Step 3: Validating conversion...
{
  "error": "Failed to validate",
  "raw": "```json\n{\n  \"logic_preserved\": true,\n  \"confidence_score\": 0.92,\n  \"matched_transformations\": [\n    \"Aggregation of completed orders per customer (total_sales, order_count, last_order_date)\",\n    \"10% boost to total_sales for customers whose original total_sales > 1,000\",\n    \"Set\u2011based implementation replacing the cursor\",\n    \"Use of a view (or table) instead of a temp table to expose the final metrics\"\n  ],\n  \"discrepancies\": [\n    \"Column name mismatch: original uses `order_total`, dbt model references `order_amount`\",\n    \"The unused `CustomerSegments` CTE is omitted in the dbt conversion (not a functional loss but a difference in the model graph)\",\n    \"Materialization difference: original uses a temp table that is dropped automatically; dbt materializes as a view by default, which may affect persistence and performance\",\n    \"Potential precision/scale differences \u2013 original defines `total_sales` as DECIMAL(10,2); dbt does not explicitly set precision, relying on the source column type\",\n    \"No explicit handling of NULLs or zero totals \u2013 both implementations assume the source data is clean\"\n  ],\n  \"missing_logic\": [\n    \"Segment classification logic (VIP/Premium/Standard) is present in the original CTE but not used in the final output; if it is required downstream, it should be added to the dbt mart\",\n    \"Any side\u2011effects of the cursor (e.g., row\u2011by\u2011row locking) are not reproduced, but they are not part of the business logic\"\n  ],\n  \"recommendations\": [\n    \"Align column names: either rename `order_amount` in the source model to `order_total` or alias it in the intermediate model to avoid confusion.\",\n    \"Add an explicit `DECIMAL(10,2)` cast for `total_sales` in the mart to guarantee the same precision as the original procedure.\",\n    \"If persistence or incremental loading is desired, change the mart materialization to `table` or `incremental` and add appropriate `unique_key` and `incremental_strategy` configs.\",\n    \"Include the `CustomerSegments` logic in a separate model if segment information is needed downstream.\",\n    \"Add a test to verify that customers with `total_sales` exactly 1,000 are not boosted, and those just above 1,000 are.\",\n    \"Consider adding a `not_null` test on `order_total`/`order_amount` in the source model to guard against null aggregation results.\"\n  ],\n  \"test_scenarios\": [\n    \"Verify that the row count of the dbt mart matches the row count returned by the original procedure.\",\n    \"Compare the sum of `total_sales` from the dbt mart with the sum returned by the procedure.\",\n    \"Spot\u2011check a set of customers (e.g., IDs 1, 2, 3) to ensure the boost logic is applied correctly.\",\n    \"Test edge cases: a customer with `total_sales` exactly 1,000 (should not be boosted) and a customer with 1,000.01 (should be boosted).\",\n    \"Run the procedure and the dbt model on a dataset with no completed orders to confirm both return zero rows.\",\n    \"Measure performance on a large dataset to confirm that the set\u2011based view is faster than the cursor implementation.\"\n  ]\n}\n```"
}

🎯 Migration Complete!
Confidence Score: 0.0%

Multi-Agent Systems (MAS)

MAS means that multiple specialized agents working together can handle complex migrations which would overwhelm a single agent. Think of it like a migration team — T-SQL experts, dbt developers, DuckDB optimizers, and validators working in concert.

Many popular frameworks like LangGraph orchestrate these migrations. Each agent brings specialized expertise:

# LangGraph team for T-SQL to dbt migration
from langgraph.graph import StateGraph, START, END
from typing import TypedDict

class MigrationState(TypedDict):
    tsql_procedure: str
    parsed_logic: dict
    dbt_models: dict
    optimizations: dict
    current_agent: str
    
def tsql_analyst(state: MigrationState) -> MigrationState:
    """Expert in SQL Server internals, CTEs, cursors, and dynamic SQL"""
    parsed_logic = analyze_tsql_structure(state["tsql_procedure"])
    return {**state, "parsed_logic": parsed_logic, "current_agent": "analyst"}

def dbt_developer(state: MigrationState) -> MigrationState:
    """Specializes in modern data transformation patterns and dbt best practices"""
    dbt_models = convert_to_dbt_models(state["parsed_logic"])
    return {**state, "dbt_models": dbt_models, "current_agent": "developer"}

def duckdb_optimizer(state: MigrationState) -> MigrationState:
    """Deep knowledge of columnar storage, parquet files, and in-memory processing"""
    optimizations = optimize_for_duckdb(state["dbt_models"])
    return {**state, "optimizations": optimizations, "current_agent": "optimizer"}

# Build migration workflow
workflow = StateGraph(MigrationState)
workflow.add_node("analyze", tsql_analyst)
workflow.add_node("convert", dbt_developer)
workflow.add_node("optimize", duckdb_optimizer)

# Define sequential flow
workflow.add_edge(START, "analyze")
workflow.add_edge("analyze", "convert")
workflow.add_edge("convert", "optimize")
workflow.add_edge("optimize", END)

# Execute migration
app = workflow.compile()
result = app.invoke({
    "tsql_procedure": "CREATE PROCEDURE sp_customer_analytics AS ...",
    "parsed_logic": {},
    "dbt_models": {},
    "optimizations": {},
    "current_agent": ""
})
Run this example yourself 🔧

Script: 2_multi_agent_workflow.py

Command:

uv run 2_multi_agent_workflow.py

Expected Output:

View markdown file in new tab 

🚀 LangGraph Multi-Agent T-SQL to dbt Migration

🎯 Starting LangGraph migration workflow...

📊 TSQLAnalyst analyzing stored procedure...
🔄 DbtDeveloper creating dbt models...
⚡ DuckDBOptimizer optimizing for columnar analytics...

==================================================
📋 LANGGRAPH MIGRATION RESULTS
==================================================

✅ Migration completed successfully!

🔍 Final Agent: duckdb_optimizer

📊 Parsed Logic:
{
  "logic_components": [
    "Temporal aggregation using DATEADD/DATEDIFF functions",
    "SUM aggregation for monthly sales",
    "Window functions for ranking and rolling calculations"
  ],
  "data_sources": [
    "orders"
  ],
  "transformations": [
    "Monthly sales aggregation by customer",
    "Ranking customers by monthly sales",
    "3-month rolling sales calculation"
  ],
  "complex_patterns": {
    "has_cursor": false,
    "has_dynamic_sql": false,
    "has_temp_tables": true,
    "has_while_loops": false
  },
  "dependencies": [
    "orders table"
  ],
  "conversion_complexity": "medium"
}

🔄 Generated dbt Models:
{
  "staging_models": [
    {
      "name": "stg_orders",
      "sql": "SELECT * FROM {{ source('raw', 'orders') }} WHERE order_status = 'completed'",
      "materialization": "view"
    }
  ],
  "intermediate_models": [
    {
      "name": "int_monthly_sales",
      "sql": "SELECT date_trunc('month', order_date) as month_start, customer_id, SUM(order_total) as monthly_sales FROM {{ ref('stg_orders') }} GROUP BY 1, 2",
      "materialization": "table"
    }
  ],
  "mart_models": [
    {
      "name": "mart_sales_summary",
      "sql": "SELECT month_start, customer_id, monthly_sales, ROW_NUMBER() OVER (PARTITION BY month_start ORDER BY monthly_sales DESC) as rank, SUM(monthly_sales) OVER (PARTITION BY customer_id ORDER BY month_start ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as rolling_3month_sales FROM {{ ref('int_monthly_sales') }} WHERE monthly_sales > 0",
      "materialization": "table"
    }
  ],
  "tests": [
    "unique(mart_sales_summary, [month_start, customer_id])",
    "not_null(mart_sales_summary, [customer_id, monthly_sales])"
  ],
  "documentation": "Sales summary mart with customer ranking and rolling calculations"
}

⚡ DuckDB Optimizations:
{
  "columnar_optimizations": {
    "mart_sales_summary": [
      "month_start",
      "customer_id", 
      "monthly_sales",
      "rank",
      "rolling_3month_sales"
    ]
  },
  "function_conversions": [
    {
      "sql_server_function": "DATEADD(month, DATEDIFF(month, 0, order_date), 0)",
      "duckdb_function": "date_trunc('month', order_date)",
      "example": "date_trunc('month', '2024-01-15') = '2024-01-01'"
    }
  ],
  "memory_settings": {
    "max_memory": "4GB",
    "temp_directory": "/tmp/duckdb",
    "threads": 4
  },
  "performance_tips": [
    "Use parquet format for intermediate models",
    "Partition large fact tables by month",
    "Create indexes on frequently joined columns"
  ],
  "parquet_export": [
    "COPY mart_sales_summary TO 'output/sales_summary.parquet' (FORMAT PARQUET)"
  ]
}

🎯 LangGraph workflow completed with 0 errors

The agents collaborate by passing parsed logic, suggesting optimizations, and validating each other's work.

Looking great? Yes indeed, but be careful with pitfalls! The key to effective isn't throwing more agents at the problem — it's finding the sweet spot between parallelization and coordination overhead. Stick to a good number of specialized agents, run independent tasks in parallel while keeping dependent workflows sequential, batch similar procedures through the same agent to reduce context switching, cache common patterns between agents, and use early exit conditions when confidence thresholds are met.

Too many agents will create chaos, while too few miss opportunities.

Key Takeaways

  • Agents ≠ Workflows: They're the same concept rebranded — structured sequences that can loop back, self-correct, and adapt based on observations
  • Architecture patterns unlock complexity: ReAct for iterative analysis, Plan-and-Execute for dependency management, Self-Reflection for validation loops
  • Multi-agent sweet spot: 3-5 specialized agents with clear boundaries beat large teams — focus on parallelizing independent tasks while keeping dependencies sequential

Next Up

Now that our agents can think and reason, how do they access the knowledge they need? Enter RAG systems...

Technical deep dive series — Part 2 of 5

← Part 1: User Intent & Prompting | Part 3: RAG Systems →

📚 Context Engineering Deep Dive Series:

  1. User Intent & Prompting: The Art of Making LLMs Understand What You Really Want
  2. Agents & Reasoning (You are here)
  3. RAG Systems: When Your LLM Needs to Phone a Friend
  4. Action Tools: How LLMs Finally Learned to Stop Talking and Start Doing
  5. Memory Systems: Teaching LLMs to Remember (Without Going Broke)

🎯 Start with the overview: Context Engineering: How RAG, agents, and memory make LLMs actually useful

Dat Nguyen

Dat Nguyen

Data & AI @ Tech Lead

Comments

Join the discussion! Your thoughts and feedback are valuable.

💡 Comments are powered by GitHub Discussions. You'll need a GitHub account to comment. Make sure Discussions are enabled in the repository.

Loading comments...

Related Articles