Partitioning splits a table into parts by the value of a column, most often a date, so queries and writes can touch only the parts they need. It makes backfills and late-data fixes cheaper, because a single day can be replaced without rewriting the table. Too many small partitions slow things down, so the column must suit the data.
For example, a page_views table partitioned by view_date stores each day's rows in its own partition. A query for last week reads seven partitions and skips the other three years. A late fix to March 3 deletes and rewrites only that partition. Without partitioning, both operations would scan or rewrite the whole table, which for a large table can take hours and a lot of compute.
The usual guidance is to partition only large tables, by a column with a moderate number of values that both queries and loads filter on, and to aim for partitions of about a gigabyte or more. Partitioning a small table, or partitioning by a column such as a customer id with millions of values, creates many tiny files, and the time spent listing and opening them outweighs any skipping. On Databricks, liquid clustering is now often recommended instead of partitioning for new tables, and Snowflake manages micro-partitions automatically, so explicit partitioning matters most for large date-based tables and for file-based formats.
Partitioning also defines a unit of work: backfills, retention rules that drop old data and fixes for late-arriving records all operate one partition at a time. See also Apache Iceberg, whose hidden partitioning lets queries filter on the original column.
A partition column should be chosen once and rarely changed, because changing it means rewriting the whole table in most formats. When in doubt for a new table, start without partitions and add clustering, then partition only if the table grows large and every query and load filters by date. See also incremental load.
Emolysis's Backfill and Late-arriving shapes replace exactly the partitions in their range. Compare clustering. Partition by a column that both queries and loads filter on; a date is usually that column.