Skip to content

Schema Profile — design & roadmap

Status: active / evolving (first producer shipped 2026-07-03). This doc is the working reference for the schema-profile feature so we can keep building out its use cases.

What it is

A schema profile is a persisted, platform-neutral description of a data object's columns — name, canonical JDBC type, measured size, and derivation — saved to Postgres so it can be reused downstream (DDL generation, sizing, drift detection) independently of whichever platform produced it.

The storage layer (t_sql_profile_table / t_sql_profile_column + helper functions + decode views) predates this work but sat dormant — schema, seq_profile_id, upsert_/clear_/add_ PL/pgSQL functions, and v_sql_profile_* views all existed in initdb.sql with no writer or reader. The JDBCTARGETPARQUET step is the first producer.

Out of scope: METAPROFILE (t_column_profile_sql → t_column_profile_results) is a separate feature serving data modelers, unrelated to this work. Don't conflate the two — schema profile here is structural (columns, canonical types, sizes) for reuse/DDL, not data-modeling metrics.

The producer: JDBCTARGETPARQUET

org.maestro.engine.steps.pipeline.core.JdbcTargetParquet (registered in StepProvider; WPF adds it to the TRANSFORM menu for Redshift + ClickHouse jobs). It reads the onstage PSV, projects/decodes in Spark, writes local Parquet, then calls saveSchemaProfile().

Relevant fixes that shaped the current behavior: - Decode in Spark with native unbase64, not the target vendor's $B64EXPRESSION (that resolves to e.g. ClickHouse base64Decode(), which Spark can't resolve). This step always decodes in Spark regardless of job type. - Binary columns (BLOB / VARBINARY / LONGVARBINARY) are blanked at extract (ParallelJdbcSourceSanitized) — they were being base64-downloaded as raw bytes, producing garbage and PSV bloat. CLOB/NCLOB/SQLXML (text) still flow through. (Open: carry binary as real binary in both the parquet and the profile — see roadmap.)

Storage model

t_sql_profile_table (header, one row per object)

column notes
profile_id PK, nextval('seq_profile_id')
profile_type plain. Currently "PARQUET".
profile_name base64. jobName.connection.catalog.schema.table
data_object_path base64. connection.catalog.schema.table
data_object_name base64. tableName
created_by / updated_by plain (job.userName, else engine)
agent_arg_data plain. Currently "fileFormat=Parquet;compression=snappy" (under-used).

t_sql_profile_column (one row per column)

column notes
profile_id, column_order plain
column_name base64
column_data_type base64. "<java.sql.Types id>|<name>" e.g. 12|VARCHAR, -155|DATETIMEOFFSET
column_expression base64. The Spark projection expression (provenance)
column_length plain. Measured byte-length, grow-only, standardized (see below)
column_decimal_digits plain. Column.scale

Functions & views

  • upsert_t_sql_profile_table(id, type, name, path, obj, by, by, arg) → profile_id (id 0 ⇒ insert via seq, else update in place).
  • clear_t_sql_profile_columns(profile_id), add_t_sql_profile_column(profile_id, order, name, type, expr, length, scale).
  • Read via the decode views v_sql_profile_table / v_sql_profile_column — they base64-decode the encoded fields. Base tables store base64 (matches the writer via Crypt.encodeStr).

Current writer behavior (saveSchemaProfile)

Non-fatal (never fails the parquet write). Flow: 1. Look up existing profile_id by profile_name → idempotent re-run. 2. Read prior column_lengths (before clearing) → basis for grow-only. 3. Measure this run's max byte-length per column from the written parquet: one Spark aggregation max(octet_length(cast(col as string))). (We measure the real data, not declared metadata — the extract's incremental Column.length doesn't reach the target step reliably.) 4. upsert header → clear columns → add each column. 5. column_length = standardizeLength(max(prior, measured)): - grow-only across runs (never shrinks), - rounded up to the next power of two (min 16, cap 65532), forced multiple of 4, - bucket boundaries are idempotent, so the value is stable run-to-run until a value outgrows its bucket (no churn). 6. column_data_type = sqlTypeId|name from Column.sqlType via a reflection reverse-map over the engine SQLTypes mirror (includes vendor -155 DATETIMEOFFSET). Platform-neutral + splittable. 7. The eltm_txid maintenance column is included (as -5|BIGINT, order-last) so the profile's column count matches the written Parquet. A consumer that COPYs the Parquet into a table built from this profile (see below) needs it, or Redshift's FORMAT AS PARQUET fails on a column-count mismatch.

Deploy: engine SSH-hotswap of sqlm.jar to .201; live as of 2026-07-03 (eltm_txid inclusion 2026-07-05).

Inspect: 

select p.profile_name, c.column_order, c.column_name, c.column_data_type,
       c.column_length, c.column_decimal_digits
  from v_sql_profile_column c
  join v_sql_profile_table  p on p.profile_id = c.profile_id
 where c.profile_id = (select max(profile_id) from t_sql_profile_table)
 order by c.column_order;

Use cases to build out (roadmap)

  1. Cross-platform DDL generation. Given (sqlType id, column_length, column_decimal_digits) per column, render a CREATE TABLE for any target dialect — reuse the same mapping the engine's Column.updateDataType(jobType) already uses. A profile → DDL helper/step is the natural next piece. This is the core payoff of storing the canonical type id.
  2. Downstream target auto-sizing. Feed column_length (already headroom'd, mult-of-4) into target VARCHAR(n) sizing so tables are created wide enough without per-run estimation.
  3. A profile reader / consumer. ✅ First consumer shipped: the CopyIntoRS step reads a profile by name (WPF Browse Profile) to build a matching Redshift temp table and COPY an S3 Parquet into it — see Loading S3 Parquet into Redshift. More consumers (seed a target transitMeta, drive a generic load) are the natural next pieces.
  4. Schema-drift detection. Because writes are idempotent + grow-only, diffing a profile across runs (new/removed columns, type changes, length growth) gives drift alerts.
  5. Producers beyond parquet. Generalize saveSchemaProfile (it's currently a JdbcTargetParquet method) so other target/source steps can persist profiles — candidate for a shared BaseStep or helper so ClickHouse/Redshift/Snowflake targets can profile too.
  6. Binary columns. Currently blanked at extract, so they show length 0 / no real type. Decide whether to (a) carry them as real binary in parquet + profile them as BLOB/VARBINARY, or (b) keep excluding them and mark them in the profile.
  7. agent_arg_data payload. Only holds a format string today. Could carry the source query / extract config (AgentArg) so a profile is a complete, replayable object definition.
  8. UI surface. The dormant WPF ProfileCache window (reads the unrelated hd_table_meta) is a stub; a real viewer over v_sql_profile_* would make profiles usable from the client.

Open questions

  • Should profile_type distinguish source vs target profiles, or encode the producer step type?
  • Length semantics: byte-length (current, matches VARCHAR sizing) vs char-length — confirm per target.
  • Retention: keep history (versioned profiles) or always overwrite in place (current)?

Source pointers

  • source/root-engine-core/.../steps/pipeline/core/JdbcTargetParquet.java — saveSchemaProfile, standardizeLength, sqlTypeLabel, getSourceIdentity, getDecodeList.
  • source/root-engine-core/.../engine/metadata/SQLTypes.java — the java.sql.Types mirror (+ -155).
  • source/root-engine-install/db/initdb.sql — tables, seq_profile_id, upsert_/clear_/add_ fns, v_sql_profile_* views.