@ingestron/pack-synapse

The Azure Synapse serverless SQL pack: SQL views over the files a pack lands, transforms written as views, and views over tables another pack stores, so reporting tools such as Power BI read them as ordinary SQL tables. Name it in your standard: packs: [synapse@2].

One views.sql covers every environment. A value is a SQLCMD variable, $(name), only where environments differ, such as the database name; everything the same in every environment is written as it is. Each environment has a small script, environments/<environment>.sql, that sets those values and runs views.sql, so a reviewer reads the views once and sees exactly what changes between dev and prod.

npm install @ingestron/pack-synapse   # the ingestron CLI already includes it

Methods

Method	Action	Makes
synapse-view	store	A view over the table's landed files, or over its transform's query. Nothing runs after a load.
synapse-sql	transform	The transform's SQL in T-SQL, over other tables' views; synapse-view keeps it.
synapse-serve	serve	A view over the files another pack's store keeps, read from where that pack says.

Views read Parquet and CSV files through an external data source and type each column from the table's columns. views.sql is re-runnable: it selects each view's database with USE, and creates or alters each view. Views change only when contracts, source metadata, transforms or naming change.

Views only

With a pack that lands files, such as ADF, Synapse can be the whole store. List it first, even though ADF runs first: the order in packs is precedence, and the first pack names and types the views, such as the database name ({{ project }}_{{ environment }}) and the column types:

packs: [synapse@2, adf@2]
store: { default: synapse-view }

To serve tables another pack stores, list Synapse after that pack and choose synapse-serve for the tables to serve.

Name the external data source in platform.yaml, for each method you use:

settings:
  synapse-view:
    data_source: landing
    data_source_location: abfss://landing@acct.dfs.core.windows.net

Every file a view reads must be under data_source_location. A view over files that each load adds (ADF's increments layout) returns every load's rows; for the current data only, land one file per table (snapshot).

ingestron request lists what the platform team creates: the serverless database, the external data source with its managed-identity credential, the workspace's read access to the storage, and report users' permissions.

Columns and transforms

Each view's columns come from the table's columns, which are its contract's, or its source metadata's when it has no contract, typed from their logicalType. When Synapse is listed first, a column's physicalType is used as written, so DECIMAL(18,2) keeps money exact; otherwise the type follows the logicalType. Strings use the UTF-8 binary collation Microsoft recommends for Parquet, so comparisons are case-sensitive. Parquet and CSV files with a header are read by column name: a column the landing pack did not rename is read under its physicalName.

A transform's SQL is one T-SQL query over its inputs' views, written {{ tables.<input> }}. It sits inside the view, so it cannot start with WITH or end with ORDER BY (a trailing ; is dropped), and it names only tables listed in inputs.

Columns the landed files carry beyond the table's columns, such as ADF's audit columns, follow them. They are read as text, and timestamps are converted to DATETIME2(7).

Checks and cleansing

In a layer whose does lists cleanse or deduplicate, each view cleanses its columns in its SELECT (TRIM, NULLIF, CASE, UPPER or LOWER, COALESCE, and time zones with AT TIME ZONE) and keeps the last row per primary key with ROW_NUMBER(). Time zones are converted with their Windows names, such as GMT Standard Time for Europe/London. A zone Windows has no name for is not converted, and ingestron check warns about that column (NOT_APPLIED).

In a layer whose does lists check, a view leaves out the rows that fail a drop check. A view can't stop a load, so warn and fail checks keep their rows, and Ingestron also writes checks.sql:

build/synapse/
  checks.sql
  checks/dev.sql
  checks/prod.sql

checks.sql reads the same files the views read, cleansed the same way. It counts each row check's failing rows, the rows a drop check leaves out included, and counts each table check over the rows the view keeps, as other engines count the table they store. It returns one row per check: the view, the check, its action, the rows it counted and whether it passed. Then it raises an error for the first fail check that failed.

Patterns (pattern), array checks (items), quarantine and kept results are not applied: serverless T-SQL has no regular expressions, views type no arrays, and a view keeps nothing. ingestron check warns about each one it finds, with the code NOT_APPLIED and the reason.

Run checks.sql after each landing

checks.sql stops nothing unless something runs it after each landing: the views read the files whenever they are queried, and nothing runs the checks for you. Run it in one of these ways:

• sqlcmd. From build/synapse/, run the environment's checks script, which sets that environment's values and stops at the first error:

  sqlcmd -S <workspace>-ondemand.sql.azuresynapse.net -i checks/prod.sql

  <workspace> is your Synapse workspace's name. Sign in as for views.sql (see Deploy).

• A pipeline's Script activity, in Azure Data Factory or Synapse, after the activity that lands the files. Paste the text of checks.sql as one script, of type Query. checks.sql is one batch with no GO, so it runs as it is. Where environments differ, put each environment's values from checks/<environment>.sql in place of each $(name). When a fail check fails, the activity fails with that check's error. When the run succeeds, the activity's output holds the counts.

• CI. Run the sqlcmd command above as a scheduled step, or one that starts after each landing, as for views.sql in In CI.

Views or tables

	Views (Synapse)	Tables (a store pack, served with synapse-serve)
Runs after each load	Nothing	The store pack's job
Cost	Data scanned each time a report reads a view	The job's run time, plus data scanned when read
Upsert, history	No: the files as they are	Yes
Heavy joins over large data	Recomputed on every read	Computed once per load

Settings

Setting	Where	Means
data_source	platform	The external data source's name in the serverless database; one name, one location.
data_source_location	platform	Where it points, abfss://<file system>@<account>.dfs.core.windows.net or https://<account>.dfs.core.windows.net/<file system>; every file a view reads must be under it.
csv	standard	header (default true) and delimiter (default ,) for CSV files; when not set here, the landing step's CSV settings or reader options (ADF's header and sep) are used.

