> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/alter-job.md).

# 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](#rename-job)

## Syntax

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

{% hint style="warning" %}
Note that not all job options are mutable.
{% endhint %}

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](#ingestion-jobs), [replication](#replication-jobs), or [transformation](#transformation-jobs)) to see the comprehensive list of mutable options.

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

### Ingestion jobs

* [Amazon Kinesis](/content/reference-1/sql-commands/jobs/create-job/ingestion/amazon-kinesis.md)
* [Amazon S3](/content/reference-1/sql-commands/connections/create-connection/amazon-kinesis.md)
* [Apache Kafka](/content/reference-1/sql-commands/jobs/create-job/ingestion/apache-kafka.md)
* [Confluent Kafka](/content/reference-1/sql-commands/jobs/create-job/ingestion/confluent-kafka.md)
* [Microsoft SQL Server](/content/reference-1/sql-commands/jobs/create-job/ingestion/microsoft-sql-server.md)
* [MongoDB](/content/reference-1/sql-commands/jobs/create-job/ingestion/mongodb.md)
* [MySQL](/content/reference-1/sql-commands/jobs/create-job/ingestion/mysql.md)
* [PostgreSQL](/content/reference-1/sql-commands/jobs/create-job/ingestion/postgresql.md)

### Replication jobs

* [Microsoft SQL Server](/content/reference-1/sql-commands/jobs/create-job/replication/microsoft-sql-server.md)
* [MongoDB](/content/reference-1/sql-commands/jobs/create-job/replication/mongodb.md)
* [MySQL](/content/reference-1/sql-commands/jobs/create-job/replication/mysql.md)
* [PostgreSQL](/content/reference-1/sql-commands/jobs/create-job/replication/postgresql.md)

### Transformation jobs

* [Amazon Redshift](/content/reference-1/sql-commands/connections/create-connection/amazon-redshift.md)
* [Data Lake Tables](/content/reference-1/sql-commands/jobs/create-job/transformation/job-options/data-lake-tables.md)
* [Elasticsearch](/content/reference-1/sql-commands/jobs/create-job/transformation/job-options/elasticsearch.md)
* [PostgreSQL](/content/reference-1/sql-commands/jobs/create-job/transformation/job-options/postgresql.md)

***

## Examples

### Alter one job option

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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. &#x20;

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

```sql
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`:&#x20;

```sql
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:

```sql
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**: &#x20;

{% code overflow="wrap" %}

```sql
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);
```

{% endcode %}

### 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**:

```sql
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`.&#x20;

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:

{% code overflow="wrap" %}

```sql
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);;
```

{% endcode %}

### 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:

{% code overflow="wrap" %}

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

{% endcode %}

### Re-snapshot a table

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

```sql
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.&#x20;

The following example re-snapshots the **customer\_address** table, which is synchronized by the **load\_raw\_data\_from\_mssql** job.

```sql
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:

```sql
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.&#x20;

```sql
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](/content/reference-1/monitoring/system-catalog/information-schema/jobs.md) table. The following query performs a wildcard search to find all jobs with a name prefix of **ingest\_**.

```sql
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.&#x20;


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/alter-job.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.