Sample Queries Beta

Use our sample queries to get the most out of your historical data.

Limit data to currently active records

If you want to limit your data to currently active records, specify a TRUE value for the _fivetran_active boolean:

/* Show active rows for any data */
select *
from salesforce.opportunity
where _fivetran_active;
content_copy

Get records from a point in time

If you want to get records from a particular point in time, specify the timestamp:

select * 
from salesforce.opportunity
where [timestamp] between _fivetran_start and _fivetran_end;
content_copy

Build a timeline of changing table values

Use these sample queries to add historically accurate dimensions or filters to your time-based reporting by joining to existing data at the same level of time aggregation. We have written sample queries for Snowflake, BigQuery, and RedShift.

History tables enable analysis of data over time. For example, say that you wish to show how groups of customers are changing by day. This is very easy to do with a Fivetran history table. Generate a range of dates you are interested in, and then map the versions of accounts to them using the intervals between _fivetran_start and _fivetran_end.

Snowflake

with timestamps as (
select
  dateadd(day, row_number() over (order by seq4())-1, to_timestamp('2019-01-01')) as timestamp
  from table(generator(rowcount => 365))
)
select
  to_date(timestamp) as date,
  id,
  type
from salesforce.account, timestamps -- Insert rolling timestamps via cross join to bridge start and end timestamps
where timestamp between _fivetran_start and _fivetran_end
order by 1, 2;
content_copy

BigQuery

with timestamps as (
  select *
  from unnest(generate_timestamp_array('2019-01-01', '2019-12-31', interval 1 day)) as timestamp
)

select
date(timestamp) as date,
id,
type
from salesforce.account, timestamps -- Insert rolling timestamps via cross join to bridge start and end timestamps
where timestamp between _fivetran_start and _fivetran_end
order by 1, 2;
content_copy

Redshift

with timestamps as (
  select cast('2020-01-01' as timestamp) - (i * interval '1 day') as timestamp
  from generate_series(1,365) as i
)
select
  trunc(timestamp) as date,
  id,
  type
from salesforce.account, timestamps -- Insert rolling timestamps via cross join to bridge start and end timestamps
where timestamp between _fivetran_start and _fivetran_end
order by 1, 2;
content_copy