Query past instances of data

NOTE: This functionality is only available for Enterprise customers.

While most of the time you probably want to query the current version or state of your datasource, there are times when it would be handy to be able to go back in time and query the datasource as it was in a previous state–say two versions ago, or five days ago, or on August 22, 2020. If your table does not accumulate data, but rather only holds its current state, you would not be able to query back in time. For example, if you are looking at a company’s current employee table which has no historical information–just the state since the last update–you would have no way of knowing how many employees the company had at this time last year.

With data.world’s back-in-time query functionality you can search both ingested-data sources and Snowflake live connections for previous states of the data. Note that this functionality is NOT for queries against live data connections for sources other than Snowflake.

Here are some examples of the syntax:

SELECT COUNT(*)
FROM supported_functions AT (timestamp => "20210722")

returns the number of records on 7/22/2021 at midnight GMT (format of the timestamp is “YYYYMMDD”). If you wanted to be more specific for the time you could use “YYYYMMDDTHHMMSSZ” T and Z are both necessary for that format. For example:

SELECT COUNT(*)
FROM supported_functions AT (timestamp => "20210722T143000Z")

would return the count at 2:30 PM GMT on July 22, 2021.

SELECT COUNT(*)
FROM supported_functions AT (OFFSET => -3600)

returns the number of records an hour ago (3600 seconds). Note this query could also be written as:

SELECT COUNT(*)
FROM supported_functions AT (OFFSET => -5*24*60*60)

If you wanted to ignore the date and focus on a previous version, the command would be something like

SELECT COUNT(*)
FROM supported_functions AT (VERSION => "tip-1")

where “tip-1” is the previous version–”prev” can also be used for previous version. “tip” is a synonym for the current version. To get the results from three versions back it would be “tip-3”.