RAW TO RELIABLEby Lee YaoData Engineer & Analytics Systems Builder
Menu
Back to Work
Project 01Financial Data PlatformCompleted Oct 2026 · 100 CIKs requested

RAW TO RELIABLE / CASE STUDY / PROJECT 01

SEC Financial Data Platform

Real SEC company facts move through Python ingestion, validation, Snowflake and dbt marts into anomaly flags and a Tableau dashboard; a Kafka and Spark stream processes filing events.

95 of 100 requested company records were ingested; 64,619 filtered financial facts feed tested models, rule-based anomaly flags, and an annual Tableau dashboard. A separate Kafka and Spark path handles filing events.

Project stateCompleted
View Source on GitHub

Focus areas

  • Batch ingestion
  • Financial-data normalization
  • Statement classification
  • Data quality
  • dbt financial marts
  • Rule-based anomaly detection
  • Filing-event streaming

Technology stack

PythonAWS S3 (historical)KafkaSpark Structured StreamingSnowflakedbtAirflowGreat ExpectationsTableauSQLDocker

01 / Executive Overview

Executive Overview

This completed portfolio system turns real SEC EDGAR Company Facts JSON into comparable financial datasets. Python ingested and archived source responses in S3, flattened and filtered financial facts, and loaded a Snowflake RAW table. Great Expectations validated the historical raw archive and checks the warehouse table and final marts; dbt creates financial and anomaly models consumed by Tableau.

A second pipeline produces SEC filing events through Kafka. Spark Structured Streaming validates and deduplicates them, quarantines invalid events, and merges valid micro-batches into a separate Snowflake table. Its output has a dbt staging model; it is not the source of the financial dashboard.

Implemented

  • Historical batch

    Company Facts ingestion, filtering, Snowflake load, and validation completed.

  • Analytical output

    dbt financial marts, anomaly flags, and annual Tableau dashboard completed.

  • Filing-event stream

    Kafka, Spark, quarantine, Snowflake MERGE, and dbt staging validated.

02 / Problem

Problem

SEC company facts contain repeated comparative periods, varying XBRL tags, amended filings, and both duration and point-in-time values. Direct aggregation can double count periods or compare unlike metrics.

The pipeline keeps the source records, selects filing-aware facts, validates each analytical boundary, and produces consistent company-period metrics and explicit anomaly flags for review.

Engineering problem dimensions

  • 01Issuer-specific XBRL tags
  • 02Quarterly versus annual reporting grain
  • 03Duration versus instant facts
  • 04Comparative filings and amendments
  • 05Failed or empty company responses
  • 06Duplicate and invalid filing events

03 / Scope & Limits

Scope & Limits

The historical ingestion run is labeled 2024 Q1, but each Company Facts response contains historical facts. The dashboard shows annual periods through 2025; the archive label is not a filter on financial reporting years.

Requested CIKs
100
Ingested companies
95
Failed requests
5
Filtered financial facts
64,619

Known limitations

  • The first-100-CIK sample does not cover all SEC issuers.
  • Five requests failed in the recorded ingestion summary; the source does not classify their causes.
  • Mapped metrics and accepted filing forms limit coverage of issuer-specific disclosures.
  • Anomaly flags use fixed rules and require analytical review.
  • The S3 archive was exported to local cold storage and AWS resources were decommissioned; rerunning raw validation requires restoring that archive or a new source load.

04 / Architecture

Architecture

SEC PLATFORM / IMPLEMENTED ARCHITECTURE / FIG. 01
Implemented

Historical batch data flow

ImplementedSEC Company Facts APIReal company JSON
ImplementedPython + S3 archiveHistorical raw landing; AWS decommissioned
ImplementedPython stagingFlatten, classify, filter
ImplementedSnowflake RAWSEC_FINANCIALS table
Implementeddbt + TableauFinancial marts, anomaly flags, dashboard

Filing-event data flow

ImplementedPython producerSEC filing events
ImplementedKafka + SparkValidate, watermark, deduplicate
ImplementedSnowflake MERGEAccession-number key
Implementeddbt stagingFiling-event source model

Quality & orchestration boundaries

ImplementedGreat ExpectationsRaw, warehouse, and mart checks
ImplementedAirflow batch DAGWarehouse check → dbt build → mart check
The batch path produces financial marts; a separate event stream lands validated filings in Snowflake. Airflow orchestrates the warehouse checks and dbt build.

