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.
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.
sql file we'll compose will look like this:
sql reads the
shifts_summary model and returns the
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.
Once saved, you can run the model. In the top right corner of your
sql panel, you will see 3 buttons. Theses are:
- Document Model: Opens the documentation for the model
- Test Model: Tests the model against the database
- Run Model: Runs the model SQL against the database
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.
In this Run panel, you can:
- Download the results as a
- Copy the results to your clipboard
- Change the number of rows to fetch
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.
Congrats on creating your first model! Once you have the following panel open, you are ready to move to your first test.