> For the complete documentation index, see [llms.txt](https://docs.duql.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.duql.dev/intermediate/generate.md).

# Generate

The `generate` function in DUQL is used to create new columns or modify existing ones based on expressions, calculations, and conditional logic. It's a powerful tool for data transformation and enrichment within your query pipeline.

## Syntax

```yaml
generate:
  <new_column_name>: <expression>
```

## Parameters

| Parameter  | Type   | Required | Description                                                                |
| ---------- | ------ | -------- | -------------------------------------------------------------------------- |
| `generate` | object | Yes      | Key-value pairs where keys are new column names and values are expressions |

## Behavior

* Creates new columns or modifies existing ones based on the provided expressions.
* Can reference other columns, use functions, and implement conditional logic.
* Executes in the order specified within the `generate` block.

## Examples

### Basic Column Generation

```yaml
generate:
  full_name: f"{first_name} {last_name}"
```

### Boolean Flags

```yaml
generate:
  is_northern: latitude > 0
  should_have_shipped_today: order_time < @08:30
```

### Conditional Logic

```yaml
generate:
  discount:
    case:
    - total_purchases > 1000: 0.15
    - total_purchases > 500: 0.10
    - total_purchases > 100: 0.05
    - true: 0
```

### Complex Calculations

```yaml
generate:
  bmi: weight / (height / 100) ^ 2
  health_status:
    case:
    - bmi < 18.5: "Underweight"
    - bmi < 25: "Normal"
    - bmi < 30: "Overweight"
    - true: "Obese"
```

### Using SQL Functions

```yaml
generate:
  clean_text: 
    sql'REGEXP_REPLACE(LOWER(text), ''[^a-z0-9 ]'', '''')'
  word_count:
    sql'ARRAY_LENGTH(SPLIT(clean_text, '' ''))'
```

### Financial Calculations

```yaml
generate:
  revenue: price * quantity
  profit_margin: (revenue - cost) / revenue * 100
  performance:
    case:
    - profit_margin > 30: "Excellent"
    - profit_margin > 20: "Good"
    - profit_margin > 10: "Average"
    - true: "Poor"
```

### Geospatial Calculations

```yaml
generate:
  distance: 
    sql'ST_Distance(ST_Point(long, lat), ST_Point(-74.006, 40.7128))'
  is_nearby: distance < 10
```

## Best Practices

1. 🏷️ Use clear and descriptive names for generated columns.
2. 🧮 Break down complex calculations into multiple steps for readability.
3. 🔍 Validate your generated columns, especially when using complex expressions.
4. 🚀 Consider performance implications when generating columns with heavy calculations.
5. 📊 Use `case` statements for complex conditional logic.
6. 🔄 Remember that columns generated earlier can be used in subsequent generations within the same `generate` block.

***

> 💡 **Tip:** The `generate` function is your Swiss Army knife for data transformation in DUQL. Use it to create new insights, segment your data, and prepare it for deeper analysis. Don't hesitate to combine multiple `generate` steps in your pipeline for complex transformations!