Data Engineering Fundamentals

1. A product team runs a reporting query over two years of orders on the production PostgreSQL database, and checkout slows down. What is the usual fix?

• Copy the data into an analytical store and run reports there, away from the live database
• Add more indexes to the orders table so the report and checkout both run equally fast
• Move checkout onto a read replica and keep running the report on the primary database
• Run the report more often on smaller ranges so each query holds fewer locks on the table

2. Why are frequent single-row UPDATEs slow in a columnar warehouse?

• Columnar warehouses lock the whole database for every update until the next nightly load
• Updates are converted to full table scans because columnar tables cannot hold any keys
• One row's values are spread across many column files, each of which has to be rewritten
• Each update triggers a recompression of every table in the schema, not just the one row

3. Finance runs month-end reports while data scientists run heavy jobs on the same tables. In a warehouse that separates storage and compute, how do you stop them slowing each other down?

• Copy the tables into a second warehouse and sync them back every night
• Schedule the two groups on alternate days so they never overlap again
• Give each group its own compute cluster reading the same stored data
• Partition the tables by team so each group only ever reads its own rows