How Airbyte Builds Resilient Syncs

Learn how Airbyte builds resilient syncs to database and data warehouse destinations

ENGINEERING

December 23, 2023

3 min read

Edward Gao

Summarize with AI:

In spite of the ELT paradigm, every EL(t) provider has to make some decisions about what your data looks like when it arrives in your warehouse. With Destinations V2, Airbyte is moving to a world where your data looks the way you expect it - and (spoiler alert) it's not normalized. Destinations V2 keeps the best of what we had with V1 - and improves on it in a few important ways.
‍
Airbyte has always chosen to give you control of your data processing from within your own warehouse, rather than within our systems. This has a number of advantages. For one, it's faster than doing that massive computations within Airbyte's systems. Typing and deduping in your warehouse is also more private and secure by design. Airbyte never persists your data within our infrastructure, and we only hold onto your data, exclusively in-memory, for the shortest time possible.
‍

V2 keeps these benefits, while further improving the usability of the tables Airbyte creates for you. Most importantly, we've significantly simplified the schema we generate within your destination warehouse. V2 still provides the same common-sense transformations as V1 (such as producing columns with reasonable data types, and adding metadata about when your data was synced), but no longer generates a large number of join tables. Your analytics warehouse performs best with wide, denormalized tables - why would you want your ELT provider to generate anything else?

For example, consider our Shopify source. There's an orders stream, with a discount_codes field. Rather than generating a separate discount_codes table in your warehouse, which you would need to join back to its parent orders table to do anything interesting, we simply leave the discount_codes as a column in the orders table. In a traditional, fully-normalized schema, if you wanted to find all orders using a specific discount code, you'd need to do an expensive join:
‍


SELECT * FROM orders
INNER JOIN order_discount_code
ON order_discount_code.order_id = orders.id
WHERE order_discount_code.code = 'your_discount_code';

‍
But in our V2 schema, discount_codes is just another column on your table, so you can avoid the join completely: (BigQuery SQL dialect for illustrative purposes; all databases now have robust JSON query support)
‍


SELECT * FROM orders
WHERE EXISTS (  SELECT 1 FROM UNNEST (JSON_EXTRACT_ARRAY(discount_codes)) c  
WHERE JSON_VALUE(c.code) = 'your_discount_code');

‍

Removal of SCD Tables

We've also removed the SCD tables that legacy normalization produced. The overwhelming majority of users didn't find these tables useful, and many other users were misled about what these tables represented. This reduces the clutter in your warehouse (and of course, saves you some Snowflake or BigQuery credits as well!).

Advantages of Raw Tables

One thing that isn't changing with V2 is the existence of "raw tables", which contain data as it was emitted from the source. For example, if you're syncing from the Shopify source, this table contains the exact response payload that we received from Shopify's API. This is a powerful paradigm! One feature that it immediately enables for all V2 destinations is improved schema evolution support. If your source data's schema changes - for example, a field's type is modified - we will simply rebuild your destination tables using the preexisting raw data, without needing to resync any records. If the previous data type was compatible with the new type, this is essentially invisible; if the previous data type cannot be safely cast to the new type, we will populate the new airbyte_meta column with an error. And this all happens without needing to resync any data. Schema changes like this happen all the time.

Destinations V2 Availability

Destinations V2 is available today for BigQuery and Snowflake, with even more destinations coming soon. We're excited for you to try it out. And if you were previously rolling your own transformations, we'd love for you to give V2 a shot!

About the Author

Edward Gao

Edward Gao is a Software Engineer on the Destinations team at Airbyte. He specializes in backend technologies, databases and data warehouses.