Python & modeling

Solution

Type 1 overwrites the attribute; no history. Type 2 inserts a new row with valid_from / valid_to (and usually is_current) so a fact can join the dimension as it looked on the event date. If they ask Type 3, that is “limited history in extra columns.”

Solution

What one row represents. Mismatched grains fan out joins and invent revenue. Write the grain in the model name (fct_order_item, not fct_orders if the row is a line). If you cannot say the grain, you are not ready to write the SQL.

Solution

Row-level and schema checks closest to the transform (dbt tests, Great Expectations, or assertions in the Spark job). Orchestrator checks are for “did the file arrive / is row count 0.” Python pandas checks on a 200 GB extract are the wrong layer. Say the failure mode: fail the run vs quarantine vs warn.

Solution

If it already lives in a warehouse, write SQL (or dbt). Spark when the data is files on a lake and the job is distributed. pandas when it fits in memory on one box — and 40 GB does not. “I would load it into pandas and merge” ends the interview.

Solution

A star is worse when there is one consumer, one grain, and every dashboard already wants the same 80 columns — a wide table (or a mart that is already joined) is simpler. A star wins when many facts share conformed dimensions and you need as-of history. Do not model for a textbook; model for the join path.

Solution

Join on the business key AND fact.event_ts BETWEEN dim.valid_from AND dim.valid_to. If you only join is_current = true, yesterday’s late order picks up today’s address. This is the classic senior modeling question.

Solution

It is a Python loop. Use vectorized pandas/numpy or push the work to SQL/Spark.

Solution

No. Parquet/Arrow with a schema. Pickle is a security and compatibility smell.

Solution

The GIL will not give you 8×. Use processes, Polars, or a warehouse.

Solution

Non-idempotent create. Use an idempotency key or an upsert on a natural key.