Question

A data analyst maintains a reporting table that pulls from a 50-million-row source transaction system. The nightly ETL job now takes over six hours because it reloads the entire table each run. Business users only need newly added or modified transactions reflected, and the source table includes a reliable 'last_modified' timestamp column. Which extraction approach should the analyst adopt to reduce load time while keeping the reporting table accurate?

Accepted Answer

Switch to an incremental (delta) load that extracts only rows where last_modified is after the previous run's high-water mark

Answer

Continue the full refresh but schedule it to run weekly instead of nightly

Answer

Extract a random 10% sample of the source table each night to approximate the changes

Answer

Truncate the reporting table each morning and let users query the source system directly until the load finishes