What the semantic layer is

The Query Builder does not run SQL directly against raw database tables. Instead, you query semantic models. These are curated, read-friendly views that Sovos publishes on top of the underlying compliance data. Each semantic model does the following:

• Exposes a stable set of columns with friendly, uppercase names, for example INVOICENUMBER, NETTOTAL, TAXCODE.
• Hides the messy source structure (JSON blobs, country-specific staging tables, versioning) behind a clean projection.
• Is multi-tenant. Every row carries ORGANIZATIONID and COMPANYID, and the platform automatically scopes results to data you are entitled to see.

When you pick a data source in the Query Builder, you are choosing one of these semantic models. This document tells you what each model means, what its columns contain, how the models join together, and how to write correct SQL against them.

How models map to the Query Builder experience

A saved query stores three things: the raw SQL, the semantic model or models it reads, and an alias for each model. In the SQL you write FROM <alias>, and the alias is normally the model's variant name. For example:

SELECT SOURCEDOMAIN, COUNT(*) AS TOTAL_INVOICES
FROM inv -- 'inv' is the alias bound to the Intelligence INVOICES model
GROUP BY SOURCEDOMAIN

Most saved queries alias the model to its own name, so you will usually see FROM VAT_INVOICES, FROM SAFT_INVOICES, and so on. You can join more than one model in a single query by binding several aliases.

The four domains at a glance

SQL dialect and conventions

Dialect

Snowflake SQL. Functions such as TO_CHAR, COALESCE, DATEADD, DATE_TRUNC, UPPER, SUM, COUNT(DISTINCT ...), and LISTAGG all work.

Column names

Column names are uppercase in the models. Snowflake folds unquoted identifiers to uppercase, so nettotal and NETTOTAL resolve to the same column. Use quotes only for output aliases you want to display verbatim, for example SUM(NETTOTAL) AS "Total Net".

Text-typed keys and codes

Many identifiers and even some numeric-looking codes are stored as TEXT. Compare them as strings, for example WHERE TAXCODE = '380', not = 380.

Dates

Some models type dates as DATE, others as TIMESTAMP_NTZ. Filter with string literals, for example BETWEEN '2025-01-01' AND '2025-12-31', and be aware a TIMESTAMP_NTZ column includes a time component.

The universal tenancy keys

The four domains describe the same businesses from different stages of the compliance lifecycle, so they share tenancy keys but are not pre-joined. Join them yourself on the keys below.

Every model in every domain exposes:

• ORGANIZATIONID: the Sovos organization (tenant)
• COMPANYID: the specific legal entity or company within the organization

COMPANYID is the most reliable key for stitching a single company's data across CN, VAT Filing, SAF-T, and Intelligence. Several models also carry a SOURCECOMPANYCODE or SOURCECOMPANYID, the identifier of the company as it appears in the source ERP, which is useful when the same entity is loaded from multiple systems.

How the domains relate

Every model in every domain shares the same tenancy key: CompanyId plus OrganizationId. The four domains describe the same businesses from different stages of the compliance lifecycle:

Within each domain, models relate as follows:

CN

CN_CANONICAL_INVOICES joins to CN_CANONICAL_INVOICES_LINES (header to its lines) on DOCUMENTUUID or INVOICEUUID. CN_ARCHIVE_INVOICES is a separate archive-status view, joined to the canonical model on NATURALKEY or DOCUMENTID, and CompanyId.

VAT Filing

VAT_INVOICES.TaxCode joins to VAT_TAX_CODES.InternalVatCode.

SAF-T

SAFT_TRANSACTIONS joins to SAFT_TRANSACTIONS_LINES on TransactionId, which in turn joins to SAFT_TAX_CODES on (TaxType, TaxCode). SAFT_INVOICES joins to SAFT_INVOICES_LINES on (Direction, InvoiceNumber, InvoiceDate, InvoiceType), and around 50 further master and child tables branch from these. See the SAF-T models reference for the full set.

Intelligence

INVOICES.SourceDomain distinguishes rows that originated in SAF-T from rows that originated in VAT Filing.

You do cross-domain joins in analytics, for example reconciling VAT-return totals against the SAF-T audit file, typically keyed on CompanyId plus a period and invoice number. Because grains differ between domains, always aggregate to a common grain before comparing.

The basic shape of a query

SELECT <columns and aggregates>
FROM <MODEL_NAME> -- the semantic model / alias
WHERE <filters, usually a date range and/or DIRECTION>
GROUP BY <non-aggregated columns>
ORDER BY <sort columns>

You don't add tenancy filters for organization or company. The platform applies them for you.

Handling dates

Invoice-type models frequently have several date columns (issue date, reporting date, transaction date, GL posting date) and any one of them can be NULL. Filter across the alternatives and sort on the first non-NULL value:

WHERE INVOICEDATE BETWEEN '2025-01-01' AND '2025-12-31'
 OR REPORTINGDATE BETWEEN '2025-01-01' AND '2025-12-31'
 OR TRANSACTIONDATE BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY COALESCE(INVOICEDATE, REPORTINGDATE, TRANSACTIONDATE)

