DynamoDB to BigQuery: How to Sync Your Data

Syncing DynamoDB to BigQuery turns a schemaless key-value store into typed warehouse tables you can query with SQL and join against the rest of your data. Airbyte does this without code, but there is a problem it has to solve first, and understanding how it solves it is the difference between a pipeline you trust and one that quietly drops fields: DynamoDB tables have no schema, and BigQuery tables must have one.

The connector bridges that gap by inferring a schema from a sample of your data. That inference is well documented, and its assumptions are where this pipeline succeeds or fails.

How does Airbyte infer a schema from a schemaless table?

The DynamoDB source runs discovery in three steps. It scans the table with a scan limit of 1,000, repeating the scan if the returned data set exceeds 1 MB, until it has at least 1,000 items. It then combines all the distinct top-level attributes found across those items. Finally it determines each attribute's type from the value it saw and maps it to an Airbyte type.

The documentation is explicit about the assumption underneath: the implementation assumes that the same attribute present in different items has the same type and the same nested structure. In a table where every item is the same shape, that holds and the inferred schema is correct.

In a single-table design it does not. If you have followed the standard DynamoDB pattern of storing several entity types in one table, discriminated by a sort key prefix, then attributes genuinely differ in type and presence between items. Two things then go wrong. Attributes that appear only in item types absent from the first 1,000 scanned records will not appear in the schema at all, so those fields never reach BigQuery. And where the same attribute name holds different types across entity types, the inferred type reflects whichever the connector happened to see.

Neither failure announces itself. The sync succeeds and the data is simply incomplete, which makes this worth checking deliberately: compare the discovered schema against a list of attributes you know exist before you trust the first load. If you are running a single-table design, expect to model entity types separately downstream rather than treating the landed table as one clean entity.

What are the rules for incremental sync?

The connector supports full refresh and incremental append. It does not replicate deletes, so an item removed from DynamoDB stays in BigQuery until a full refresh replaces the table.

The cursor rules are unusually strict and worth reading twice. The cursor cannot be nested; it must be a top-level attribute on the item. It must never be blank. And it must be a string or an integer, because the connector infers the cursor type from the schema it generated, treating a string cursor as ISO 8601 and an integer cursor as epoch time.

The documentation warns that with any other arrangement, incremental results may be unpredictable and will depend entirely on DynamoDB's own comparison algorithm. That is a strong caution, and the practical reading is that your cursor attribute needs to be present and populated on every single item. A cursor that is sometimes missing is not a partial problem here; it makes incremental behaviour unreliable.

If your items do not carry such an attribute, adding one at write time in the application is a smaller job than working around its absence.

Credentials, reserved words, and permissions

Configuration takes an AWS endpoint and a region. Credentials are optional in a specific and useful way: supplying an access key ID and secret access key gives you user-based access, while omitting both makes the connector fall back to the default credentials provider, using whatever role is executing the container workload in AWS. On self-managed Airbyte running inside AWS, role-based access avoids storing long-lived keys entirely.

Two other settings solve problems you will otherwise spend time diagnosing. Reserved attribute names takes a comma-separated list of attributes whose names collide with DynamoDB reserved words or contain special characters, which is common with names like status, size, or timestamp. And an option to ignore tables with missing read permissions lets a sync proceed across the tables it can read rather than failing wholesale.

Because extraction is scan-based, it consumes read capacity on the table. On a provisioned-capacity table serving production traffic, schedule syncs with that in mind, and if you are on Airbyte Cloud with IP restrictions, add Airbyte's IP addresses to your allow list.

Landing the data in BigQuery

The BigQuery destination uses direct load, writing straight to final tables. Batched Standard Inserts is the default and suits most DynamoDB volumes; GCS staging adds a bucket, an HMAC key, and a Storage Object Admin grant, and does not support buckets using customer-managed encryption keys.

Your service account needs BigQuery User and BigQuery Data Editor. Set the dataset location to match the datasets you will join against, since BigQuery cannot join across physical locations and the location is fixed at creation. Since the source does not support namespaces, every table lands in the single destination schema you configure, so a naming convention is worth agreeing before the first sync if you are syncing many tables.

Nested attributes arrive as structured JSON rather than flat columns. DynamoDB items are often deeply nested, so plan a downstream model that extracts the fields your analysts actually query.

Frequently asked questions

Why are some of my attributes missing in BigQuery?

Schema discovery samples roughly 1,000 items. Attributes that appear only in items outside that sample are not in the inferred schema. This is most common in single-table designs where several entity types share a table.

Can I use a nested attribute as the cursor?

No. The cursor must be a top-level attribute, must never be blank, and must be a string in ISO 8601 format or an integer epoch value.

Do deleted items disappear from BigQuery?

No. The connector does not replicate incremental deletes, so a periodic full refresh is the only way to bring the destination back in line with the table.

My sync fails on an attribute called status. Why?

It is a DynamoDB reserved word. Add it to the reserved attribute names setting, which takes a comma-separated list, so the connector escapes it in expressions.

Get your DynamoDB data into BigQuery

Check the inferred schema against the attributes you know exist, make sure your cursor is top-level and always populated, and use role-based access where you can. For another document store into the same warehouse, see our guide to MongoDB to BigQuery. For a relational source, see PostgreSQL to BigQuery.

Start syncing now →