ALTER JOB

The ALTER JOB command enables you to update the mutable properties of your jobs and add or drop expectations.

To rename a job, please see: Rename job

Syntax

ALTER JOB <job_identifier> 
    SET <job_option> = <value>;

Note that not all job options are mutable.

Job options such as END_AT, RUN_PARALLELISM, AGGREGATION_PARALLELISM, CONTENT_TYPE, COMPUTE_CLUSTER, and COMMENT are always editable for all jobs.

On the other hand, options such as START_FROM, COMPRESSION, RUN_INTERVAL, and ALLOW_CARTESIAN_PRODUCTS are not editable for any jobs.

Furthermore, there are certain job options that are only available for specific job types; check the documentation linked below for your job type (ingestion, replication, or transformation) to see the comprehensive list of mutable options.

All mutable options are denoted by — editable in the individual option descriptions.

Ingestion jobs

Replication jobs

Transformation jobs

Examples

Alter one job option

ALTER JOB transform_orders_and_insert_into_athena
    SET COMPUTE_CLUSTER = high_memory_cluster;

Alter multiple job options

You can alter multiple job options in the same statement, using the SET keyword for each option you need to change:

ALTER JOB transform_orders_and_insert_into_athena
    SET COMPUTE_CLUSTER = high_memory_cluster
    SET END_AT = NOW;

Add an expectation

You can easily add an expectation to an existing job using the ALTER JOB command. The syntax below shows how to alter a job you have already created:

ALTER JOB <job_name> 
    ADD EXPECTATION <expectation_name> 
    EXPECT <sql_predicate> ON VIOLATION { DROP | WARN }

In the following example, an expectation named exp_orderid_not_null is added to the load_raw_data_from_mssql job, to check the orderid value in each event is not null. If an event is discovered with a NULL value, it is dropped from the ingestion:

ALTER JOB load_raw_data_from_mssql
   ADD EXPECTATION exp_orderid_not_null EXPECT orderid IS NOT NULL ON VIOLATION DROP;

Drop an expectation

To drop an expectation, use the ALTER JOB command using the following syntax:

ALTER JOB <job_name> 
    DROP EXPECTATION <expectation_name>;

When you drop an expectation, the entry is dropped from the system.monitoring.expectations system table, so if you want to retain a count of warnings or dropped rows, ensure you take a copy of these values prior to dropping the expectation. Furthermore, values are removed from the system.monitoring.jobs table.

For example, to drop the expectation created in the above example, use the following syntax:

ALTER JOB load_raw_data_from_mssql 
    DROP EXPECTATION exp_orderid_not_null;

Replication job examples

You can make changes to a replication group by including the UPDATE REPLICATION GROUP syntax. When you remove a schema, table, or column from a replication group, the data remains on the target but synchronization is stopped.

Add schema and all tables

In the following example, the humanresources schema, including all tables, is added to the existing replication group, group_1, using INCLUDED_SCHEMA_DEFINITION ADD:

ALTER JOB postgres_replication_to_snowflake
    UPDATE REPLICATION GROUP group_1
    UPDATE INCLUDED_SCHEMA_DEFINITION ADD (humanresources.*);

To perform the same operation using a regular expression, use the following syntax:

ALTER JOB mssql_replication_to_snowflake
    UPDATE REPLICATION GROUP replicate_to_snowflake_prod
    SET INCLUDED_TABLES_REGEX = ('humanresources\..*$');

To update the INCLUDED_TABLES_REGEX or EXCLUDED_COLUMNS_REGEX definitions, use the SET keyword rather than UPDATE.

Add schema and exclude tables and columns

You can ignore specific tables and/or columns to prevent them being copied to the replication group when you add a new schema.

In the example below, the humanresources schema is added, the shift table (and all columns) is excluded, and the birthdate and gender columns are excluded from the employee table. All other objects in the humanresources schema are copied to group_1:

ALTER JOB postgres_replication_to_snowflake
    UPDATE REPLICATION GROUP group_1
    UPDATE INCLUDED_SCHEMA_DEFINITION ADD (humanresources.*)
    UPDATE EXCLUDED_SCHEMA_DEFINITION ADD (humanresources.shift.*, humanresources.employee.birthdate, humanresources.employee.gender);

Remove a table

You can remove one or more tables from an existing schema in the replication group and the data will cease to be copied. In the example below, the shift table in the humanresources schema is removed from group_1:

ALTER JOB postgres_replication_to_snowflake
    UPDATE REPLICATION GROUP group_1
    UPDATE EXCLUDED_SCHEMA_DEFINITION ADD (humanresources.shift.*);

Remove a schema

Removing a schema from a replication group is as simple as replacing the ADD keyword with REMOVE.

In the following example, the humanresources schema is removed from group_1, so that all tables within this schema will no longer be replicated. Note that the EXCLUDED_SCHEMA_DEFINITION should also be removed when running this statement:

ALTER JOB postgres_replication_to_snowflake
    UPDATE REPLICATION GROUP group_1
    UPDATE INCLUDED_SCHEMA_DEFINITION REMOVE (humanresources.*)
    UPDATE EXCLUDED_SCHEMA_DEFINITION REMOVE (humanresources.shift.*, humanresources.employee.birthdate, humanresources.employee.gender);;

Remove multiple tables

You can remove multiple tables using a comma-separated list. The following example removes the shift, employee, and department tables from the humanresources schema, along with their columns. All other tables in the schema will continue to replicate:

 ALTER JOB postgres_replication_to_snowflake
    UPDATE REPLICATION GROUP group_1
    UPDATE EXCLUDED_SCHEMA_DEFINITION ADD (humanresources.shift.*, humanresources.employee.*, humanresources.department.*);

Re-snapshot a table

For jobs ingesting CDC data, you can manually re-snapshot a table in the replication job using the following syntax.

ALTER JOB <cdc_job_name> 
    RESNAPSHOT TABLE <full_table_name>

Note that while the re-snapshot takes place, other tables in the replication will continue synchronizing.

The following example re-snapshots the customer_address table, which is synchronized by the load_raw_data_from_mssql job.

ALTER JOB load_raw_data_from_mssql 
    RESNAPSHOT TABLE dbo.customer_address;

Rename job

You can rename a job with immediate effect using the following syntax:

ALTER JOB <old_job_name> RENAME TO <new_job_name>;

Example

In the first line of the following example, the ingest_customers job is renamed to ingest_customers_emea to clarify which customers are being ingested from the source.

The next line renames the ingest_orders job to ingest_orders_emea, again clarifying the data ingested by this job.

ALTER JOB ingest_customers RENAME TO ingest_customers_emea; 

ALTER JOB ingest_orders RENAME TO ingest_orders_emea;

To view the details for a job before or after you rename it, you can query the system.information_schema.jobs table. The following query performs a wildcard search to find all jobs with a name prefix of ingest_.

SELECT * 
FROM system.information_schema.jobs
WHERE name LIKE 'ingest_%';

This enables you to check the job details and status to ensure you rename the correct job, or to find all jobs you want to rename.

Last updated 1 month ago