What lands in the target environment
Most automation tools deliver rows. PDQ delivers a described, constrained and classified schema — and the rows.
The tables in your target database are generated from the same metadata that drives the loading. That is why they arrive documented rather than needing documenting: comments, primary keys, foreign keys, unique constraints, category tags and sensitivity tags are emitted by the generator, from declarations made once upstream. Nobody writes a COMMENT ON COLUMN script. Nobody re-tags PII in the catalogue after the fact.
This page describes what actually appears in the target, where each part of it comes from, and how to get it back out again.
Declared once, materialised everywhere
A field is described in one place — the sourcefile data contract, or the model attribute — and that description travels the whole way down.
| You declare | Where you declare it | What appears in the target |
|---|---|---|
description | Sourcefile field, model attribute, model object | Column and table COMMENT in the generated CREATE TABLE |
keyFieldIndicator + keyOrder | Sourcefile field | Primary key on the publish table; unique constraint on the core table |
Relationship (toObject, toAttribute, relationType) | Model | Foreign key, child → parent |
fieldDomain | Sourcefile field, via Field Categorisation | Category tag on the column, and on child tables that inherit the key |
sensitive | Sourcefile field | Sensitivity tag, and a column mask where the platform supports one |
validFrom | Sourcefile field | SCD Type 2 versioning column; the validity period on the historised core |
dataType | Sourcefile field | The column type, mapped to the target platform's own type |
Change the declaration, re-generate, and the target changes with it. The description in the console, the comment on the column and the definition in the repository cannot drift apart, because there is only one of them.
Structure — keys and constraints
Constraints are generated from metadata that already exists. A business key is already declared to make deduplication and upserts work; a relationship is already declared to make the model hang together. The generator turns both into database objects rather than leaving them as platform-internal knowledge.
| Object | Generated from | Where it lands |
|---|---|---|
| Unique constraint | Business keys | Core tables — inline where the platform requires it, ALTER … ADD elsewhere |
| Foreign key | Relationship metadata | Child → parent, de-duplicated |
| Primary key | Business keys, with __fileKey included | Publish tables |
All three default to on. To keep the pre-3.2 output, set ENABLE_CORE_UNIQUE, ENABLE_FOREIGN_KEY and ENABLE_PUBL_PRIMARY_KEY to 0.
The DEVELOPMENT path emits only structural column changes — CREATE TABLE IF NOT EXISTS and ALTER ADD/DROP COLUMN. Comments, unique and foreign-key DDL are definition-only, and duplicate executable statements are de-duplicated in the audit trace. A development deploy therefore does not rewrite decoration it did not change.
Documentation — comments at creation time
Column and table comments are emitted inside the CREATE TABLE on Snowflake and Databricks, not bolted on afterwards. A table is documented from the moment it exists, so there is no window in which it exists undocumented.
- The comment text is the description written on the sourcefile field, the model attribute or the model object.
- Staging landing tables carry fixed technical descriptions on
rowId,fileName, theVARIANTcolumn andloadedTs— so even the plumbing explains itself. - Apostrophes in descriptions are escaped on every comment path, so a description reading
the customer's regiondoes not break the statement.
The same descriptions render in the console's generated documentation and in the definitions produced by the SQL Generator API. One source, three surfaces.
Classification — tags and masking
Classification is not a separate cataloguing exercise that happens after the warehouse is built. It is a property of the field, and it is applied to the target as part of building the table.
| Concept | Configuration | Emitted as |
|---|---|---|
| Category tag | TAG_NAME, with a default category | A tag on the column, carrying its fieldDomain |
| Sensitive data | SENSITIVE_TAG_NAME, SENSITIVE_TAG_VALUE | A sensitivity tag on every field flagged sensitive |
| Masking | Platform-dependent | A masking policy on Snowflake, dynamic data masking on the T-SQL family, SECURITY LABEL on PostgreSQL — and nothing on Databricks, where sensitive produces a tag only |
On Databricks classification is emitted as SET TAGS, because that is the facility Unity Catalog actually reads; no mask is generated there. Categories are added to published tables, referencing only the parent table. The full per-platform picture is in Differences between target platforms.
TAG_NAME, SENSITIVE_TAG_NAME and SENSITIVE_TAG_VALUE have defaults, and the defaults are easy to live with by accident. Decide the scheme that matches your catalogue's conventions before the first generation run, not after a few hundred tables carry the wrong tag name.
Classification follows the key down the hierarchy
When a hierarchical source is normalised into a table set, a child table carries the business keys inherited from its ancestor levels. Those key columns arrive with their domain tags attached: a classification declared on a parent level is applied to the child table where the key lands.
The inheritance rules are deliberately conservative:
- Ancestors are walked nearest-first, and only fill gaps.
- A domain declared on the table's own level always wins.
- Each alias is emitted once.
- Only key fields are inherited.
The practical effect is that a column classified once — say, a national identity number used as a business key — stays classified everywhere it is carried, without anyone tracking where it was copied to.
What each platform actually enforces
Governance is emitted per platform, using the facility that platform provides. What that means in practice differs:
| Target | Constraints | Comments | Classification |
|---|---|---|---|
| Snowflake | Unique, primary and foreign keys generated, all NOT ENFORCED | COMMENT inside CREATE TABLE | Tags; QUERY_TAG carries the run |
| Databricks | None emitted — the UNIQUE, PK and FK templates are deliberately omitted | COMMENT inside CREATE TABLE | SET TAGS in Unity Catalog |
| SQL Server / Fabric | Enforced unique constraints; foreign keys generated but not enforced | Extended properties on SQL Server; not carried on Fabric | Extended properties |
| Synapse | Unique NOT ENFORCED; no foreign keys — dedicated pools do not support them | Not carried | Extended properties |
| PostgreSQL | Enforced unique and foreign keys, sequences | Base layer only | Side table |
Databricks, Snowflake and Synapse emit no enforced UNIQUE, and only PostgreSQL enforces a foreign key. Duplicates still cannot reach the core — deduplication on the business key is part of the generated loading pattern — so what the missing constraint costs you is protection against writes that bypass that load path. See Differences between target platforms for the full breakdown.
Every statement carries the job that issued it
Metadata is not only captured about the pipeline — it is pushed into the target's own observability surfaces. Every statement the worker sends carries the unit of work it belongs to: app, worker version, kind (dwa / dm / qpi), job, step, run, queue, retry, plus the step's own object, attribute and relationship identifiers. Nothing needs configuring, and a static QUERY_TAG or APP value you already set is preserved rather than discarded.
| Target | Native facility the tag lands in |
|---|---|
| Snowflake | QUERY_TAG, as JSON |
| Databricks | user_agent_entry |
| PostgreSQL | application_name (63-byte limit) |
| SQL Server / Synapse / Fabric | ODBC APP → program_name |
| All targets | Leading sqlcommenter comment |
The comment is emitted in sqlcommenter form — sorted, percent-encoded key='value' pairs that database observability tooling already recognises.
Why this matters beyond debugging: a query in the target's own history can be joined back to the pipeline run that issued it. That is the join key cost attribution has been missing — consumption recorded by the warehouse on one side, the declaration that caused it on the other.
Metadata captured everywhere, and actionable
Every stage writes its own record, and every record is addressable — in the console, and through the API.
| What is captured | Where it is captured | What you can do with it |
|---|---|---|
| Delivery audit log | Landing zone, per file | Trace a delivery through Landing → Raw → Trusted → Published |
| Profiling statistics | Profile zone, per sourcefile | See type conformance, null ratios, distributions and schema drift |
| Loading statistics | Per sourcefile and per object | Rows staged and inserted, per run |
| Publisher audit | Per publish | Confirm each publish landed in its destination table |
| Quality-check results | Per check, keyed (qpi, runDttm) | Distinguish a rule that caught something from a check that was itself broken |
| Action audit log | Per console action | Who did what, in which role, on which page, and when |
| Lineage | Derived from live metadata | Systems → source files → models → exports |
Two design decisions make this actionable rather than merely present:
- A failed load is visible, not absent. Audit logging in the landing zone retries five times, then pushes the message to a
<zone>-parkedqueue and reports failure. Parked messages are not retried automatically — monitor those queues. - A quality run reads as data, not as a single pass/fail. Each check records its own state and result. A check whose SQL ran but whose test failed is
CompletedwithresultState=fail; a check whose SQL raised isFailedwith the real error and traceback.
Getting it out — APIs and OpenLineage
None of this is locked inside the platform. It is metadata, and metadata is exportable.
REST API
The AME REST API is the same interface the console and the generators read — there is no privileged internal channel. The v3 family serves configuration, structure, schedules, logs and lineage, with /api/v3.2 for
object-level generation.
/api/v4 is where the data product contract lives. A data product specification is the
/api/v4 data product contract — the two are the same thing under two names. It is a separate
family from v3 rather than a successor to it: v3 describes how the platform is configured and what
it did, v4 describes what is being delivered.
- Field Categorisation endpoints fetch, create and update field domain categorisations, with full version tracking.
- Log-by-date endpoints and publisher audit endpoints expose the operational record.
- Source file endpoints return the full data contract, including
listLevels,targetNormalizationand Published schemas. - Definition generation through the SQL Generator API returns the generated DDL itself, so a definition can be diffed in CI before it is deployed.
The sourcefile data contract is negotiated rather than assumed — tried at v3.2, then v3.1, then v3.
OpenLineage
PDQ emits OpenLineage events, so lineage lands in whatever catalogue or observability platform you already run rather than only in the PDQ console.
- Start and end events for all executions, from the SQL execution engine.
- Parent-job events from QPI quality checks, so a check attaches to the run it belongs to.
- Attribute-level lineage available as a detailed option, alongside dataset-level events.
- Published patterns, trusted datasets and PDQ views are all represented, with namespaces resolved per zone.
The practical consequence: the catalogue you already run does not need a pipeline of its own to know where a column came from.
What this replaces
| The usual arrangement | With PDQ |
|---|---|
| A cataloguing project that tags PII after the warehouse is built | The tag is a property of the field, applied when the table is created |
| A data dictionary that drifts from the database | The comment in the database is the description in the repository |
| Constraints documented in a modelling tool and never deployed | Keys and relationships generated as database objects |
| Lineage maintained by hand, or inferred by parsing SQL | Lineage emitted as OpenLineage events by the run itself |
| Warehouse spend attributed by guesswork | Every query in the target's history carries the job that issued it |
Governance stops being a parallel project running beside the pipeline, and becomes a property of the pipeline.
Next steps
- Differences between target platforms — what each of the six supported engines actually enforces, carries and cannot do
- Data management capabilities — the full capability list, including governance and metadata
- DWA layer — the field-level governance flags in detail
- Data warehouse automation — modelling the keys and relationships the constraints come from
- Data lake service — declaring classification on the sourcefile fields
- What changes in 3.2 — the current product version: generated governance, and Databricks and PostgreSQL as targets