I spent three weeks building what the industry calls a modern data stack. dlt pulls daily prices from Yahoo Finance, lands them in DuckDB locally and Snowflake in production, and dbt turns them into a star schema. The verified build is seven models — two staging, two intermediate, three marts — and twenty-two tests, running clean on 5,010 rows of real market data: ten tickers across 501 trading days, from July 2024 to July 2026.
The stack itself is not the interesting part. What I didn't expect was how many of my mistakes were mistakes of description rather than of code — claiming something the pipeline didn't quite do. These are the six that cost me the most.
1. Define the grain before the SQL
The grain is the sentence that says exactly what one row represents. For fct_daily_prices it's one row per ticker per trading day. Writing that down took ten seconds. Writing it down first changed everything downstream.
Once the grain is fixed, modeling decisions stop being matters of taste. Can I join ticker metadata straight into the fact? No — a ticker with two metadata rows would fan it out and inflate every aggregate built on it. Where does daily_return belong? In an intermediate model, computed once, so the mart stays thin.
But a grain in a YAML comment is just an intention. What makes it real is a surrogate key built from ticker + trade_date and a unique test on that key. The key is the grain expressed as a value; the test is the grain expressed as something that can fail — together, a sentence turned into a build-time guarantee. If a duplicate ever shows up — a bad merge, a source that starts returning intraday rows — the build breaks instead of quietly double-counting. A grain you haven't tested is a grain you're hoping for.
2. Make ingestion idempotent
The first thing I check on any new pipeline is what happens when I run it twice.
dlt handles this with a merge on ticker + date rather than an append. I verified it the boring way: ran the pipeline, counted rows, ran it again, counted again. Both runs leave the raw prices table at 5,010 rows with zero duplicate keys.
An ingestion job that duplicates on re-run is a job you can never re-run with confidence, and that turns every partial failure into an investigation: did it get halfway? Do I truncate first? Idempotency is what makes "just run it again" a safe answer — and "just run it again" is most of what operating a pipeline actually is.
3. Warehouse portability is not a dbt-only concern
The pitch for dbt portability is seductive: same models, swap the target, run anywhere. dbt build --target prod and you're on Snowflake.
Except dbt only transforms. It reads tables that something else loaded. Point the dbt profile at Snowflake while the ingestion still writes to DuckDB and you get a project querying a warehouse where the raw tables were never loaded. dbt is right to fail — the sources aren't there — but the error surfaces one layer from the cause, and I spent longer than I'd like reading it as a dbt config problem.
Both halves have to move together:
DESTINATION_TYPE=snowflake python ingestion/pipeline.py
dbt build --target prod --profiles-dir .DESTINATION_TYPE selects the dlt destination; --target selects the dbt one. Portability is a property of the pipeline, not of dbt. And to be precise about where this stands: the Snowflake target is wired and documented, but the production build against a live account is still an open item. The switch is designed; the evidence run isn't captured yet.
4. Read your build output honestly
The one I'd keep if I could keep only one.
dbt closes a build with a summary line:
Done. PASS=29 ERROR=0Twenty-nine is a good-looking number. It is also the sum of models and tests. My project has 7 models and 22 tests; seven plus twenty-two is twenty-nine.
The honest breakdown is in target/run_results.json, where every node carries its own resource type:
import json
from collections import Counter
nodes = json.load(open("target/run_results.json"))["results"]
print(dict(Counter(n["unique_id"].split(".")[0] for n in nodes)))
# {'model': 7, 'test': 22}The README now says 7 models and 22 tests, because that's what the artifact says.
The lesson generalizes well past dbt. Console summaries are written for the person watching the run, not for the person quoting it three weeks later — they aggregate, they round, they optimize for a glance. The artifact sitting next to them is the record. Any number headed into something a reader will treat as a claim — a README, a portfolio, a status update — should come from the artifact, not the summary.
The failure mode here isn't dishonesty. It's convenience. PASS=29 was right there, and it was bigger. That's how a portfolio ends up claiming more tests than it has: not by lying, but by declining to check a number that arrived pre-inflated.
5. Let the calendar tell the truth
Most date-dimension tutorials build a spine: generate every date in a range, join the facts onto it, backfill the gaps. For plenty of domains that's right.
Markets aren't one of them. My dim_dates has 501 rows over a two-year window — one per day the market was actually open. A full calendar spine would pad that out with weekends and holidays: days when nothing traded because nothing could.
So I built the dimension from observed trading days instead. The trade-off is deliberate: with a spine, every gap is expected, which means a real gap — a failed load, a ticker that dropped out mid-window — looks exactly like a Saturday. Building from observed days makes the calendar assert something: these are the days the market was open and we have data for them. A date missing from that set is a signal worth chasing rather than noise to filter out.
Structure your data so that broken looks different from normal. A model that can't be wrong can't tell you anything either.
6. Know your data's limits
I pull prices with auto_adjust=False. Splits and dividends aren't back-propagated, so daily_return is a raw close-to-close change, not a total return. On an ex-dividend date it reads the payout as a price drop rather than a distribution to holders.
I could have flipped the flag. I kept it, because unadjusted prices are what the source actually reports, and I wanted the fact to mirror the source rather than quietly reinterpret it. That's a defensible choice. What wouldn't be defensible is making it silently.
So daily_return carries the limitation in its column description, and the README says it plainly. That costs nothing, and it's the difference between a model someone can use correctly and one that eventually embarrasses whoever trusted it. Every dataset has an edge where it stops being true. The job isn't to pretend otherwise — it's to make the edge visible before someone else finds it in a board deck.
What carried over
Four of these six are one lesson in different clothes: make the pipeline tell the truth about itself. The grain test, the double-run check, the count read from run_results.json, the calendar with honest gaps — none of them make the data better. They make it harder to be wrong without noticing.
The stack was the easy part. dlt and dbt are well documented, and a working pipeline is a weekend of reading away. What took three weeks was the habit of verifying a claim before publishing it — and the one time I skipped it, the wrong number was already on screen, already bigger, already easy to believe.