### Complete Order Semantic Model Example

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

A comprehensive YAML example defining a semantic model for orders, including version, model details, entities, dimensions (time, categorical), and measures.

```yaml
version: 1
semantic_models:
  - name: orders
    description: "订单事实表，包含所有订单交易信息"
    alias: "订单"
    model: "ref('stg_orders')"
    tags: ["sales", "transaction", "core"]
    defaults:
      agg_time_dimension: order_date
    
    entities:
      - name: order_id
        type: primary
        description: "订单唯一标识符"
        alias: "订单ID"
      
      - name: customer
        type: foreign
        expr: customer_id
        description: "客户标识符"
        alias: "客户"

      - name: location
        type: foreign
        expr: location_id
        description: "店铺位置标识符"
        alias: "门店"
    
    dimensions:
      - name: order_date
        type: time
        description: "订单创建日期"
        alias: "下单时间"
        expr: "created_at"
        type_params:
          time_granularity: day
      
      - name: order_status
        type: categorical
        description: "订单状态"
        alias: "订单状态"
        expr: status
        enum_values:
          - value: "pending"
            label: "待处理"
          - value: "processing"
            label: "处理中"
          - value: "shipped"
            label: "已发货"
          - value: "delivered"
            label: "已送达"
          - value: "cancelled"
            label: "已取消"
        type_params: {}
      
      - name: payment_method
        type: categorical
        description: "支付方式"
        alias: "支付方式"
        enum_values:
          - value: "credit_card"
            label: "信用卡"
          - value: "debit_card"
            label: "借记卡"
          - value: "paypal"
            label: "PayPal"
          - value: "cash"
            label: "现金"
        type_params: {}
    
    measures:
      - name: total_amount
        description: "订单总金额"
        alias: "总金额"
        expr: amount
        agg: sum
        agg_time_dimension: "order_date"
      
      - name: order_count
        description: "订单数量"
        alias: "订单数"
        expr: "1"
        agg: count
      
      - name: avg_order_value
        description: "平均订单价值"
        alias: "平均订单金额"
        expr: amount
        agg: avg
```

--------------------------------

### Measure Configuration Examples

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Provides YAML examples demonstrating how to configure various measures, including total amount, order count, average order value, and unique customer count.

```yaml
measures:
  - name: total_amount
    description: "订单总金额"
    alias: "总金额"
    expr: amount
    agg: sum
    agg_time_dimension: "order_date"

  - name: order_count  
    description: "订单数量"
    alias: "订单数"
    expr: "1"
    agg: count

  - name: avg_order_value
    description: "平均订单价值"
    alias: "平均订单金额"
    expr: amount
    agg: avg

  - name: unique_customers
    description: "独特客户数量"
    alias: "客户数"
    expr: customer_id
    agg: count_distinct
```

--------------------------------

### Time Dimension Configuration Example

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Provides examples of configuring time dimensions, specifying names, descriptions, aliases, expressions, and time granularity parameters.

```yaml
dimensions:
  - name: order_date
    type: time
    description: "订单创建日期"  
    alias: "下单时间"
    expr: "created_at"
    type_params:
      time_granularity: day

  - name: registration_month
    type: time
    description: "客户注册月份"
    alias: "注册月份" 
    expr: "DATE_TRUNC('month', registration_date)"
    type_params:
      time_granularity: month
```

--------------------------------

### Python Example: Submitting Custom Metrics

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/data_assistance_system_prompt.txt

Example of how to use the Datadog Python library to submit custom metrics to Datadog.

```Python
from datadog import statsd

# Submit a count metric
statsd.increment('my_app.my_metric.count', tags=['environment:production', 'service:webserver'])

# Submit a gauge metric
statsd.gauge('my_app.my_metric.gauge', 42.5, tags=['environment:production', 'service:webserver'])

# Submit a histogram metric
statsd.histogram('my_app.my_metric.histogram', 123, tags=['environment:production', 'service:webserver'])
```

--------------------------------

### Entity Configuration Example

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Illustrates how to configure entities within a semantic model, specifying their names, types (primary, foreign), descriptions, aliases, and expressions.

```yaml
entities:
  - name: order_id
    type: primary
    description: "订单唯一标识符"
    alias: "订单ID"
    
  - name: customer
    type: foreign  
    expr: customer_id
    description: "客户标识符"
    alias: "客户"
    
  - name: location
    type: foreign
    expr: location_id  
    description: "店铺位置标识符"
    alias: "门店"
```

--------------------------------

### 推荐的文件结构

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

