How to Debug SQL Generation Issues Using Semantic SQL Logging in DAT

Capture the sql_generate Server-Sent Event (SSE) from the DAT API to inspect the intermediate semantic SQL produced by the LLM before dialect conversion, allowing you to isolate whether errors originate from the model's understanding or the SQL converter.

The DAT platform (junjiem/dat) transforms natural language questions into executable database queries through a two-stage pipeline that first generates semantic SQL then converts it to dialect-specific SQL. When you need to debug SQL generation issues using semantic SQL logging, examining this intermediate output allows you to determine whether the LLM misunderstood the question or the converter failed to translate the semantic SQL correctly.

Understanding the Semantic SQL Pipeline

DAT processes natural language queries through a defined sequence of transformations. Understanding this flow is essential for effective debugging.

Step	What Happens	Source Code
1. Client sends a request	POST /api/v1/ask/stream receives an AskRequest.	AskController.java
2. Agent runs the query	ProjectService.ask creates a StreamAction. The DefaultAskdataAgent builds semantic SQL via generateSql.	DefaultAskdataAgent.java
3. Semantic SQL is generated	DatabaseAdapter.generateSql delegates to SemanticSqlConverter.convert.	GenericSqlDatabaseAdapter.java
4. Semantic SQL → Dialect SQL	SemanticSqlConverter.convert parses the semantic SQL with ANTLR (ansiSqlParser) and rewrites it to the target dialect.	SemanticSqlConverter.java
5. Semantic SQL is streamed back	Every StreamEvent containing event.getSemanticSql() triggers a SQL_GENERATE_EVENT SSE payload (event: sql_generate).	sendStreamEvent in AskController.java (lines 68‑71)
6. Client receives the event	The client filters for sql_generate to see the exact semantic SQL that the agent emitted.	—

Why Semantic SQL Logging Is Critical for Debugging

The semantic SQL represents the exact text the LLM produced before any dialect-specific transformation occurs. This intermediate layer serves as the definitive checkpoint for isolating failures.

When you debug SQL generation issues using semantic SQL logging, you can distinguish between two distinct failure modes:

• LLM Output Errors: The semantic SQL contains incorrect table names, malformed joins, or logic that misinterprets the natural language question. These issues must be fixed through prompt engineering or model selection.
• Converter Errors: The semantic SQL is valid but the SemanticSqlConverter throws a SqlParseException or generates incorrect dialect-specific syntax. These issues require fixes to the ANTLR grammar or dialect generator.

By capturing the semantic SQL via the sql_generate SSE event, you eliminate guesswork and identify exactly which stage of the pipeline requires attention.

How to Capture Semantic SQL Events

The DAT platform streams the semantic SQL through Server-Sent Events (SSE) via the sql_generate event type. You can capture this output using command-line tools, browser-based JavaScript, or custom WebSocket clients.

Listening to SSE Events from the API

Use curl with grep to filter for semantic SQL generation events in real-time:

curl -N -H "Content-Type: application/json" \
     -d '{"question":"Show total sales per country","agentName":"default"}' \
     http://localhost:8080/api/v1/ask/stream \
  | grep -A1 '^event: sql_generate'

This outputs the event name followed by the JSON payload containing the semantic_sql field.

Capturing Events in JavaScript

In a browser environment, use EventSource to listen for the specific event type:

const source = new EventSource('/api/v1/ask/stream', {
  method: 'POST',
  body: JSON.stringify({
    question: 'Top 5 products by revenue', 
    agentName: 'default'
  }),
  headers: {'Content-Type': 'application/json'}
});

source.addEventListener('sql_generate', ev => {
  const payload = JSON.parse(ev.data);
  console.log('Semantic SQL:', payload.semantic_sql);
});

WebSocket Client for Real-Time Monitoring

For server-side Java applications, implement a WebSocket listener to capture the semantic SQL:

import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.WebSocket;
import java.util.concurrent.CompletionStage;

public class SemanticSqlWatcher {
    public static void main(String[] args) {
        HttpClient client = HttpClient.newHttpClient();
        client.newWebSocketBuilder()
            .buildAsync(URI.create("ws://localhost:8080/api/v1/ask/stream"),
                new WebSocket.Listener() {
                    @Override
                    public CompletionStage<?> onText(WebSocket webSocket,
                                                     CharSequence data,
                                                     boolean last) {
                        String msg = data.toString();
                        if (msg.contains("\"semantic_sql\"")) {
                            System.out.println("Semantic SQL: " + msg);
                        }
                        return WebSocket.Listener.super.onText(webSocket, data, last);
                    }
                });
    }
}

