Skip to content

Validate existing SQL Server tables against the Accept Family #135

Description

@victorchutw

Implements ADR-0065 (the validation logic and introspection surface). Part of the SQL Server destination cycle (map #106, sliced from #113).

Blocked by #134

Context

Every load mode that meets an existing table validates it at session begin, before any row is written. This slice builds that as an offline module: given the introspected shape of the destination table, the dataset schema, the load mode, and accept_datetime_rounding, decide acceptance — or produce the new write-phase failure incompatible_destination_table listing every violation at once. The module also owns the introspection query text and its decoding into a table-shape struct; executing it against a live server arrives with the full-refresh slice.

The contract

Per ADR-0065, a mapped column is acceptable when misfits are impossible or loud, never silent:

  • Acceptable: the created shape itself (Generate SQL Server DDL and bulk rows from the dataset schema #134); the lossless widenings DATETIME2(7) and DECIMAL of wider precision at the same scale; the loud-failing narrower types INT/SMALLINT/TINYINT (overflow raises), NVARCHAR(n) (over-length raises), and DECIMAL of narrower precision at the same scale.
  • Opt-in tier — exactly DATETIME and DATETIME2(p<6), admissible only under destination-level accept_datetime_rounding: true.
  • Never acceptable: SMALLDATETIME, REAL/FLOAT(24), VARCHAR, DECIMAL scale mismatches, DATE, TIME, DATETIMEOFFSET.
  • Nullability: a NOT NULL column cannot receive a nullable field.
  • Extra table columns (outside the dataset): acceptable iff nullable, IDENTITY, or NOT NULL with a DEFAULT; an extra NOT NULL column without a default is a violation.
  • Mapped IDENTITY: rejected on append and full refresh; on merge acceptable iff it is a merge key; a non-key mapped IDENTITY is rejected in every mode.
  • Mapped DEFAULT-carrying column: rejected on append and full refresh when the dataset field is nullable; accepted when the field is NOT NULL or the mode is merge.
  • All violations collect into one incompatible_destination_table failure listing every violation — never first-violation-only.
  • Introspection: query text against the system catalog (column names, types with precision/scale/length, nullability, IDENTITY, DEFAULT presence) for [schema].[table], plus decoding into the module's struct.

Acceptance criteria

  1. Table-driven unit tests cover every rule above — per mode × type family × nullability × opt-in flag — including merge's IDENTITY-key acceptance and both DEFAULT rules.
  2. A multi-violation table yields a single failure listing all violations, each violation naming the column and the reason.
  3. Query construction is unit-tested (schema/table quoting); the live introspection round-trip lands gated in the full-refresh slice, not here.
  4. No CHANGELOG entry yet — the code and the new failure code become reachable with the first live mode, whose slice names incompatible_destination_table in its entry.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    ready-for-agentFully specified and ready for an AFK agent

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions