Sample Queries Betalink
Use our sample queries to get the most out of your historical data.
Limit data to currently active recordslink
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;
Get records from a point in timelink
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;
Build a timeline of changing table valueslink
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
.
Snowflakelink
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 through cross join to bridge start and end timestamps
where timestamp between _fivetran_start and _fivetran_end
order by 1, 2;
BigQuerylink
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 through cross join to bridge start and end timestamps
where timestamp between _fivetran_start and _fivetran_end
order by 1, 2;
Redshiftlink
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 through cross join to bridge start and end timestamps
where timestamp between _fivetran_start and _fivetran_end
order by 1, 2;