Core

Incremental Models and MERGE/UPSERT

Incremental models process only new or changed rows instead of rebuilding a table from scratch, using a high-watermark to select the delta and a MERGE/UPSERT to apply it. The hard parts are late-arriving data, idempotent re-runs, and choosing a watermark that does not silently drop rows. Applied-AI interviews probe it because full refreshes do not scale, and a subtly wrong incremental quietly loses or double-counts data.

a free account unlocks the core curriculum tier · no card

COURSES COVERING THIS TOPIC

No lesson covers this one directly yet. These teach the surrounding topic from the beginning.

Applied AI Engineering·The interviewPremium12mTurning this course into a study planA concrete four-week plan mapping the seven modules onto the question bank, plus what to do differently if your interview is next week rather than next month.

RELATED CONCEPTS

Idempotent Data Pipelines: Reruns Without Duplicate Rows Change Data Capture Slowly Changing Dimensions (SCD)Backfills and Reprocessing

PRACTICE THIS IN REAL QUESTIONS

SQL & Data EngineeringSCD Type 2 in Delta Lake: track history with one atomic MERGE.→SQL & Data EngineeringWhat is a MERGE (upsert), and how do you use it for incremental loads and SCDs?→SQL & Data EngineeringGiven rows with start and end timestamps, merge all overlapping intervals per user in SQL.→SQL & Data EngineeringGroup a stream of user events into sessions in SQL (30-minute inactivity gap) using window functions.→SQL & Data EngineeringDesign a data pipeline that is safe to re-run: idempotent writes, late data, and exactly-once effects.→SQL & Data EngineeringTop-N per group in SQL: ROW_NUMBER vs RANK vs DENSE_RANK.→

COMPANIES THAT ASSUME THIS

Scale AI Palantir Databricks Snowflake Zendesk Clay

NEXT IN DATA & SQL ENGINEERINGBatch vs Streaming→