Modern Data Stack ELT

Context

Modern data teams dropped hand-crafted ETL in favor of ELT: load first, transform inside the warehouse with version-controlled SQL. This project implements that pattern end-to-end, from a raw market feed to a BI-ready star schema. It is the foundation the rest of my Analytics Engineering work builds on — the other projects consume these marts rather than starting over.

Business Problem

How do you turn raw stock-market data — daily prices plus ticker metadata — into trustworthy, versioned, documented analytical models a BI team can consume without rework, and keep the same models running on both a zero-cost local warehouse and a production cloud warehouse?

Architecture

Warehouse portability is a property of the whole pipeline, not just dbt. Both halves point at the same target: the dlt destination is selected by DESTINATION_TYPE, and dbt switches with --target. Swapping only the dbt profile would leave it querying a warehouse where the raw tables were never loaded.

Data Source

• Source: Yahoo Finance via yfinance — daily OHLCV prices plus ticker metadata (name, sector, industry) for a basket of 10 sector-diverse large caps.
• Volume: 10 tickers × ~2 years = 5,010 daily price rows, covering 2024-07-15 → 2026-07-14 (501 trading days).
• Grain of raw prices: one row per ticker per trading day.
• The window rolls: the pipeline pulls period="2y" relative to the run date, so a later reproduction reports a later window and a slightly different row count. The figures above are the snapshot of the documented build, not a constant.
• Known limitation: prices are unadjusted (auto_adjust=False), so splits and dividends are not back-propagated — daily_return is a raw close-to-close change, not a total return.

Methodology

Kimball dimensional modeling on an ELT backbone:

1. Ingestion (dlt): incremental load of prices (merge on ticker + date) and ticker metadata — no duplication on re-run.
2. Staging (dbt): one model per source table — rename, cast, standardize. No joins here.
3. Intermediate (dbt): business logic and enrichments — daily_return, calendar attributes.
4. Marts (dbt): star schema — a fact and its dimensions, ready for BI.
5. Documentation & lineage: dbt docs generates the model and column dictionary and the lineage graph straight from the code.

Challenges

The interesting part of this project was not the happy path. These are the problems it actually had to solve:

The grain has to be proven, not claimed

The grain — one row per ticker per trading day — was written down before any SQL. But a comment claiming a grain proves nothing. The fact carries a surrogate key built from ticker + trade_date, and a unique test on that key is what actually proves the grain holds on every build.

dlt rejected a mutable default argument

The first ingestion run failed with ValueError: mutable default <class 'list'> for field tickers is not allowed. dlt turns a resource's arguments into a spec dataclass, and dataclasses refuse mutable defaults. The fix was to drop the list default arguments and read the module-level constant inside the resource — a small bug with a real lesson about how the tool introspects your code.

Yahoo rate-limits, and the pipeline has to survive it

The source returns HTTP 429 under load. Each call is wrapped in a bounded linear backoff, so throttling slows the run instead of failing it — without turning a retry loop into an unbounded one.

Portability is not a dbt-only concern

The obvious mistake is to think a warehouse swap is a profile change. It isn't: dbt would happily point at Snowflake and find no source tables, because the ingestion never loaded them there. Both halves had to follow the same target — the dlt destination switches on DESTINATION_TYPE, dbt on --target.

Comparing two engines on live data means pinning the window first

Running against Snowflake for real produced the same 5,010 / 501 / 10 rows — but the checksums didn't match, which looked alarming for a moment. Row by row over the window both loads shared, close_price was identical on all 5,000 rows; volume differed on exactly 10, all on the same date: the last trading day of the earlier load, one per ticker, each revised slightly upward.

That's not a pipeline bug. The loads ran ~22 hours apart and the source consolidates a session's volume after the close. The two windows had also rolled — 2024-07-15 → 2026-07-14 versus 2024-07-16 → 2026-07-15, the same row count over a window shifted by a day, exactly what period="2y" does against a live feed.

Done. PASS=29 is not a test count

dbt's console summary sums models and tests. I published "23 tests" from an earlier build by reading that line — it was 5 models plus 18 tests. The honest source is target/run_results.json:

{'model': 7, 'test': 22}   # 29 nodes total, not 29 tests

Let the calendar tell the truth

dim_dates is built from the trading days actually observed in the data, not from a generated date spine. The calendar therefore contains only days the market was open — so a missing date is a real signal rather than an expected gap.

Results

dbt build runs clean on real market data — 7 models, 22 tests, 0 errors:

Models by layer: 2 staging (views) · 2 intermediate (views) · 3 marts (tables). Tests: not_null and unique on every key, plus relationships from the fact to both dimensions — 22 passing, 0 failing. Reproduced clean-room from an empty warehouse: same 5,010 / 501 / 10 rows.

Proven on both warehouses

The whole pipeline — ingestion and transformation — ran against each warehouse, not just a profile switch:

Tech Stack

View the code on GitHub ↗