Tutorial index: Big Data for Beginners: tutorial series
Last post covered fact tables, dimension tables, the star schema and the snowflake schema. Beyond those there’s more industry jargon to learn. This post explains the big data slang: full tables, incremental tables, zipper tables, transaction tables, and snapshot tables. Some of it may not click yet — it will once you work with Hive — so take this as foundational knowledge.
Full Table
Easy to grasp from the name: it holds all the data, old and new alike. No partitioning — everything lives in one partition and records the total.
Incremental Table
Also easy: whatever is new since the last export is the increment. Only changed data gets reported; unchanged data is skipped. One partition per day, recording the amount added.
Snapshot Table
The name suggests taking a picture of the data, and that’s essentially it: capture all the data within a time window — a click of the shutter. Everything inside that window gets saved. Each report is the complete data for a period, with one partition per day.
Zipper Table
The tables above are common, but the zipper table may be new to some — let me spend more time on it.
A zipper table is typically the result of processing and preserving historical changes to information: it exists to retain past states, preserving every state while saving space.
Consider a large dataset where some fields change, but rarely — maybe once every few months or years — and the business needs to analyze those state changes. Taking a daily full snapshot isn’t realistic at that volume; you’d burn through disk capacity. That’s what zipper tables are for.
Shopping order state changes are a classic example: the state may be placed, paid, shipped, completed, and possibly branching into returned. The requirement is to see an order’s state at any historical point. Let’s walk through the normal placed → paid → shipped → completed flow to illustrate a zipper table:
start_date: when this record’s lifecycle beginsend_date: when this record’s lifecycle endsend_date = '9999-12-31 23:59:59'means the record is currently valid — also called the closing date
Here’s a zipper table holding basic order information plus each record’s lifecycle. With a table like this you can read both the latest current data and data as of any past point in time.
| order no. | user | state | start_date | end_date |
|---|---|---|---|---|
| 12345678 | RenFei | completed | 2021-01-07 18:24:55 | 9999-12-31 23:59:59 |
| 25478512 | Zhang San | paid | 2021-01-03 15:45:36 | 9999-12-31 23:59:59 |
| 12345678 | RenFei | shipped | 2021-01-04 10:32:15 | 2021-01-07 18:24:54 |
| 12345678 | RenFei | paid | 2021-01-03 15:45:24 | 2021-01-04 10:32:14 |
| 25478512 | Zhang San | placed | 2021-01-03 15:40:21 | 2021-01-03 15:45:36 |
| 12345678 | RenFei | placed | 2021-01-03 15:40:20 | 2021-01-03 15:45:23 |
To query order ‘12345678’ as of now: select * from order where end_date = '9999-12-31 23:59:59' and order_no = '12345678' — that returns the “completed” state.
To query order ‘12345678’ as of 2021-01-04 08:32:14: select * from order where start_date <= '2021-01-04 08:32:14' and end_date >= '2021-01-04 08:32:14' and order_no = '12345678' — that returns the “paid” state.
Transaction Table
As its name suggests, a transaction log: every data change produces a record, reflecting the actual change history of the data. It’s somewhat similar to a zipper table, but a zipper table stores data at the zipper granularity — only changes along specific dimensions — while a transaction table stores every single modification record.