Matillion ETL Interview Questions: Job Scope, Variables, and Recoverable Loads

A parent job passes a date to a child load. The child finishes without an obvious configuration error, yet it processes the wrong day. Later a transient warehouse failure causes the parent to retry, and the target table gains duplicate rows. A good Matillion ETL interview answer does not start with “use a Retry component.” It identifies which product edition is in scope, where a variable resolves, what job boundary the retry covers, and what database state survived the failed attempt.

This guide is specifically about Matillion ETL (METL) orchestration and transformation jobs, not a claim that the newer Data Productivity Cloud has identical menus or variable semantics. The parent/child example and load design are original interview exercises. Official Matillion ETL documentation supports the job, variable, Run Orchestration, and Retry behavior cited below; no candidate report is used to assert a particular employer's questions.

First question: what kind of job owns each step?

Matillion ETL documentation distinguishes orchestration jobs from transformation jobs. Orchestration manages workflow steps such as loading external data, DDL, and calling other jobs; transformation defines warehouse-side data transformations. A parent orchestration can call a child orchestration through Run Orchestration, wait for its completion, and follow success or failure links. That boundary is useful for isolating a repeatable load step and its retry policy.

Before drawing components, specify the warehouse platform and METL version because available components can vary. Define the input window, target table, and success condition. In the original example, the parent starts a daily load for business date 2026-09-22, passes it to a child, the child stages source rows, validates them, and merges into a curated table. A final reconciliation compares expected source keys with published target keys. None of these names describes a built-in Matillion template; they are a proposed design the candidate should defend.

Parent orchestration
  Start → set run_date → Run Orchestration(child_load)
                             ├─ success → reconcile → End Success
                             └─ failure → record failed window → End Failure

Child orchestration
  land source → validate stage → publish target → return status

The job boundary determines what a retry repeats. If the parent retries only a failed Run Orchestration call, the child may replay the whole load. If the child made nontransactional external calls or committed warehouse writes before failing, replaying it is not automatically safe. Ask exactly which operations are atomic on the selected warehouse and which are outside its transaction boundary. A green component in the UI cannot by itself prove that the business window is complete.

Second question: which variable value does the child use?

Matillion ETL has environment and job variables with different scopes. Its scope guide says environment variables are per project and job variables are per job. The job-variable documentation states that a job variable overrides an environment variable of the same name within that job. General variable documentation describes resolution as job, then environment, then system. These are official METL facts; do not infer the value of a child variable merely from a parent's canvas label.

Use a deliberate collision to test the answer. The project environment defines run_date = 2026-09-21. The parent job has a job variable run_date = 2026-09-22. The child job also declares its own public job variable run_date, with default 2026-09-20. If the parent simply calls the child without configuring a scalar variable assignment, the child will resolve its own job-level default rather than inheriting the parent's same-named job variable by magic. If the Run Orchestration component explicitly sets the child's scalar run_date to the parent's value, the child receives the intended 2026-09-22, subject to the component's configuration and variable visibility. The environment value is shadowed while the child job variable exists.

Location	run_date value in the exercise	What to check
Project environment	2026-09-21	Selected environment and project
Parent job variable	2026-09-22	Parent's Manage Variables and runtime override
Child job variable default	2026-09-20	Child's Manage Variables and visibility
Run Orchestration scalar mapping	Parent value to child run_date	Explicit Set Scalar Variables entry
Child effective value	2026-09-22 only when mapping applies	Job/task run details and a safe diagnostic log

The table is an original scenario, not a claim that the product fills these values automatically. A private child variable cannot be discovered or overwritten through the calling component in the same way as a public one, according to the official job-variable page. Also distinguish a variable's scope from its copied/shared behavior in iterator branches. For parallel iterations, Matillion's job-variable guidance recommends copied scope so each parallel component gets its own copy. If the interviewer says values change unpredictably in parallel loads, ask whether an iterator or branch shares mutable state.

The safe fix is to use distinct, explicit variable names or an explicit parent-to-child mapping, then log the effective nonsecret run date at the start of the child. Test the child alone and through the parent. Avoid logging credentials or sensitive connection values. A schedule may select a different environment than the interactive user expected; record the environment name and project in the runbook. A successful variable substitution in a test environment does not prove production used the same selection.

Third question: where should a retry live?

