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

> Debug SQL generation issues with DAT semantic SQL logging. Inspect intermediate SQL from the LLM before dialect conversion to identify model or converter errors.

- Repository: [Junjie.M/dat](https://github.com/junjiem/dat)
- Tags: how-to-guide
- Published: 2026-03-05

---

**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`](https://github.com/junjiem/dat/blob/main/AskController.java) |
| **2. Agent runs the query** | `ProjectService.ask` creates a `StreamAction`. The `DefaultAskdataAgent` builds semantic SQL via `generateSql`. | [`DefaultAskdataAgent.java`](https://github.com/junjiem/dat/blob/main/DefaultAskdataAgent.java) |
| **3. Semantic SQL is generated** | `DatabaseAdapter.generateSql` delegates to `SemanticSqlConverter.convert`. | [`GenericSqlDatabaseAdapter.java`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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:

```bash
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:

```javascript
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:

```java
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`](https://github.com/junjiem/dat/blob/main/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:

```java
// 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:

```xml
<!-- 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`:

```java
// 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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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`](https://github.com/junjiem/dat/blob/main/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.