Ingestion-Time partitioned tables: Tables partitioned based on the data's ingestion (load) date or arrival date.
Field-based partitioned table: Tables that are partitioned based on the timestamp/Date column.
When you create a table partitioned by ingestion time, it automatically loads data into date-based partitions that denotes the date when the data arrived.
The tables have a pseudo column named _PARTITIONTIME that contains a date-based timestamp for data that is loaded into the table. By using _PARTITIONTIME in your query, you can restrict the number of partitions scanned. However, some uses of pseudo-columns do not limit the number of partitions scanned, you can find more details on that here.
These tables allow you to provide any TIMESTAMP or DATE columns of your choice. The data is automatically sent to the appropriate partition based on the date value(in UTC). We don't need a _PARTITIONTIME pseudo column instead the partitioning column can be used to restrict the amount of data scanned.
When you create field-based partitioned tables, two special partitions are created:
- The __NULL__ partition representing rows with NULL values in the partitioning column
- The __UNPARTITIONED__ partition representing data that exists outside the allowed range of dates
For information on partitioned tables and its quotas/limit please go through their official documentation.