展示了DAT项目推荐的文件组织方式，包括模型文件（.sql）和语义模型文件（.yaml）的存放位置，以及共置和分离两种组织方式的优缺点。

```text
models/
├── customers.sql       # 客户数据模型
├── customers.yaml       # 客户语义模型
└── marts/
    ├── orders.sql          # 订单数据模型
    ├── orders.yaml          # 订单语义模型
    ├── products.sql        # 产品数据模型
    └── products.yaml        # 产品语义模型
```

--------------------------------

### Configuration Viewing

Source: https://github.com/junjiem/dat/blob/main/dat-cli/README.md

Lists the current project configuration information. This helps in understanding the project's setup and settings.

```bash
# Example: View project configuration
dat config list
```

--------------------------------

### DAT Project Configuration (`dat_project.yaml`)

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/GUIDE.md

Defines the structure and configuration options for a DAT project, including database, embedding, LLM, content store, and agent settings.

```yaml
- version (integer, optional): Project's version
- name (string, required): Project's name
- description (string, optional): Project's description
- db (object, required): Data source configuration
  - provider (string, required): Database adapter factory identifier
  - configuration (object): Configuration options
- embedding (object, optional): Embedding model configuration
  - provider (string, required): Embedding model factory identifier
  - configuration (object): Configuration options
- embedding_store (object, optional): Embedding store configuration
  - provider (string, required): Embedding store factory identifier
  - configuration (object): Configuration options
- llms (list[object]): Large language models configurations
  - name (string, required): LLM's name
  - provider (string, required): Large language model factory identifier
  - configuration (object): Configuration options
- content_store (object, optional): Content store configuration
  - provider (string, required): Content store factory identifier
  - llm (string, required): You need to fill in correct llm
  - configuration (object): Configuration options
- agents (list[object]): Ask data agents configurations
  - name (string, required): Agent's name
  - description (string, optional): Agent's description
  - provider (string, required): Askdata agent factory identifier
  - llm (string, required): You need to fill in correct llm
  - semantic_models (list[string], optional): You need to fill in correct semantic model names
  - configuration (object): Configuration options
```

--------------------------------

### Starting and Stopping the Datadog Agent

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/data_assistance_system_prompt.txt

Commands to manage the Datadog Agent service on different operating systems.

```Bash
# For systemd-based systems (e.g., Ubuntu, CentOS 7+)
sudo systemctl start datadog-agent
sudo systemctl stop datadog-agent
sudo systemctl restart datadog-agent

# For init.d-based systems (e.g., older Debian/Ubuntu)
sudo service datadog-agent start
sudo service datadog-agent stop
sudo service datadog-agent restart

# For macOS
sudo -- /opt/datadog-agent/bin/agent/agent start
sudo -- /opt/datadog-agent/bin/agent/agent stop
```

--------------------------------

### SQL Rules and Samples

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_system_prompt.txt

Provides guidelines and examples for writing SQL queries, including specific rules and function usage based on the database schema.

```SQL
-- Example SQL query based on schema
SELECT column1, column2
FROM your_table
WHERE column1 = 'some_value';
```

--------------------------------

### Build DAT Project

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/GUIDE.md

Command to build a DAT project. It takes the root directory of the project as an argument and generates a `.dat` directory and build states file.

```bash
dat build -p ./ROOT_DIRECTORY_OF_YOUR_PROJECT
```

--------------------------------

### Non-Additive Dimension Configuration Example

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Illustrates the configuration of a non-additive dimension for a measure like account balance, specifying the dimension name, window choice, and window groupings.

```yaml
measures:
  - name: account_balance
    description: "账户余额"
    expr: balance
    agg: sum
    non_additive_dimension:
      name: "balance_date"
      window_choice: "max"
      window_groupings: ["account_id"]
```

--------------------------------

### Categorical Dimension Configuration Example

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Shows how to configure categorical dimensions, including their names, descriptions, aliases, expressions, and enumerated values with labels.

```yaml
dimensions:
  - name: order_status
    type: categorical
    description: "订单状态"
    alias: "订单状态"
    expr: status
    enum_values:
      - value: "pending"
        label: "待处理"
      - value: "processing" 
        label: "处理中"
      - value: "shipped"
        label: "已发货"
      - value: "delivered"
        label: "已送达"
    type_params: {}

  - name: product_category
    type: categorical
    description: "产品类别"
    alias: "商品分类"
    enum_values:
      - value: "electronics"
        label: "电子产品"
      - value: "clothing"
        label: "服装"
      - value: "books"
        label: "图书"
    type_params: {}
```

--------------------------------

### 分类维度设计示例

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

展示了分类维度设计的最佳实践，强调为分类维度提供完整的枚举值列表，并提供了产品类别的示例。

