Warehouses

Why store a warehouse table by column?

An aggregate reads one column for many rows. Column files skip the columns the query does not use, and they compress well.

What is the grain of a table?

What one row represents. If you cannot say it in a sentence, the metrics on that table will be double-counted.

What is a fact table?

The measurements at a stated grain, such as one row per order line, with keys pointing at the dimensions.

What is a dimension?

The descriptive context for a fact: the customer, the product, the day. Facts refer to it by key.

What is a star schema?

One fact table surrounded by dimensions. Queries join out from the fact, and the grain stays obvious.

What is a surrogate key?

A key you generate in the warehouse, stable even if the source's natural id is reused or missing.

What is a natural key?

An id that already means something in the source, such as an order number. It is what you dedupe on.

What is a slowly changing dimension?

A dimension whose attributes change, and for which you have chosen whether to overwrite history or keep the old value.

What does a type-2 change keep?

A new row for the new value, with a start and end, so a fact from last year still joins to last year's attribute.

What does a type-1 change do?

It overwrites the attribute. History now looks like the present. Use it only when the old value must not be kept.

What is a conformed dimension?

The same dimension, with the same keys, used by more than one fact table, so the metrics can be compared.

How should you partition a large fact?

By a column almost every query filters, often the date. A partition you never filter on still has to be planned.

What is clustering?

Sorting rows inside a partition by a column, so a filter on that column skips blocks. It helps when the partition alone is still huge.

What is an incremental model?

A model that recomputes only the new or changed partitions. A bug in the logic means those partitions must be rebuilt on purpose.

When do you fully refresh a model?

When the logic change affects old rows, or the table is small enough that rebuilding it is cheaper than reasoning about deltas.

What is a warehouse test for?

A check that fails the job when a key is null, a value is duplicated, or a row count drops. The dashboard then does not silently move.

Why test uniqueness of the grain?

Two rows for one real event double every sum. The test is the grain, stated as a constraint.

Why test that a foreign key is present?

A missing key drops the fact out of an inner join, and the metric shrinks with no change in the business.

What does a freshness test catch?

A table that stopped updating while the job still reports success, because there was nothing new to fail on.

What is a materialized table?

The query's result, stored, so readers do not each rerun the joins. It is only as fresh as the last build.

What is a view?

A query stored as a name. It is always current and always recomputed. A heavy view on every dashboard load is an incident.

How does a lake differ from a warehouse?

A lake holds files in open formats, often before a strict model. A warehouse is the modeled tables you query with a SQL engine.

What is a lakehouse table?

Files in the lake plus a table format that gives transactions, schema, and updates, so more than one job can write safely.

Why do table formats keep a manifest?

So a reader knows which files belong to the current snapshot, and a writer can add a snapshot without corrupting a reader.

What is snapshot isolation on a table?

A reader sees one committed snapshot. A writer produces the next. Readers do not see a half-written file list.

What does vacuuming a table do?

It removes files no current snapshot needs, after they are old enough that a long-running reader is done with them.

What is a multi-column cluster, such as Z-order?

A layout that helps filters on more than one column, when you cannot partition by all of them.

Why collect column statistics?

The planner uses them to choose a join order and to skip blocks. Stale stats pick a bad plan on a table that has grown.

What is a fan-out join?

A join that multiplies rows because the other side is not unique at the grain you assumed. Sums then count the same fact many times.

What is a bridge table?

A table that resolves a many-to-many relationship, so the fact grain does not have to repeat for each match.

What is a junk dimension?

A small dimension of flag combinations, so the fact does not carry a crowd of low-cardinality flags as its own columns.

Why use a date dimension?

So week, holiday, and fiscal period are looked up, not reimplemented in every query.

How do you store a daily grain across zones?

Decide which zone defines the day, and say so. Mixing UTC dates and local dates in one table splits one day into two.

How do you store money?

As an integer of minor units, or a decimal, plus the currency code. A float will not add up to the source.

What is a late-arriving dimension?

A fact arrives before the dimension row it points at. You keep a placeholder, or the fact's key points at nothing until the dimension lands.

What audit columns help?

When the row was loaded and by which job. They are how you delete one bad load without guessing.

Why separate a raw schema from a mart?

Raw is restricted and messy. The mart is the contract analysts query. Mixing them means a dashboard depends on a staging table.

Why does one metric need one name?

Two columns called revenue that mean different things will both be used. The definition lives with the column.

What is a merge key?

The columns that identify a row you will update. A wrong key inserts duplicates or overwrites the wrong row.

What is predicate pushdown?

The engine skips files or blocks using the filter, before reading the column data. Partitioning and stats are what make it possible.

What is a broadcast join?

The small side is sent to every worker, so the large side is not shuffled. It is a win only while the small side stays small.

What is a shuffle?

Moving rows across workers so rows with the same key land together. It is often the expensive part of a join or a group.

What is a disk spill?

A worker runs out of memory and writes the intermediate result to disk. The query still finishes, slowly.

Why not always use a larger warehouse?

You pay for the size the whole time it is on. A bigger engine speeds some queries and does nothing for a query bound on one hot file.

What is a result cache?

The engine reuses the answer of an identical query. It hides a stale table until the underlying data is marked changed.

What is a zero-copy clone?

A new table name that shares the current files, so you can test a rebuild without copying the bytes, until you write.

How do you undo a bad warehouse load?

Restore the previous snapshot, or delete the rows tagged with that job. Both require that the load was isolated enough to find.

Why tag warehouse usage?

So the bill can be tied to a team or a dashboard. An untagged warehouse is a shared cost nobody reduces.

What is a slot or a credit, in warehouse terms?

A unit of compute the engine spends on a query. A runaway query spends them until a limit or a person cancels it.