Self-Assessment Quiz: Data Warehouses

Twenty questions. Aim for 16 or more.

Question 1

Warehouses generally have no B-tree indexes because:

• A. Columnar data cannot be indexed
• B. Scans are cheap enough that indexed lookup rarely wins, and zone maps provide most of the skipping
• C. Indexes are too expensive to store
• D. Warehouses do not support the syntax

Question 2

Most warehouses treat a declared PRIMARY KEY as:

• A. An enforced uniqueness constraint
• B. An optimizer hint and documentation — not enforced
• C. A clustering key
• D. A partitioning specification

Question 3

Projection pushdown means:

• A. Skipping row groups whose statistics exclude them
• B. Reading only the columns the query names
• C. Pushing aggregation to the storage layer
• D. Compressing before transmitting

Question 4

Predicate pushdown works only if:

• A. The table has an index
• B. The data is physically ordered by the column you filter on
• C. The query uses a WHERE clause
• D. Statistics have been refreshed

Question 5

For the two-column aggregate in §8.2, a column store read approximately how much less than a row store, compressed?

• A. 2× less
• B. 20× less
• C. 100× less
• D. 1,000× less

Question 6

Which encoding best suits a channel column with four distinct values?

• A. Delta encoding
• B. Dictionary encoding
• C. Bit packing
• D. Run-length encoding alone

Question 7

Which column compresses worst?

• A. order_id (monotonic)
• B. status (7 values, sorted)
• C. email (high cardinality)
• D. quantity (1–30)

Question 8

Sorting a table by a low-cardinality column before writing it:

• A. Has no effect on size
• B. Can shrink it several times over, and is free at write time
• C. Only helps if the engine supports clustering
• D. Increases size due to sort overhead

Question 9

Which is NOT enabled by separated storage and compute?

• A. Workload isolation
• B. Zero-copy cloning
• C. Elasticity
• D. Lower network latency on cold reads

Question 10

Snowflake bills compute:

• A. Per byte scanned
• B. Per credit-second the warehouse is up, with a 60-second minimum per resume
• C. Per query
• D. Per node-hour, always on

Question 11

Moving a Medium Snowflake warehouse from 60-minute to 60-second auto-suspend changed annual cost from roughly:

• A. $70,080 to $36,500
• B. $36,500 to $8,176
• C. $12,264 to $8,176
• D. $8,176 to $2,000

Question 12

BigQuery's characteristic mistake is:

• A. An idle warehouse left running
• B. A bad distribution key
• C. SELECT * and queries on unpartitioned tables
• D. Over-clustering

Question 13

An unpartitioned 4 TiB table on BigQuery on-demand costs how much per query, regardless of the WHERE clause?

• A. $0.25
• B. $6.25
• C. $25.00
• D. It depends on the filter

Question 14

Redshift's characteristic mistake is:

• A. Not using materialized views
• B. A bad distribution key, causing skew and cross-network redistribution on joins
• C. Loading files that are too large
• D. Failing to enable result caching

Question 15

The working file size range for a bulk load into a warehouse is:

• A. 1–10 MB compressed
• B. 100–250 MB compressed
• C. 1–2 GB compressed
• D. Size does not matter

Question 16

Snowflake's COPY INTO load metadata:

• A. Never expires
• B. Expires after 64 days, so an older backfill silently reloads files
• C. Expires after 7 days
• D. Must be enabled explicitly

Question 17

The consistent cost ranking in a platform this size, largest first, is:

• A. Storage, BI compute, transformation compute, idle compute
• B. Transformation compute, BI compute, idle compute, storage
• C. Idle compute, storage, transformation compute, BI compute
• D. BI compute, storage, transfer, transformation compute

Question 18

Which day-one control is off by default and is the best guard against an accidental full scan?

• A. Auto-suspend
• B. Query tagging
• C. A per-query byte limit such as maximum_bytes_billed
• D. A resource monitor

Question 19

In a warehouse query profile, spilling to remote storage indicates:

• A. Normal behavior for large queries
• B. The strongest signal that a query needs attention — often a join producing far more rows than expected
• C. A network problem
• D. That result caching is disabled

Question 20

DuckDB's first practical limit is usually:

• A. Data volume above 1 TB
• B. Concurrency, at roughly 3–5 simultaneous users
• C. Lack of window function support
• D. Inability to read Parquet

Answer Key

1. B — §8.1. Zone maps provide most of the skipping benefit at a fraction of the maintenance cost.

2. B — §8.1. This surprises people badly, and it is part of why Chapter 23's tests exist — the database will not do this for you.

3. B — §8.2. The first of the two columnar savings.

4. B — §8.2. The statistics exist regardless; if values are scattered, every row group's range spans everything and nothing can be skipped.

5. C — §8.2. 2.07 GB against roughly 20.7 MB.

6. B — §8.3. Four distinct values across 6.48M rows: 2 bits per row plus a small dictionary, about 24×.

7. C — §8.3. High-cardinality free text barely compresses. That contrast is the design lesson.

8. B — §8.3. Free at write time, paid back on every read forever. It is why compaction jobs sort.

9. D — §8.4. Latency on cold reads is a cost of the architecture, not a benefit — compute reads over a network rather than from local disk.

10. B — §8.5. Per second the warehouse is up, not per second it is working. That distinction is the whole of the idle-warehouse mistake.

11. B — §8.5. $36,500 → $8,176. Never-suspend is $70,080; 5 minutes is $12,264.

12. C — §8.5. Per-byte pricing makes projection pushdown visible on the invoice.

13. C — §8.5. 4 TiB × $6.25 = $25.00, every time, filter or no filter.

14. B — §8.5. Chapter 4's skew problem, made explicit as a configuration choice.

15. B — §8.6. Too small and per-file overhead dominates; too large and you lose parallelism.

16. B — §8.6. Genuinely useful, and a trap for exactly one reason.

17. B — §8.7. Storage is usually fourth and is where everyone looks first.

18. C — §8.7. It fails the query rather than running it and charging you.

19. B — §8.8. Frequently the fan trap from Chapter 6 §6.9 appearing as a performance problem rather than as a wrong number.

20. B — §8.9. Which has nothing to do with data size — and two of the three thresholds do not.

Topic map