Skip to main content

Selecting the right timestamp column for your Data Pool

This guide explains how to select the right timestamp column for your Data Pool.

It covers how Propel uses the timestamp column to organize your data, the guidelines to select the appropriate timestamp for your Data Pool, and what to do if you need to change the primary timestamp.

  1. How Propel Uses the Primary Timestamp to Organize Your Data
  2. Guidelines to Select the Appropriate Timestamp
  3. Changing the Timestamp Column

Requirements​

How Propel Uses the Primary Timestamp to Organize Your Data​

Propel uses the Data Pool's primary timestamp to order and partition your data in Data Pools. This ordering and partitioning enables Propel to efficiently query the data for different time ranges and reduces the amount of data that needs to be processed to serve a query. Queries that filter on the primary timestamp will always be optimized, but the queries that don’t, won’t. The Data Pool's timestamp will be the time dimension for any Metric powered by the Data Pool, and so Metric queries will always be optimized.

Guidelines to Select the Appropriate Timestamp​

As a general principle, select the date/time column that you need to report on in your application as the primary timestamp for your Data Pool. For event data, the primary timestamp is typically when the event occurred. For example, for "page view" events, choose the column that captures when the page view event occurred, as you are likely to show page views over time. For dimension tables, you can choose when the object was created. For example, for a “customers” Data Pool, you’ll want to choose when the customer was created if you need to report on the number of customers over time.

Changing the Timestamp Column​

Once a Data Pool is created, you cannot change its primary timestamp. If you need to query data based on a different timestamp, or if you need to change the timestamp of an existing Data Pool, you must create a new Data Pool with the new primary timestamp and sync the data again.