Matillion ETL's Retry component repeats a connected component until success or a configured maximum number of retries is reached; the original attempt is not counted as a retry. The docs warn that on Snowflake or Redshift, retrying inside a transaction block can fail again if the underlying transaction has not rolled back. They recommend attaching Retry to a Run Orchestration component when that called job contains its own transaction logic. This is a product-specific caveat worth naming, not a license to promise that every warehouse and every job is automatically recoverable.

The interview decision is the retry unit. A network timeout before any write may be safe to retry immediately. A failure after staging but before publish may leave a staging artifact that should be replaced or identified by run ID. A failure after a committed merge needs a rerun design that does not add the same business keys again. Retrying a generic transformation component three times without knowing which state survived can multiply data or conceal a persistent schema problem.

Propose a child load with a stable window key and a staging table keyed by run_date plus source identifier. Before publishing, validate schema, uniqueness, count ranges, and watermark completeness. Publish with a warehouse operation whose repeat behavior is defined: for example, a transactionally supported partition replace, or a deterministic merge keyed by source business ID and update timestamp. Record the run ID, source snapshot, and target version. These are editorial design recommendations; Matillion's Retry component does not implement idempotency for you.

Failure injection: replay the same date safely

Assume the child lands 1,000 source records for 2026-09-22 but fails after staging 600. The parent sees failure. What should the operator do? First inspect the Task History and component error, determine whether the target publish step began, and compare source, stage, and target counts by business key for that exact date. If only stage rows exist, clear or replace that run's stage subset and rerun the same date. If the target has partial committed rows, use the documented merge or partition replacement path and reconcile after the rerun. Do not advance the watermark until the target state and counts are verified.

A minimal rerun checklist for the interview is concrete:

1. Freeze the input date, selected environment, source snapshot or extraction range, and warehouse target. Do not silently switch to “today.”
2. Find the failure boundary: external extraction, stage write, validation, transformation, or publish. Record what committed.
3. Confirm the effective job-variable values in the child and the parent-to-child mapping. Correct a scope collision before retrying.
4. Rerun only a unit designed for replay. Use a stable business key or atomic partition replacement; avoid append-only duplication.
5. Reconcile distinct keys, row counts, checksums or totals, late records, and rejected rows against the source contract. Mark success only after these checks.

The example's 1,000 and 600 are hypothetical. Do not claim that Matillion automatically restores a table to its previous state after a component failure. Failure links control job flow, while warehouse transactions and the chosen SQL/load pattern determine data state. If a source API is nonrepeatable or changes between runs, save the extracted snapshot or define a bounded extraction watermark; otherwise the second attempt may process different records even with the same run_date.

Fourth question: how do you prevent a variable or load incident from recurring?

Make the contract visible at the job boundary. The child should declare required public inputs, acceptable date format, and expected environment. The Run Orchestration component should map each scalar deliberately; a same-name collision should be either removed or tested. Add a small validation step that fails before writing if the run date is missing, outside the expected window, or inconsistent with the staged source partition. This makes a wrong variable fail loudly rather than producing a plausible but wrong table.

For recovery, test three injected faults in a nonproduction environment: timeout before landing, failure after partial staging, and failure after a publish attempt. For each, inspect what remains, rerun with the same window, and verify the target contains one correct record per business key. A fourth test changes the environment selection to ensure the job cannot write into the wrong warehouse unnoticed. Keep the task log, warehouse query, and reconciliation report together so the next engineer can tell whether a retry was safe.

If the interviewer asks about Data Productivity Cloud, state that this article's precedence, component names, and UI paths come from Matillion ETL documentation. Check the DPC-specific docs and product edition before claiming identical behavior. This distinction matters because version migration can change variable models and job packaging. The general principle of explicit scope and idempotent replay transfers; a particular menu click or precedence rule may not.

Sources and Further Reading

• Matillion ETL, Jobs — orchestration and transformation job roles.
• Matillion ETL, Job Variables — job scope, same-name precedence, visibility, and copied/shared behavior.
• Matillion ETL, Variables — variable types and lookup order.
• Matillion ETL, Scope of Features — per-project and per-job scope.
• Matillion ETL, Run Orchestration — child job call, variable mapping, and success/failure links.
• Matillion ETL, Retry — attempt semantics and transaction warning.