Data Transformation and Modeling practice questions

fillna can take a dictionary of column-to-value pairs so each column gets its own default.

fillna({"quantity": 0, "discount": 0.0}) fills only the two named columns. An unscoped fillna(0) also changes unit_price, dropna removes rows instead of filling them, and na.replace replaces existing values rather than nulls.

1. Identify which columns need a default and which must keep nulls.
2. Choose fillna, because the rows must stay.
3. Pass a dictionary so only the named columns are filled.

Exam tip: fillna({col: value}) fills only the columns you name.

Standardizing data types in silver means parsing strings with the pattern the source actually uses.

to_date(column, pattern) parses a string using an explicit datetime pattern, so 'MM/dd/yyyy' fits month-first dates. A plain cast only understands ISO dates, the day-first pattern misreads the values, and date_format goes the other way, producing a string.

1. Look at the source string layout: month, day, year.
2. Pick a parsing function that accepts a pattern.
3. Match the pattern to the layout exactly.

Exam tip: Parse non-ISO date strings with to_date and a pattern that matches the source.

try_cast converts what it can and returns NULL for invalid values instead of failing the query.

With ANSI mode on, CAST of a malformed string raises an error and the CTAS fails. try_cast(amount AS DECIMAL(12,2)) returns NULL for 'N/A' and converts every valid value, which meets all three requirements. Wrapping CAST in coalesce does not prevent the error, and casting to DOUBLE fails the same way.

1. Note that ANSI mode makes invalid casts raise errors.
2. The rows must stay, so filtering them out is not an option.
3. Use the cast variant that returns NULL on invalid input.

Exam tip: Need a cast that never fails on bad input? Use try_cast; it returns NULL.

Monetary values belong in an exact DECIMAL type with enough precision for the largest amount.

DECIMAL(precision, scale) is exact, so cent values add up without rounding drift; precision 18 leaves room for large totals. DOUBLE and FLOAT are approximate binary types, and DECIMAL(5,2) is exact but too small for amounts of 1000 and above.

1. The requirement is exact cent-level totals.
2. Exclude approximate floating types.
3. Choose a DECIMAL with enough digits before the decimal point.

Exam tip: Money: exact DECIMAL with enough precision, never FLOAT or DOUBLE.

The save mode decides what a write does when the target silver table already exists.

A full rebuild should replace the table, and mode("overwrite") does that atomically in Delta Lake, so a retry produces the same result. Append duplicates on reruns, ignore stops refreshing after the first run, and errorifexists fails once the table exists.

1. The job rebuilds the whole table each night.
2. A retry must give the same result as one successful run.
3. Choose the save mode that replaces existing contents.

Exam tip: Full rebuild of a silver table = overwrite; append is for new rows only.

CREATE OR REPLACE TABLE AS SELECT (CTAS) is the SQL way to write a cleaned result to a new silver table.

CTAS with OR REPLACE materializes the SELECT into a Delta table and swaps in the new contents each run. IF NOT EXISTS stops refreshing after the first run, INSERT INTO appends duplicates, and a view does not store data.

1. The output must be a physical table.
2. Each run must replace, not add to, the contents.
3. Pick CREATE OR REPLACE TABLE AS SELECT.

Exam tip: Rebuild a silver table in SQL with CREATE OR REPLACE TABLE ... AS SELECT.