Data Model Overview

The SEC-ETL API reads from a PostgreSQL data warehouse built on a star-schema design. Data flows through a 10-phase ETL pipeline that transforms raw SEC EDGAR XBRL filings into clean, standardized financial statements.

Star Schema

The warehouse uses a classic star-schema design:

Dimension Tables

Table	Purpose
dim_concepts	XBRL concept metadata (qname, data type, balance, period type)
dim_entities	Company information (ticker, CIK, SIC, sector, industry)
dim_filings	Filing metadata (report type, fiscal dates)
dim_periods	Fiscal periods (instant, start date, end date)
dim_units	Units of measure (USD, shares, etc.)
dim_standard_metric_map	Maps company XBRL QNames to canonical metric names

Fact Tables

Table	Purpose
xbrl_facts	Raw staging — facts as parsed from XBRL without FK constraints
fact_xbrl_facts	Normalized fact table with full foreign keys to all dimension tables
fact_xbrl_derived_facts	Quarterly facts with quality flags for flow vs. instant data
fact_calculated_metrics	Financial ratio results with A–F letter grades

TimescaleDB Hypertable

Table	Purpose
market_prices	OHLCV price history from Yahoo Finance

Data Quality Pipeline

XBRL Filing → Parse → Stage → Normalize (FK resolution) → Compute Quarters → Map Metrics → Derive → Ratios

Each step includes audit checks and quarantine mechanisms. Facts that fail FK resolution are logged to error_log for review.

Key Design Decisions

1. Values are stored as text to preserve XBRL’s arbitrary precision. Convert to numeric in your application.
2. Quarterly data is derived — most XBRL filings report cumulative (YTD) flow data. The pipeline uses window functions to compute Q2–Q4 increments.
3. Instant facts are deduplicated — when multiple filings report the same point-in-time fact, only the latest filing’s value is retained.
4. Supersedence is honored — newer filings replace earlier filings for the same period and concept.