Query
Category: Import and export
Version: 1.0
Last updated: November 19, 2025
Author: Any2Info
Description
The Query node allows you to execute SQLite queries against the output produced by the previous node in the data flow. All data passed from the preceding node is exposed through a virtual table named Dataflow, enabling you to further filter, transform, and select values as needed.
In addition to the Dataflow table, the node also provides access to any existing resultsets. Each resultset is stored internally as an SQLite table, allowing you to query them directly. This makes it possible to combine, join, or enrich the incoming data with data retrieved from these resultsets.
SQLite
Since the datahub operates locally—and may even run as an edge device—it must use a lightweight, embedded database engine to execute queries directly on the device. For this purpose, the datahub uses SQLite, which provides fast, serverless data processing with minimal resource requirements.
Because SQLite follows its own SQL dialect, some syntax and functions differ from systems such as SQL Server. As a result, certain SQL expressions may behave differently than users expect. Even so, SQLite offers a powerful and efficient environment for the filtering, transformation, and data-combination tasks performed within the Data Hub.
SQLite functions
SQLite supports a wide range of functions and SQL constructs that can be used to filter, and transform data within the datahub.
Below is an overview of commonly used SQLite functions and syntax elements that are relevant when writing queries in the Query node.
1. Selection and Limiting
LIMIT • Retrieves a specific number of rows (SQLite’s equivalent of TOP). • Example: SELECT * FROM table LIMIT 10
LIMIT … OFFSET • Used for paging (skip a number of rows, then return the next set). • Example: LIMIT 10 OFFSET 20
2. String Operations
|| • Concatenates two or more strings. • Example: firstName || ' ' || lastName
substr(column, start, length) • Returns a substring starting at position 1. • Example: substr(name, 1, 3)
length(column) • Returns the number of characters in a string.
upper(column) • Converts a string to uppercase.
lower(column) • Converts a string to lowercase.
TRIM(column) • Removes leading and trailing whitespace.
LTRIM(column) • Removes whitespace from the left side of the string.
RTRIM(column) • Removes whitespace from the right side of the string.
TRIM(column, 'x') • Removes a specific character from both sides. • Example: TRIM(column, '.')
3. Joins
INNER JOIN • Returns rows that exist in both tables. • Example: SELECT … FROM A JOIN B ON A.id = B.fk
LEFT JOIN • Returns all rows from the left table and matching rows from the right table. • Example: SELECT … FROM A LEFT JOIN B ON A.id = B.fk
Not supported in SQLite • RIGHT JOIN • FULL OUTER JOIN • CROSS APPLY / OUTER APPLY
4. Type Conversions
CAST(column AS INTEGER) • Converts a value to an integer.
CAST(column AS REAL) • Converts a value to a floating-point number (double).
CAST(column AS TEXT) • Converts a value to a string.
5. Date and Time Functions
date('now') • Returns the current date (YYYY-MM-DD).
time('now') • Returns the current time (HH:MM:SS).
datetime('now') • Returns the current date and time.
strftime(format, value) • Formats a date or time value. • Example: strftime('%Y-%m-%d', 'now')
date('now', '+x days') • Adds or subtracts days. • Example: date('now', '+7 days')
datetime('now', '-x hours') • Adds or subtracts hours. • Example: datetime('now', '-1 hour')
6. Logical Operations and NULL Handling
CASE WHEN … THEN … END • Conditional logic for branching.
IFNULL(value, fallback) • Returns the fallback value if the first value is NULL (similar to ISNULL in SQL Server).
COALESCE(a, b, …) • Returns the first non-NULL value from the list.
7. Collations and Case Sensitivity
COLLATE NOCASE • Performs case-insensitive comparison. • Example: column = 'abc' COLLATE NOCASE
COLLATE BINARY • Performs case-sensitive comparison. • Example: column = 'abc' COLLATE BINARY
Context tables
In addition to the standard [DataFlow] table, the Query node provides access to two special context tables:
[DataFlow.Context]
[DataFlow.Nodes]
These tables expose information about the current DataFlow session and can be used for auditing, monitoring, logging, reporting, or creating runtime summaries directly within a query.
The context tables are only created when they are referenced in the query. The current Query node is excluded from the context information. As a result, these tables only contain information about nodes that have already been executed within the current session.
DataFlow.Context
The [DataFlow.Context] table contains a single row with summary information about the current DataFlow session.
SessionId
Unique identifier of the current DataFlow session.
DataFlowId
Identifier of the DataFlow, if available.
CollectionId
Identifier of the collection in which the DataFlow is running.
Name
Name or description of the DataFlow execution.
Triggered
Indicates how the flow was started.
Started
Date and time when the DataFlow session started.
NodeCount
Number of executed nodes in the current session, excluding the current Query node.
WarningCount
Total number of warnings logged so far, excluding the current Query node.
ErrorCount
Total number of errors logged so far, excluding the current Query node.
Example
Combining runtime information with the current data:
DataFlow.Nodes
The [DataFlow.Nodes] table contains one row for each node that has already been executed in the current DataFlow session.
NodeId
Unique identifier of the node execution.
Parent
Identifier of the parent node. Empty for the root node.
Name
Display name of the node as shown in the execution log.
ExtensionId
(GUID) of the executed DataFlow function.
Started
Date and time when the node started execution.
Finished
Date and time when the node finished execution.
RowCount
Number of output rows produced by the node.
WarningCount
Number of warnings logged for the node.
ErrorCount
Number of errors logged for the node.
Example
Runtime summary example:
Practical Example: Continue Processing Only When No Errors Have Occurred
The context tables can be used to build conditional logic within a DataFlow.
For example, the following query returns all rows from the current [DataFlow] table only when no errors have been logged by previously executed nodes:
If one or more errors have occurred earlier in the DataFlow, the query returns no rows.
This can be combined with the Has Rows precondition on a subsequent node. The next node will only execute when all previously executed nodes completed without errors, providing a simple mechanism to stop processing when a failure has occurred earlier in the DataFlow.
Tips & Best Practices
Be aware of SQLite-specific syntax. Some SQL Server features (such as RIGHT JOIN or FULL OUTER JOIN) are not available, and certain functions behave differently.
Use the Dataflow table as the primary input for your query, and only reference result sets when needed.
Errors & Troubleshooting
• Syntax differences from SQL Server. Errors may appear if SQL Server syntax is used. Features like RIGHT JOIN, FULL OUTER JOIN, or certain functions are not supported in SQLite.
Changelog
1.0
December 4, 2025
Initial documentation version added.
1.1
June 8, 2026
Added support for the system tables:
dataflow.context
dataflow.nodes
Last updated
Was this helpful?