Databricks Certified Data Engineer - Professional
Get started today
Ultimate access to all questions.
Comments
Loading comments...
Ultimate access to all questions.
An e-commerce company updates its product inventory monthly. The main inventory is stored in a delta table named inventory with columns: product_id, product_name, quantity, price, and is_active. At month's end, they receive updates in a delta table updated_inventory with the same schema. If a product_id is missing in updated_inventory, the is_active column in inventory should be set to false. Which MERGE INTO statement correctly updates the inventory table under these conditions?_
A
MERGE INTO inventory USING updated_inventory ON inventory.product_id = updated_inventory.product_id WHEN MATCHED THEN UPDATE SET inventory.quantity = updated_inventory.quantity, inventory.price = updated_inventory.price WHEN NOT MATCHED BY SOURCE THEN INSERT (product_id, product_name, quantity, price, is_active) VALUES (updated_inventory.product_id, updated_inventory.product_name, updated_inventory.quantity, updated_inventory.price, true) WHEN NOT MATCHED BY TARGET THEN UPDATE SET is_active = false;
B
MERGE INTO inventory USING supplier_updates ON inventory.product_id = updated_inventory.product_id WHEN MATCHED THEN UPDATE SET inventory.quantity = updated_inventory.quantity, inventory.price = updated_inventory.price WHEN NOT MATCHED BY SOURCE THEN INSERT (product_id, product_name, quantity, price) VALUES (updated_inventory.product_id, updated_inventory.product_name, updated_inventory.quantity, updated_inventory.price) WHEN NOT MATCHED THEN UPDATE SET is_active = false;_
C
MERGE INTO inventory USING updated_inventory ON inventory.product_id = updated_inventory.product_id WHEN MATCHED THEN UPDATE SET updated_inventory.quantity = inventory.quantity, updated_inventory.price = inventory.price WHEN NOT MATCHED THEN INSERT (product_id, product_name, quantity, price, is_active) VALUES (updated_inventory.product_id, updated_inventory.product_name, updated_inventory.quantity, updated_inventory.price, false) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET is_active = true;
D
MERGE INTO updated_inventory USING inventory ON updated_inventory.product_id = inventory.product_id WHEN MATCHED THEN UPDATE SET updated_inventory.quantity = inventory.quantity, updated_inventory.price = inventory.price WHEN NOT MATCHED THEN INSERT (product_id, product_name, quantity, price, is_active) VALUES (inventory.product_id, inventory.product_name, inventory.quantity, inventory.price, true) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET is_active = false;
E
MERGE INTO inventory USING updated_inventory ON inventory.product_id = updated_inventory.product_id WHEN MATCHED THEN UPDATE SET inventory.quantity = updated_inventory.quantity, inventory.price = updated_inventory.price WHEN NOT MATCHED THEN INSERT (product_id, product_name, quantity, price, is_active) VALUES (updated_inventory.product_id, updated_inventory.product_name, updated_inventory.quantity, updated_inventory.price, true) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET is_active = false;