ODA User Guide
Transformations in ODS
Learn how to create and manage transformations in Optra Data Studio to convert raw data into business-ready outputs.
Transformations are the core mechanism for converting raw or intermediate data into business-ready outputs in Optra Data Studio. They define how data flows from source systems through the Medallion architecture (Bronze, Silver, Gold) and represent the data quality rules, business logic, and refinement operations that shape your data pipeline. Each transformation is a defined unit of work that processes source data and produces a refined output, maintaining lineage and governance throughout the process.
Transformations operate within the context of the Medallion layers—Bronze transformations focus on technical normalization, Silver transformations standardize and enrich data for reusability, and Gold transformations curate data into business-ready outputs. This layered approach allows organizations to reduce repeated logic, maintain consistent definitions, and create clear separation between raw ingestion, reusable business preparation, and final consumption.
Transformation Types
ODS supports multiple transformation types to accommodate different authoring patterns and use cases:
Click on the cards to display an example.
Example: Code Refinement
A common use case is normalizing inconsistent product names before they are used in reporting. For example, a source table may contain values such as iphone 13, iPhone 13, and IPHONE-13. A code refinement step can standardize these into a single clean value such as iPhone 13 before business logic or dashboards consume the table.
Transformation Stages and Flow
Transformations operate through a multi-stage pipeline that separates concerns and improves testability:
- Source Selection — Define input data by selecting source datasets or tables from Bronze or Silver layers. Users can browse source schemas, view sample data, and understand metadata to inform transformation design.
- Transformation Logic — Write or configure transformation logic using the selected type (SQL, Python, semantic mapping, etc.). For multi-stage transformations, each stage is independently runnable, allowing you to validate intermediate results before proceeding to the next step.
- Data Quality Rules — Apply data quality rules to validate transformation output. Over 30 quality rules are available, including schema consistency checks, null value thresholds, data type validation, and business logic enforcement. Quality rules can be configured to quarantine failing records, log violations, or halt execution based on criticality.
- Destination Configuration — Specify the output destination, including table name, storage format, and schema. For full-load or incremental pipelines, declare the transformation type to control how data is written.
- Execution and Monitoring — Execute the transformation through a job or manual run, then monitor results through the lineage canvas and run logs. Each execution is tracked with status, timestamps, and detailed logs for diagnostics and troubleshooting.
Full and Incremental Pipelines
Transformations support four pipeline declaration types:
Full-Load (Complete Refresh)
Full-load transformations process all source data and completely rewrite the destination table on every execution. This approach is suitable for dimensions, snapshots, and datasets where full consistency is more important than incremental efficiency. Full-load pipelines are simpler to implement and debug but can be more resource-intensive for large datasets.
Full History
Full History preserves and reloads complete historical records rather than only the latest state. Use this mode when historical completeness and traceability are required for audit, trend analysis, or time-sliced reporting.
Incremental (Delta Processing)
Incremental transformations process only new or changed data since the last run, appending or updating destination records accordingly. This approach is efficient for large datasets, time-series data, and event streams where you only need to capture changes. Incremental transformations require clear identification of change indicators (dates, IDs, timestamps) and are optimized for streaming or near-real-time architectures.
Incremental Insert
Incremental Insert ingests only new rows since the last watermark and inserts them into the destination without applying full upsert/replace semantics. This mode is suitable for append-only or event-style datasets where records are not expected to be updated after insertion.
Creating a Transformation
- Open a project in Data Studio to access the lineage canvas.
- On the canvas, click the + action on a zone node to start adding a transformation for that data flow.
- In the transformation selector panel, click Select a transformation and choose the type.
- Choose from the user-visible options based on zone context:
Code RefinementSimple SQLSemantic Layer(shown in Silver zone)Unnest JSON(shown when nested JSON is detected)
- In the transformation configuration screen:
- Set Tags to categorize and organize your transformation
- Define the transformation type (
full,full_history,incremental, orincremental_insert) - Add source(s) from the Source section (available sources are linkable upstream/current-zone sources)
- If using Simple SQL, add stages and run each stage independently for validation
- If using Code Refinement, edit code (Lua/Python), run Test, then Save
- Configure data quality rules to validate output data
- Configure projection/schema options where applicable
- Test your transformation by running it and reviewing results in the stage output panel or run logs.
- Save the transformation. There is no separate publish action for transformations.
Transforming Data
A Transformation node runs code or SQL against a Zone's data to refine it into the next layer of the pipeline. Unnest JSON is a special transformation category that Optra offers only when a zone's schema contains nested data, to flatten that nested JSON into tabular columns.
- On the canvas, click the quick-add (
+) control on a Zone node to open the Select a transformation panel. - Optra inspects the zone's schema (and, if needed, its sample data) for nested fields—object/struct types, arrays, or JSON-typed/string columns that look like JSON. If any are found, Unnest JSON appears in the category list with a "Nested JSON detected" tag, alongside the always-available Code Refinement and Simple SQL categories (and Semantic Layer for Silver-zone transformations). If no nested fields are found, Unnest JSON is not offered.
- Select the Unnest JSON category. Optra immediately creates a new Transformation node (auto-named "Transformation", or "Transformation 2", etc. if the name is already taken) with its category set to Unnest JSON, and opens its panel on the JSON Relational Model selector.
- Choose a model by clicking one of the two cards:
- Wide Table — flattens all nested fields and array elements into a single table using underscore-separated column names (for example
address_city,items_0_price). Best for ad-hoc queries on a denormalized dataset. - Relational — keeps root fields in a parent table and puts each array field into its own child table joined by a parent key. Best for relational queries that need to preserve array cardinality.
- Wide Table — flattens all nested fields and array elements into a single table using underscore-separated column names (for example
- Click Save in the panel footer. Saving persists the chosen
JSON Relational Model. If you chose Relational, saving also automatically creates the additional child-table output nodes on the canvas and refreshes the graph; choosing Wide Table does not add any extra nodes. - If the zone's schema changes later and no longer contains nested fields, reopening this transformation shows a warning banner: "Source schema no longer contains nested fields. This transformation may produce unexpected results."
Once saved, the canvas label on a Transformation node shows its configured category (for example "Unnest JSON"); before it is configured, the node simply reads "Transformation". The node itself does not display a separate run-status badge (success/failed/running) on the canvas—selecting it only reopens its configuration panel. To check whether a transformation actually ran successfully, open the Job panel's Runs tab and inspect the pipeline run log instead (see Running the Pipeline); the node's color/selection state on the canvas is not a reliable indicator of its last run outcome.
Adding Silver and Gold Transforms
A pipeline advances through the medallion layers by alternating Transformation nodes with Zone nodes: each Transformation reads from one Zone and writes to the next. Which next zones are offered depends on the zone you are transforming from: Raw and Bronze can both lead to Bronze, Silver, or Gold; Silver can lead to Silver or Gold; Gold can only lead to Gold.
- On the Bronze Zone node (for example the output of the Unnest JSON transformation above), click its quick-add (
+) control to open Select a transformation again, and pick a category (Code Refinement, Simple SQL, or Unnest JSON if nested fields are present) to create the next Transformation node. - Configure and Save that Transformation node.
- Click the quick-add (
+) control on the new Transformation node. Because its source zone (Bronze) has more than one valid next zone, Optra opens a Select a destination panel listing Destination options—Raw Zone, Bronze Zone, Silver Zone, Gold Zone—instead of adding a zone automatically. - Click Silver Zone. Optra adds the Silver Zone node, copies the upstream projection and schema onto both the transformation and the new zone, and shows "Silver zone added successfully."
- Repeat the pattern from the Silver zone: add another Transformation from it (this time Semantic Layer is also offered as a category, since it is only available when transforming from a Silver zone), save it, then click its quick-add control. Because Silver's only next zones are Silver or Gold, if more than one option remains you again get the Select a destination panel—click Gold Zone to add it, shown with "Gold zone added successfully." If Silver only has one valid next zone available, Optra adds it directly without showing the destination picker.
Naming the Bronze Output of a Transformation
The name Optra gives a new Zone node when you add it from a Transformation (via the quick-add and Select a destination steps above) depends on that transformation's category:
- Unnest JSON (category
relationalize_json): the output zone is named to match the Transformation node's own name exactly. Raw -> Unnest JSON -> Transformation means a Transformation node named "Transformation" reading from Raw produced a Bronze zone also named "Transformation"—which is whyBronze.Transformationis a valid table reference in Run Query. - Every other category (Code Refinement, Simple SQL, Semantic Layer): the output zone instead gets an auto-generated default name like "Bronze zone" (or "Bronze zone 2", etc. if that name is taken), not a descriptive name.
Raw -> Simple SQL -> bronze_copy_device_messages is the second case after a rename: a Simple SQL transformation reading from Raw first produced a zone node named "Bronze zone", which was then renamed to "bronze_copy_device_messages" using the Renaming Zone Layer Tables steps above, so later queries use Bronze.bronze_copy_device_messages instead of the generic default name.
Simple SQL Transformation
Simple SQL is one of the Transformation categories introduced in Transforming Data—selecting it creates a Transformation node whose body is one or more plain SQL Stages instead of a full code editor or the Unnest JSON model picker.
Stages
- Open a Transformation node whose category is Simple SQL; its panel includes a Stages section not shown for Unnest JSON or Code Refinement.
- Click Add stage to create a new stage; each stage gets a short alias badge (for example
source1,stage2) and a name. - Click a stage row to open it, or hover it and click the trash icon to remove it—removal asks "Are you sure to remove ...?" with Yes/No confirmation and cannot be undone.
Select only certain columns
- In the Transformation panel's Projection box, select only specific columns to carry forward from the Source's schema—the same mechanism used elsewhere in the pipeline.
- If the destination zone is Silver or Gold, Select All and Clear buttons are available for quick bulk selection, alongside the individual column checkboxes.
- If the destination is not Silver or Gold, the projection cannot be modified here at all—Optra shows a read-only bullet list of columns and the note "Projection cannot be modified because the destination not in Silver or Gold zone".
- The Schema table alongside Projection always reflects only the columns currently selected, so review it before saving.
Incremental Type
A Simple SQL transformation's Type selector offers four values: Full, Full History, Incremental, and Incremental Insert (see Explain the four types of Simple SQL Transformations for what each one means).
- Watermark Column selection: choosing Incremental or Incremental Insert reveals a Watermark column dropdown listing columns from the schema; pick the column Optra should use to detect rows that are new or changed since the last run.
- Watermark Column Interval Selection: next to it, a read-only Watermark type field shows the interval/type Optra classifies the chosen column as (
timestamp,date,integer, orlong)—this is detected automatically from the column's schema type, not a separate value you choose.