Unlock Schema

Overview

Unlock Schema lets you take full control of the target-side schema for a replicated entity. By default, Gluesync derives the target schema from the source. When you unlock the schema, you can change data types, add or remove columns, and introduce technical fields required by downstream use cases. Schema changes are typically paired with a User Defined Function (UDF) to transform data accordingly.

Use Unlock Schema when you need more flexibility than a 1:1 source-to-target mapping. For simple mirroring, keep the schema locked.

When to use Unlock Schema

  • Resolve data type mismatches. For example, convert a string date in the source to a proper date/timestamp in the target. Unlock the schema to change the target column type, then implement the conversion via a UDF.

  • Redefine the column set at target. For example, the source has phone and prefix columns, while the target expects a single phone_number field. Unlock the schema to remove/add columns and merge values via a UDF.

  • Add technical fields. For multi-source consolidation (e.g., merging similar tables into one target), add fields like source_table so you can track the origin of each row. Populate these fields via a UDF at runtime.

How it works

  • Locked schema (default): Target schema is inferred from the source and managed by Gluesync.

  • Unlocked schema: You explicitly define the target schema: add/remove columns, change types, and introduce technical fields. Gluesync will use your custom schema for replication.

  • Transformations with UDFs: When your target schema diverges from the source, provide a UDF that maps and transforms incoming records to your custom target format. See User Defined Functions for guidance and examples.

Unlocking the schema shifts responsibility for keeping target columns consistent with your transformation logic. Ensure your UDFs produce values that match the new types and column names you define.

Prerequisites

  • Permissions: You must have rights to edit entities and schemas in Core Hub.

  • Target DDL: Ensure the target system can accept the resulting schema (e.g., the table exists with required columns, or automatic DDL is enabled if available in your environment).

Usage steps

  1. Access the Fields editor from the object’s browser.

  2. Click the Unlock schema button.

  3. Edit the schema: change data types, add or remove columns as needed.

  4. Define a UDF to handle conversions, merges, and population of any technical fields to align runtime data with the new schema.

  5. Save and deploy your changes, then monitor replication and logs.

Unlocked schema overview

Examples

  • String to date conversion

  • Unlock schema and set order_date type to timestamp.

  • In your UDF, parse the source order_date_str (e.g., YYYY-MM-DD) and output a proper timestamp to order_date.

  • Merge columns into one

  • Unlock schema and create phone_number while removing phone and prefix from target.

  • In your UDF, build phone_number by concatenating prefix and phone with any required normalization.

  • Add technical fields

  • Unlock schema and add source_table to the target schema.

  • In your UDF, set source_table based on the origin of the record so downstream consumers can trace lineage.

Best practices

  • Keep transformations in UDFs focused and deterministic.

  • Validate data types after changes; log parsing or casting errors in your UDF for observability.

  • Name technical fields clearly (e.g., source_table, ingestion_ts).

  • Document mappings from source fields to target fields in comments within your UDF and in team docs.

  • Test on staging or with sample data before production rollout.

Limitations and considerations

  • Unlocking schema may require target-side DDL. Ensure the destination has the required columns and types.

  • Backward-incompatible changes (e.g., dropping target columns) may require a backfill or re-sync to avoid errors.

  • Complex transformations can impact throughput; keep UDFs efficient.

Troubleshooting

  • Type casting failures: Review UDF parsing logic and verify the target column type matches the produced value.

  • Missing columns at target: Apply the necessary DDL or reconfigure the target to include the new fields.

  • Unexpected nulls: Ensure your UDF sets all required fields when the schema is unlocked.

See also