```yaml
# 推荐：提供完整的枚举值
- name: product_category
  description: "产品类别"
  type: categorical
  enum_values:
    - value: "electronics"
      label: "电子产品"
    - value: "clothing"
      label: "服装"
    - value: "books"
      label: "图书"

```

--------------------------------

### 客户维度模型定义

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

定义了一个名为'customers'的客户维度模型，包含客户ID、邮箱、注册日期、客户细分、年龄组以及客户数量等字段，并指定了数据源和聚合时间维度。

```yaml
version: 1
semantic_models:
  - name: customers
    description: "客户维度表，包含客户的基本信息"
    alias: "客户"
    model: "ref('dim_customers')"
    tags: ["customer", "dimension"]
    defaults:
      agg_time_dimension: registration_date
    
    entities:
      - name: customer_id
        type: primary
        description: "客户唯一标识符"
        alias: "客户ID"
      
      - name: email
        type: unique
        description: "客户邮箱地址"
        alias: "邮箱"
    
    dimensions:
      - name: registration_date
        type: time
        description: "客户注册日期"
        alias: "注册时间"
        expr: "created_at"
        type_params:
          time_granularity: day
      
      - name: customer_segment
        type: categorical
        description: "客户细分"
        alias: "客户类型"
        enum_values:
          - value: "vip"
            label: "VIP客户"
          - value: "regular"
            label: "普通客户"
          - value: "new"
            label: "新客户"
        type_params: {}
      
      - name: age_group
        type: categorical
        description: "年龄段"
        alias: "年龄组"
        expr: "CASE 
                WHEN age < 25 THEN 'young'
                WHEN age BETWEEN 25 AND 45 THEN 'middle'
                ELSE 'senior'
               END"
        enum_values:
          - value: "young"
            label: "年轻客户(25岁以下)"
          - value: "middle"
            label: "中年客户(25-45岁)"
          - value: "senior"
            label: "中老年客户(45岁以上)"
        type_params: {}
    
    measures:
      - name: customer_count
        description: "客户总数"
        alias: "客户数量"
        expr: "1"
        agg: count_distinct

```

--------------------------------

### Semantic Model Basic Properties Table

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Provides a detailed breakdown of the basic properties for semantic models, outlining the purpose, necessity, and data type for each component.

```APIDOC
Semantic Model Basic Properties:
  name: The unique name of the semantic model.
    - Required: Yes
    - Type: String
  description: A description containing important details.
    - Required: No
    - Type: String
  model: Reference to the data model using the ref function.
    - Required: Yes
    - Type: String
  alias: An alias for the semantic model.
    - Required: No
    - Type: String
  tags: An array of tags for categorization and retrieval.
    - Required: No
    - Type: Array
  defaults: Default configurations for the model, currently only supports agg_time_dimension.
    - Required: Yes
    - Type: Object
    - Properties:
      agg_time_dimension: The name of the time dimension (required if measures are included).
        - Required: Yes
        - Type: String
  entities: Columns serving as join keys, indicating their type as primary, foreign, or unique.
    - Required: Yes
    - Type: List
  dimensions: Columns used for grouping or slicing measures, can be time or categorical.
    - Required: Yes
    - Type: List
  measures: Aggregations applied to columns in the data model, can be final measures or building blocks for complex measures.
    - Required: No
    - Type: List
```

--------------------------------

### Intent Classification Definitions

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/intent_classification_system_prompt.txt

Defines the criteria for classifying user intents into TEXT_TO_SQL, GENERAL, and MISLEADING_QUERY, including requirements and examples for each category.

```APIDOC
TEXT_TO_SQL:
**When to Use:**
- The user's inputs are about modifying SQL from previous questions.
- The user's inputs are related to the database schema and requires an SQL query.
- The question (or related previous query) includes references to specific tables, columns, or data details.

**Requirements:**
- Include specific table and column names from the schema in your reasoning or modifying SQL from previous questions.
- Reference phrases from the user's inputs that clearly relate to the schema.

**Examples:**
- "What is the total sales for last quarter?"
- "Show me all customers who purchased product X."
- "List the top 10 products by revenue."

GENERAL:
**When to Use:**
- The user seeks general information about the database schema or its overall capabilities.
- The combined queries do not provide enough detail to generate a specific SQL query.

**Requirements:**
- Highlight phrases from the user's inputs that indicate a general inquiry not tied to specific schema details.

**Examples:**
- "What is the dataset about?"
- "Tell me more about the database."
- "How can I analyze customer behavior with this data?"

MISLEADING_QUERY:
**When to Use:**
- The user's inputs is irrelevant to the database schema or includes SQL code.
- The user's inputs lacks specific details (like table names or columns) needed to generate an SQL query.
- It appears off-topic or is simply a casual conversation starter.

**Requirements:**
- Incorporate phrases from the user's inputs that indicate the lack of relevance to the database schema.

**Examples:**
- "How are you?"
- "What's the weather like today?"
- "Tell me a joke."
```

