Query

The Query node executes SQLite queries against the output produced by the previous node in the dataflow. All data passed from the preceding node is exposed through a virtual table named Dataflow, letting you filter, transform, and select values as needed. The node also provides access to any existing resultsets: each is stored internally as an SQLite table, so it can be queried, combined, joined, or used to enrich the incoming data.

Why SQLite

Because Datahub operates locally (and may even run as an edge device), it uses a lightweight, embedded database engine to execute queries directly on the device: SQLite, which provides fast, serverless data processing with minimal resource requirements. Since SQLite follows its own SQL dialect, some syntax and functions differ from systems such as SQL Server, so certain SQL expressions may behave differently than expected.

SQLite functions

Selection and limiting

• LIMIT: retrieves a specific number of rows (SQLite’s equivalent of TOP), e.g. SELECT * FROM table LIMIT 10.
• LIMIT ... OFFSET: used for paging, e.g. LIMIT 10 OFFSET 20.

String operations

• ||: concatenates strings, e.g. firstName || ' ' || lastName.
• substr(column, start, length): returns a substring starting at position 1, e.g. substr(name, 1, 3).
• length(column): returns the number of characters in a string.
• upper(column) / lower(column): converts a string to uppercase / lowercase.
• TRIM(column) / LTRIM(column) / RTRIM(column): removes leading and/or trailing whitespace.
• TRIM(column, 'x'): removes a specific character from both sides, e.g. TRIM(column, '.').

Joins

• INNER JOIN: returns rows that exist in both tables, e.g. 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.
• Not supported in SQLite: RIGHT JOIN, FULL OUTER JOIN, CROSS APPLY / OUTER APPLY.

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.

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, e.g. strftime('%Y-%m-%d', 'now').
• date('now', '+x days'): adds or subtracts days, e.g. date('now', '+7 days').
• datetime('now', '-x hours'): adds or subtracts hours, e.g. datetime('now', '-1 hour').

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.

Collations and case sensitivity

• COLLATE NOCASE: case-insensitive comparison, e.g. column = 'abc' COLLATE NOCASE.
• COLLATE BINARY: case-sensitive comparison, e.g. 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] and [DataFlow.Nodes]. These 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, so these tables only contain information about nodes that have already been executed within the current session.

DataFlow.Context

A single row with summary information about the current DataFlow session.

Column	Description
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.

SELECT * FROM [DataFlow.Context]

SELECT d.*, c.SessionId, c.Started
FROM [DataFlow] d
CROSS JOIN [DataFlow.Context] c

DataFlow.Nodes

One row for each node that has already been executed in the current DataFlow session.

Column	Description
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.

SELECT Name, RowCount, WarningCount, ErrorCount
FROM [DataFlow.Nodes]
ORDER BY Started

SELECT
    c.Name AS DataFlowName,
    c.Started,
    c.NodeCount,
    c.WarningCount,
    c.ErrorCount,
    n.Name AS NodeName,
    n.RowCount
FROM [DataFlow.Context] c
LEFT JOIN [DataFlow.Nodes] n ON 1 = 1
ORDER BY n.Started

Practical example: continue processing only when no errors have occurred

The context tables can be used to build conditional logic within a DataFlow. The following query returns all rows from the current [DataFlow] table only when no errors have been logged by previously executed nodes:

SELECT d.*
FROM [DataFlow] d
CROSS JOIN [DataFlow.Context] c
WHERE c.ErrorCount = 0

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, so the next node only executes when all previously executed nodes completed without errors: a simple mechanism to stop processing when a failure occurred earlier in the DataFlow.

Tips

• 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 resultsets when needed.

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.