QueryChat

When using SEMANTIC_VIEW(), you can apply filters in two different stages of the query lifecycle:

1. Inside the function (Pre-aggregation): Filters the base data before metrics are computed. Use this to limit the scope of the calculation (e.g., only including specific account types in a revenue sum).
2. Outside the function (Post-aggregation): Filters the results after metrics have been computed. Use this to filter the final aggregated values (e.g., showing only rows where revenue exceeds a certain threshold).

Examples

Pre-aggregation filter (Inside):

SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
    WHERE REF_ENTITIES.ACC_TYPE_CD = 'EXT'
)

Post-aggregation filter (Outside):

SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
)
WHERE NET_REVENUE > 1000000

-- Pre-aggregation: only include 'EXT' accounts in the calculation
SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
    WHERE REF_ENTITIES.ACC_TYPE_CD = 'EXT'
)

-- Post-aggregation: compute all, then filter results
SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
)
WHERE NET_REVENUE > 1000000

Some DRAW layers compute statistics. To access these computed values, use the REMAPPING keyword within the DRAW clause.

Examples:

Density histogram:

VISUALISE FROM products
DRAW histogram MAPPING price AS x REMAPPING density AS y

Bar showing proportion:

VISUALISE FROM sales
DRAW bar MAPPING region AS x REMAPPING proportion AS y

Overlay histogram and density:

VISUALISE FROM measurements
DRAW histogram MAPPING value AS x SETTING opacity => 0.5
DRAW density MAPPING value AS x REMAPPING intensity AS y SETTING opacity => 0.5

-- Density histogram (instead of count)
VISUALISE FROM products
DRAW histogram MAPPING price AS x REMAPPING density AS y

-- Bar showing proportion
VISUALISE FROM sales
DRAW bar MAPPING region AS x REMAPPING proportion AS y

-- Overlay histogram and density on the same scale
VISUALISE FROM measurements
DRAW histogram MAPPING value AS x SETTING opacity => 0.5
DRAW density MAPPING value AS x REMAPPING intensity AS y SETTING opacity => 0.5

-- Violin plot
SELECT department, salary FROM employees
VISUALISE department AS x, salary AS y
DRAW violin

querychat uses Large Language Models (LLMs) to translate natural language into SQL queries. To ensure accuracy without compromising privacy, it follows these principles:

1. Schema Retrieval: Instead of sending raw data to the LLM, querychat uses a schema retrieval tool. The LLM calls this tool on demand to learn about table structures, including column names, types, ranges, and categorical values.
2. SQL Execution: When the LLM generates a query, querychat executes it against a SQL database (using DuckDB by default) to retrieve precise results.
3. Contextual Interpretation: The query results are provided back to the LLM so it can interpret and summarize the findings for the user.

Key Design Benefits:

• Reliable: Results come from actual database execution, not LLM-generated summaries, reducing hallucinations.
• Safe: Tools are read-only by design. To ensure production safety, it is recommended to use read-only database permissions.
• Reproducible: The generated SQL can be exported and run in other environments.

The SEMANTIC_VIEW() function supports two types of filtering, which behave differently regarding when the filter is applied in the computation lifecycle:

1. Inside the function (Pre-aggregation): The WHERE clause inside the SEMANTIC_VIEW() parentheses filters the base data before metrics are computed. Use this to limit the scope of the calculation (e.g., only including specific account types in a sum).
2. Outside the function (Post-aggregation): The WHERE clause after the function call filters the results after metrics have been computed. Use this to filter the final aggregated rows (e.g., finding all dimensions where the total revenue exceeds a threshold).

Examples

Pre-aggregation (Filter base data):

SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
    WHERE REF_ENTITIES.ACC_TYPE_CD = 'EXT'
)

Post-aggregation (Filter results):

SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
)
WHERE NET_REVENUE > 1000000

-- Pre-aggregation: only include 'EXT' accounts in the calculation
SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
    WHERE REF_ENTITIES.ACC_TYPE_CD = 'EXT'
)

-- Post-aggregation: compute all, then filter results
SELECT * FROM SEMANTIC_VIEW(
    MODEL_NAME
    METRICS T_DATA.NET_REVENUE
    DIMENSIONS REF_ENTITIES.ACC_TYPE_CD
)
WHERE NET_REVENUE > 1000000

When generating SQL queries for the querychat chatbot, follow these structural and naming rules to ensure compatibility with the underlying query engine and the parent query execution flow.

Structural Rules

• No trailing semicolons: Never end a query with a semicolon (;). The system wraps your query, and a semicolon will cause syntax errors.
• Single statement only: Return exactly one SELECT statement. Do not provide multiple statements.
• No procedural or meta statements: Do not use EXPLAIN, SET, variable declarations, transaction controls (BEGIN, COMMIT, ROLLBACK), DDL (CREATE, ALTER, DROP), INTO clauses, or locking hints (FOR UPDATE, FOR SHARE).

Column Naming Rules

• Alias all computed/derived columns: Every expression that is not a simple column reference must have an explicit alias.
• Ensure unique column names: The result set must not contain duplicate column names, especially when performing joins.
• Avoid SELECT * with JOINs: Explicitly list columns to prevent duplicate names and ensure a predictable schema.
• Avoid reserved words as unquoted aliases: If an alias is a reserved word, quote it appropriately for the database dialect.

Statistical layers (like bar, histogram, density, violin, smooth, and boxplot) compute new values. Use the REMAPPING keyword within a DRAW clause to access these computed statistics.

Example: Using density instead of count in a histogram

-- Density histogram (instead of count)
VISUALISE FROM products
DRAW histogram MAPPING price AS x REMAPPING density AS y

-- Bar showing proportion
VISUALISE FROM sales
DRAW bar MAPPING region AS x REMAPPING proportion AS y

-- Violin plot
SELECT department, salary FROM employees
VISUALISE department AS x, salary AS y
DRAW violin

To provide users with actionable next steps, use <span class="suggestion"> tags. These render as clickable buttons in the UI.

Formats

1. List Format (Recommended) Use explicit HTML <ul> and <li> tags for reliability. This is the most common pattern for follow-up options at the end of a response.

<ul>
<li><span class="suggestion">Show me examples of …</span></li>
<li><span class="suggestion">Explain how …</span></li>
</ul>

2. Inline Format Use within prose for secondary options. These render as clickable text links.

You might want to <span class="suggestion">explore the advanced features</span>.

Guidelines

• Always provide suggestions at the start of a conversation, when beginning a new exploration, or after completing a topic.
• Write complete, natural prompts (e.g., "Show records from the year 2023" instead of "2023").
• Only suggest actions that can be performed with available tools.
• Avoid generic phrases like "If you'd like to..." or "Would you like to explore...".
• Never use nested lists; use headings to group suggestions instead.

##### Analyze the data
<ul
<li><span class="suggestion">What's the average …?</span></li>
<li><span class="suggestion">How many …?</span></li>
</ul>