# Querying Append-Only Tables

Information note

**This guide is applicable to:**

* [Destinations configured to use Append-Only Loading](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior#reference--destinations-loading-behavior), or
* Tables configured to use Append-Only Loading, such as [Google Ads’ Report tables](https://help.qlik.com/en-US/stitch/integrations/saas/google-ads#data-loading-append-only)

When data is loaded using [Append-Only Loading](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior#reference--destinations-loading-behavior), existing records aren’t updated, but instead appended to tables as new rows. This means that as time goes on, tables will contain different versions of the same record, reflecting how the record has changed over time.

While data stored this way can provide insights and historical details, sometimes you may just want the latest version of a record. In this guide, we’ll cover:

* [Things to know before using this guide](https://help.qlik.com/en-US/stitch/replication/loading/querying-append-only-tables#before-using-guide)

* [Columns you can use to identify record versions](https://help.qlik.com/en-US/stitch/replication/loading/querying-append-only-tables#using-system-column)

* [A querying strategy that retrieves the latest version of every record](https://help.qlik.com/en-US/stitch/replication/loading/querying-append-only-tables#latest-version-every-row)

* [How to simplify querying by creating a view in your destination](https://help.qlik.com/en-US/stitch/replication/loading/querying-append-only-tables#create-destination-views)

## Before using this guide

Before using this guide, note that:

* You may need to modify the queries in this guide to use them yourself

* Stitch Support’s expertise lies in replicating data, and as such does not provide data analysis or querying assistance. We can, however, help with data discrepancies.

  If you’d like assistance with analysis or business intelligence solutions, we recommend reaching out to one of our [analytics partners](https://www.stitchdata.com/partners/).

## Using system columns to identify record versions

Every table created by Stitch contains columns prepended with `_sdc`. These are system columns created and used by Stitch to load data into your destination.

For this guide, we’ll focus on just two columns:

| **Column name** | **Description** |
| --- | --- |
| **\_sdc\_sequence** | A Unix epoch (in nanoseconds) that indicates the order in which data points were considered for loading.<br><br>Stitch uses this column’s values in a few places to correctly order rows for loading, but it can be also used to retrieve the latest version of a record from an Append-Only table.<br><br><br><br><br>This is the primary column our strategy will use. |
| **\_sdc\_batched\_at** | Timestamp indicating when Stitch loaded the batch the record was a part of into the destination.<br><br><br><br><br>Our strategy will use this column as a "tie breaker." |

## Retrieving the latest version of every record

Information note **Note**: The queries in this section are only intended to demonstrate one approach to querying. You may need to modify the queries to use them yourself.

Let’s take a look at an example. Assume we have an `orders` table that contains:

* A Primary Key of `id`,
* The system `_sdc` columns added by Stitch, and
* Other order attribute columns

### Only using \_sdc\_sequence

If you wanted to create a snapshot of the latest version of this table, you could run a query like this using `_sdc_sequence`:

```
Example only using _sdc_sequence
```

```
SELECT DISTINCT orders.*
           FROM [stitch-analytics-bigquery-123:ecommerce.orders] orders
     INNER JOIN (
                  SELECT id,
                         MAX(_sdc_sequence) AS sequence
                    FROM [stitch-analytics-bigquery-123:ecommerce.orders]
                  GROUP BY id
                ) latest_orders
             ON orders.id = latest_orders.id
            AND orders._sdc_sequence = latest_orders.sequence
```

Here’s what’s happening in this query:

1. The subquery retrieves a list of every record’s Primary Key and maximum `_sdc_sequence` value.
2. The outer query selects distinct versions of the latest version of every record.
3. Lastly, the outer query joins the table to the list retrieved by the subquery, which makes all other columns available for querying.

### Using \_sdc\_batched\_at as a tie breaker

If only using `_sdc_sequence` doesn’t yield the desired results, we recommend using `_sdc_batched_at` as a “tie breaker”:

```
Example using _sdc_sequence and _sdc_batched_at
```

```
SELECT DISTINCT orders.*
           FROM [stitch-analytics-bigquery-123:ecommerce.orders] orders
     INNER JOIN (
                  SELECT id,
                         MAX(_sdc_sequence) AS sequence,
                         MAX(_sdc_batched_at) as batched_at
                    FROM [stitch-analytics-bigquery-123:ecommerce.orders]
                  GROUP BY id
                ) latest_orders
             ON orders.id = latest_orders.id
            AND orders._sdc_sequence = latest_orders.sequence
            AND orders._sdc_batched_at = latest_orders.batched_at
```

The `_sdc_batched_at` value indicates the time that Stitch loaded the batch containing the record into the destination. Selecting a record’s maximum `_sdc_batched_at` and `_sdc_sequence` values excludes versions of the record from older batches from the results.

## Create views in your destination

To make this easier, you can turn queries like the one above into a view. We recommend this approach because a view will encapsulate all the logic and simplify the process of querying against the latest version of your data.

Refer to the documentation for your destination for more info on creating views:

* [Amazon Redshift](http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_VIEW.html)
* [Google BigQuery](https://cloud.google.com/bigquery/querying-data#views)
* [Microsoft Synapse SQL Analytics](https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-views)
* [PostgreSQL](https://www.postgresql.org/docs/9.4/static/sql-createview.html)
* [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/create-view.html)