SQL Model
At Y42 we have dedicated ourselves to building a full-stack data platform that anyone can run. We have built a drag-&-drop UI for data modeling which enables any user to turn raw data into clean datasets for data analysis & reports. With drag-&-drop nodes users can build data modeling workflows without the need to write a single line of code.
Although we have seen our users enjoying the drag-&-drop UI, we wanted to remove any possible friction regarding data modeling. That's why we moved away from being a solely no-code platform and introduced SQL into our modeling module.
In this article we'll explain how you can work with our SQL modeling tool.
Adding a SQL data model
- Click on Model in the left navigation bar.
- Click Add... in the top right-hand corner of your screen and choose SQL Model
- Type a name for your model, and choose whether it should be public or private.
- Click on Create.
A new SQL model is being created. You will be automatically redirected to the SQL editor.
How to start with a SQL query
Start your Y42 SQL model as you would start a UI Model. Select a table and preview the table to get an understanding of the table you have selected.
To select a table, start with typing SELECT * FROM @
Use @ to browse among the tables in your Y42 account. These tables include tables from your integrations and your models. The first part of the table name indicates the integration/model name. The second part shows the table name. You will see a long cryptic ID in the end. This is a Y42 internal unique ID to identify the different versions of the table.
Please note:
- When you create a new column, you need to name this column with the AS command. Otherwise the editor will give you an error message.
- Please do not mix up ' and ". Please use one of them consistently.
Preview your SQL query
To preview your SQL query, simply click on Preview Query as soon as you're done with editing your query.
Commit your SQL query and create a table
As soon as you are done writing your query, click on commit to save the query and to create the table.
Add another table to your SQL model
As similar to the UI model, you can also add another output table to your SQL model. Just simply click on Add Table to write another query. After committing this second table, your SQL model will create 2 output tables.
Tips
Our SQL model covers all BigQuery functions. To get an understanding about which functions you can use and how to use them, please have a look on the BigQuery documentation:
- https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
- https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators