Upsert staged changes into a target table

SQL data engineering interview problem. Difficulty: advanced. Pattern: Upsert. About 20 minutes. Part of the Pro drill bank.

accounts_target holds the current state of each account. accounts_staging holds new or changed rows that you want to apply to it. Both are identified by account_id. This kind of job is called an upsert: update the rows that exist, insert the rows that do not. Do not change either of the 2 tables. Instead, create a table called accounts_work with the same rows as accounts_target, and apply the staging rows to this copy. If an account_id already exists and its plan or balance is different, update it. If the account_id does not exist yet, insert it. Every other row is left alone. Finish with a SELECT of the copy. The script may run more than once, so it must always give the same result.

Requirements

  • Create accounts_work as a copy of accounts_target.
  • Update a row only when its plan or balance really changed.
  • Insert staging rows whose account_id is not in the copy yet.
  • Do not touch rows that are only in the target.
  • End with a SELECT of account_id, plan, balance, ordered by account_id.

Constraints

  • account_id is unique in both tables, so each key matches at most 1 row.
  • Never modify accounts_target or accounts_staging.
  • Use CREATE OR REPLACE TABLE so a second run starts again from the target and gives the same result.

Examples

Input: accounts_target account_id | plan | balance 1 | basic | 100 2 | pro | 250 3 | basic | 80 4 | pro | 500 accounts_staging account_id | plan | balance 2 | pro | 275 3 | basic | 80 5 | basic | 40 6 | pro | 900 Output: account_id | plan | balance 1 | basic | 100 2 | pro | 275 3 | basic | 80 4 | pro | 500 5 | basic | 40 6 | pro | 900 Why this passes: Account 2 changes to 275, account 3 is unchanged, accounts 5 and 6 are new, and accounts 1 and 4 are not in staging so they stay as they were.

Input: accounts_target account_id | plan | balance 1 | basic | 10 accounts_staging account_id | plan | balance 1 | pro | 10 2 | basic | 5 Output: account_id | plan | balance 1 | pro | 10 2 | basic | 5 Why this passes: One update and one insert.

Topics: lakebench, sql, merge, upsert, dml.

More SQL interview questions · All interview problems · Learn data engineering

advanced

Upsert staged changes into a target table

Interview-style drill: Apply staged rows to a copy of the target: update changed keys, insert new ones, leave the rest.

`accounts_target` is the current state and `accounts_staging` holds incoming rows keyed by `account_id`. Do not change either table. Work on a copy: 1. Create a table named `accounts_work` with the same rows as `accounts_target`. 2. Apply `accounts_staging` to it: a key that already exists is updated when its `plan` or `balance` differs, a key that does not exist is inserted, and every other row is left alone. 3. Finish with a SELECT of the table. Columns: `account_id`, `plan`, `balance`. Order by `account_id`. The script must be safe to run twice.