Skip to main content

Model: Transform Data

Data models in Rill are composed of SQL SELECT statements that operate on source data. They allow you to join, transform, and clean data.

SQL transformations​

Data transformations in Rill Developer are powered by DuckDB and their dialect of SQL (DuckDB SQL). Please visit DuckDB SQL documentation to learn about how to write your queries.

Adding a data model​

Using the UI​

To add a new data model using the UI, click "+" by Models in the left hand navigation pane. You can now begin typing a DuckDB SQL SELECT query for your model in the code editor – with keystroke-by-keystroke feedback.

Using code​

When you add a data model using the UI, a code definition will automatically be created as a <model_name>.sql file in the models folder in your Rill project.

In addition, you can create a model outside of the application and add it to Rill by placing a <model_name>.sql file in the models directory containing a DuckDB SQL SELECT statement. Rill will automatically detect and parse the model next time you run rill start.

How to use data models​

One Big Table and dashboarding​

It is powerful to be able to translate many ad hoc questions into a data framework that can answer a class of questions at scale. For example, high-level insights that are relevant to a company (how much revenue did we make last week?) are more actionable for an employee if it is relevant for their role (how did my campaign increase revenue last week?).

To experience the full potential of Rill, model your data sources into "One Big Table" – a granular resource that contains as much information as possible and can be rolled up in a meaningful way. This flexible OBT can be combined with a generalizable metrics definition to enable ad hoc slice-and-dice discovery through Rill's interactive dashboard.

Intermediate processing​

Models can also be cross-referenced between each other to produce the final output for your dashboard. The advantage here is more complex, intermediate data transformations can be utilized to achieve your final source for the dashboard. Example ideas for modeling:

  • Lookups for id/name joins
  • Unnesting and merging complex data types
  • Combining multiple sources with data cleansing or transformation requirements