Skip to main content

SQL Sensors

SQL sensors are a type of model designed to trigger dependent data models based on true/false SQL statements similar to those created in validations. They can be used to identify when ingestions are complete, thus triggering dependent data models.

Create a SQL Sensor#

To create a new validation select the add MODEL button just as you would a typical data model. In the Type dropdown select Sensor.

SQL Sensor type selection

Name#

Select a name for your SQL sensor. There are a few rules to creating a name:

  • No more than 32 characters long
  • Use lowercase letters
  • Use numbers but not as the first character
  • Include underscores (ie. snake_case)

Description#

Write a description about your SQL sensor. You can always come back and edit this.

Pipeline#

Select a pipeline for the SQL sensor. You can change this at any time.

Template#

Templates allow you to work with prewritten code. Select a template to populate the SQL editor. Templates make it easier to start building queries. You can find out more about templates and how to build them in the Templates tutorial here.

SQL Editor#

Here is where you write or edit a true/false SQL query. For your first query let's make it a small and simple dataset that will populate quickly in your data warehouse.

Dependencies#

Dependencies are other models that the current model is dependent on. This is vital to making great pipelines as it tells FloSQL what models to run before other models. We have a unique feature that scripts through the query and populates all existing models in the dependency section. See the image below for this in action.

Example#

An example of a SQL sensor is to ensure that the an ingestion has completed before kicking off the rest of the scheduled dependencies. It is good practice to create a separate pipeline of SQL sensors for a particular ingestion you intend to your SQL sensors to depend on. For example, name the pipeline public after the public schema of a database production ingestion. This will ensure that all dependent data models will now recognize what was an outside dependency as an inside dependency.

For a daily ingestion the following SQL query will check whether the Maximum timestamp is greater than the current date. If the statement is true the platform dependent data models will kick off:

SELECT MAX(order_purchase_timestamp) >= DATE(CURRENT_TIMESTAMP)FROM {{this}}