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.

Solution

Batch the API (or SQL IN lists), then maybe a pool. iterrows is the slowest pandas pattern. Profile: is the time pandas or the network? Fix the algorithm first.

Solution

Merging on date without as-of (merge_asof with direction=backward) so future labels leak. Leakage is a join bug. Say the as-of key and that the label was known at t.

Solution

Parquet (or Arrow IPC) with a schema. pickle is Python-version brittle and not a lake format. If Spark is downstream, write partitioned Parquet and let Spark read it.

Solution

Tiny dimensions, one-off migrations, or when the source is an API. Warehouse SQL is the default for 50M-row facts. A pandas SCD2 that loads the whole history into RAM is a ticket waiting to happen.

Solution

The code under test is not using the mocked clock — it reads Airflow context, or a default argument evaluated at import. Inject logical_date. Freezegun does not fix a default arg trap.

Solution

pandas will OOM. polars lazy can stream/sink if you do not collect the whole join. Still: this job belongs on a warehouse or Spark if it runs daily. Laptops are not prod.

Solution

A hash of PII is still PII if it is reversible or unique. Use a vault token from a real identity service, or a surrogate that is not derived from the email. Hashing is not anonymization.

Solution

pandas NaN is not SQL NULL; uniqueness and groupby dropna defaults surprise people. Prefer nullable dtypes or stay in SQL for grain tests. Show a failing test, not a blog rant.