The Airbyte Column Nobody Queries: Find Every Silent Sync Failure in 15 Minutes

You added a new field to your CRM. Three weeks later, someone in finance asks why 4% of deal values in the warehouse are NULL when the CRM UI clearly shows a number. You check the connector — green checkmark, every sync. You check the destination table — data's there, mostly. What you don't check, because almost nobody does, is the one column Airbyte already used to tell you exactly what happened: _airbyte_meta.

If you're running Airbyte with Destinations V2 (the default since late 2023 for Snowflake, BigQuery, Postgres, and most warehouse destinations), every raw table Airbyte writes gets four metadata columns alongside your actual data: _airbyte_raw_id, _airbyte_extracted_at, _airbyte_generation_id, and _airbyte_meta. That last one is a JSON blob, and it is not decoration. It contains a changes array that records, per record, per field, exactly when Airbyte had to alter or drop a value to get it into your destination — and why.

This matters because Airbyte's typing and deduping step is lenient by design. If a source sends a string where your destination schema expects an integer, or a value too large for the target column, or malformed JSON in a JSON column, Airbyte doesn't fail the sync. It nulls the field, writes the row anyway, and logs a change record in _airbyte_meta. That's the right behavior for pipeline reliability — a single bad field shouldn't take down an entire sync — but it means data quality problems ship into your warehouse silently unless you go looking for them.

What's actually in _airbyte_meta

The shape is consistent across destinations. Each row's _airbyte_meta looks roughly like this:

{
  "sync_id": 1234,
  "changes": [
    {
      "field": "deal_value",
      "change": "NULLED",
      "reason": "DESTINATION_TYPECAST_ERROR"
    }
  ]
}

The changes array is empty for the overwhelming majority of rows — which is exactly why it's easy to ignore. It only fills in for the rows worth caring about. The reason codes you'll see most often are DESTINATION_TYPECAST_ERROR (value didn't match the destination column type), DESTINATION_SERIALIZATION_ERROR (value couldn't be serialized, often oversized numbers or malformed nested objects), DESTINATION_RECORD_SIZE_LIMITATION (the row exceeded a destination size cap), and SOURCE_RECORD_SIZE_LIMITATION. Each is a distinct, actionable failure mode — not noise.

The 15-minute query

Pick any raw stream table you actually rely on downstream and run this. Snowflake:

select
  _airbyte_extracted_at::date as sync_date,
  f.value:field::string   as field_name,
  f.value:change::string  as change_type,
  f.value:reason::string  as reason,
  count(*) as affected_rows
from raw_schema.your_stream,
     lateral flatten(input => _airbyte_meta:changes) f
group by 1, 2, 3, 4
order by affected_rows desc;

Postgres (destinations that write meta as jsonb):

select
  _airbyte_extracted_at::date as sync_date,
  c->>'field'  as field_name,
  c->>'change' as change_type,
  c->>'reason' as reason,
  count(*) as affected_rows
from raw_schema.your_stream,
     jsonb_array_elements(_airbyte_meta->'changes') c
group by 1, 2, 3, 4
order by affected_rows desc;

BigQuery needs one extra unnest step since _airbyte_meta is a JSON type there:

select
  date(_airbyte_extracted_at) as sync_date,
  json_value(c, '$.field')  as field_name,
  json_value(c, '$.change') as change_type,
  json_value(c, '$.reason') as reason,
  count(*) as affected_rows
from `raw_dataset.your_stream`,
     unnest(json_query_array(_airbyte_meta, '$.changes')) as c
group by 1, 2, 3, 4
order by affected_rows desc;

Run that against your two or three most business-critical streams and you'll usually find at least one field with a nonzero count within the first minute. On a mid-sized CRM or billing sync, seeing 1-5% of rows carrying a DESTINATION_TYPECAST_ERROR on a specific field is common — and it's almost always traceable to a source schema change (a field that used to be numeric now sometimes carries a currency symbol, a boolean field that started returning "yes"/"no" instead of true/false) that nobody flagged because the sync itself never went red.

Turning it into a standing check, not a one-off

The query above is the audit. The fix that actually pays off is making it recurring, because schema drift at the source is exactly the kind of thing that reappears. If you're on dbt, this is a natural custom singular test:

-- tests/assert_no_new_airbyte_typecast_errors.sql
select *
from {{ source('raw', 'your_stream') }},
     lateral flatten(input => _airbyte_meta:changes) f
where _airbyte_extracted_at >= dateadd(day, -1, current_date())
  and f.value:reason::string = 'DESTINATION_TYPECAST_ERROR'
having count(*) > 0

A failing test here fails your dbt run loudly, in CI, the same day the source starts sending a value your schema can't hold — instead of three weeks later when someone in finance notices a number that doesn't add up. If you don't run dbt, the same query wrapped in a five-minute cron job with a Slack webhook on nonzero rows gets you the same outcome.

The underlying lesson generalizes past Airbyte: any tool that trades hard failures for graceful degradation is quietly generating a log of what it degraded. That log is only useful if something reads it. Fifteen minutes writing one query against a column you're already paying to store is a lot cheaper than the meeting where you explain why a warehouse number has been wrong for a month.