Query and reconciliation reference

SQL conventions

Dialect: Queries and SQL rules run on Snowflake. Use Snowflake SQL syntax and functions (for example REGEXP_SUBSTR, ILIKE, QUALIFY, DATE_TRUNC)
Read-only: The system rejects statements that modify data or schema: INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE.
Identifiers: Quote mixed-case column and alias names with double quotes, for example InvoiceNumber.
Output aliases: Give each selected column an alias that differs from the raw source column name (for example, COUNTRY AS "Country"). A query can preview successfully but fail to save with Semantic model must use different alias if an output column reuses a source column name.
Statements: End with a semicolon.

Query layouts

Basic table: One query, single source.
Cross-check: Two queries compared through match conditions (reconciliation).
Side-by-side: Two queries shown in parallel, without matching.

Parameters

Parameters let a query be run with different values. Reference a parameter in SQL with the Snowflake-style bind syntax :name (for example, :StartDate). Each parameter has a Name (required), an optional Description, and a Type. In the query builder, the type options are String, Number and decimal, and Date.

In the query builder, typing :name auto-detects the parameter and opens a configuration popup. It's highlighted until you assign a type. When you create a report from a query, you must supply a value for every parameter. The number of values must match the number of parameters.

Filtering, grouping, and aggregation

Filtering

Narrows which rows you see. It's available in three places:

In a query as a SQL WHERE clause (often driven by parameters)
In a report or Data grid through each column's filter control
In a rule through Filter conditions.

Grouping and aggregation

Rolls many rows up into totals. In a query, group with GROUP BY and aggregate with functions such as SUM, COUNT, AVG, MIN, and MAX. For example, total gross per country. In a report grid, a column's Group by groups rows on screen. To summarize a finished report without changing its query, use Pivot. Drag fields into Rows and Columns and choose an aggregation (sum, count, average) for the Values. See Work with a report for more information.

Reconciliation match syntax

A cross-check (reconciliation) query is two SELECT statements, each ending with a semicolon, followed by the marker #[MATCH] and a comma-separated list of column = column conditions (referencing each query's output aliases), ending with a semicolon. Use the marker exactly once. It must follow at least one query and must not be followed by more queries (exactly two queries).

Provide at least one match condition, and give every key a value. The two result sets are referenced as source1 and source2 when writing SQL rules. Reconciliation reports automatically add STATUS, MATCH_ID, and ID columns, but don't define these yourself. For the source tables and columns you can reference, see Data model schema reference.

CODE

SELECT 'SAFT' AS "Source", "INVOICENUMBER" AS "InvoiceNumber", ROUND(GROSSTOTAL,2) AS "GrossAmount"
FROM SAFT_INVOICES WHERE INVOICEDATE BETWEEN :StartDate AND :EndDate;
SELECT 'VAT Filing' AS "Source", "INVOICENUMBER" AS "InvoiceNumber", ROUND(GROSSTOTAL,2) AS "GrossAmount"
FROM VAT_INVOICES WHERE INVOICEDATE BETWEEN :StartDate AND :EndDate;
#[MATCH]
"InvoiceNumber" = "InvoiceNumber",
"GrossAmount" = "GrossAmount";

SELECT 'SAFT' AS "Source", "INVOICENUMBER" AS "InvoiceNumber", ROUND(GROSSTOTAL,2) AS "GrossAmount"
FROM SAFT_INVOICES WHERE INVOICEDATE BETWEEN :StartDate AND :EndDate;
SELECT 'VAT Filing' AS "Source", "INVOICENUMBER" AS "InvoiceNumber", ROUND(GROSSTOTAL,2) AS "GrossAmount"
FROM VAT_INVOICES WHERE INVOICEDATE BETWEEN :StartDate AND :EndDate;
#[MATCH]
"InvoiceNumber" = "InvoiceNumber",
"GrossAmount" = "GrossAmount";

How matching works

A cross-check reconciliation compares two result sets, referenced as source1 and source2. A row is Matched when the match conditions you defined are satisfied. The within operator applies a tolerance (see below). Matched records share a Match Id.

Cardinality

When a key matches several records on the other side, behavior depends on the path:

