Models
Detailed guide on custom SQL models and how to create, update, and fetch their details in the RudderStack dashboard.
Last updated
Was this helpful?
Detailed guide on custom SQL models and how to create, update, and fetch their details in the RudderStack dashboard.
Last updated
Was this helpful?
RudderStack's Models feature lets you create models by defining custom SQL queries. You can then run these queries on your warehouse and send the resulting data to specific destinations. You can create as many models and reuse them with their corresponding Reverse ETL sources.
With this feature, you can:
Build models with complex SQL queries via an intuitive UI.
Manage the views of all the models synced to different destinations in one place.
Reuse the existing models in multiple connections.
Models is a Reverse ETL feature. For more information on setting up your Reverse ETL sources using models, refer to the Importing Data using Models guide.
Currently, RudderStack supports this feature for the following Reverse ETL sources:
You can find the Models option in the left navigation bar of the RudderStack dashboard that lets you view all the existing models, as shown:
Follow these steps to create a model:
Log in to the RudderStack dashboard.
Click on Models in the left navigation bar and select New model.
Select the Reverse ETL source for the model and click on Next.
Enter a name for the model.
Select your existing warehouse credentials or click on Create Credentials from Scratch and enter the warehouse-specific details.
Your warehouse account must have the necessary user permissions for RudderStack to access it. For more information on these user permissions, refer to the Granting Permissions section of the relevant Reverse ETL source.
Next, enter the Description for the model. Then, add your custom SQL query in the Query section.
Click on Run Query to preview the result.
The Preview section displays the preview of the 50 resultant rows in a paginated format.
Click on Next to create the model.
Once a model is created, you can use it to send the data from a Reverse ETL source to a specified destination. For more information and detailed steps, refer to the Importing Data using Models guide.
Follow these steps to update an existing model:
Click the model you want to update and select the Configuration tab.
Click on Edit Configuration and make the required changes in Description and Query.
If you have edited the query, click on Run Query to see if the new query generates the desired result.
Finally, click on Finish to save your changes.
If you update a model's query, you also need to check and update the relevant JSON mapping or Visual Data Mapping. To validate if the query is running fine, you can manually trigger a full sync.
To get details about an existing model, you can click on it to see the options listed below:
Rename: Lets you rename the model by clicking on the edit icon next to the model's name.
Sources: Displays the sources associated with the model. To know more on how to use a model with a source, refer to the Setting Up Connections Using Models guide.
Configuration: Displays and lets you edit the SQL Query and Description.
Settings: This option contains the following two settings:
Permanently delete the model: Lets you delete the model by clicking on the Delete button. The Delete button is only enabled when no source is using the model.
Credentials: Lets you edit the credentials used for the model.
After editing the credentials, it is recommended to validate the query by running it and fetching the preview via the Configuration tab as explained in the Updating an existing model section above.
When setting up a Reverse ETL source, once you proceed after entering the connection credentials, you will see the following three validations under the Verifying Credentials option:
These options are explained below:
Verifying Connection: This option indicates that RudderStack is trying to connect to the warehouse with the information specified in the connection credentials.
If this option gives an error, it means that one or more fields specified in the connection credentials are incorrect. Verify your credentials in this case.
Able to List Schema: This option checks if RudderStack is able to fetch all the schema details using the provided credentials.
Able to Access RudderStack Schema: This option implies that RudderStack is able to access the RudderStack schema that you have created by successfully running the commands in the User Permissions section of the relevant Reverse ETL source.
If this option gives an error, verify if you have successfully created the RudderStack schema and given RudderStack the required permissions to access it.
For more information on any of the sections covered in this guide, you can contact us or start a conversation in our Slack community.
Once you select or add your warehouse credentials and click on Next, RudderStack will validate them before you can proceed with the setup. For more information on these validations, refer to the FAQ section below.
