Differences between target platforms
PDQ generates SQL for six target platforms from one model. The model does not change when the target does — the same objects, attributes, relationships, business keys and classifications produce the same warehouse shape everywhere.
What changes is the dialect underneath, and dialects are not equally capable. Some engines enforce a foreign key; some record it and ignore it; one cannot express it at all. Some take a column description inside CREATE TABLE; some need a follow-up statement; some have nowhere to put it. Those differences are not defects to work around later — they are properties of the engine you chose, and they decide which guarantees you get from the database and which you have to get somewhere else.
This page states them plainly, per platform, so the choice is made before the first generation run rather than discovered after a few hundred tables.
The SQL generator emits for Snowflake, Databricks, SQL Server, Azure Synapse, Microsoft Fabric and PostgreSQL. Everything below describes those six.
At a glance
| Snowflake | Databricks | SQL Server | Synapse | Fabric | PostgreSQL | |
|---|---|---|---|---|---|---|
| Reads source files via | Named STAGE | read_files() | OPENROWSET(BULK …) | OPENROWSET(BULK …) | OPENROWSET(BULK …) | pg_lake foreign table |
| Unique constraint | Declared, NOT ENFORCED | Not emitted | Enforced | Declared, NOT ENFORCED | Enforced | Enforced |
| Foreign key | Declared, NOT ENFORCED | Not emitted | Declared, NOCHECK | Not emitted | Declared, NOT ENFORCED | Enforced |
| Column comments | Inline in CREATE TABLE | Inline in CREATE TABLE | Extended properties | — | — | Base layer only |
| Category tags | Native column TAG | Unity Catalog SET TAGS | Extended property | Extended property | Extended property | Side table _column_tags |
Masking of sensitive | Masking policy | — (tag only) | Dynamic data masking | Dynamic data masking | Dynamic data masking | SECURITY LABEL (postgres_anon) |
| Iceberg publish | Native | Delta UniForm | — | — | — | Falls back to a plain table |
| In-database scheduling | CREATE TASK | — | — | — | — | — |
| Compute warehouse | USE WAREHOUSE | — | — | — | — | — |
| Surrogate keys | SEQUENCE | GENERATED ALWAYS AS IDENTITY | IDENTITY | IDENTITY | IDENTITY | IDENTITY |
The rest of this page explains what each row costs you.
Constraints — what is actually enforced
This is the difference that matters most, and the one most easily misread. A constraint appearing in the generated DDL is not the same as a constraint the engine will enforce at write time.
| Target | Unique on core | Foreign key | What the database actually guarantees |
|---|---|---|---|
| PostgreSQL | UNIQUE, enforced | FOREIGN KEY, enforced | Both. A duplicate business key or an orphan child is rejected at load time. |
| SQL Server | UNIQUE NONCLUSTERED, enforced | Added WITH NOCHECK, then disabled | Uniqueness only. The foreign key is documentation the optimiser may not trust. |
| Fabric | UNIQUE NONCLUSTERED, enforced | NOT ENFORCED | Uniqueness only. |
| Snowflake | UNIQUE … NOT ENFORCED | FOREIGN KEY … NOT ENFORCED | Neither. Both are metadata, for tooling and the optimiser. |
| Synapse | UNIQUE … NOT ENFORCED | No statement emitted — dedicated SQL pools do not support foreign keys | Neither. |
| Databricks | No statement emitted | No statement emitted | Neither. Delta has no UNIQUE, and PK/FK are informational only. |
On Snowflake, Databricks and Synapse the database will not stop a duplicate business key — but nothing generates one. Deduplication on the declared business key is part of the core model's generated loading pattern, so the core holds one record per business key on every target, whatever the constraint says. An unenforced UNIQUE is metadata for the optimiser and for tooling, not a gap to cover.
What an enforced constraint would additionally catch is a write that bypasses the generated load — a manual INSERT, a backfill script, another tool writing straight into the core. PostgreSQL, SQL Server and Fabric reject those; the other targets accept them, and a QPI quality check is how you would notice.
All three constraint families default to on and are controlled by ENABLE_CORE_UNIQUE, ENABLE_FOREIGN_KEY and ENABLE_PUBL_PRIMARY_KEY. Turning them off is a way to keep pre-3.2 output; it is not a way to make an unenforced constraint enforced.
Descriptions and comments
A description declared once on a sourcefile field or a model attribute lands differently depending on what the engine has to receive it.
| Target | Where the description lands | Consequence |
|---|---|---|
| Snowflake | COMMENT inline in CREATE TABLE, plus ALTER … COMMENT on change | Documented from the moment the table exists |
| Databricks | COMMENT inline in CREATE TABLE, plus ALTER … COMMENT on change | The same, and visible in Unity Catalog |
| SQL Server | sp_addextendedproperty (MS_Description), after creation | Present, but in extended properties rather than the table definition |
| Synapse / Fabric | Not emitted | Descriptions live in AME and the console, not in the warehouse |
| PostgreSQL | COMMENT ON in the base layer; core and publish tables carry no generated comment | Partial |
Only Snowflake and Databricks document a table at birth — the comment is part of the CREATE, so there is no window in which the table exists undocumented. Everywhere else the description is either applied afterwards or not carried into the database at all, and the console's generated documentation is the authoritative surface.
Apostrophes in descriptions are escaped on every path. Inline comments make that matter more than it used to: an unescaped apostrophe would break the whole CREATE TABLE rather than one trailing ALTER.
Classification and masking
Category tags come from fieldDomain; sensitivity comes from the sensitive flag. Both are emitted using the facility the platform actually reads.
| Target | Category tag | sensitive produces |
|---|---|---|
| Snowflake | MODIFY COLUMN … SET TAG | A masking policy applied to the column |
| Databricks | ALTER COLUMN … SET TAGS in Unity Catalog | A classification tag only — no mask |
| SQL Server / Synapse / Fabric | sp_addextendedproperty, one named property per column | ADD MASKED WITH (FUNCTION = 'default()') — dynamic data masking |
| PostgreSQL | A row in the per-schema _column_tags side table | SECURITY LABEL FOR anon … 'MASKED WITH VALUE NULL' |
Three things follow from this table:
- Databricks flags sensitive columns; it does not mask them. The tag is what Unity Catalog reads and what governance tooling can act on, but the value is still readable. If masking is a requirement, it has to come from a Unity Catalog policy you own, not from the generated DDL.
- Snowflake expects the masking policy to exist. The generated statement attaches
default_masking_policy; it does not create it. Create and grant the policy before the first run, or the sensitivity statements fail. - PostgreSQL masking needs an extension.
SECURITY LABEL FOR anonrequiresCREATE EXTENSION anon;and its label provider. Without it the statements parse but fail at execution.
PostgreSQL keeps categories in a side table for a structural reason: a Postgres column has exactly one comment, so tags stored as comments would overwrite each other and the description. The side table holds one row per tag, which keeps multiple tags per column and lets a single category be dropped without touching the rest.
TAG_NAME, SENSITIVE_TAG_NAME and SENSITIVE_TAG_VALUE have defaults that are easy to accept by accident. Set them to your catalogue's scheme before the first generation run — renaming a tag across a few hundred tables afterwards is a migration, not a setting change.
Data types
The same declared dataType resolves to a different physical type per target. The differences are small, and three of them change behaviour.
| Declared | Snowflake | Databricks | SQL Server | Synapse | Fabric | PostgreSQL |
|---|---|---|---|---|---|---|
| Text | STRING | STRING | NVARCHAR(255) | NVARCHAR(500) | VARCHAR(500) | VARCHAR(255) |
| Integer | INTEGER | INTEGER | DECIMAL(18,0) | DECIMAL(18,0) | BIGINT | NUMERIC(18,0) |
| Decimal | DECIMAL(28,8) | DECIMAL(28,8) | DECIMAL(28,8) | DECIMAL(28,8) | DECIMAL(28,8) | NUMERIC(28,8) |
| Timestamp | TIMESTAMP | TIMESTAMP | DATETIME2 | DATETIME2 | DATETIME2(3) | TIMESTAMP |
| Time | TIME | STRING | TIME | TIME | TIME | TIME |
| Boolean | BOOLEAN | BOOLEAN | BIT | BIT | BIT | BOOLEAN |
| Surrogate key | NUMBER(38,0) | BIGINT | BIGINT | BIGINT | BIGINT | BIGINT |
- Databricks has no
TIMEtype. A time-only field arrives asSTRING. Ordering still works lexically for zero-padded values, but arithmetic does not — model it as a timestamp if you need to compute with it. - Text lengths are bounded on the relational targets and unbounded on the columnar ones. A value that sits comfortably in Snowflake's
STRINGcan be truncated or rejected at 255 characters on SQL Server and PostgreSQL, and at 500 on Synapse and Fabric. Check the longest expected value for free-text fields before targeting a relational engine. - Casting is strict on PostgreSQL. Postgres has no
TRY_CAST, so the base-view and core attribute casts use plainCASTand a malformed value raises at load time rather than resolving toNULL. On every other target a bad value degrades quietly; on Postgres it stops the load. Profiling before the first core load is worth more here than anywhere else.
How source files are read
Each engine reaches object storage its own way, and each way has a prerequisite that must exist before the generated SQL runs. The generator does not create these.
| Target | Mechanism | Must exist beforehand |
|---|---|---|
| Snowflake | COPY INTO … FROM @<stage>/path | STORAGE INTEGRATION, STAGE, FILE FORMAT, WAREHOUSE; an EXTERNAL VOLUME for Iceberg publish |
| Databricks | read_files() against an abfss:// URL or a Unity Catalog volume | STORAGE CREDENTIAL + EXTERNAL LOCATION with READ FILES granted; a SQL Warehouse or DBR 13.3 LTS and later |
| SQL Server / Synapse / Fabric | OPENROWSET(BULK …) with a format file | MASTER KEY, DATABASE SCOPED CREDENTIAL, EXTERNAL DATA SOURCE, and the format file deployed |
| PostgreSQL | A persistent pg_lake foreign table per Published table | PostgreSQL 15 or later, CREATE EXTENSION pg_lake CASCADE, cloud credentials configured for pg_lake, CREATE on the Published schema |
Snowflake is the only target where credentials and the base URL live inside the database, in the STAGE object — a Snowflake database object, which keeps that name whatever PDQ calls its own layers. Everywhere else the generated SQL carries the storage URL and the engine resolves access separately.
Two behaviours are worth knowing:
- Synapse Published loads apply no filename filter. Every other target narrows the load to the file being processed; on Synapse a Published load ingests the whole resolved path. Size the path accordingly, and expect re-reads.
- PostgreSQL reads newline-delimited JSON through a foreign table created once, in the Published definition. Its path is fixed at first creation, and the publisher depends on that definition having created it — so deploy the Published definition before publishing.
Orchestration
| Target | Scheduling inside the database | Compute selection | Statement style |
|---|---|---|---|
| Snowflake | CREATE TASK, when the generator is run for tasks | USE WAREHOUSE prologue | Procedural — EXECUTE IMMEDIATE … BEGIN … END |
| Databricks | None — use Workflows and Jobs | — | Sequential ;-separated statements |
| SQL Server / Synapse / Fabric | None — use your own scheduler | — | Batches separated by GO;, DDL guarded by IF NOT EXISTS |
| PostgreSQL | None — use your own scheduler | — | Sequential ;-separated statements |
Snowflake is the only target that can schedule itself. On every other platform the generated script is a plain sequence of statements, and something outside the database has to run it — Databricks Workflows, Data Factory, Airflow, or the DataOps scheduler.
The run timestamp follows the same split: a session variable on Snowflake, a bind variable across the T-SQL family, and an inline quoted literal on Databricks and PostgreSQL, which have no procedural variable to bind to.
Publish table formats
The publish formats set out on the DLS layer page do not resolve identically everywhere.
| Target | TABLE | ICEBERG |
|---|---|---|
| Snowflake | Standard table | Native CREATE ICEBERG TABLE, requiring an EXTERNAL VOLUME |
| Databricks | Delta table | External Delta table with UniForm — Iceberg metadata generated alongside, readable by Snowflake, Trino and others |
| PostgreSQL | Standard table | Falls back to a standard table |
| SQL Server / Synapse / Fabric | Standard table | Not supported — keep the format on TABLE |
If multi-engine access to published data is a requirement, Snowflake and Databricks are the two targets that deliver it.
Per-platform notes
Snowflake
The reference implementation, and the most complete. Everything the generator can emit, it emits here: inline comments, native tags, masking policies, Iceberg, tasks, sequences. The trade-off is setup — the STAGE, integration, file format, warehouse and, for Iceberg, the external volume must all exist and be granted before the first run.
Databricks
The strongest catalogue story and the weakest constraint story. Tags land where Unity Catalog reads them and comments are inline, but nothing is enforced: no UNIQUE, no working primary or foreign keys, no mask on sensitive columns. Quality checks are the only way a violation here becomes visible at all, so treat them as mandatory rather than optional — bearing in mind that they report after the fact rather than prevent. Two operational details: dropping a tag is not re-runnable, because Databricks raises if the tag is already absent; and DROP COLUMN requires Delta name-based column mapping, which the generator enables before dropping.
SQL Server
The reference relational target, and the one that enforces the most while carrying the least metadata: uniqueness is real, descriptions go to extended properties, foreign keys are recorded but disabled. The Published table is never truncated on reload — allow for that in retention and in any row-count reconciliation.
Azure Synapse
SQL Server's structure with dedicated-pool limits layered on top: no foreign keys at all, NOT ENFORCED unique constraints, no cross-database queries, no descriptions in the warehouse, and no filename filter on Published loads. Distribution and index choices are not emitted by the generator — review the DDL and add them before production use. Deeply nested JSON can also hit the OPENROWSET line-length limit; see the FAQ for the resolution.
Microsoft Fabric
T-SQL family, with the Fabric Warehouse surface being a moving subset of SQL Server. Uniqueness is enforced, foreign keys are NOT ENFORCED, descriptions are not carried. The Published source URL folds the trusted-zone segment into the path, so the trusted-zone setting has to match your OneLake or container layout. Because the Fabric T-SQL surface changes, review generated DDL against the current surface before a first production run.
PostgreSQL
The only target that enforces both uniqueness and referential integrity, which makes it the strictest place to run a model — and the one where a modelling error surfaces as a failed load rather than as bad data. Two further consequences of that strictness: identifiers are emitted unquoted and case-folded, so a model object named after a reserved word (Order, User) will fail; and casts are strict, so malformed values raise rather than resolving to NULL.
Choosing a target
| If this matters most | Choose |
|---|---|
| Referential integrity guaranteed by the database | PostgreSQL |
| Governance metadata your catalogue can read | Snowflake or Databricks |
| Documentation carried into the warehouse itself | Snowflake or Databricks |
| Multi-engine access to published data | Snowflake, with native Iceberg, or Databricks, with UniForm |
| Scheduling without another orchestrator | Snowflake |
| An existing Microsoft estate | SQL Server, or Fabric for the newer surface |
Whatever the target, the gaps are knowable in advance, and each has a planned response: quality checks where constraints are unenforced, console documentation where comments are not carried, catalogue policy where masking is not generated. The point of this page is that the response is planned rather than improvised.
Be precise about what a QPI check does, though. A QPI check reports. It does not block the load and does not prevent bad data reaching consumers — it makes the deviation visible. Where a target does not enforce a constraint, a duplicate business key can be written, and a QPI check is how you find out, not how you prevent it.
Next steps
- What lands in the target environment — what the generated schema contains, and where each part comes from
- Data quality checks — how an unenforced constraint becomes visible after the fact
- DLS layer — publish patterns and formats · DWA layer — field-level governance
- Installing and securing the platform — selecting the data platform for an installation
- What changes in 3.2 — the current product version: generated governance, and Databricks and PostgreSQL as targets