Skip to main content

Configuring Interval Validation

Interval Validation is a method for comparing data records that were modified within a specified time window. You can define the time window for comparison in two ways:

  • Absolute Interval: A window defined by specific start and end timestamps (e.g., "from 2025/11/29 1:00 PM to 2025/11/30 5:00 PM").

  • Relative Interval: A window defined using a duration relative to the run’s start time (e.g., "from 8 hours to 4 hours" prior to the run start time, or “between 1 hour ago to now” where now refers to the run start time).

You can apply the same time window to both Source and Target datasets or specify different windows for each dataset to account for replication lag or other timing offsets.

To use Interval Validation, both Source and Target tables must contain a timestamp or datetime column. Go to Supported data systems to see which timestamp and datetime data types are supported for your chosen Source and Target systems.

You can use Interval Validation with both validation scopes—Validation Set (see screenshot below) and Quick Start (Validation Pair). The interval settings apply to all Validation Pairs in the configuration.

Interval Validation is useful in use cases such as the following:

  • Targeted interval-based checks: Validate only the records updated within a specific time window—useful for isolating discrepancies after a batch load, deployment, ETL refresh, point-in-time fix, or other discrete data event.

  • Post-Incident Verification: After resolving a data issue or pipeline outage, validate only the records updated during the impacted period to verify that repairs took effect and no residual inconsistencies remain.

  • Aligning with replication cadence: Interval Validation works well when your Validation schedule mirrors your replication cycle. For example, if replication runs every two hours, you can validate only the records updated during that same two-hour window, keeping each run focused and efficient.

Setting up Interval Validation

  1. On the Create Validation screen, select Quick Start (Validation Pair) or Validation Set as your scope.

  2. On the Select The Validation Type screen, choose Interval Validation.

    The screenshot below shows the Validation Types when you choose the Validation Set scope.

    Validata_ValidationConfig_Interval_1Select.png

  3. Set up the Connection Profiles to connect Validata to your Source and Target systems.

  4. On the Configure Validation screen, you can configure Interval Validation as well as the Advanced Settings for the Validation configuration.

  5. Interval Validation: You must choose how to represent the time-ranges—using either an Absolute time interval or a Relative time interval.

    Validata_ValidationConfig_Interval_2Location.png

  6. Advanced settings: See Advanced Settings.

  7. After entering all required settings on the Configure Validation screen, click Verify and Save to proceed. Validata will validate your inputs and then create the new Validation

Using an Absolute time window

Setup parameters

  1. Same Start and End times for the Source and Target datasets: You can set a fixed Start Time and End Time on the UI, as shown below.

    When you apply the same Start and End Times to the Source and Target datasets, Validata selects only the records from each Source table and its corresponding Target table that were updated within that exact time range. This time window is applied consistently across all Validation Pairs in the configuration, ensuring that each pair is compared over the same fixed period of activity.

    Validata_ValidationConfig_Interval_3Absolute_1a.png

  2. Separate Start and End Times for the Source and Target datasets: You can also set separate Start and End Times for the Source and Target datasets when you need to account for replication latency or other timing offsets, as shown below.

    In this mode, Validata selects records from each Source table based on the Source time window, and records from each corresponding Target table based on the Target time window. These windows apply to all Validation Pairs in the configuration, allowing each comparison to reflect the timing realities of your data pipeline while still using consistent logic across pairs.

    For example, assume you are replicating data from a data system in the Pacific Time Zone to one in the Eastern Time Zone, which is three hours ahead. With a one-minute replication delay and each system logging update times in its local timezone, a record updated in the Source at 4:00 AM PT will appear in the Target at 7:01 AM ET. In scenarios like this, you can use Interval Validation and configure separate time windows for the Source and Target to account for the timezone and latency offset. The screenshot below illustrates this concept.

    Validata_ValidationConfig_Interval_3Absolute_2.png

Supported data types

The interval specified in the UI must exactly match the timestamp values displayed in the database table. This validation is timezone-agnostic—Validata filters records that fall within the specified interval based on the literal timestamp values, regardless of timezone considerations.

The table below lists the supported data types for absolute time-range key selection:

Data System

Supported data type for absolute time ranges

BigQuery

  • DATETIME

  • TIMESTAMP

Databricks

  • TIMESTAMP

MySQL

  • DATETIME

  • TIMESTAMP

Oracle

  • TIMESTAMP

PostgreSQL

  • timestamp

  • timestamp without time zone

Snowflake

  • DATETIME

  • TIMESTAMP

  • TIMESTAMP_NTZ (including TIMESTAMPNTZ, TIMESTAMP WITHOUT TIME ZONE)

SQL Server

  • datetime

  • datetime2

  • smalldatetime

Operational considerations

  • You can use an absolute time window for one-time Validation Runs. This approach is ideal for spot checks because you are able to specify the unique Start and End times for every individual run.

    However, absolute time windows aren't suitable for recurring runs because every subsequent execution would compare the same records that were updated in the exact same time interval. For recurring runs, it is best to use Interval Validation with relative time windows

