Read Query

The Read Query operation allows you to write your own query to extract the data of an entity, instead of the default read of the whole table or collection. It gives you complete control over how the data is retrieved and is executed before any transformation operations in the pipeline.

In the operations grid it appears in Query Operations as Read Query ("Use a custom SQL/no-sql query to extract data from the tap").

Overview

The Read Query operation enables you to:

• Write custom SQL SELECT statements, SOQL queries, or MongoDB filters and aggregation pipelines
• Join tables, filter records and aggregate data as needed
• Apply a different query to each entity
• Use project variables inside the query
• Test the query against the tap before saving it

Configuration

Entity-Level Query Customization

The operation shows the list of entities of the model, with a Search Entities box on top. Each entity has three icons:

Only the entities with a query are saved in the operation. The rest are read as usual.

Query Editor

The editor opens in a window titled Edit entity - <entity> with two tabs:

• Query: the query text. It is required ("The query text is required"). The placeholder shows SELECT * FROM <entity> and a MongoDB aggregation example.
• Field List: the fields of the entity in the schema, with their Field, Type and Length. Use it to check that the query returns the columns the entity expects.

The button at the top right of the text box (Show variables) lists the project variables. Picking one inserts $(name) at the cursor. Variables are replaced with their values when the query is tested and when the job runs.

Query Syntax

The query should return the fields the entity expects, with the same names, because the downstream operations and the sink work with the schema of the entity.

Test Query

The Test Query button runs the query against the tap of the model and shows the result below the editor:

• Query executed successfully, "Retrieved N record(s)". The test reads at most 5 records, so N is between 0 and 5. It only counts records; no data is shown.
• Query has errors, followed by the error message returned by the database.
• "An unexpected error occurred while testing the query" when the test could not be run.

The test uses the project variables, so $(name) is replaced as it would be in a job. Variables that don't exist are left as they are, and the database will usually report an error.

When you edit the query text, the last result is cleared.

Saving the Query

Save also tests the query:

• If the last Test Query succeeded and the text has not changed since, the query is saved directly.
• Otherwise the query is tested first. It is saved only if the test succeeds. If it fails, the error is shown and the window stays open, so you can fix the query.

Testing needs the ManageRules permission and a reachable tap.

 Save
  │
  ├── last test OK? ── yes ──► saved
  │
  └── no ──► run test (max 5 records)
               ├── OK ─────► saved
               └── error ──► "Query has errors", not saved

How the query is used

• The query replaces the default read of the entity, so the Where conditions, the selected fields and the Order of that entity are not applied. Put them in the query.
• The number of records of Limit is applied to the query on every database, always taking the first records:

• A ;, blank lines and comments at the end of a SQL query are removed before it runs, so they don't break the subquery.
• Your own LIMIT, FETCH FIRST, TOP or ORDER BY stays inside the subquery: the job reads at most the records your query returns, and never more than the Limit.
• SQLite taps run the Read Query too.

Entity groups

When the entity is the main entity of an entity group, write the query once on the main entity. When the job runs, each partition of the group gets a copy of that query in which every occurrence of the main entity name is replaced by the name of the partition:

Main entity orders:        SELECT * FROM orders WHERE status = 'paid'
Partition orders_202401:   SELECT * FROM orders_202401 WHERE status = 'paid'
Partition orders_202402:   SELECT * FROM orders_202402 WHERE status = 'paid'

• The query of a partition is replaced by the copy, even if you wrote one on the partition itself.
• The replacement is a plain text replacement of the entity name, with the same case, as it appears in the entity list. Write the table name in the query exactly like that, and avoid other names that contain it (with the main entity orders, a join with orders_items would become orders_202401_items).
• Which groups are expanded depends on the include/exclude operation of the rule, as for the other operations. See Groups in rules.

Ensure Referential Integrity

Below the entity list there is an Ensure Referential Integrity checkbox. It is the same option as in Where, Limit and Synthesize: if it is checked in any of these operations, it is on for the whole rule. In Read Query, Where and Limit the checkbox is only shown on SQL taps (Postgres, MySQL, Google Cloud SQL, Oracle, MSSQL, DB2, SQLite and Databricks); on other taps the option has no effect.

With it, a child row is extracted only if the parent row it references is extracted too. When a parent entity has a Read Query, its children are checked against the rows returned by that query.

Examples

Basic Custom Query

To replace the default extraction for a customers entity with a custom query:

1. Add the Read Query operation to your rule
2. Click the pen icon of the customers entity
3. Write a custom query like:

SELECT customer_id, first_name, last_name, email, phone, created_date
FROM customers
WHERE status = 'active'
ORDER BY created_date DESC

4. Click Test Query and check the result
5. Click Save

Query with Variables

With a project variable country:

SELECT * FROM customers WHERE country = '$(country)'

The value of the variable is pasted into the text as is, so add the quotes the database needs.

Complex Query with Joins

To create a unified view of orders and customer data:

SELECT o.order_id, o.order_date, o.total_amount,
       c.first_name, c.last_name, c.email,
       p.product_name, p.category
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= DATEADD(month, -6, GETDATE())

MongoDB Aggregation

To read only active customers without the notes field:

[
  { "$match": { "status": "active" } },
  { "$project": { "notes": 0 } }
]

Best Practices

• Use Test Query before saving, and check the Field List tab
• Keep queries as simple as possible for better performance
• Use indexes appropriately in your queries
• Make the column names match the entity field names
• Variables are pasted into the query text, so only use trusted values

This operation provides advanced users with the flexibility to extract exactly the data they need, bypassing the need for multiple filtering operations.