Tinybird

# Tinybird Best Practices

Guidance for Tinybird file formats, SQL rules, optimization patterns, and data modeling. Use this skill when creating or editing Tinybird datafiles.

## When to Apply

- Creating or updating Tinybird resources (.datasource, .pipe, .connection)
- Writing or optimizing SQL queries
- Designing endpoint schemas and data models
- Organizing project structure and data layers
- Working with materialized views or copy pipes
- Implementing deduplication patterns
- Reviewing or refactoring Tinybird project files

## Rule Files

- `rules/project-files.md`
- `rules/build-deploy.md`
- `rules/datasource-files.md`
- `rules/pipe-files.md`
- `rules/endpoint-files.md`
- `rules/materialized-files.md`
- `rules/sink-files.md`
- `rules/copy-files.md`
- `rules/connection-files.md`
- `rules/sql.md`
- `rules/endpoint-optimization.md`
- `rules/tests.md`
- `rules/deduplication-patterns.md`

## Quick Reference

- Project local files are the source of truth.
- Build target comes from `tinybird.config.json` `dev_mode` (`local` or `branch`).
- `tb deploy` targets Tinybird Cloud production.
- Commands like `tb sql` and `tb logs` default to local unless `--cloud` or `--branch=<branch-name>` is set.
- SQL is SELECT-only with Tinybird templating rules and strict parameter handling.
- Use MergeTree by default; AggregatingMergeTree for materialized targets.
- Filter early, select only needed columns, push complex work later in the pipeline.


# Datasource Files

- Content cannot be empty.
- Datasource names must be unique.
- No indentation for property names (DESCRIPTION, SCHEMA, ENGINE, etc.).
- Use MergeTree by default.
- Use AggregatingMergeTree for materialized targets.
- Always use JSON paths for schema (example: `user_id` String `json:$.user_id`).
- Array syntax: `items` Array(String) `json:$.items[:]`.
- DateTime64 requires precision (use DateTime64(3)).
- Only include ENGINE_PARTITION_KEY and ENGINE_PRIMARY_KEY when explicitly requested.
- Import configuration:
  - S3/GCS: set IMPORT_CONNECTION_NAME, IMPORT_BUCKET_URI, IMPORT_SCHEDULE (GCS supports @on-demand only, S3 supports @auto).
  - Kafka: set KAFKA_CONNECTION_NAME, KAFKA_TOPIC, KAFKA_GROUP_ID.
- For landing datasources created from a .ndjson file with no schema specified, use:
  - `SCHEMA >`
  - `` `data` String `json:$` ``

Example:

```
DESCRIPTION >
    Some meaningful description of the datasource

SCHEMA >
    `column_name_1` Type `json:$.column_name_1`,
    `column_name_2` Type `json:$.column_name_2`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "partition_key"
ENGINE_SORTING_KEY "sorting_key_1, sorting_key_2"
```

## Updating Data Source Schemas (Cloud)

If a schema change is incompatible with the deployed Cloud Data Source, add a `FORWARD_QUERY` to transform existing data to the new schema. The query is a SELECT list only (no FROM/WHERE). It runs over existing data at read time until the next deploy compacts it.

### When to use `FORWARD_QUERY`

- Adding a new column that requires a default value for existing rows
- Changing a column type (e.g., String to UUID, Int32 to Int64)
- Renaming a column
- Removing a column (just omit it from the SELECT)

### Examples

Adding a new column with a default:

```
FORWARD_QUERY >
    SELECT *, 'unknown' as source
```

Changing a column type:

```
FORWARD_QUERY >
    SELECT timestamp, accurateCastOrDefault(session_id, 'UUID') as session_id, action, version, payload
```

Renaming a column:

```
FORWARD_QUERY >
    SELECT old_name as new_name, other_column
```

### After migration

Once the deploy applies the `FORWARD_QUERY` and the schema change is live, the `FORWARD_QUERY` has done its job. You can remove it from the datafile in a subsequent deploy if no further schema changes are pending. Keeping stale `FORWARD_QUERY` blocks around adds unnecessary complexity.

#