Models

Create, update, and fetch details from custom SQL models.

  •   4 minute read  

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 views of all models synced to different destinations in one place.
  • Reuse existing models in multiple connections.
success
Models is a Reverse ETL feature. For more information on setting up your Reverse ETL sources using models, see Importing Data using Models.
info

RudderStack supports this feature for the following Reverse ETL sources:

Creating a new model

Follow these steps to create a model:

  1. Log in to the RudderStack dashboard and go to Activate > Models.
  2. Click New model.
RudderStack Models
  1. Select the Reverse ETL source for the model and click Next.
  2. Enter a name for the model.
  3. Select your existing warehouse credentials or click Create Credentials from Scratch and enter the warehouse-specific Connection Credentials.
warning
Your warehouse account must have the necessary permissions for RudderStack to access it. For more information, see the Permissions section of the respective Reverse ETL source.
Model settings
info
Once you select or add your warehouse credentials and click Next, RudderStack will validate them before you can proceed with the setup. For more information on these validations, see FAQ.
  1. Next, enter the Description for the model. Then, add your custom SQL query in the Query section.
info

Note that:

  • The Preview section displays the preview of the 50 resultant rows in a paginated format.
  • You can add single-line/multiline comments above the query. However, RudderStack supports only multiline comments at the end of the query. Support for single-line comments is coming soon.
  • You don’t need to add a semi-colon at the end of a query. RudderStack handles this automatically before running the query on the warehouse data.
  1. Click Run Query to preview the result.
Model preview result
  1. Click Next to create the model.

You can now use this model to send data from your Reverse ETL source to the specified destination. For more information and detailed steps, see Importing Data using Models.

Updating an existing model

  1. Click the model you want to update and go to the Configuration tab.
  2. Click Edit Configuration and make the required changes in Description and Query.
  3. If you have edited the query, click Run Query to verify that new query generates the desired result.
  4. Click Finish to save your changes.
warning

Note that:

  • If you update the 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.

Model details

  • Rename: Lets you rename the model. Click the the edit icon next to the model name.

  • Overview: Displays the Reverse ETL sources using the model. To learn more on how to use a model with a source, see Import data using models.

  • Configuration: Displays and lets you edit the SQL Query and Description.

  • Settings: This option contains the following two settings:

    • Credentials: Lets you edit the warehouse credentials used for the model.
    • Permanently delete the model: Lets you delete the model. The Delete button is enabled only when no source is connected to the model.
info
After editing the credentials, validate the query by running it and seeing the preview, as explained in the Updating an existing model section.

Connecting a model to a destination

  1. Create a new model in the RudderStack dashboard by going to Activate > Models.
  2. Set up a reverse ETL source by going to Collect > Sources. Select the warehouse used to create the model.
  3. Under Source type, specify Model and configure the rest of the settings.
Configure RudderStack models
  1. Connect the reverse ETL source to your destination. Configure the destination settings and click Continue.
  2. In the Data Mapping section, select the model created in step 1 in from the dropdown.
Connect RudderStack models
  1. Follow the steps listed in the Importing data using models guide for configuration settings required to import and sync data from this model.

FAQ

What do the three validations under Verifying Credentials imply?

Once you proceed after entering the connection credentials, you will see the following validations under the Verifying Credentials option:

Validations

These options are explained below:

  • Verifying Connection: Indicates that RudderStack is trying to connect to the warehouse with the information specified in the connection credentials.
warning
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: Checks if RudderStack is able to fetch all schema details using the provided credentials.

  • Able to Access RudderStack Schema: Checks if RudderStack has the required access to the _RUDDERSTACK schema. To create the schema, run all commands listed in the Permissions section of the respective Reverse ETL source documentation:

warning
If this option gives an error, verify if you have successfully created the _RUDDERSTACK schema and given RudderStack the required permissions to access it.

Questions? Contact us by email or on Slack