ingestron request lists what the platform team creates: the serverless database, a master key if it has none, the credential <data_source>_identity, the external data source, the workspace's read access to the storage (with a resource instance rule when the storage has a firewall), and report users' permissions.

One script for every environment

Most people use this through the ingestron command, which writes the files to build/synapse/:

build/synapse/
  views.sql
  environments/dev.sql
  environments/test.sql
  environments/prod.sql

In views.sql, the values that differ are variables:

USE [$(database)];
GO

IF SCHEMA_ID(N'bronze') IS NULL EXEC(N'CREATE SCHEMA [bronze]');
GO
CREATE OR ALTER VIEW [bronze].[orders] AS
SELECT *
FROM OPENROWSET(
  BULK 'erp/orders/orders.parquet',
  DATA_SOURCE = '$(data_source)',
  FORMAT = 'PARQUET'
) WITH (
  [id] BIGINT,
  [total] FLOAT
) AS [rows];
GO

Each environment's script sets them, then includes views.sql:

:on error exit
:setvar database "erp_prod"
:setvar data_source "landing"
:r views.sql

Variables are named after what they stand for: database (or database_<layer> when an environment's views are in several databases), schema_<layer>, view_<table>, path_<table>, data_source, delimiter_<table>, and value_<table> for a value in a transform's code. The rules are in spec/behaviour.md.

In code:

import { loadDefinitions, type Pack, type ResolvedModel } from "@ingestron/definitions";
import { synapse } from "@ingestron/pack-synapse";

// The pack that lands the files, such as @ingestron/pack-adf.
declare const landing: Pack;

const models: Record<string, ResolvedModel> = {};
for (const environment of ["dev", "prod"]) {
  const result = loadDefinitions("definitions", {
    packs: [landing, synapse],
    environment,
    deployTime: true,
    environmentsTogether: true,
  });
  if (result.model) models[environment] = result.model;
}
const { files, problems } = synapse.buildEnvironments!(models);
for (const problem of problems) console.log(problem.code, problem.message);
for (const path of Object.keys(files)) console.log(path);

Deploy

Run each environment's script with sqlcmd from the folder that holds views.sql, the same files for every environment. sqlcmd reads the :r path from the folder it starts in, not from the script's own folder. On your own computer:

cd build/synapse
sqlcmd -S <workspace>-ondemand.sql.azuresynapse.net --authentication-method ActiveDirectoryInteractive -i environments/dev.sql

Replace <workspace> with your Synapse workspace's name. This is sqlcmd (Go); install it with winget install sqlcmd or brew install sqlcmd, or from its releases. The script stops at the first error with a non-zero exit code, so a CI step fails when a view does.

In CI

Use sqlcmd (Go), pinned to a release, with --authentication-method ActiveDirectoryDefault. That sign-in uses the Azure CLI's: after azure/login in GitHub Actions, and inside an AzureCLI@2 step in Azure DevOps. The ODBC sqlcmd cannot sign in this way: its -G signs in with a user name and password, Microsoft Entra integrated authentication or an interactive prompt, never with the pipeline's sign-in.

GitHub's hosted runners and Azure DevOps's Microsoft-hosted agents use the same images. The Ubuntu images have no sqlcmd, and the Windows images have only the ODBC sqlcmd, so install sqlcmd (Go) first, checked by its SHA-256. On Linux, in a run step (GitHub) or a bash step (Azure DevOps):

curl -fsSL -o sqlcmd.tar.bz2 https://github.com/microsoft/go-sqlcmd/releases/download/v1.10.0/sqlcmd-linux-amd64.tar.bz2
echo "92516d98c63d99b0994de5b61350c91f6915f9b76f139a59039fbcb225c2e987  sqlcmd.tar.bz2" | sha256sum -c -
dir="$RUNNER_TEMP/sqlcmd"   # Azure DevOps: dir="$AGENT_TEMPDIRECTORY/sqlcmd"
mkdir -p "$dir" && tar -xjf sqlcmd.tar.bz2 -C "$dir" sqlcmd
echo "$dir" >> "$GITHUB_PATH"   # Azure DevOps: echo "##vso[task.prependpath]$dir"

On Windows, in a PowerShell step, which puts sqlcmd (Go) before the image's ODBC sqlcmd on PATH:

Invoke-WebRequest https://github.com/microsoft/go-sqlcmd/releases/download/v1.10.0/sqlcmd-windows-amd64.zip -OutFile sqlcmd.zip
if ((Get-FileHash sqlcmd.zip -Algorithm SHA256).Hash -ne "A4C28332FCC6E497D655E53AC8F4939F4AB170F9CFD32D0FA5081B60F4D9D691") { throw "sqlcmd.zip is not the v1.10.0 release" }
$dir = "$env:RUNNER_TEMP\sqlcmd"   # Azure DevOps: $dir = "$env:AGENT_TEMPDIRECTORY\sqlcmd"
Expand-Archive sqlcmd.zip -DestinationPath $dir
$dir | Out-File -FilePath $env:GITHUB_PATH -Encoding utf8 -Append   # Azure DevOps: Write-Host "##vso[task.prependpath]$dir"

Then, signed in, from build/synapse:

• GitHub Actions: after azure/login, a run step with working-directory: build/synapse.
• Azure DevOps: an AzureCLI@2 step with workingDirectory: build/synapse.

sqlcmd -S <workspace>-ondemand.sql.azuresynapse.net --authentication-method ActiveDirectoryDefault -i environments/prod.sql

Do not pass -X, which stops sqlcmd reading environment variables, or -x, which stops it replacing variables.

Values supplied when deploying

A value written as ${envvar:NAME} in the definitions is supplied when deploying: views.sql uses it as $(NAME), and the scripts of the environments that supply it leave it unset. Set an environment variable named exactly NAME, in the same case, on the deploy step: sqlcmd (Go) looks it up as written, and on Linux the case matters. Azure DevOps passes a secret to a step only through the step's env:, and gives other pipeline variables upper-case names, so map the value there (env: { NAME: $(NAME) }), as in GitHub Actions (env: { NAME: ${{ secrets.NAME }} }). sqlcmd -v NAME=<value> works too. The script stops before any view when the value is missing. The pack lists these values, with the name to set, in deployValues.

Write the name in upper case. Azure DevOps gives pipeline variables upper-case names, so a name such as synapse_source is not found there unless the step maps it with env: under that name, and generating warns about it.

sqlcmd puts the value into views.sql as it is, inside a string, so it cannot hold '. Only a whole value can be supplied this way: a ${envvar:…} inside a longer value, or in a transform's code, is refused. When the files and the data source share one, such as the storage account in abfss://landing@${envvar:ACCOUNT}.dfs.core.windows.net, views.sql needs no variable for it.

What changed in 2.2

The pack declares what it does with the standard's intent: what a table keeps and how its data is loaded, written once for every engine. Views keep nothing, so the pack applies none of it, and the files are the same as with 2.1.

• No setting moved, so there is no SETTING_MOVED warning.
• A table that a view keeps and whose intent asks for something a view cannot do, such as history: latest or audit: loaded_at, gets the warning NOT_APPLIED, with the reason. What the landing pack does, such as the files it lands, is not reported.
• The Platform has a synapse section, which takes no fields.
• Views cleanse and deduplicate, and leave out the rows drop checks fail, in the layers whose does asks for it; checks.sql and checks/<environment>.sql count every check (Checks and cleansing). A layer without does gets the views it had: each contract rule there is NOT_APPLIED, with a hint to add check to the layer's does.
• Needs @ingestron/definitions 1.14 or later.

What changed in 2.0

• One views.sql in build/synapse/, with environments/<environment>.sql for each environment, in place of a views.sql per environment in build/<environment>/. Change the standard to synapse@2, then delete the old folders; ingestron check fails until they are gone.
• Deploy with sqlcmd -i environments/<environment>.sql from build/synapse/ (see Deploy). views.sql with variables does not run on its own, so Synapse Studio cannot run it; run the same command on your own computer instead. SSMS has a SQLCMD mode, but how it reads :r paths has not been tested with these files.
• views.sql starts with USE [<database>], also when the views are in one database, so it no longer matters which database sqlcmd connects to. The database is the one the naming standard gives ({{ project }}_{{ environment }} by default). If you ran a 1.x views.sql connected to a database with another name, change the naming to that name before the first 2.0 run; otherwise the run fails, or creates the views a second time in the database the naming gives.
• Otherwise databases, schemas and view names are as in 1.x, so the first 2.0 run alters each existing view in place. As in 1.x, nothing drops a view that is no longer generated.
• ${envvar:…} values are supplied when deploying, not read when generating. Set them in the deploy step (see Values supplied when deploying).
• Environments may differ only in values: a table built in some environments only, files read in another format, or a transform's code that reads other inputs is ENVIRONMENTS_DIFFER. A value that differs cannot hold ', ] or a line break, which sqlcmd would put into views.sql as they are.
• A view that would hold $( anywhere, or a line that sqlcmd reads as one of its commands (such as GO, :r, :setvar or !!), is refused in every environment.
• The header no longer names the environment.
• Needs @ingestron/definitions 1.9 or later.

Sources

Synapse syntax and permissions follow Microsoft Learn, accessed 2026-11-01; sqlcmd's commands, variables, variants, sign-in and installation, and the hosted runners, follow Microsoft Learn, GitHub's documentation, the runner images and the sqlcmd (Go) v1.10.0 source, accessed 2026-11-05. The pages are listed in spec/references.md.

Documentation

User documentation: https://docs.ingestron.io.

Working on this pack

pnpm install
pnpm validate   # format, licences, lint, types, tests, README code and API report

Requires Node 22.13 or later and pnpm 10. How code is written here: CONTRIBUTING.md. Security: SECURITY.md.

Contact

Questions and feedback: hello@ingestron.io. Security reports: security@ingestron.io (see SECURITY.md).

Licence

Licensed under the Apache License 2.0; see NOTICE.