Jobs

This section is with regards to all actions related to creating jobs to schedule the cachings.

How jobs work with cache (high-level)

Jobs wrap cache operations (CACHE, REFRESH CACHE, VALIDATE CACHE) or custom SQL so they can run on a schedule or on demand (RUN JOB).

Job types (conceptual)

Kind	What it runs	Typical use
Single cache job	One `CACHE` / `REFRESH CACHE` / `VALIDATE CACHE` statement	Simple schedule for one resource
Bulk job (`BEGIN` … `END`)	Multiple cache statements in one job, optionally with `RUN JOB`	Parallel refresh of many mappings, then a dependent view/cube
Custom job	Arbitrary SQL (not a cache DDL statement)	Non-cache workflows

Full vs incremental vs CDC in jobs

Full refresh job — REFRESH CACHE MAPPING|VIEW name with no WHERE clause. Rebuilds the entire cache.
Incremental refresh job — REFRESH CACHE ... WHERE <partition filter>. Requires the resource to already be cached with a partition. The filter selects which partition slice is replaced (or which changed rows are upserted for CDC). Common windows: last 1 day, last 7 days, last month.
CDC validate job — VALIDATE CACHE MAPPING|VIEW name on a timbr-cache CDC materialization to apply hard deletes after refreshes.

Bootstrap rule for incremental/CDC: create the cache first with CACHE ... (or a one-shot job that runs CACHE), then schedule incremental REFRESH jobs. You cannot incrementally refresh a resource that was never fully cached.

Job options

In addition to the schedule (REFRESH), a job can declare:

Option	Meaning
`RETRY '{n}'`	Number of retries on failure
`RETRY INTERVAL '{seconds}'`	Wait between retries (seconds)
`PARALLEL '{n}'`	For bulk jobs: size of the thread pool used to run the statements inside `BEGIN` … `END` (default 1, max default 100)

CREATE OR REPLACE JOB {job_name}
  REFRESH '{refresh_value}'
  RETRY '3'
  RETRY INTERVAL '60'
  PARALLEL '4'
  AS BEGIN
    -- statements
  END

Intervals for refresh_value

Can be one of the following values:

none - Job runs only once (or only when invoked with RUN JOB)
daily - Job will run once a day
weekly - Job will run once a week
monthly - Job will run once a month
yearly - Job will run once a year
cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information

Create job to schedule a full cache refresh on a mapping

Creates a recurring job to do a full cache refresh on a mapping

Required information to create a job that fully refresh a cache for a mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to be created
{refresh_value} - The interval for which the job should be run.
{mapping_name} - The name of the cache for the mapping you want to fully refresh

Intervals for refresh_value

Can be one of the following values:

none - Job runs only once
daily - Job will run once a day
monthly - Job will run once a month
yearly - Job will run once a year
cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information

CREATE OR REPLACE JOB {job_name}
    REFRESH '{refresh_value}' 
        AS REFRESH CACHE MAPPING {mapping_name}

Create job to schedule an incremental cache refresh on a mapping

Creates a recurring job to do a full cache refresh on a mapping

Required information to create a job that fully refresh a cache for a mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to be created
{refresh_value} - The interval for which the job should be run.
{mapping_name} - The name of the cache for the mapping you want to incrementally refresh
{partition_property} - The name of the property by which the cache is partitioned by
{days_value} - An integer representing the amount of days to subtract from the current date in order to apply the cache.

Intervals for refresh_value

Can be one of the following values:

none - Job runs only once
daily - Job will run once a day
monthly - Job will run once a month
yearly - Job will run once a year
cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information

CREATE OR REPLACE JOB {job_name}
    REFRESH {refresh_value}
    AS REFRESH CACHE MAPPING {mapping_name}
        WHERE {partition_property} > CURRENT_DATE - {days_value} 

Create job to schedule a full cache refresh on an ontology view

Creates a recurring job to do a full cache refresh on an ontology view

Required information to create a job that fully refresh a cache for an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to be created
{refresh_value} - The interval for which the job should be run.
{view_name} - The name of the cache for the ontology view you want to fully refresh

Intervals for refresh_value

Can be one of the following values:

none - Job runs only once
daily - Job will run once a day
monthly - Job will run once a month
yearly - Job will run once a year
cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information

CREATE OR REPLACE JOB {job_name}
    REFRESH '{refresh_value}' 
        AS REFRESH CACHE VIEW {view_name}

Create job to schedule an incremental cache refresh on an ontology view

Creates a recurring job to do a full cache refresh on an ontology view

