Skip to content
BENIBIX Learn

Storage & Warehousing

Where large volumes of data are kept for analysis · 49 terms

Sort
Compare

Data Warehouses

13

Databases built for reports, the team-sized slices cut from them, and the cloud products that sell them.

A big store of data built for reports. Clean data from many apps lives there.

A warehouse in the cloud. You pay for the space and the work apart.

The compute in Snowflake that runs your queries. You pay per second while it runs.

Code you write in Python or Java that runs in Snowflake.

Google's warehouse with no servers to run. You pay for the data each query reads.

Amazon's warehouse in the cloud. Each query is split up and run on many nodes.

A free, open-source database that stores data in columns. It is built for quick sums.

A small slice of the warehouse for one team. It holds only what they need.

Warehouse Design

5

How a warehouse is built inside. Many machines share one query, data is kept apart from them, and new rows pass through set tables.

One query split across many machines. Each works on its own slice of the data.

Data and the machines that read it are kept apart. Each can grow on its own.

A table where new data waits. It is checked before it moves on.

A way to build a warehouse. It uses hubs, links and satellites.

A fast copy of a table. It shares its files until one side changes.

Data Lakes

16

Cheap stores of raw files, and the layers that make them act like a warehouse.

A cheap store where each file is saved whole and found by its key.

Amazon's object store. Files sit in buckets, and each has a key.

Azure's store for data lakes. It is Blob Storage with real folders added.

A big, cheap store of raw files of every kind. Shape comes later.

A data lake with warehouse tools on top. One copy of the data serves all.

Three layers of tables. Bronze is raw, silver is clean, gold is ready to use.

A table that reads files kept in some other place. The data is not copied in.

A lake table with too many tiny files. Each read is slow.

Packing lots of small files into a few big ones. Reads get fast.

Table Formats

7

Logs kept beside lake files that turn a folder into a table you can update and roll back, and the catalogs that tell every engine where each table is.

A log that turns a folder of files into a table. Updates become safe.

A table format that many tools share. It hides how the files are split.

A table format first made by Databricks. It is Parquet files plus a log of changes.

A table format built for fast updates. Jobs can read just what changed.

Reading a table as it was at an earlier time. Mistakes can be undone.

A list that tells tools where each lake table is right now.

The catalog that came with Apache Hive. Many tools still use it.

Storage Formats

6

How data is laid out in a file, by row or by column, and the open formats that do it.

Each row is kept together on disk. It is quick for one record at a time.

A table kept one column at a time. A report reads just the columns it needs.

A file type that keeps data by column. Most data lakes use it.

An open, column-based file format from the Hive world. It is close to Parquet.

A shared way to hold tables in memory. Tools can swap them fast.

A small file type that keeps whole rows. The plan for the data rides along.

Dimensional Modeling

11

Laying out warehouse tables as facts to count and the details that describe them.

Warehouse tables split in two kinds. Facts count things. Dimensions say who, what and where.

What one row of a table stands for, such as one sale or one day.

Table Types

6

The kinds of table in a dimensional model, and the keys and history rules they carry.

The table of events you count, such as sales. It holds numbers and keys.

A table that names the things in the facts, such as shops and dates.

A dimension whose details change over time. Type 2 keeps a row per version.

One shared table, used by many fact tables, so their sums match.

A made-up ID the warehouse gives each row. It means nothing on its own.

An ID that comes from the real world or the source, like an email address.

Schema Shapes

3

How fact and dimension tables are joined together, drawn as a shape.

One fact table in the middle. Each dimension table joins straight to it.

Like a star, but the side tables are split up. Each part is a small table.

One wide table with all the details on each row. Queries need no joins.

Table Partitioning

3

Splitting and sorting big tables so a query reads only the parts it needs.

A big table cut into parts by one column. Most often that column is a date.

Skipping the table parts a query cannot need. Less is read, so it is faster.

Rows are kept in order by one column. Then a query can skip blocks.