Debugging the SQL Conversion Step

Once you have captured the semantic SQL, you may need to investigate how it is transformed into dialect-specific SQL. The conversion happens in SemanticSqlConverter.java using ANTLR parsing.

Adding Debug Logs to SemanticSqlConverter

Patch the converter to log both the input semantic SQL and the output dialect SQL:

// Inside SemanticSqlConverter.convert(...)
public String convert(@NonNull String semanticSql) throws SqlParseException {
    semanticSql = semanticSql.trim();
    semanticSql = semanticSql.endsWith(";") ? 
            semanticSql.substring(0, semanticSql.length() - 1) : semanticSql;
    
    // DEBUG: dump raw semantic SQL before parsing
    log.debug("[SemanticSqlConverter] Parsing semantic SQL: {}", semanticSql);
    SqlNode sqlNode = ansiSqlParser.parseQuery(semanticSql);
    
    // Generate dialect specific sql
    String dialectSql = sqlDialectGenerator.generate(sqlNode);
    log.debug("[SemanticSqlConverter] Dialect SQL: {}", dialectSql);
    return dialectSql;
}

Enabling Detailed Logging Configuration

Configure Logback to capture DEBUG-level output from the semantic conversion layer:

<!-- src/main/resources/logback.xml -->
<logger name="ai.dat.core.semantic" level="DEBUG"/>
<logger name="ai.dat.server.openapi.controller.AskController" level="INFO"/>

Validating Semantic Model Definitions

If the semantic SQL references non-existent fields, validate your model definitions using SemanticModelUtil:

// Example in a test or debugging script
String modelSql = SemanticModelUtil.semanticModelSql(adapter.semanticAdapter(), model);
log.info("Model SQL template: {}", modelSql);

This helps identify when the LLM generates references to tables or columns not defined in the semantic model.

Summary

To effectively debug SQL generation issues using semantic SQL logging in the DAT platform:

• Capture the intermediate representation by listening for the sql_generate SSE event in AskController.java, which contains the raw semantic SQL produced by the LLM.
• Isolate the failure stage by comparing the semantic SQL against the final dialect SQL—if the semantic SQL is malformed, the issue lies in the LLM or prompt engineering; if conversion fails, the issue is in SemanticSqlConverter.java.
• Instrument the converter by adding DEBUG logs to SemanticSqlConverter.convert() to capture both the input semantic SQL and the generated dialect SQL.
• Validate semantic models using SemanticModelUtil when the LLM references undefined tables or columns.
• Configure logging at the package level (ai.dat.core.semantic) to capture parser errors and conversion details without modifying production code.

Frequently Asked Questions

What is semantic SQL in the DAT platform?

Semantic SQL is an intermediate, dialect-agnostic SQL representation generated by the LLM agent in DefaultAskdataAgent.java. It serves as the bridge between natural language understanding and database-specific execution, allowing the system to parse and convert the query to PostgreSQL, DuckDB, or other supported dialects through SemanticSqlConverter.java.

How do I know if the error is from the LLM or the SQL converter?

Inspect the semantic SQL returned in the sql_generate event. If the semantic SQL contains incorrect table names, malformed joins, or logic errors, the LLM misunderstood the question. If the semantic SQL looks correct but SemanticSqlConverter.convert() throws a SqlParseException at line 84 where ansiSqlParser.parseQuery(semanticSql) is called, the error lies in the ANTLR parser or dialect generator.

Can I disable semantic SQL logging in production?

Yes. The sql_generate event emission in AskController.java only occurs when clients connect to the SSE stream. You can disable DEBUG logging for ai.dat.core.semantic in your logback.xml or suppress the SSE event at the client level. However, keeping the event stream enabled with INFO-level logging is recommended for production debugging without significant performance impact.

Where does the semantic SQL to dialect conversion happen?

The conversion occurs in dat-core/src/main/java/ai/dat/core/semantic/SemanticSqlConverter.java. The convert() method uses ANTLR's ansiSqlParser to parse the semantic SQL string into a SqlNode, then delegates to sqlDialectGenerator to produce the final database-specific query. This is the critical point to instrument when debugging conversion failures.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}