Your first model

Models are the foundational component of Quary. Models are data transformations from one shape to another. They form the logic that you will use to build up your data warehouse.

Each model is a .sql file stored in the models directory. The name of the model is derived from the name of the file; for example, a file called employees.sql is the definition for the employees model. The subdirectory structure inside of models does not affect the name of the model; it's a useful structure for organisation. The following guide is a continuation of the sample project guide and will walk you through creating your first model.

Create model

To add your own model, create a .sql file. For this example, let's suppose we want to create a model called employees_by_shift which returns the employees ordered by the number of shifts worked. To do this, create a file called employees_by_shift.sql and save it in the models directory as shown below.

Create SQL file

The sql file we'll compose will look like this:

SELECT
    employee_id,
    first_name,
    last_name,
    total_shifts AS number_of_shifts
FROM
    q.shifts_summary
ORDER BY
    total_shifts DESC

The above sql reads the shifts_summary model and returns the employee_id, first_name, last_name and total_shifts columns. It also orders the results by the total_shifts column in descending order. q is the way to refer to other models in Quary. While typing, you may have noticed the autocomplete feature which will help you reference other models.

Autocomplete from

Run model

Once saved, you can run the model. In the top right corner of your sql panel, you will see 3 buttons. Theses are:

  1. Document Model: Opens the documentation for the model
  2. Test Model: Tests the model against the database
  3. Run Model: Runs the model SQL against the database
Quary command toolbar

Start by pressing the Play Button/Run Model to check that our model returns some useful data. Once clicked, you should see a results table, as following.

Run the model

In this Run panel, you can:

  • Download the results as a .csv file
  • Copy the results to your clipboard
  • Change the number of rows to fetch

Model documentation

Just like you ran the model with the play button, you can also open model documentation tab with the book icon. This should open the following panel where you can see the columns that Quary infers from the model, some inferred documentation, the lineage of the model as well as the results.

Run the model

Congrats on creating your first model! Once you have the following panel open, you are ready to move to your first test.

Sample project
Your first test