Using a Relative time window

Specifying relative time windows

When you define a relative time window, Validata calculates the specific time boundaries for the comparison based on the exact moment the Validation Run is launched.

For example, if you specify a window “from 4 hours to 2 hours ago” in the Validation configuration and the run starts at 3:00 PM, Validata will compare records updated between 11:00 AM and 1:00 PM.

In the table below, we show how you can define a relative time window for Interval Validation.

Example

Time specification for Interval Validation

Comments

30 days ago

30d or 1mo

  • The earliest allowable start time is 30 days (or 1 month) before the current time of the Validation Run.

1 week 6 days ago

1w 6d

  • When specifying intervals in weeks, you can refine the range only down to days. Hours and minutes are not allowed.

  • For example, “1w 6d” is valid, but “1w 6d 2h” is not.

  • If you enter a week-based range using hours or minutes, Validata automatically converts it to the equivalent week-day format by rounding down to the nearest day.

2 days 10 hours ago

2d 10h

  • When specifying intervals in days, you can refine the range only down to hours. Minutes are not allowed.

  • For example, “2d 10h” is valid, but “2d 10h 30m” is not.

  • If you enter a day-based range using minutes, Validata automatically converts it to the equivalent day-hour format by rounding down to the nearest hour.

3 hours 24 minutes ago

3h 24m

  • When specifying intervals in hours, you can refine the range down to minutes.

  • The minimum supported unit is one minute.

  • For example, “3h 24m” or “3h 25m” are valid, but “3h 24m 30s” is not.

53 minutes ago

53m

  • When specifying intervals in hours, the minimum granularity is 1 minute.

  • For example, you can specify “2d 10h” but not “2d 10h 30m”.

The current time (or the time when the Validation Run started)

now

  • The closest allowable end time is now, which represents the exact time the Validation Run begins.

Setup parameters

  1. Time-range: You can define the time window using the time slider—which supports ranges from hours up to several days or weeks—or by entering the relative start and end times directly in the UI. If you manually specify the Interval Validation window, the relative end must be closer to the Validation Run’s start time than the relative start to ensure a valid time range.

  2. Same Start and End times for the Source and Target datasets: You can set the relative Start Time and End Time on the UI, as shown below.

    When you apply the same window to both datasets, Validata selects only the records from each Source table and its corresponding Target table that were updated within the computed time range. This window is applied consistently across all Validation Pairs, ensuring that every pair is compared over the same period of activity.

    For example, assume you want to schedule a recurring run every three hours to compare the data updated in the Source during the previous three-hour period and replicated to the Target. You can configure Interval Validation to use any relative range that spans three hours—such as “from 4 hours ago to 1 hour ago,” as shown in the screenshot below.

    Validata_ValidationConfig_Interval_4Relative_1a.png

  3. Separate Start and End Times for the Source and Target datasets: You can set different relative Start and End Times for the Source and Target datasets when you need to account for replication latency or other timing offsets.

    In this mode, Validata uses the Source time window to select records from each Source table and the Target time window to select records from each corresponding Target table. These windows apply to all Validation Pairs, allowing each comparison to reflect the timing realities of your data pipeline while maintaining consistent validation logic across pairs.

    For example, assume you want to schedule a recurring run every three hours, and updates to the Source reach the Target within five minutes. In this case, you can use Interval Validation with different relative windows for the Source and Target so they both represent the same logical three-hour period. For instance, you can set the Source time window to “from 4 hours ago to 1 hour ago” and the Target window to “from 3 hours 55 minutes ago to 55 minutes ago” to account for the five-minute replication delay. This setup is illustrated in the screenshot below.

    Validata_ValidationConfig_Interval_4Relative_1b.png

Supported data types

When using relative intervals, all calculations are performed in Universal Time Coordinated (UTC):

  • now is evaluated as the exact UTC timestamp at query execution time.

  • All interval arithmetic is calculated in UTC.

The interval specified in the UI must exactly match the timestamp values displayed in the database table. This validation is timezone-agnostic—Validata filters records that fall within the specified interval based on the literal timestamp values, regardless of timezone considerations.

The table below lists the supported data types for relative time-range key selection:

Data System

Supported data type for relative time ranges

BigQuery

  • TIMESTAMP

Databricks

  • TIMESTAMP

MySQL

  • TIMESTAMP

Oracle

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

PostgreSQL

  • timestamp with time zone

  • timestamptz

Snowflake

  • TIMESTAMP_LTZ (including TIMESTAMPLTZ, TIMESTAMP WITH LOCAL TIME ZONE)

  • TIMESTAMP_TZ (including TIMESTAMPTZ, TIMESTAMP WITH TIME ZONE)

SQL Server

  • datetime

  • datetime2

  • smalldatetime

Operational considerations

  • If you set different time windows for the Source and Target datasets, the datetime or timestamp values in corresponding records will naturally differ. In this case, when configuring the Validation Pairs, do not select those columns for comparison, as they will produce OUT-OF-SYNC outcomes even when the rest of the data is equivalent.