Sample Queries Beta

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

Querying for deleted records in Snowflake

If you want to query for records that have been deleted in Snowflake:

/* Example Query */
SELECT distinct ID from (
 select *
 , max(_fivetran_start) over (partition by id) as max_start
 FROM SALESFORCE."ACCOUNT"
 )
where _fivetran_start = max_start
and not _fivetran_active;

/* Alternative Example Query */
SELECT DISTINCT ID, 
FROM SALESFORCE."ACCOUNT"
WHERE NOT EXISTS (
  SELECT 1
  FROM SALESFORCE."ACCOUNT" t2
  WHERE t2.ID = SALESFORCE."ACCOUNT".ID
    AND t2._fivetran_active = TRUE
);

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;

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;

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('2024-08-01')) as timestamp
  from table(generator(rowcount => 2))
)
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;

BigQuery

with timestamps as (
  select *
  from unnest(generate_timestamp_array('2024-08-01', '2024-08-15', 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;

Redshift

with timestamps as (
  select cast('2024-08-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;

Result

The partial result for the queries above looks as follows:

DATE	ID	TYPE
2024-08-01	accountID-1	Customer - Direct
2024-08-01	accountID-2	Customer - Channel
2024-08-02	accountID-1	Customer - Direct
2024-08-02	accountID-2	Customer - Channel