SQL rules: Resolve to a deterministic many-to-one grouping: candidate rows are collapsed by the match key so each group resolves to a single Match Id. A cleanup step then reopens any Match Id that ends up with only one record. A Match Id exists only when two or more records are grouped.
Cross-check (#[MATCH]) initialization: Pairs the two sides with a join on your match conditions. If a key matches multiple records on the other side, the winning partner among the candidates is not guaranteed. Choose match keys specific enough to identify a single counterpart (for example, invoice number plus amount) to keep results deterministic.
Manual Match: Can group several selected records under one shared Match Id.

Duplicate keys

There is no de-duplication step. Duplicate keys within one source flow through the match. After a rule runs, any Match Id left with fewer than two records is reset to Open.

Reconciled versus unreconciled

In the report Overview, Reconciled counts only Matched rows and Unreconciled counts only Open rows. Known Exception and Resolved rows are counted in neither, so Reconciled plus Unreconciled doesn't necessarily equal the total. There is no "matched but outside tolerance" state. Tolerance is part of the match test, so a row outside tolerance simply does not match and stays Open.

Tolerance (the within operator)

Tolerance is applied per condition. Dates use an absolute day window (the match date, plus or minus the tolerance in days). Numbers use a percentage relative to the reference value; when the reference value is 0, an absolute comparison is used instead. The tolerance value must be a non-negative whole number. In a SQL rule, tolerance is whatever you write in the expression (for example, ABS(a - b) <= 100), which is absolute.

Rules versus Manual Match (precedence)

Re-running rules re-categorizes rows and can overwrite earlier results, including Manual Match. Each rule sets the Match Id and status for every row its condition touches, without checking whether the row was matched manually. A later rule that targets a row as Open clears a Match Id set by a manual match. Rules apply in Priority order (ascending), so the last applicable rule wins for a given row. Manual matches aren't preserved across a rule re-run that targets the same rows. Re-apply Manual Match after re-running rules if needed.

Manual Match mechanics

You can pair one record with another, or select several records to group under one shared Match Id. The system checks only that each record isn't already matched. It doesn't verify that the two records come from different sources. In the user interface a manual match pairs a record from one source with a record from the other, so pairs are cross-source in practice, but same-source pairing is not blocked by the server.

Reconciliation row statuses

Each reconciliation row has one of four statuses:

Open: The default state. Rows start here and return here when unmatched or when a rule clears a match. Sub-categories include Not started, Pending correction, Investigate further, Draft, and On hold.
Matched: Set when match conditions are met. A sub-category records how the match was made: Auto (the automatic engine or a rule), Manual (a user match), or AI.
Known Exception: User-set. Flags a row as an accepted or known difference.
Resolved: User-set. Marks a row as handled.

The system sets Open and Matched (Auto); users set Matched (Manual), Resolved, Known Exception, or return a row to Open.

Rule conditions

A rule uses either Filter conditions or a SQL rule, not both. At least one is required.

Filter conditions

Take the form When [field] [operator] [value], combined with AND/OR. Operators by field type:


Field type	Operators
TEXT	=, !=, doesNotEqual, equals, contains, startswith, endswith, isAnyOf, isempty, isnotempty
NUMBER	=, !=, doesNotEqual, equals, contains, <, <=, >, >=, between, within, isAnyOf, isempty, isnotempty
DATE / TIMESTAMP_NTZ	=, !=, doesNotEqual, equals, contains, <, <=, >, >=, between, within, isempty, isnotempty
BOOLEAN	=, !=, doesNotEqual, equals, true, false, isempty, isnotempty

The within operator needs a tolerance value; between takes a range. A condition can compare a column to another column, or a column to a fixed value.

SQL rules

Reference source1 and source2 columns (quote mixed-case names), for example source1."GrossAmount" > source2."GrossAmount" * 0.95. SQL rules are read-only. As you type, the editor shows Error, Warning, and Info markers, and you can save after the SQL is non-empty and error-free.

Best-practice hints (query editor)

As you write a query, the editor flags:

SELECT * (warning)
HAVING without an aggregate (warning)
JOIN without ON or USING (error)
ORDER BY by column position (warning)
A leading LIKE wildcard (warning)
UNION ALL, EXISTS over IN (SELECT ...), and caution with DISTINCT (info).

Note:

Warnings are advisory. Errors block saving.

Validation errors you may see

Query shorter than six characters: Invalid query.
More than one match marker: Only one #[MATCH] could be added.
#[MATCH] with other than two queries: When specifying #[MATCH], two queries are needed...
Empty or invalid match conditions: Invalid match condition setup.
Multiple statements without #[MATCH]: For a RawQuery only one query is allowed.
Output alias reuses a source column name: Semantic model must use different alias.

Validation constraints

Minimum query length: six characters.
Query name: up to 100 characters. Description and parameter fields: up to 200 characters.
A reconciliation query contains exactly two queries (used with #[MATCH]).
XLSX exports are capped at Excel's limit of 1,048,576 rows.

Data and Analytics