--------------------------------

### 度量设计示例

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

展示了度量设计的最佳实践，包括选择合适的聚合函数（如sum, count）以及处理非加性度量（如账户余额）。

```yaml
# 普通度量
- name: revenue
  description: "收入总额"
  expr: amount
  agg: sum

# 计数度量
- name: order_count
  description: "订单数量"
  expr: "1"
  agg: count

# 非加性度量（如余额）
- name: account_balance
  description: "账户余额"
  expr: balance
  agg: sum
  non_additive_dimension:
    name: "snapshot_date"
    window_choice: "max"
    window_groupings: ["account_id"]

```

--------------------------------

### Run DAT Agent

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/GUIDE.md

Command to run an agent specified in the DAT project. It takes the project root directory and the agent name as arguments. An incremental build is performed before running.

```bash
dat run -p ./ROOT_DIRECTORY_OF_YOUR_PROJECT -a AGENT_NAME
```

--------------------------------

### 复杂SQL表达式示例

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

演示了如何在DAT语义模型的`expr`字段中使用复杂的SQL表达式，包括函数调用和条件判断，以计算利润率。

```yaml
- name: profit_margin
  description: "利润率"
  expr: "(revenue - cost) / revenue * 100"
  agg: avg

```

--------------------------------

### 命名规范示例

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

提供了DAT项目中推荐的命名规范，强调使用有意义、一致的名称，并提供清晰的描述和别名，对比了推荐和不推荐的写法。

```yaml
# 推荐
- name: customer_lifetime_value
  description: "客户生命周期价值"
  alias: "客户LTV"

# 不推荐  
- name: clv_calc
  description: "LTV calculation field"

# 推荐
- name: monthly_recurring_revenue
  description: "月度经常性收入，指每月可预期的稳定收入"
  alias: "月度经常性收入"

# 不推荐
- name: mrr
  description: "MRR"

```

--------------------------------

### 时间维度设计示例

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

展示了时间维度设计的最佳实践，强调根据业务需求选择合适的时间粒度，并提供了订单月份的示例。

```yaml
# 推荐：根据业务需求选择合适的粒度
- name: order_month
  description: "订单月份"
  type: time
  expr: "DATE_TRUNC('month', order_date)"
  type_params:
    time_granularity: month
```

--------------------------------

### Data Analyst Reasoning Plan Generation

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_reasoning_system_prompt.txt

This section details the process for generating a step-by-step reasoning plan to answer user questions based on database schemas and user history. It covers considerations for timeframes, SQL query construction, and output formatting.

```Markdown
### TASK ###
You are a helpful data analyst who is great at thinking deeply and reasoning about the user's question and the database schema, and you provide a step-by-step reasoning plan in order to answer the user's question.

### INSTRUCTIONS ###
1. Think deeply and reason about the user's question, the database schema, and the user's query history if provided.
2. Explicitly state the following information in the reasoning plan:
if the user puts any specific timeframe(e.g. YYYY-MM-DD) in the user's question, you will put the absolute time frame in the SQL query;
Otherwise, you will put the relative timeframe in the SQL query.
3. If USER INSTRUCTIONS section is provided, make sure to consider them in the reasoning plan.
4. If SQL SAMPLES section is provided, make sure to consider them in the reasoning plan.
5. Give a step by step reasoning plan in order to answer user's question.
6. The reasoning plan should be in the language same as the language user provided in the input.
7. Don't include SQL in the reasoning plan.
8. Each step in the reasoning plan must start with a number, a title(in bold format in markdown), and a reasoning for the step.
9. Do not include ```markdown or ``` in the answer.
10. A table name in the reasoning plan must be in this format: `table: <table_name>`.
11. A column name in the reasoning plan must be in this format: `column: <table_name>.<column_name>`.

### FINAL ANSWER FORMAT ###
The final answer must be a reasoning plan in plain Markdown string format
```

--------------------------------

### Entity Configuration Parameters

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Details the parameters for configuring entities, including name, type, description, alias, and expression, along with their requirements and data types.

