Free Data-Engineer-Associate flashcards

A bronze STRING column is mostly numeric text but holds some unparseable values, and ANSI behaviour is enabled. How do you convert it to DECIMAL in a silver load without failing the job, keeping bad rows with a null amount?

Use TRY_CAST instead of CAST. TRY_CAST applies the same conversion rules as CAST but returns NULL when a value cannot be represented in the target type, rather than raising a runtime error. That lets unparseable rows survive the load with a null amount instead of aborting the whole job.

Under ANSI behaviour, does CAST to a decimal type silently substitute NULL when it cannot parse a value?

No. That NULL-on-failure behaviour belonged to the legacy non-ANSI mode. With ANSI behaviour enabled, a CAST that cannot represent its input in the target type raises a runtime error and aborts the query, so TRY_CAST is required if you need a null result instead of a failure.

How does COPY INTO avoid re-loading files it has already ingested into a Delta table when it is run repeatedly on a schedule against the same folder?

COPY INTO keeps metadata tied to the target table listing every source file it has already loaded. On each run it compares the current folder contents against that record and ingests only files it has not seen before, so repeated runs against the same folder are idempotent with no streaming checkpoint needed.

If COPY INTO is rerun against a folder that still contains previously loaded files, does it duplicate those rows in the target table?

No. That behavior describes a plain read-and-append of a folder, not COPY INTO. COPY INTO maintains its own per-target record of already-ingested files and skips them, so files retained in the folder are not reloaded.

In a Lakeflow Job, a condition task sends execution down one branch, and a downstream task depends only on a task from the branch not taken, with Run if left at the default. What happens to that task and to the run result?

The downstream task is skipped, because the default Run if condition of All succeeded requires its dependency to have actually succeeded, and a task on the untaken branch never runs and is marked skipped rather than failed. A run made entirely of successful and skipped tasks is still reported as a successful run, since skipping is a distinct terminal state from failure.

If a task is skipped because its dependency sat on the untaken branch of a condition task, does that skip count as a failed dependency and fail the downstream task and the run?

No. A skipped task is not a failure state. The default Run if condition does block a dependent task whose upstream was skipped, but the dependent task is itself marked skipped, not failed, and skipped tasks do not cause the overall run to be reported as failed.

Besides SELECT on a table, what other Unity Catalog privileges does a user need to successfully query it, and why does missing them look like the table does not exist?

The user also needs USE CATALOG on the catalog and USE SCHEMA on the schema that contain the table. Unity Catalog resolves a three-level name by walking the whole securable path, and without traversal rights on every parent the path cannot be resolved at all. This missing-traversal case surfaces as a table-not-found error rather than a permission-denied error, because the engine never gets far enough to know the table exists.

Does a SELECT grant on a table only take effect for the table's owner, requiring a separate metastore-level grant for anyone else to read it?

No. A SELECT grant applies directly to the named principal, whether a user, group, or service principal, regardless of who owns the table. Unity Catalog has no requirement to duplicate a grant at the metastore level for ordinary reads; a table-level SELECT is sufficient once the traversal privileges on the catalog and schema are also in place.