So I looked at the data
One bad number, a silent failure, and 800K missing rows
There’s a running joke in data engineering that data engineers never look at the data. Whether they are trusting in upstream systems, assuming that if it compiles and executes, it’s probably good, or just lacking the domain expertise to know what to look for. A couple of weeks ago, I was ready to press publish on my Substack note on dispersion. I did one more once-over of some of the numbers and noticed something that seemed strange.
I was working on the analysis and then noticed that Netflix had a return of -89% for 2025, which couldn’t be; I would’ve heard about that. First, I went through the visuals created by Claude and my Motherduck MCP with mviz, and most of them looked plausible. But with data quality issues, once you see one thing wrong, you don’t trust anything, and I started to spiral.
The existing check I had was only for date coverage, so it required at least one value per week for each ticker in the last year. Pretty basic, and I assumed that since the data was from a service, it was correct (first mistake). It worked fine for the ETFs and Commodities, and for asset allocation forecasts and predictions. But now my use case has hit an order of magnitude more volume and series. More cracks were going to show because my complexity was outpacing my infrastructure, and I was basically building a system of record rather than a downstream data product.
So I went to the materialization in Dagster for that partition, and the run went fine with no issues or missing data. Next, I looked at the data again at a granular level, figuring it was likely a misplaced decimal point during the database load API call. There was either an issue with my data provider or a skill issue on my part. A
classic silent failure.
I did some research into what is causing it by looking at the API docs for the data structure and found that there is an exchange column. As we all remember, larger companies will list on multiple exchanges. For this, we were pulling data for Mexican exchanges, and the price was in that currency, so the differential makes sense. I also went through all of our ingestion tables. I found the number of likely affected rows (the Motherduck MCP with Claude code is really ergonomic for this type of stuff, albeit not very token-efficient).
The Fix
I looked up the price history and news for Netflix, and it turns out that they had a 5:1 split in November of 2025. Which, when you adjust for that, yields the true return. This necessitated a dbt model with split-adjusted values to calculate clean returns. That model included an asset consisting solely of the dates of stock splits.
Additionally, this would be a good opportunity to apply some statistical data quality checks. Here’s the full list:
dbt Schema Tests (staging/schema.yml)
On stg_sp500_companies_prices:
ohlc_consistency (custom macro) -- Validates open/high/low/close price logic (high >= low, etc.), severity: warn
usd_currency_only (custom macro) -- Ensures all prices are in USD, severity: warn
not_null on date and symbol columns
dbt Data Quality Models (data_quality/)
These SQL models run statistical anomaly detection across all OHLC tables, including S&P 500 prices:
Model: What it checks
dq_stale_prices Consecutive days with identical OHLC values (stale/duplicate data)
dq_return_spikes Single-day price moves exceeding 15% (likely data errors for large caps)
dq_zscore_anomalies Prices >2 standard deviations from 21-day rolling mean
data_quality_anomalies Union of all the above into a single triage table
–-dq_return_spike.sql
{{ config(
tags=[’data_quality’]
) }}
-- Check 2: Day-over-Day Return Spike Detection
-- Flags single-day moves > 15% which are likely data errors for large-cap stocks/ETFs.
{% set tables = ohlc_source_tables() %}
{% for table_name in tables %}
(
with {{ table_name }}_returns as (
select
symbol,
cast(date as date) as date,
close,
open,
high,
low,
lag(close) over (partition by symbol order by date) as prev_close,
(close / nullif(lag(close) over (partition by symbol order by date), 0) - 1) as daily_return,
(open / nullif(lag(close) over (partition by symbol order by date), 0) - 1) as overnight_return
from {{ source(’staging’, table_name) }}
)
select
‘{{ table_name }}’ as source_table,
symbol,
date,
‘return_spike’ as check_type,
coalesce(
case
when abs(daily_return) > 0.15 and abs(overnight_return) > 0.15
then ‘daily return ‘ || round(daily_return * 100, 1) || ‘% and overnight ‘ || round(overnight_return * 100, 1) || ‘%’
when abs(daily_return) > 0.15
then ‘daily return ‘ || round(daily_return * 100, 1) || ‘%’
else ‘overnight return ‘ || round(overnight_return * 100, 1) || ‘%’
end,
‘return spike detected’
) as failure_reason,
open,
high,
low,
close,
null::double as adj_close,
current_timestamp as detected_at
from {{ table_name }}_returns
where
prev_close is not null
and (abs(daily_return) > 0.15 or abs(overnight_return) > 0.15)
-- Exclude known stock splits (these cause legitimate large price moves)
and not exists (
select 1
from {{ ref(’corporate_actions’) }} ca
where ca.source_table = ‘{{ table_name }}’
and ca.symbol = {{ table_name }}_returns.symbol
and ca.date = {{ table_name }}_returns.date
and ca.action_type = ‘split’
)
)
{% if not loop.last %}union all{% endif %}
{% endfor %}
```Finally, I added a validation step that writes that table to the Google Sheet and then executes a macro to call the google finance API and look up the values, and confirm as a last resort and write the result back to Motherduck.
I set it to a weekly schedule, and its more of a one-time fix, but as I have been doing large backfills to populate the database, it has its uses. Everyone needs some backup sometimes.
A Disgusting Betrayal
During the process of fixing the tables and repopulating the data, I encountered a schema mismatch in one of my assets. Claude, being the eager beaver that it is, thought it would be better to add drop-create logic similar to (and much smaller) tables that use a drop-and-recreate pattern. Instead of altering the table in SQL and doing a quick migration, as I would have wanted to, it just started adding code. I was in one of those lazy moods where I wanted to wrap this thing up and move on to the next one. So when I reran the changes on the Netflix asset, I noticed my sp500_prices_raw table was about 800K rows too light. But the asset is fixed!
This was one of those fool me once, shame on me, fool me twice, cant get fooled again moments with Claude, and now my practice is too be more focused on what exactly Claude is doing and intervening whenever it goes off the rails, which happens often enough that the risk reward of running a bunch of agents in parallel, doesn’t work for me with my current setup. So I need to make some investments there.
Corporate Actions
During this process, I ran into an interesting data modeling problem that was not an issue for ETFs and commodities but for individual companies. So we went a layer deeper and added complexity (50 series or whatever -> 504). Also, splits aren’t the only so-called corporate actions. Companies get acquired, merged, renamed, etc. I’m working on building those pipelines out now. The asset model also made it conceptually simple to enhance while plugging into the infrastructure.
The whole thing took about 30 minutes of active work, not counting the backfill that ran for a few days against API rate limits. AI accelerated every step. But it only worked because I had a good basis in domain knowledge to stop and question the -89% return on Netflix, where to look in the Dagster run logs, what an exchange column implied about currency denomination, and that stock splits need to be modeled as their own asset. The current AI discourse treats the bottleneck as execution speed. It’s not, even if it has been for decades. The bottleneck is knowing what’s broken and why, which is a domain knowledge problem that no amount of token throughput solves. I mean you can build out abstractions and guardrails to assist with that or you can be more focused with what problems you are trying to solve. The tooling makes you faster at being right. It also makes you faster at being wrong, as my 800K missing rows will confirm.