```APIDOC
Entity Configuration Parameters:
  name: The entity name, must be unique within the semantic model.
    - Required: Yes
    - Type: String
  type: The entity type: primary, foreign, or unique.
    - Required: Yes
    - Type: String
  description: A description of the entity.
    - Required: No
    - Type: String
  alias: An alias for the entity.
    - Required: No
    - Type: String
  expr: Reference an existing column or create a new column using a SQL expression.
    - Required: No
    - Type: String
```

--------------------------------

### Dimension Configuration Parameters

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Outlines the parameters for configuring dimensions, including name, type, type parameters, description, alias, expression, and enum values.

```APIDOC
Dimension Configuration Parameters:
  name: The dimension name, must be unique within the same semantic model.
    - Required: Yes
    - Type: String
  type: The dimension type: categorical or time.
    - Required: Yes
    - Type: String
  type_params: Specific parameters for the type, such as time granularity.
    - Required: Yes
    - Type: Object
  description: A clear description of the dimension.
    - Required: No
    - Type: String
  alias: An alias for the dimension.
    - Required: No
    - Type: String
  expr: Defines the underlying column or SQL query.
    - Required: No
    - Type: String
  enum_values: Enumerated value definitions for categorical dimensions.
    - Required: No
    - Type: Array
    - Properties:
      value: The actual value of the enum.
        - Required: Yes
        - Type: String
      label: The display label for the enum value.
        - Required: Yes
        - Type: String
```

--------------------------------

### Assistant's Role and Goal

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/misleading_assistance_system_prompt.txt

Defines the assistant's persona as a helpful guide for data understanding. It highlights the task of identifying misleading user questions and suggesting better alternatives based on the database schema.

```APIDOC
Assistant Role:
  - Helpful assistant for data understanding.

Primary Goal:
  - Guide users to understand their data better.
  - Identify potentially misleading user questions.
  - Suggest better questions to ask.

Key Considerations:
  - User's specified language for answers.
  - Proper Markdown formatting (headers, lists, tables).
  - Response length limits (150 words for Chinese/Korean/Japanese, 110 words for others).
  - Prohibition of SQL code in responses.
  - Consideration of database schema when suggesting questions.
```

--------------------------------

### Creating Proxy Keys with Combined Columns

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Demonstrates how to create a proxy key by combining multiple columns using a specified delimiter, useful when a table lacks a natural primary key.

```yaml
entities:
  - name: brand_target_key # 实体名称或标识
    type: foreign # 可以是任何实体类型键
    expr: date_key || '|' || brand_code # 定义链接字段以形成代理键的表达式
```

--------------------------------

### Time Granularity Options

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Lists the supported time granularities for time dimensions, including second, minute, hour, day, week, month, quarter, and year.

```APIDOC
Time Granularity Options:
  - second
  - minute
  - hour
  - day
  - week
  - month
  - quarter
  - year
```

--------------------------------

### Semantic Model Basic Properties

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Defines the fundamental attributes of a semantic model, including its name, description, model reference, aliases, tags, default configurations, entities, dimensions, and measures.

```yaml
version: 1
semantic_models:
  - name: 语义模型名称                    ## 必填
    description: 模型描述               ## 可选
    model: "ref('some_model')"         ## 必填 
    alias: 语义模型别名                 ## 可选
    tags: [标签列表]                   ## 可选
    defaults:                         ## 必填
      agg_time_dimension: 维度名称      ## 如果包含度量则必填
    entities:                         ## 必填
      - 详见实体配置章节
    dimensions:                       ## 必填  
      - 详见维度配置章节
    measures:                         ## 可选
      - 详见度量配置章节
```

--------------------------------

### Project Initialization

Source: https://github.com/junjiem/dat/blob/main/dat-cli/README.md

Initializes a new project directory and its configuration files. This is the first step in setting up a new DAT project.

```bash
# Example: Initialize a new project
dat init
```

--------------------------------

### Business Knowledge Rendering

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_user_prompt_template.txt

Displays business knowledge content if the 'docs' variable is available. It iterates through the 'docs' list, printing each item separated by '---'.

```jinja
{% if docs %}
### BUSINESS KNOWLEDGE ###
{% for item in docs %}
---
{{ item }}
{% endfor %}
{% endif %}
```

--------------------------------

### SQL Samples Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_reasoning_with_followup_user_prompt_template.txt

Conditionally displays SQL samples if the 'sql_samples' variable is present. It iterates through each sample, showing the question and the corresponding SQL query.

```jinja
{% if sql_samples %}
### SQL SAMPLES ###
{% for item in sql_samples %}
Question: {{ item.question }}
SQL: {{ item.sql }}
{% endfor %}
{% endif %}
```

--------------------------------

### Input Parameters

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/data_assistance_user_prompt_template.txt

