Data Warehousing.

Analytical data storage, dimensional modeling, and warehouse architecture. Covers 14 sub-skills.

• Data Warehousing · 1 of 3

  OLAP vs OLTP

  Which physical storage choice most distinguishes a modern analytical warehouse from a transactional database serving point lookups for an app?

  AAppend-only log segments compacted by recency for fast tail readsBHash-partitioned key-value pages tuned for single-record fetch latencyCRow-oriented heap with B-tree indexes covering most query predicatesDColumnar layout with vectorized scans, optimized for wide aggregations

• Data Warehousing · 2 of 3

  OLAP vs OLTP

  An analytics engineer is comparing dbt mart materializations on a warehouse with materialized views inside the operational DB. Which property points to building marts on the warehouse?

  AConsumers read denormalized output inside the user request pathBOutput must be visible inside the same user transaction as the writeCTransformations must not contend with user-write throughput at peakDPipeline must enforce row-level foreign keys on every write event

• Data Warehousing · 3 of 3

  OLAP vs OLTP

  Comparing CockroachDB with Snowflake for the same mixed reporting and transactional workload, which property still favors Snowflake despite Cockroach being marketed as HTAP?

  ASub-millisecond per-row reads are required for user-facing checkoutBGeo-partitioned writes must be pinned to a region for residencyCStrong serializable transactions are needed across replicas globallyDHeavy ad-hoc scans elastically scale a separate compute cluster