Skip to content

spec

etl_craft.handlers.sql.spec

A SQL task's parameters, read and checked before anything touches the warehouse.

Every mistake in a task's definition fails the task here with the parameter, its value and the remedy, so no action ever starts on a half-valid definition.

  • SQL_ACTION: one of the eight actions.
  • TARGET_OBJECT: schema.table, in the active warehouse profile's database, or database.schema.table.
  • SOURCE_SQL or SOURCE_SQL_FILE (every action but DROP_TABLE): the read-only SELECT, inline or as a file under sql_files/; exactly one of them.
  • PIPELINE_RUN_ID_SUBSTITUTION: true replaces $$pipeline_run_id with the run id.
  • PIPELINE_RUN_ID_FILTER: true replaces $$pipeline_run_id_filter with pipeline_run_id = <id>, or 1=1 on a FULL refresh.
  • RUN_DATE_SUBSTITUTION: true replaces $$run_date with the date the run runs as of, DATE 'YYYY-MM-DD': the day it started, or the date of a backfill run.
  • MERGE_KEY (SCD1_MERGE, SCD2_MERGE, DELETE_ROWS): |-separated key columns.
  • MERGE_COMPARE_COLUMNS (the merges): ordered |-separated scalar columns hashed into version-2 HASH_KEY; floating point is refused, and decimals require a declared scale.
  • MERGE_DEDUPE_ORDER (the merges): ORDER BY terms choosing the row kept per key.
  • SCHEMA_EVOLUTION (OVERWRITE_TABLE and the merges): true adds new SELECT columns to the target.
  • PRESERVE_TARGET (SCD1_MERGE): true keeps a target value where the source is NULL.
  • HARD_DELETE (DELETE_ROWS): true deletes rows; otherwise they get DELETE_FLAG='Y'.
  • SETUP_FOR (SETUP_TABLE): the action that writes the table, whose audit columns it gets; needed when no other task in the pipeline writes it.

WRITERS module-attribute 

WRITERS = frozenset({SqlAction.CREATE_TABLE, SqlAction.OVERWRITE_TABLE, SqlAction.APPEND_TABLE, SqlAction.SCD1_MERGE, SqlAction.SCD2_MERGE})

The actions that write rows into their target, and so have audit columns of their own.

PARAMETERS module-attribute 

PARAMETERS = frozenset({'SQL_ACTION', 'TARGET_OBJECT', 'SOURCE_SQL', 'SOURCE_SQL_FILE', 'PIPELINE_RUN_ID_SUBSTITUTION', 'PIPELINE_ID_SUBSTITUTION', 'TASK_RUN_ID_SUBSTITUTION', 'PIPELINE_RUN_ID_FILTER', 'RUN_DATE_SUBSTITUTION', 'MERGE_KEY', 'MERGE_COMPARE_COLUMNS', 'MERGE_DEDUPE_ORDER', 'SETUP_FOR', 'TABLE_FORMAT', *FLAGS, *STORAGE_PARAMETERS})

The task parameters a SQL task reads.

SqlTask dataclass

A SQL task's checked definition.

select_sql is the SELECT with its tokens replaced, or None for DROP_TABLE; source names where it came from, for messages.

read_sql_task 

read_sql_task(context: TaskContext) -> SqlTask

Read and check the SQL task's parameters; SqlGuardError or MetadataError if wrong.

parse_flag 

parse_flag(params: Mapping[str, str], name: str) -> bool

Read a yes/no parameter: true or false in any case; absent means false.

resolve_select 

resolve_select(config: ConnectorConfig, params: Mapping[str, str], *, pipeline_run_id: int, refresh_type: str, run_date: date = LINEAGE_RUN_DATE, pipeline_id: int = 0, task_run_id: int = 0) -> tuple[str, str]

Return a SQL task's SELECT, inline or from its file, with the tokens replaced.

Also returns where it came from, for messages. SqlGuardError or MetadataError when the task has no usable single read-only SELECT.