Defines the input parameters for the project, including the current time, user's query, and language. These are essential for context-aware processing.

```jinja
Current time: {{ query_time }}
User's question: {{ query }}
Language: {{ language }}
```

--------------------------------

### Supported Aggregation Types

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Lists the supported aggregation types for measures in Dat, along with a brief description of each.

```APIDOC
Supported Aggregation Types:
  sum: Sum of values.
  min: Minimum value.
  max: Maximum value.
  avg: Average value.
  count: Count.
  count_distinct: Distinct count.
  median: Median calculation.
  sum_boolean: Sum of boolean types.
  none: No aggregation (default).
```

--------------------------------

### Input Parameters

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/misleading_assistance_user_prompt_template.txt

Defines the input parameters for the project, including the current time, user's query, and language. These are essential for context-aware processing.

```jinja
Current time: {{ query_time }}
User's question: {{ query }}
Language: {{ language }}
```

--------------------------------

### Query Information Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_user_prompt_template.txt

Outputs current time, user's question, and language. This section provides context for the user's interaction.

```jinja
### QUESTION ###
Current time: {{ query_time }}
User's Question: {{ query }}
Language: {{ language }}
```

--------------------------------

### SQL Query Generation Template

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_with_followup_user_prompt_template.txt

This Jinja2 template outlines the structure for generating SQL queries based on various inputs including semantic models, SQL samples, synonyms, business knowledge, user query history, and the current user question. It also incorporates a reasoning plan for the generation process.

```Jinja2
### TASK ###
Given the following user's follow-up question and previous SQL query and summary,
generate one SQL query to best answer user's question.

### SEMANTIC MODELS ###
{% for semantic_model in semantic_models %}
    {{ semantic_model }}
{% endfor %}

{% if sql_samples %}
### SQL SAMPLES ###
{% for item in sql_samples %}
Question:
{{ item.question }}
SQL:
{{ item.sql }}
{% endfor %}
{% endif %}

{% if synonyms %}
### NOUN AND SYNONYMS ###
{% for item in synonyms %}
Noun: {{ item.noun }} Synonyms: {{ item.synonyms|join(', ') }}
{% endfor %}
{% endif %}

{% if docs %}
### BUSINESS KNOWLEDGE ###
{% for item in docs %}
---
{{ item }}
{% endfor %}
{% endif %}

### User's QUERY HISTORY ###
{% for history in histories %}
Question: {{ history.question }}
SQL: {{ history.sql }}
{% endfor %}

### QUESTION ###
Current time: {{ query_time }}
User's Follow-up Question: {{ query }}

{% if sql_generation_reasoning %}
### REASONING PLAN ###
{{ sql_generation_reasoning }}
{% endif %}

Let's think step by step.
```

--------------------------------

### Entity Types

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Describes the different types of entities: primary, foreign, and unique, detailing their characteristics regarding record uniqueness and nullability.

```APIDOC
Entity Types:
  primary: A primary key has only one record for each row in a table and includes every record in the data platform. It must contain unique values and cannot contain null values. Using a primary key ensures that each record in the table is distinct and identifiable.
  foreign: A foreign key is a field (or group of fields) in one table that uniquely identifies a row of another table. Foreign keys establish a link between the data in the two tables. It can include zero, one, or more instances of the same record. It can also contain null values.
  unique: A unique key contains only one record for each row in a table, but may contain a subset of records in the data warehouse. However, unlike a primary key, a unique key allows null values. Unique keys ensure that the values in a column are distinct, with the exception of null values.
```

--------------------------------

### Output Format for Intent Classification

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/intent_classification_system_prompt.txt

Specifies the JSON structure required for the output of the intent classification task, including rephrased question, reasoning, and the classified intent.

```APIDOC
{
    "rephrased_question": "<rephrased question in full standalone question if there are previous questions, otherwise the original question>",
    "reasoning": "<brief chain-of-thought reasoning (max 20 words)>",
    "intent": "MISLEADING_QUERY" | "TEXT_TO_SQL" | "GENERAL"
}
```

--------------------------------

### Synonyms Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_user_prompt_template.txt

Renders a list of nouns and their associated synonyms. If 'synonyms' data exists, it iterates through each entry, displaying the noun and its synonyms joined by a comma.

```jinja
{% if synonyms %}
### NOUN AND SYNONYMS ###
{% for item in synonyms %}
Noun: {{ item.noun }} Synonyms: {{ item.synonyms|join(', ') }}
{% endfor %}
{% endif %}
```

--------------------------------

### Project Building

Source: https://github.com/junjiem/dat/blob/main/dat-cli/README.md