Group by day or month with TO_CHAR:

SELECT TO_CHAR(COALESCE(INVOICEDATE, GLPOSTINGDATE), 'YYYY-MM') AS "Month", SUM(NETTOTAL)
FROM SAFT_INVOICES
GROUP BY TO_CHAR(COALESCE(INVOICEDATE, GLPOSTINGDATE), 'YYYY-MM')

Worked examples

The following are real saved queries, lightly formatted, that run against these models.

Intelligence: portfolio totals by source (simple)

CODECopy

SELECT SOURCEDOMAIN, COUNT(*) AS TOTAL_INVOICES, COUNT(DISTINCT COUNTRY) AS COUNTRIES, COUNT(DISTINCT COMPANYID) AS COMPANIES, SUM(NETTOTAL) AS TOTAL_NET, SUM(GROSSTOTAL) AS TOTAL_GROSS, MIN(INVOICEDATE) AS EARLIEST_DATE, MAX(INVOICEDATE) AS LATEST_DATE FROM INVOICES GROUP BY SOURCEDOMAIN ORDER BY TOTAL_INVOICES DESC;

SELECT SOURCEDOMAIN,
 COUNT(*) AS TOTAL_INVOICES,
 COUNT(DISTINCT COUNTRY) AS COUNTRIES,
 COUNT(DISTINCT COMPANYID) AS COMPANIES,
 SUM(NETTOTAL) AS TOTAL_NET,
 SUM(GROSSTOTAL) AS TOTAL_GROSS,
 MIN(INVOICEDATE) AS EARLIEST_DATE,
 MAX(INVOICEDATE) AS LATEST_DATE
FROM INVOICES
GROUP BY SOURCEDOMAIN
ORDER BY TOTAL_INVOICES DESC;

VAT Filing: all 2025 VAT transaction lines (simple, date handling)

CODECopy

SELECT DIRECTION, INVOICENUMBER, INVOICEDATE, COUNTERPARTNAME, NETTOTAL, VATTOTAL, GROSSTOTAL, CURRENCYCODE, TAXCODE, TAXRATE, COUNTRY FROM VAT_INVOICES WHERE INVOICEDATE BETWEEN '2025-01-01' AND '2025-12-31' OR REPORTINGDATE BETWEEN '2025-01-01' AND '2025-12-31' OR TRANSACTIONDATE BETWEEN '2025-01-01' AND '2025-12-31' ORDER BY COALESCE(INVOICEDATE, REPORTINGDATE, TRANSACTIONDATE), INVOICENUMBER;

SELECT DIRECTION, INVOICENUMBER, INVOICEDATE, COUNTERPARTNAME,
 NETTOTAL, VATTOTAL, GROSSTOTAL, CURRENCYCODE, TAXCODE, TAXRATE, COUNTRY
FROM VAT_INVOICES
WHERE INVOICEDATE BETWEEN '2025-01-01' AND '2025-12-31'
 OR REPORTINGDATE BETWEEN '2025-01-01' AND '2025-12-31'
 OR TRANSACTIONDATE BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY COALESCE(INVOICEDATE, REPORTINGDATE, TRANSACTIONDATE), INVOICENUMBER;

SAF-T: daily invoice volumes and amounts for a month (moderate)

CODECopy

SELECT TO_CHAR(COALESCE(INVOICEDATE, GLPOSTINGDATE), 'YYYY-MM-DD') AS "Date", UPPER(DIRECTION) AS "Direction", COUNT(*) AS "Invoice Count", SUM(NETTOTAL) AS "Total Net", SUM(GROSSTOTAL) AS "Total Gross" FROM SAFT_INVOICES WHERE INVOICEDATE BETWEEN '2025-08-01' AND '2025-08-31' OR GLPOSTINGDATE BETWEEN '2025-08-01' AND '2025-08-31' GROUP BY TO_CHAR(COALESCE(INVOICEDATE, GLPOSTINGDATE), 'YYYY-MM-DD'), UPPER(DIRECTION) ORDER BY 1, 2;

SELECT TO_CHAR(COALESCE(INVOICEDATE, GLPOSTINGDATE), 'YYYY-MM-DD') AS "Date",
 UPPER(DIRECTION) AS "Direction",
 COUNT(*) AS "Invoice Count",
 SUM(NETTOTAL) AS "Total Net",
 SUM(GROSSTOTAL) AS "Total Gross"
FROM SAFT_INVOICES
WHERE INVOICEDATE BETWEEN '2025-08-01' AND '2025-08-31'
 OR GLPOSTINGDATE BETWEEN '2025-08-01' AND '2025-08-31'
GROUP BY TO_CHAR(COALESCE(INVOICEDATE, GLPOSTINGDATE), 'YYYY-MM-DD'), UPPER(DIRECTION)
ORDER BY 1, 2;

VAT Filing: VAT by tax code, enriched from the tax-code master (join)

CODECopy

