Keeping a warehouse secure, recoverable, fast and correct.
16.1 Security
A warehouse gathers the data of the whole organisation in one place, so access to it must be controlled carefully. The table below shows the main security controls and how ShopMart uses each one.
| Control | ShopMart example |
|---|---|
| Role-based access | The "Store Manager" role can read Sales data and the "HR Analyst" role can read Employee data, but neither role can read the other's data |
| Row-level security | The Lucknow store manager sees only the rows where store_id = 17 |
| Column masking | Analysts see a customer's PAN as XXXXX1234F |
| Views | A view called "sales_no_cost" hides purchase costs from store staff |
| Audit trail | Every query on the Customer table is logged with the user, the time and the filter used |
| Encryption | Backups and network traffic are encrypted |
ExplanationEach layer of control limits what a person can see to what that person's job requires. Security checks add work to every query, so they are applied inside the database, through views and row filters, rather than separately in each report.
16.2 Backup and recovery
The figure shows a weekly schedule. One full backup is taken each week and a small incremental backup each day, which keeps the time spent on backups short. To recover from a failure, the backups are restored in order.
There are several types of backup, which the table below describes.
| Backup type | What it saves | Example |
|---|---|---|
| Complete (full) | Everything | Sunday night: 30 TB in 6 hours |
| Incremental | Only the changes since the last backup | Monday night: 80 GB in 15 minutes |
| Partial | Selected partitions | Only the 2026 partitions, because older ones never change |
| Online (hot) | Taken while users are working | Daily incremental backups during quiet hours |
| Offline (cold) | Taken with the database shut down | A quarterly full backup before an upgrade |
Example (restore time)The full backup copies 30 TB in 6 hours, so the backup system works at 30 ÷ 6 = 5 TB an hour. When a disk fails on Thursday, the full backup from Sunday must be restored first, which takes 30 ÷ 5 = 6 hours. The incremental backups from Monday, Tuesday and Wednesday, each about 80 GB, are restored next; together they hold 3 × 80 = 240 GB, or 0.24 TB, which takes 0.24 ÷ 5 ≈ 0.05 hours, about 3 minutes. Finally, Thursday's nightly load, which takes 2 hours, is run again. The total recovery time is therefore about 6 + 0.05 + 2 ≈ 8 hours.
ExplanationBecause a warehouse is non-volatile and partitioned by time, the old partitions, such as those for 2020 to 2024, can be backed up once and then set to read-only. Only the current partitions need to be backed up often. Recovery plans should also be tested regularly by actually restoring a backup to a spare server, so that a recovery time like the 8 hours above is known in advance rather than discovered during a failure.
16.3 Tuning the data warehouse
Tuning keeps a warehouse fast as its data and its users grow. It covers three main areas.
Load tuning. Load tuning makes the nightly load finish within the time available.
ExampleShopMart's nightly load must finish between 1 a.m. and 5 a.m., which is a window of 4 hours, but it was taking six hours. Splitting the load into four parallel streams would ideally cut the time to 6 ÷ 4 = 1.5 hours. In practice, coordinating the streams and rebuilding indexes add about half an hour, so the load now takes about 1.5 + 0.5 = 2 hours, finishing at 3 a.m. with 2 hours to spare.
ExplanationLoading is limited mainly by disk input and output, so running it in parallel and using bulk (direct-path) loading instead of row-by-row inserts gives the biggest improvement.
Query tuning. Query tuning makes the queries that users run return faster.
ExampleOne dashboard took 45 seconds to load. Adding a bitmap index on the category column and a monthly summary table brought this down to 3 seconds, which is 45 ÷ 3 = 15 times faster, and a saving of 42 seconds every time each of the 200 managers opens it.
ExplanationIndexes reduce the number of rows a query must read, and summary tables reduce the number of rows that are stored for the query to read.
Monitoring usage. Monitoring shows how users actually query the warehouse, so tuning can follow real needs.
ExampleIn one month, the query logs record 20,000 queries, and 14,000 of them ask for weekly figures, which is 14,000 ÷ 20,000 = 70 per cent. A weekly summary table is therefore added, while a yearly summary table that was used by only 12 queries, or 0.06 per cent, is removed.
ExplanationTuning is never finished, because it is driven by the way users query the warehouse, and that changes over time.
16.4 Testing the data warehouse
A warehouse is tested at several levels before and after it goes live. The table below lists the types of test, an example of each at ShopMart, and the condition for passing.
| Test type | ShopMart example | Passes when |
|---|---|---|
| Unit testing | Test the job that converts POS product codes | Every code maps to exactly one product_key |
| Integration testing | Run the full path from the POS extract through staging and the fact table to the dashboard | The dashboard shows the test day's sales |
| Data validation | Compare row counts and rupee totals with the source | The bill count and total amount match exactly |
| System and performance testing | Run 200 dashboard users at the same time | 95% of queries finish in under 5 seconds |
| Backup and recovery testing | Restore last week's backup to a spare server | The restored totals match production |
| User acceptance testing (UAT) | The finance team checks the March revenue report | The report matches the audited ledger |
Example (data validation)After the nightly load, the POS system reports 1,99,700 bill lines worth ₹3,12,45,600 for the day. The warehouse fact table for the same day holds 1,99,700 rows, and its amounts also add up to ₹3,12,45,600. Both the count and the total match, so the load passes. If the warehouse had shown 1,99,640 rows, the difference of 60 rows would point straight to a loading error.
ExplanationTesting a warehouse is mainly about the correctness of the data, not only the code. A report that runs perfectly but shows the wrong totals is still a failure. For this reason, data validation is repeated after every load, and it is often automated.
Exam points
- Row-level security restricts which rows a user can see; column masking hides sensitive values.
- An incremental backup saves only changes since the last backup; recovery restores the last full backup, then each incremental in order.
- Hot (online) backups run while users work; cold (offline) backups need the database shut down.
- Old time partitions can be backed up once and set read-only.
- Warehouse testing checks data correctness, such as row counts and totals matching the source.
Independent study notes for learning purposes. Not affiliated with or endorsed by any university, publisher or course provider. Product and company names are trademarks of their respective owners.