05 / Ingestion & Flow

Ingestion & Flow

  1. 01

    Historical batch

    Ingest SEC facts

    Python requests the first 100 CIKs from SEC company_tickers.json, then fetches each Company Facts JSON response. A 0.15-second pause spaces requests; individual failures are logged and skipped.

    Implemented
  2. 02

    Raw boundary

    Preserve and inspect

    The original JSON and ingestion summary were archived in S3. Great Expectations checks object counts, sizes, parseability, identity fields, and summary counts. The S3 archive was later exported and AWS decommissioned.

    Implemented
  3. 03

    Python staging

    Normalize financial facts

    Pandas flattens XBRL facts, maps selected tags to metrics, classifies statements and durations, and retains quarterly 10-Q income facts, annual 10-K income facts, and instant balance-sheet facts.

    Implemented
  4. 04

    Snowflake + dbt

    Load and model

    A Python loader writes the filtered CSV to Snowflake SEC_FINANCIALS. dbt views standardize and canonicalize periods; table marts expose company financials and anomaly flags.

    Implemented
  5. 05

    Airflow quality gate

    Orchestrate the analytical build

    The manually triggered Docker Airflow DAG verifies the project mount, validates the warehouse, runs dbt build, then validates marts. Ingestion and the streaming path are outside this DAG.

    Implemented
  6. 06

    Separate streaming path

    Process filing events

    Kafka feeds Spark Structured Streaming. Invalid events go to Parquet quarantine; valid events use watermark deduplication and a Snowflake MERGE keyed by accession number, then enter dbt staging.

    Implemented

06 / Warehouse & Modeling

Warehouse & Modeling

Snowflake RAW holds the loaded financial table and filing-event table. dbt separates staging views, intermediate views, and materialized marts; this project does not implement a separate CORE schema or a Snowflake VARIANT landing layer.

RAW source

Implemented

SEC_FINANCIALS contains the filtered flat financial records; SEC_FILING_EVENTS_STREAM holds validated filing-event rows after MERGE.

dbt staging

Implemented

stg_sec_financials and stg_sec_filing_events select and name fields from the two Snowflake sources.

  • cik
  • entity_name
  • metric_name
  • value
  • form
  • filed_date
  • accn

dbt intermediate

Implemented

Income and balance-sheet models select canonical facts by company, metric, and period, prioritizing non-restatements and using filing date and accession as tie-breakers. Quarterly metrics organize analysis grain.

dbt marts

Implemented

dim_companies, fct_company_financials, and fct_financial_anomalies serve company-period analysis, ratios, and rule-based flags.

Implemented analytical models

  • Company dimension
  • Company financial fact
  • Financial anomaly fact
  • Filing-event staging model

Duration semantics

  • Income facts from 10-Q forms use quarter duration; 10-K forms use full-year duration.
  • Balance-sheet facts use instant values.
  • Later comparative filings are ranked to avoid duplicate analytical periods.

07 / Data Quality

Data Quality

The archived S3 data was validated before AWS decommissioning. Current warehouse and mart checks, dbt tests, and the separate streaming validation path cover the downstream system.

Validate source structure and reporting context before trusting a financial comparison.
Implemented

  • Raw archive

    Great Expectations checks 90–100 JSON objects, CIK format, parse success, entity name, facts structure, size, and ingestion counts.

  • Warehouse

    The SEC_FINANCIALS suite checks exact row count, accepted metrics, USD units, forms, non-null identifiers and values, statement types, durations, and period states. The README reports 10 of 10 expectations passed.

  • dbt

    The project defines 37 data tests across its models; the filing-event staging model reports 13 passing checks with no warnings or errors.

  • Marts

    Two Great Expectations suites check required identifiers and periods, ratio ranges, anomaly flags, and business rules.

  • Streaming

    Spark labels invalid events for Parquet quarantine; valid events use accession deduplication, a persistent checkpoint, and idempotent Snowflake MERGE.

08 / Engineering Decisions