SELECT t.COUNTRYCODE, i.TAXCODE, t.VATRATETYPE, t.DESCRIPTION, SUM(i.NETTOTAL) AS NET, SUM(i.VATTOTAL) AS VAT FROM VAT_INVOICES i LEFT JOIN VAT_TAX_CODES t ON i.TAXCODE = t.INTERNALVATCODE WHERE i.INVOICEDATE BETWEEN '2025-01-01' AND '2025-12-31' GROUP BY t.COUNTRYCODE, i.TAXCODE, t.VATRATETYPE, t.DESCRIPTION ORDER BY VAT DESC;

SELECT t.COUNTRYCODE,
 i.TAXCODE,
 t.VATRATETYPE,
 t.DESCRIPTION,
 SUM(i.NETTOTAL) AS NET,
 SUM(i.VATTOTAL) AS VAT
FROM VAT_INVOICES i
LEFT JOIN VAT_TAX_CODES t ON i.TAXCODE = t.INTERNALVATCODE
WHERE i.INVOICEDATE BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY t.COUNTRYCODE, i.TAXCODE, t.VATRATETYPE, t.DESCRIPTION
ORDER BY VAT DESC;

SAF-T: invoice header totals joined to their lines (grain-aware join)

CODECopy

SELECT h.INVOICENUMBER, h.DIRECTION, h.INVOICEDATE, COUNT(l.INVOICELINENUMBER) AS LINE_COUNT, SUM(l.LINEEXTENSIONAMOUNT) AS LINES_NET FROM SAFT_INVOICES h JOIN SAFT_INVOICES_LINES l ON l.DIRECTION = h.DIRECTION AND l.INVOICENUMBER = h.INVOICENUMBER AND l.INVOICEDATE = h.INVOICEDATE AND l.INVOICETYPE = h.INVOICETYPE GROUP BY h.INVOICENUMBER, h.DIRECTION, h.INVOICEDATE;

SELECT h.INVOICENUMBER, h.DIRECTION, h.INVOICEDATE,
 COUNT(l.INVOICELINENUMBER) AS LINE_COUNT,
 SUM(l.LINEEXTENSIONAMOUNT) AS LINES_NET
FROM SAFT_INVOICES h
JOIN SAFT_INVOICES_LINES l
 ON l.DIRECTION = h.DIRECTION
 AND l.INVOICENUMBER = h.INVOICENUMBER
 AND l.INVOICEDATE = h.INVOICEDATE
 AND l.INVOICETYPE = h.INVOICETYPE
GROUP BY h.INVOICENUMBER, h.DIRECTION, h.INVOICEDATE;

Hidden system columns on every result

Every query result also holds ID, MATCH_ID, STATUS, ROW_ID_, and TOTALCOUNT even if you never selected them. They are query-engine helpers, not business data:

ID

A per-run row identifier. Not a stable business key, so do not join or deduplicate on it.

MATCH_ID, ROW_ID_

Internal identifiers.

STATUS

Engine row status.

TOTALCOUNT

The total row count, repeated on every row. A paging helper.

Select only the business columns you need.

Amounts are signed (VAT Filing)

NetTotal, VatTotal, and GrossTotal in VAT_INVOICES are debit minus credit, so negative values are valid (credit notes, reversals). Use SUM() for a true net position. Use ABS() only when you deliberately want magnitude.

DIRECTION governs SAF-T and VAT semantics

Sales and purchases are consolidated into single models. Constrain or group by DIRECTION (Outbound means sales, Inbound means purchase). Counterpart resolution, customer or supplier fields, and several amounts depend on it, and the counterpart join key is (COUNTERPARTID, DIRECTION).

NULL values may mean a field doesn't apply to that country (SAF-T)

SAF-T models present one consolidated view across countries. Columns that apply to only one country are NULL on rows from other countries. For example, Portugal-specific fields are NULL on Romanian rows, and vice versa. Filter by SOURCECOUNTRY or COUNTRY when comparing across countries so a country's blank column is not mistaken for missing data.

Codes are TEXT, and often numeric

Tax types, tax codes, invoice types, and document types are TEXT. Quote them in filters, for example = '380'. Their values are frequently numeric codes, for example TAXTYPE values such as 633 or 604, and INVOICETYPE values such as 380, 381, FT, and NC. Use SAFT_TAX_CODES or SAFT_TAX_CODES_DETAILS for SAF-T, and VAT_TAX_CODES for VAT, to resolve a code to its meaning. In the Intelligence INVOICES model, VAT-Filing rows carry the SourceDomain value VAT Filling. Match that exact string.

Mind the grain when joining or combining

Header models are one row per document. _LINES models are one row per line. _TAX_INFORMATION_TOTALS models are one row per document and tax code. Aggregate to a shared grain before joining across levels, or amounts multiply. In the Intelligence INVOICES model the grain differs by source (SAF-T rows are per invoice, VAT-Filing rows are per line), so aggregate within a single source rather than summing across them.

Dates: type and multiple candidates

Some date columns are DATE, others TIMESTAMP_NTZ (for example INVOICES.InvoiceDate). Invoice models expose several date columns and any one can be empty. Filter across the alternatives and sort with COALESCE(...), as shown in the worked examples.