Dual-Mode Transformations

Dual-mode transformations enable platform portability by declaring both Spark and SQL implementations for the same transformation logic.

Concept

flowchart TD
    A[Transformation Stage] --> B{Platform Profile}
    B -->|Databricks| C[Spark Implementation]
    B -->|Snowflake| D[SQL Implementation]
    B -->|AWS EMR| C
    B -->|AWS Athena| D
    B -->|Azure Synapse Spark| C
    B -->|Azure Synapse SQL| D

The Pathway Service automatically selects the appropriate implementation based on the target platform.

Declaration

transformation:
  spark:
    style: dataframe          # dataframe | stream | rule_based | ml_based | ai_generated
    reference: com.acme.transforms.OrderAggregation
    config:
      outputPartitions: 100
  sql:
    style: sql
    reference: transforms/order_aggregation.sql
    config: {}

Modes

Spark Only

Use when the transformation requires DataFrame/Dataset APIs:

transformation:
  spark:
    style: dataframe
    reference: com.acme.transforms.ComplexJoin

SQL Only

Use when the transformation is expressible in pure SQL:

transformation:
  sql:
    style: sql
    reference: "SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id"

Dual Mode

Declare both for maximum portability:

transformation:
  spark:
    style: dataframe
    reference: com.acme.transforms.OrderAggregation
  sql:
    style: sql
    reference: transforms/order_aggregation.sql

Transformation Styles

Style	Description	Typical Use
sql	Pure SQL query	Simple aggregations, filters, joins
dataframe	Spark DataFrame/Dataset API	Complex transformations, UDFs
stream	Streaming transformation	Windowed aggregations, watermarks
rule_based	Rule engine execution	Business rule application
ml_based	ML model inference	Scoring, predictions
ai_generated	AI-generated transformation	Auto-generated logic

Execution Strategy Resolution

The PathwayService.resolveExecutionStrategy() determines which implementation to use:

Platform Profile	Spark Available?	SQL Declared?	Strategy
Databricks	✅	—	SPARK_API
Snowflake	❌	✅	PURE_SQL
AWS EMR	✅	—	SPARK_API
AWS Athena/Glue	❌	✅	PURE_SQL
Azure Synapse (Spark Pool)	✅	—	SPARK_API
Azure Synapse (SQL Pool)	❌	✅	PURE_SQL

If the platform doesn't support Spark and no SQL fallback is declared, the pipeline is rejected as incompatible.

TransformationDefinition Record

public record TransformationDefinition(
    String style,           // sql, dataframe, stream, rule_based, ml_based, ai_generated
    String reference,       // Class name, file path, or inline SQL
    Map<String, Object> config  // Transformation-specific configuration
) { }

Best Practices

Always declare SQL fallback for portability

If your transformation can be expressed in SQL, declare both modes. This maximises platform compatibility.

Keep transformation config technology-free

Config values like outputPartitions are acceptable. Spark-specific configs like spark.sql.shuffle.partitions are not.

Use references for complex SQL

For multi-line SQL, use a file reference (transforms/my_query.sql) rather than inline SQL.