BI Connector Best Practices

Advanced

In this guide you’ll learn the best practices & limitations of the BI Connector in Tinybird.

The BI Connector is a PostgreSQL-compatible interface to data in Tinybird.

All Data Sources and published Pipes created in Tinybird will be available as standard Postgres tables. You can query using standard Postgres syntax & use any tool that implements the Postgres protocol. This means you can connect many of your favorite tools to Tinybird, such as Tableau, Apache SuperSet or Grafana.

Tinybird is not designed to be a replacement for the Data Warehouse, nor used as a general purpose BI platform. However, there are use cases where it makes sense to use a BI tool over Tinybird. We encourage you to reach out to discuss your needs so we can help to guide you.

The BI Connector is not enabled by default. Please contact Tinybird support (support@tinybird.co) to discuss if we can support your use case.

Best practices

Avoid queries on exposed Pipes

Both your Pipes & Data Sources are exposed in the BI Connector as tables. We recommend that you only query against Data Sources tables, and avoid using any Pipes tables. In general, queries on Data Sources tables are much faster & easier to optimise.

Use Materialized Views

Tinybird allows you to build Pipes that materialize the result as a Materialized View. Materialized Views will appear in the BI Connector as tables.

Rather than querying the raw data via the BI Connector, we recommend you build your transformations, filters, aggregations or joins in standard Tinybird Pipes to create a Materialized View. You can then use the BI Connector to read the pre-prepared Materialized View.

For example, if your dashboard shows events aggregated by day, create a Materialized View with pre-aggregated data by day, and have your BI chart read this.

Use dedicated Materialized Views for different charts

If you have multiple charts or widgets consuming data, try to use Materialized Views that are specifically built for that chart or widget. This helps to avoid additional transformation operations.

Use efficient sorting keys

Data stored in Tinybird is sorted using a sorting key. This helps to efficiently find data that is relevant to your query.

You should use the appropriate sorting key(s) for your data according to the needs of your dashboard.

For example, if your dashboard is filtering by time, you should have time in the table’s sorting key.

Avoid column mappings

Avoid using column mappings. Filtering by a mapped column can mean that the index (sorting key) is not being used.

Avoid JOINs

We recommend that you do not do JOINs over the BI Connector where at all possible. Instead, do any JOINs inside a Pipe and materialize the result as a Materialized View, then query the already-JOINed data.

Monitor usage with bi_stats_rt

The bi_stats_rt table contains observability data about your usage of the BI Connector. This is a great way to get insights into your queries and inform your optimisations.

Limitations

Some settings can be adjusted on request. Please contact Tinybird support (support@tinybird.co) to discuss if we can support your use case.

  • Query timeout is 10 seconds

  • Queries over the BI Connector are only allocated 1 CPU core per query (this only applies to the BI Connector, not to standard Tinybird Pipes)

  • Some JOIN operations are run in Postgres and not ClickHouse, which means they can run much, much slower. Avoid JOINs over the BI Connector.

  • Filters could not use sorting keys, that could make queries slower and expensive (as full scans are done)

  • Querying large datasets can result in a timeout. Large generally means many millions of rows, but this is also affected by how many columns & how large the values are.