Engineering Decisions

  1. 01

    Preserve SEC responses

    Decision
    Archive Company Facts JSON before flattening.
    Rationale
    Original XBRL tags and filing context remain available when mappings or period rules change. AWS storage was later decommissioned after export.

  2. 02

    Select canonical facts

    Decision
    Rank repeated company-metric-period records before mart construction.
    Rationale
    Later comparative filings otherwise duplicate previously reported periods in BI aggregations.

  3. 03

    Keep reporting grains distinct

    Decision
    Filter income facts by filing form and duration; keep balance-sheet facts at an instant.
    Rationale
    Annual, quarterly, and point-in-time values cannot be compared as one grain.

  4. 04

    Use liabilities fallback

    Decision
    Prefer reported liabilities and derive assets minus equity only when absent.
    Rationale
    Issuers expose different tag sets; the fallback improves coverage while preserving directly reported values.

  5. 05

    Gate the dbt build

    Decision
    Run warehouse validation before dbt and mart validation after it.
    Rationale
    The Airflow task order stops downstream modeling when an upstream check fails.

  6. 06

    Make streaming writes idempotent

    Decision
    Deduplicate in Spark and MERGE on accession number in Snowflake.
    Rationale
    A resent filing event was verified to remain a single warehouse record.

09 / Trade-offs & Limits

Trade-offs & Limits

  1. 01

    Sample coverage

    The historical run requested only the first 100 CIKs and succeeded for 95. Its five failures have no documented cause classification or automatic retry.

  2. 02

    Historical AWS layer

    S3 was used for raw storage, then exported to local cold storage and decommissioned. It is historical architecture, not a current runtime dependency.

  3. 03

    Manual batch trigger

    The Airflow DAG has schedule=None and starts after the data is loaded. It orchestrates warehouse validation, dbt, and mart validation, not SEC ingestion.

  4. 04

    Full batch reload

    The Python warehouse loader truncates SEC_FINANCIALS before writing the filtered CSV. It does not implement an incremental financial-fact load.

  5. 05

    Rule-based signals

    Revenue, margin, and debt flags use fixed thresholds. They identify review candidates; they do not diagnose financial causes.

10 / Analytics & Output

Analytics & Output

Implemented

  • 95 archived Company Facts responses and a recorded 100/95/5 ingestion summary

  • 64,619 filtered financial fact rows loaded for warehouse modeling

  • Company financial mart with revenue, income, assets, liabilities, equity, margins, and debt-to-assets ratio

  • Anomaly mart flags revenue drops over 30%, absolute net-margin shifts of at least 0.20, and debt-ratio rises of at least 0.15 versus the prior period

  • Tableau annual dashboard with company and year filters, revenue and income trends, margins, and balance-sheet leverage

  • Separate validated filing-event table and dbt staging model

Tableau SEC Company Financial Performance dashboard showing Amazon annual revenue, net income, profitability margins, assets, liabilities, and debt-to-assets ratio through 2025.
ANALYTICS OUTPUT

Final Tableau dashboard using Snowflake financial marts. The screenshot shows one company selection and annual reporting periods.

11 / Validation & Result

Validation & Result

Implemented

  • The source README reports a successful four-task Airflow run: mount check, warehouse validation, dbt build, and mart validation.

  • The warehouse suite reports 10 of 10 expectations passed; the streaming dbt staging checks report 13 PASS, 0 WARN, 0 ERROR.

  • A new filing event reached Snowflake without restarting Spark; resending its accession number left one row.

  • The source repository contains the final Tableau dashboard image and a sanitized public workbook definition.

12 / Future Improvements

Future Improvements

  1. 01

    Classify the five failed CIK requests and add bounded retries to a future ingestion run.

  2. 02

    Expand issuer and taxonomy coverage beyond the current mapped facts.

  3. 03

    Schedule and connect ingestion to the Airflow warehouse workflow if recurring batch operation is required.

  4. 04

    Review anomaly thresholds against company context before treating flags as alerts.

13 / Technology Stack

Technology Stack

Source & ingestion
  • SEC EDGAR Company Facts APIImplemented
  • Python / PandasImplemented
Historical raw storage
  • AWS S3 (decommissioned)Implemented
  • JSON / CSVImplemented
Warehouse & modeling
  • SnowflakeImplemented
  • dbt Core / SQLImplemented
Quality & orchestration
  • Great ExpectationsImplemented
  • Apache Airflow / DockerImplemented
Filing-event stream
  • KafkaImplemented
  • Spark Structured Streaming / ParquetImplemented
Analytics
  • TableauImplemented

14 / Repository / Work Navigation

Continue through the work.

Return to the full project registry or continue to the next documented system.

View Source on GitHubBack to All WorkView Next Project