Supports both incremental and forced rebuilding of the project. Incremental builds are faster, while forced builds ensure a complete rebuild.

```bash
# Example: Incremental build
dat build

# Example: Forced rebuild
dat build --force
```

--------------------------------

### Measure Configuration Parameters

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Defines the parameters used for configuring measures in Dat. Each parameter specifies its role, whether it's required, and its data type.

```APIDOC
Measure Configuration Parameters:
  name: Measure name, must be unique across all semantic models. (Required, String)
  description: Description of the measure calculation. (Optional, String)
  alias: Alias for the measure. (Optional, String)
  expr: Reference existing columns or use SQL expressions to create new columns. (Optional, String)
  agg: Aggregation type. (Optional, String)
  agg_time_dimension: Time field, defaults to the semantic model's default aggregation time dimension. (Optional, String)
  non_additive_dimension: Specify non-additive dimensions for measures that cannot be aggregated across certain dimensions to avoid incorrect results. (Optional, Object)
```

--------------------------------

### Business Knowledge Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_reasoning_with_followup_user_prompt_template.txt

Conditionally displays business knowledge documents if the 'docs' variable is present. Each document is separated by '---'.

```jinja
{% if docs %}
### BUSINESS KNOWLEDGE ###
{% for item in docs %}
---
{{ item }}
{% endfor %}
{% endif %}
```

--------------------------------

### Dat CLI Usage

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/banner1.txt

Basic usage and commands for the Dat CLI.

```bash
dat --help
dat clone <link>
dat share <directory>
dat doctor
dat doctor --fix
```

--------------------------------

### Reasoning Plan Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_user_prompt_template.txt

Conditionally displays the reasoning plan for SQL generation if 'sql_generation_reasoning' is provided. This helps understand the logic behind query construction.

```jinja
{% if sql_generation_reasoning %}
### REASONING PLAN ###
{{ sql_generation_reasoning }}
{% endif %}
```

--------------------------------

### DAT Framework Architecture

Source: https://github.com/junjiem/dat/blob/main/README.md

Illustrates the conceptual architecture of the DAT framework, highlighting the 'dat language' and 'dat engine' as core components.

```text
 .--------------.
/  dat language  \
|    .--------.   |
|   /          \  |
|  | dat engine | |
|   \          /  |
|    '--------'   |
\                /
 '--------------'
caption: the dat framework
```

--------------------------------

### DAT Framework Features

Source: https://github.com/junjiem/dat/blob/main/README.md

Outlines the key features and capabilities of the DAT framework, including data modeling, semantic modeling, LLM-based SQL generation, and testing.

```text
1. Data model (table or view) configuration;
2. Semantic model (bound to data model) configuration, including: entities, dimensions, metrics, etc.;
3. LLM-based generation of semantic SQL, conversion of semantic SQL to real SQL, and execution to return data;
4. LLM-based data exploration to assist in generating semantic models; (TODO)
5. Unit testing of data models, semantic models, and intelligent data queries; (TODO)
6. Vectorization and storage/retrieval of SQL Q&A pairs, text content, etc.; (TODO)
7. Metric configuration (can be further enhanced after building the semantic model); (TODO)
```

--------------------------------

### Synonyms Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_reasoning_with_followup_user_prompt_template.txt

Conditionally displays nouns and their synonyms if the 'synonyms' variable is present. It formats the output by joining multiple synonyms with a comma.

```jinja
{% if synonyms %}
### NOUN AND SYNONYMS ###
{% for item in synonyms %}
Noun: {{ item.noun }} Synonyms: {{ item.synonyms|join(', ') }}
{% endfor %}
{% endif %}
```

--------------------------------

### Dimension Types

Source: https://github.com/junjiem/dat/blob/main/dat-cli/src/main/resources/project_init_template/MODEL_GUIDE.md

Explains the two types of dimensions: categorical, used for descriptive attributes like location or sales region, and time, based on temporal data like timestamps or dates.

```APIDOC
Dimension Types:
  categorical: Descriptive attributes or characteristics, such as geographical location or sales region.
  time: Dimensions based on time, such as timestamps or dates.
```

--------------------------------

### User Query History Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_reasoning_with_followup_user_prompt_template.txt

Iterates through the user's query history, displaying each past question and its associated SQL query.

```jinja
{% for history in histories %}
Question: {{ history.question }}
SQL: {{ history.sql }}
{% endfor %}
```

--------------------------------

### Semantic Models Loop

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_reasoning_with_followup_user_prompt_template.txt

Iterates through a list of semantic models and displays each one. This is a common pattern for rendering lists of data.

