Keeping Data Fresh: Beginner

When you hear these in an interview, this is the concept being tested

• ▸"Do you reload everything or just the changes?"
• ▸"How do you handle incremental updates?"
• ▸"Full refresh vs incremental: when do you use each?"

After your initial answer, expect these probes

• ▸"What if the source has no updated_at column?" Full refresh is the only safe option. Or push the source team to add CDC. Some teams hash each row and compare, but that costs almost as much as a full reload.
• ▸"What about deleted rows?" Incremental with timestamps cannot detect hard deletes. You need CDC or soft deletes with a deleted_at flag.
• ▸"When would full refresh actually be better?" Small tables, tables with no reliable change marker, or after schema changes that invalidate old data.

When you hear these in an interview, this is the concept being tested

• ▸"How do you capture changes from the source database?"
• ▸"What is CDC?"
• ▸"How do you detect deleted rows?"

After your initial answer, expect these probes

• ▸"What is a WAL?" Write-Ahead Log. The database writes every change to a log before applying it. CDC tools read this log to capture changes without querying the table directly.
• ▸"Does CDC add load to the source database?" Log-based CDC adds almost none. It reads a file the database already writes. Trigger-based CDC adds significant overhead because it runs code on every row change.
• ▸"When would you NOT use CDC?" When the source is a flat file, an API, or a system that does not expose a change log. CDC is specifically for databases.

When you hear these in an interview, this is the concept being tested

• ▸"How do you track historical changes?"
• ▸"A customer changes their address. How does your pipeline handle that?"
• ▸"What is SCD Type 2?"

After your initial answer, expect these probes

• ▸"How do you join a fact table to an SCD Type 2 dimension?" Join on the business key AND filter where the fact's event_date falls between the dimension's start_date and end_date. This gives you the dimension values that were true at the time of the event.
• ▸"Type 2 tables grow forever. How do you manage that?" In practice, dimension tables grow slowly. A customer table with 10M customers and 3 changes per customer averages 30M rows. That is manageable. Partition by is_current for fast lookups.
• ▸"When would you use Type 1 instead?" When history does not matter. Fixing a misspelling, correcting a data entry error, or updating a non-analytical attribute.

When you hear these in an interview, this is the concept being tested

• ▸"The source team adds a new column. What happens to your pipeline?"
• ▸"How do you handle schema changes?"
• ▸"What breaks when the source schema changes?"

After your initial answer, expect these probes

• ▸"How do you detect schema drift automatically?" Compare the source schema to the last known schema before processing. Tools like Great Expectations or custom checks can validate column names and types on each run.
• ▸"What if you need to backfill after a schema change?" That depends on whether historical data needs the new column. If yes, you backfill with a default value or re-extract from source. If no, you apply the change going forward only.
• ▸"How do file formats handle schema evolution?" Parquet and Avro both support adding columns without breaking existing readers. But removing or renaming columns requires rewriting files.

When you hear these in an interview, this is the concept being tested

• ▸"You found a bug in your transformation. How do you fix historical data?"
• ▸"How do you backfill a table?"
• ▸"A pipeline was down for 3 days. What now?"

After your initial answer, expect these probes

• ▸"How do you backfill without impacting production queries?" Process during off-peak hours. Write to a staging table first, then swap it in. Or use partition-level REPLACE so only one partition is unavailable at a time.
• ▸"What if the source data is no longer available for the backfill period?" This is why data retention policies matter. If the raw data in your landing zone is deleted after 30 days, you cannot backfill beyond that window.
• ▸"How do you prioritize: backfill old data or keep processing new data?" New data first, always. Run backfills in a separate pipeline or lower-priority queue so current processing is not blocked.