On this page

SQL transform (sql)

Runs a SQL query over the source view(s) and emits a @dp.temporary_view. Requires exactly one of sql or sql_path.

Minimal example

Add this entry to the actions list in your flowgroup.

- name: transform_orders
  type: transform
  transform_type: sql
  source: v_orders_raw
  target: v_orders
  sql: "SELECT * FROM v_orders_raw WHERE amount > 0"

Options

Field

Type

Required

Default

Description

source

string / list

Yes

—

Input view name(s).

sql

string

Cond.

—

Inline SQL query. Exactly one of sql / sql_path.

sql_path

string

Cond.

—

Path to an external .sql file, relative to project root. Exactly one of sql / sql_path.

Substitution tokens (${token}, ${secret:scope/key}) are resolved in both inline SQL and external files. A plain ${name} that is still in the sql_path file after substitution fails lhp validate and lhp generate with LHP-CFG-010. Dotted or prefixed Spark variables such as ${mypipeline.start} or ${hiveconf:x} are left as written. To read a pipeline configuration: parameter at runtime, give it a dotted key (for example mypipeline.start_date) and reference it as ${mypipeline.start_date}; a plain ${start_date} fails even when the configuration defines it. Wrap a source in stream(view_name) inside the query to read it as a stream.

In a template action, a sql_path file that contains {{, {% or {# is rendered with the template parameters before substitution, like the template’s inline SQL. See Parameters in SQL files.

Common fields

Fields accepted by every transform_type. Type-specific fields are listed in the options above.

Field

Type

Required

Default

Description

name

string

Yes

—

Action name, unique within the flowgroup.

type

string

Yes

—

Always transform.

transform_type

string

Yes

—

One of sql, python, data_quality, temp_table, schema.

target

string

Yes

—

Name of the produced view (or temporary table). Required for all transforms.

source

string / list / mapping

Yes

—

Upstream view name(s). Accepted shape and requirement vary per type — see this type’s options.

description

string

No

auto

Comment on the generated view/table. Defaults to an auto-generated string.

operational_metadata

bool / list

No

—

Add operational metadata columns; a list selects column names. Honored by sql, python, data_quality, temp_table; ignored by schema.

depends_on

list

No

—

Extra upstream table or view references added to the dependency graph. See Dependency analysis reference.