```jinja
{% for semantic_model in semantic_models %}
{{ semantic_model }}
{% endfor %}
```

--------------------------------

### Semantic Models Loop

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_user_prompt_template.txt

Iterates through a list of semantic models and displays each one. This is a common pattern for rendering collections of data.

```jinja
{% for semantic_model in semantic_models %}
{{ semantic_model }}
{% endfor %}
```

--------------------------------

### Current Query Context

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_reasoning_with_followup_user_prompt_template.txt

Displays the current time, the user's question, and the detected language. This section provides context for the current interaction.

```jinja
### QUESTION ###
Current time: {{ query_time }}
User's Question: {{ query }}
Language: {{ language }}

Let's think step by step.
```

--------------------------------

### SQL Samples Conditional Rendering

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_user_prompt_template.txt

Conditionally renders SQL samples if the 'sql_samples' variable is present. It then iterates through each sample, displaying the question and the corresponding SQL query.

```jinja
{% if sql_samples %}
### SQL SAMPLES ###
{% for item in sql_samples %}
Question: {{ item.question }}
SQL: {{ item.sql }}
{% endfor %}
{% endif %}
```

--------------------------------

### SQL Samples Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/intent_classification_user_prompt_template.txt

Conditionally displays SQL samples if the 'sql_samples' variable is present. It iterates through each sample, showing the question and its corresponding SQL query.

```jinja
{% if sql_samples %}
### SQL SAMPLES ###
{% for item in sql_samples %}
Question: {{ item.question }}
SQL: {{ item.sql }}
{% endfor %}
{% endif %}
```

--------------------------------

### Semantic Models Loop

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/data_assistance_user_prompt_template.txt

Iterates through a list of semantic models, rendering each one. This is a common pattern for displaying collections of data.

```jinja
{% for semantic_model in semantic_models %}
{{ semantic_model }}
{% endfor %}
```

--------------------------------

### Step-by-step Thinking Prompt

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/sql_generation_user_prompt_template.txt

A concluding remark encouraging a step-by-step thinking process, often used in AI-driven response generation.

```jinja
Let's think step by step.
```

--------------------------------

### Semantic Models Loop

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/misleading_assistance_user_prompt_template.txt

Iterates through a list of semantic models, rendering each one. This is a common pattern for displaying collections of data.

```jinja
{% for semantic_model in semantic_models %}
{{ semantic_model }}
{% endfor %}
```

--------------------------------

### Business Knowledge Display

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/intent_classification_user_prompt_template.txt

Conditionally displays business knowledge documentation if the 'docs' variable is present. It iterates through each document, separating them with '---'.

```jinja
{% if docs %}
### BUSINESS KNOWLEDGE ###
{% for item in docs %}
---
{{ item }}
{% endfor %}
{% endif %}
```

--------------------------------

### SQL Query Best Practices

Source: https://github.com/junjiem/dat/blob/main/dat-core/src/main/resources/prompts/default/text_to_sql_rules.txt

This section details the constraints and best practices for generating SQL queries. It covers allowed operations (SELECT only), table and column usage, case sensitivity, date handling, JOIN requirements, aliasing, and disallowed clauses.

```SQL
SELECT statements only, NO DELETE, UPDATE, or INSERT.
ONLY use tables and columns from the database schema.
Use "*" only if all columns are requested.
ONLY choose columns belonging to mentioned tables.
DON'T INCLUDE comments and line breaks in the generated SQL query.
USE "JOIN" if choosing columns from multiple tables.
ALWAYS QUALIFY column names with table name or alias (e.g., orders.OrderId, o.OrderId).
USE "lower(<table_name>.<column_name>) like lower(<value>)" for pattern matching.
USE "lower(<table_name>.<column_name>) = lower(<value>)" for exact matches.
For date/time INT/BIGINT/DOUBLE/FLOAT columns, cast to TIMESTAMP using TO_TIMESTAMP_MILLIS, TO_TIMESTAMP_SECONDS, or TO_TIMESTAMP_MICROS.
For specific dates, provide a date range (e.g., WHERE r.PurchaseTimestamp >= '2025-07-01 00:00:00' AND r.PurchaseTimestamp < '2025-07-02 00:00:00').
USE VIEWS to simplify queries.
DON'T USE '.' in column/table aliases; replace '.' with '_'.
DON'T USE "FILTER(WHERE <expression>)" clause.
DON'T USE "EXTRACT(EPOCH FROM <expression>)" clause.
DON'T USE INTERVAL or generate INTERVAL-like expressions.
Aggregate functions are not allowed in the WHERE clause; use HAVING clause instead.
```