Transformations in ODS

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.

{
  "order_id": 101,
  "customer": {
    "customer_id": 42,
    "name": "Ava Patel"
  },
  "items": [
    {"sku": "A101", "qty": 2, "price": 19.99},
    {"sku": "B220", "qty": 1, "price": 49.50}
  ]
}

Transformation Stages and Flow

Transformations operate through a multi-stage pipeline that separates concerns and improves testability:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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

1. Open a project in Data Studio to access the lineage canvas.
2. On the canvas, click the + action on a zone node to start adding a transformation for that data flow.
3. In the transformation selector panel, click Select a transformation and choose the type.
4. Choose from the user-visible options based on zone context:
   • Code Refinement
   • Simple SQL
   • Semantic Layer (shown in Silver zone)
   • Unnest JSON (shown when nested JSON is detected)
5. In the transformation configuration screen:
   • Set Tags to categorize and organize your transformation
   • Define the transformation type (full, full_history, incremental, or incremental_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
6. Test your transformation by running it and reviewing results in the stage output panel or run logs.
7. Save the transformation. There is no separate publish action for transformations.

Backend Model and Lineage

From a technical perspective, transformations are modeled as persisted records in the oda_transformations table, linked to nodes in the project lineage through a node_id foreign key. Each transformation includes:

• Category — The transformation type (code_refinement, simple_sql, semantic_layer, relationalize_json, db_sync, notebook)
• Type — Pipeline declaration (full, full_history, incremental, incremental_insert)
• Projection — The list of columns selected for the output
• Schema — Metadata about output columns and their types
• Lineage Context — Source node IDs and destination node IDs that define data flow

When a transformation is executed as part of a job run, the backend reads its definition, resolves source data, applies the transformation logic, validates against quality rules, and populates the destination with refined data. The lineage canvas then tracks all transformations as visual connections between source and destination nodes, providing end-to-end visibility into your data pipeline.