Required information to create a job that fully refresh a cache for an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to be created
{refresh_value} - The interval for which the job should be run.
{view_name} - The name of the cache for the ontology view you want to incrementally refresh
{partition_property} - The name of the property by which the cache is partitioned by
{days_value} - An integer representing the amount of days to subtract from the current date in order to apply the cache.

Intervals for refresh_value

Can be one of the following values:

none - Job runs only once
daily - Job will run once a day
monthly - Job will run once a month
yearly - Job will run once a year
cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information

CREATE OR REPLACE JOB {job_name}
    REFRESH {refresh_value}
    AS REFRESH CACHE VIEW {view_name}
        WHERE {partition_property} > CURRENT_DATE - {days_value} 

Show SQL create job statement

Required information for show create job (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job you want to show a create statement for.

SHOW CREATE JOB `{job_name}` 

Run a job

Executes an existing job manually

Required information to run a job (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to run

RUN JOB {job_name}

Remove a job

Deletes an existing job

Required information to remove a job (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to remove

DROP JOB {job_name}

Stop a running job

Stops the execution of an existing running job

Required information to stop a running job (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_id} - The id of the job to stop

KILL JOB {job_id}

Create a bulk job (multiple cache operations)

Creates a bulk job that runs several cache statements in one unit of work.

Syntax shape

CREATE OR REPLACE JOB {job_name}
  REFRESH '{refresh_value}'
  [ PARALLEL '{parallel_threads}' ]
  AS BEGIN
    {cache_or_refresh_or_validate_statement};
    {cache_or_refresh_or_validate_statement};
    [ RUN JOB {dependent_job_name}; ]
  END

Allowed statements inside BEGIN … END:

CACHE MAPPING|VIEW ...
REFRESH CACHE MAPPING|VIEW ... (full or with WHERE for incremental)
VALIDATE CACHE MAPPING|VIEW ...
RUN JOB {other_job_name}

Execution behavior (important)

Validated against the job runner:

All statements except RUN JOB are submitted to a thread pool sized by PARALLEL (default 1).
The bulk job waits for the entire pool to finish (all CACHE / REFRESH CACHE / VALIDATE CACHE work).
Only after that wait completes, every deferred RUN JOB is executed (in order).
A RUN JOB that targets the same bulk job name is skipped (no self-recursion).

This is the supported way to:

Refresh many mappings in parallel, then
Run a dependent job (for example refresh an ontology view or cube that reads those mappings) only after all parallel work finished.

Required information (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - Name of the bulk job
{refresh_value} - Schedule interval (none, daily, weekly, monthly, yearly, or cron)
{parallel_threads} - Optional thread-pool size for parallel cache operations
{dependent_job_name} - Optional job to run after all cache statements complete

Example: parallel mapping refreshes, then dependent view job

-- Dependent job: refresh a view/cube that needs up-to-date base mappings
CREATE OR REPLACE JOB refresh_orders_cube
  REFRESH 'none'
  AS REFRESH CACHE VIEW orders_cube

-- Bulk job: refresh mappings in parallel, then run the cube job
CREATE OR REPLACE JOB refresh_orders_pipeline
  REFRESH 'daily'
  PARALLEL '4'
  AS BEGIN
    REFRESH CACHE MAPPING map_orders
      WHERE order_date > CURRENT_DATE - 2;
    REFRESH CACHE MAPPING map_order_lines
      WHERE order_date > CURRENT_DATE - 2;
    REFRESH CACHE MAPPING map_customers;
    RUN JOB refresh_orders_cube;
  END

Example: CDC upserts + validate + dependent job

REFRESH and VALIDATE on the same resource must not run concurrently. Use PARALLEL '1' (sequential pool) for that chain, or move validate into the dependent job.

CREATE OR REPLACE JOB refresh_person_cube
  REFRESH 'none'
  AS REFRESH CACHE VIEW person_cube

-- Sequential pool: refresh, then validate, then dependent job after the pool finishes
CREATE OR REPLACE JOB cdc_person_pipeline
  REFRESH '0 */15 * * *'
  PARALLEL '1'
  AS BEGIN
    REFRESH CACHE MAPPING map_person
      WHERE dt > CURRENT_DATE - INTERVAL '1' day;
    VALIDATE CACHE MAPPING map_person;
    RUN JOB refresh_person_cube;
  END

Parallel bulk for independent mappings, then a sequential follow-up job:

CREATE OR REPLACE JOB cdc_validate_and_cube
  REFRESH 'none'
  PARALLEL '1'
  AS BEGIN
    VALIDATE CACHE MAPPING map_person;
    VALIDATE CACHE MAPPING map_orders;
    RUN JOB refresh_person_cube;
  END

CREATE OR REPLACE JOB cdc_refresh_mappings
  REFRESH '0 */15 * * *'
  PARALLEL '4'
  AS BEGIN
    REFRESH CACHE MAPPING map_person
      WHERE dt > CURRENT_DATE - INTERVAL '1' day;
    REFRESH CACHE MAPPING map_orders
      WHERE modified_ts > CURRENT_DATE - INTERVAL '1' day;
    RUN JOB cdc_validate_and_cube;
  END

Example: bootstrap multiple caches in one bulk job

CREATE OR REPLACE JOB bootstrap_caches
  REFRESH 'none'
  PARALLEL '2'
  AS BEGIN
    CACHE MAPPING map_person OPTIONS (schema='output');
    CACHE MAPPING map_orders OPTIONS (schema='output');
  END

Design guidance

Put independent mapping/view refreshes in the bulk body so they can use PARALLEL > 1.
Put dependent work in a separate job and invoke it with RUN JOB so it starts only after the pool finishes.
Do not run REFRESH and VALIDATE (or two refreshes) for the same resource concurrently — keep those sequential (PARALLEL '1' or chain via RUN JOB).
For CDC resources, pair REFRESH CACHE (upserts) with VALIDATE CACHE (hard deletes) before refreshing downstream cubes/views.
Use REFRESH 'none' for one-shot pipelines you only trigger with RUN JOB.

Create job to schedule a CDC validate on a mapping

Creates a recurring job that runs hard-delete reconcile on a CDC mapping cache (timbr-cache only).

Required information (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to be created
{refresh_value} - The interval for which the job should be run
{mapping_name} - The CDC-cached mapping to validate

CREATE OR REPLACE JOB {job_name}
  REFRESH '{refresh_value}'
  AS VALIDATE CACHE MAPPING {mapping_name}

Create job to schedule a CDC validate on an ontology view

Creates a recurring job that runs hard-delete reconcile on a CDC view cache (timbr-cache only).

Required information (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to be created
{refresh_value} - The interval for which the job should be run
{view_name} - The CDC-cached view to validate

CREATE OR REPLACE JOB {job_name}
  REFRESH '{refresh_value}'
  AS VALIDATE CACHE VIEW {view_name}

Create job to schedule a CDC incremental refresh on a mapping

Example of a scheduled incremental CDC upsert. The mapping must already be cached with cdc='true' on timbr-cache.

Required information (the curly brackets {} should not be an input, they are used only as a variable substitution):

{job_name} - The name of the job to be created
{refresh_value} - Schedule interval
{mapping_name} - CDC-cached mapping
{modified_column} - Modified date/timestamp partition column
{days_value} - Lookback window in days

CREATE OR REPLACE JOB {job_name}
  REFRESH '{refresh_value}'
  AS REFRESH CACHE MAPPING {mapping_name}
    WHERE {modified_column} > CURRENT_DATE - {days_value}

Jobs

How jobs work with cache (high-level)​

Job types (conceptual)​

Full vs incremental vs CDC in jobs​

Job options​

Intervals for refresh_value​

Create job to schedule a full cache refresh on a mapping​

Create job to schedule an incremental cache refresh on a mapping​

Create job to schedule a full cache refresh on an ontology view​

Create job to schedule an incremental cache refresh on an ontology view​

Show SQL create job statement​

Run a job​

Remove a job​

Stop a running job​

Create a bulk job (multiple cache operations)​

Syntax shape​

Execution behavior (important)​

Example: parallel mapping refreshes, then dependent view job​

Example: CDC upserts + validate + dependent job​

Example: bootstrap multiple caches in one bulk job​

Create job to schedule a CDC validate on a mapping​

Create job to schedule a CDC validate on an ontology view​

Create job to schedule a CDC incremental refresh on a mapping​

How jobs work with cache (high-level)

Job types (conceptual)

Full vs incremental vs CDC in jobs

Job options

Intervals for refresh_value

Create job to schedule a full cache refresh on a mapping

Create job to schedule an incremental cache refresh on a mapping

Create job to schedule a full cache refresh on an ontology view

Create job to schedule an incremental cache refresh on an ontology view

Show SQL create job statement

Run a job

Remove a job

Stop a running job

Create a bulk job (multiple cache operations)

Syntax shape

Execution behavior (important)

Example: parallel mapping refreshes, then dependent view job

Example: CDC upserts + validate + dependent job

Example: bootstrap multiple caches in one bulk job

Create job to schedule a CDC validate on a mapping

Create job to schedule a CDC validate on an ontology view

Create job to schedule a CDC incremental refresh on a mapping