RAW TO RELIABLE / CASE STUDY / PROJECT 01
SEC Financial Data Platform
An end-to-end data platform for ingesting, validating, modeling, and monitoring SEC company financial records.
The project converts irregular SEC filing records into structured, testable, analytics-ready financial datasets while establishing the foundation for anomaly monitoring and repeatable quarterly processing.
Focus areas
- Batch ingestion
- Financial-data normalization
- Statement classification
- Data quality
- Dimensional modeling
- Anomaly monitoring
Technology direction
01 / Executive Overview
Executive Overview
The SEC Financial Data Platform is a developing batch-data system for collecting and organizing company financial records from SEC filing data. The current build focuses on a limited 2024 Q1 dataset and establishes the first reliable path from source API responses through object storage, normalization, validation, and future analytical modeling.
The immediate engineering objective is not simply to download filing data. It is to create a repeatable structure that can distinguish reporting periods, statement types, financial concepts, filing metadata, and data-quality exceptions before the records are used for analysis.
Current State
Raw ingestion and first-layer validation implemented.
Active Build
Staging validation, orchestration, layered modeling, and anomaly rules.
Target Outputs
Expanded quarter coverage, automated marts, monitoring dashboard, and alerting.
02 / Problem
Problem
SEC company financial data is structurally available but not immediately analytics-ready. Different companies use different tags, reporting periods, filing forms, frames, durations, and statement structures. A single metric may appear under multiple source tags or reporting contexts, and values cannot be compared reliably until those records are normalized and classified.
The engineering challenge is to preserve source fidelity while creating a consistent analytical layer that supports company-level and period-level comparisons, quality checks, financial-ratio analysis, and anomaly monitoring.
Engineering problem dimensions
- 01Source variation
- 02Financial concept inconsistency
- 03Reporting-period ambiguity
- 04Statement-type classification
- 05Duration versus instant metrics
- 06Data-quality exceptions
- 07Repeatable quarterly ingestion
- 08Safe backfills and reruns
03 / Scope & Constraints
Scope & Constraints
The controlled first run keeps the dataset narrow enough to develop normalization and validation rules without obscuring failures inside a broad historical load.
- Current quarter
- 2024 Q1
- Requested CIKs
- 100
- Successful
- 95
- Unsuccessful
- 5
Current constraints
- Limited company sample and quarter-specific processing
- SEC source-tag variation across issuers
- Different duration and instant metric semantics
- Incomplete financial-concept mappings
- API reliability and request handling
- Reproducible reruns with source metadata preserved
- An actively developing downstream architecture
04 / Architecture
Architecture
Source & Ingestion
Validation & Transformation
Modeling & Outputs
05 / Data Flow
Data Flow
- 01Implemented
Controlled company scope
Source Request
The ingestion process requests SEC company-facts records for a controlled list of CIK identifiers.
- 02Implemented
Source-first landing
Raw Response Preservation
Raw JSON responses are preserved before transformation so source fields and filing context remain available for validation and reprocessing.
- 03Implemented
Durable object storage
S3 Landing
Raw outputs and the ingestion summary are written to AWS S3, creating a durable landing layer for downstream processing.
- 04Implemented
First quality boundary
Raw Validation
Great Expectations checks raw object metadata, JSON structure, expected fields, CIK format, and ingestion-summary counts.
- 05Implemented
Standardized records
Staging Normalization
Python and Pandas extract and standardize company, filing, taxonomy, financial-concept, date, period, form, frame, and value fields.
- 06Implemented
Financial context
Statement & Duration Classification
Implemented logic classifies income-statement and balance-sheet metrics, quarterly and annual forms, instant records, and multiple duration bands; deeper rule validation continues.
- 07In Progress
Layered analytical model
Snowflake & dbt Modeling
Normalized records are being designed for Snowflake raw, staging, core, and mart layers that support reusable financial analysis.
- 08Planned
Decision layer
Monitoring & Analytical Outputs
Target outputs include financial-ratio monitoring, anomaly flags, Power BI reporting, and automated quality alerts.
06 / Data Modeling
Data Modeling
The current staging output preserves filing context while establishing a layered path toward reusable analytical models. RAW and STAGING responsibilities are evidenced in the current code; CORE and MARTS remain target layers.
RAW
ImplementedPreserve source company-facts JSON and ingestion metadata without removing filing context.
STAGING
ImplementedNormalize concepts, dates, forms, periods, values, statement types, and duration classifications.
- cik
- entity_name
- taxonomy
- source_tag
- value
- start_date
- end_date
- filed_date
- frame
- accn
- fy
- fp
- form
CORE
In ProgressCreate reusable company, filing, metric, statement, and reporting-period structures.
MARTS
PlannedProduce datasets for company trends, ratios, exceptions, and monitoring.
Target analytical model
- Company
- Filing
- Financial Metric
- Reporting Period
- Statement Type
- Metric Observation
- Anomaly Event
Duration semantics
- Instant records represent point-in-time values such as assets or liabilities.
- Quarter-duration records represent measurements over an approximate quarterly reporting period.
- Half-year, nine-month, full-year, and other durations require distinct classification or exclusion before comparison.
07 / Data Quality
Data Quality
Quality controls begin at the landing layer and expand as records gain analytical meaning. The current Great Expectations workflow checks raw S3 objects and ingestion metadata; staging and model-level controls are still being developed.
Validation should stop unreliable records from silently entering analytical models.
Ingestion accounting
Requested, successful, and unsuccessful company counts are preserved in the ingestion summary.
Raw schema conformity
Raw file presence, JSON parsing, identity fields, facts structure, CIK format, and object size rules.
Source context preservation
Taxonomy, source tag, accession, filing form, fiscal period, frame, and date fields remain available.
Date, duration & statement validation
Validate classification results, period semantics, and acceptable record contexts.
Uniqueness & plausibility rules
Define duplicate keys, metric-value expectations, anomaly thresholds, and failure categories.
Automated quality gates
Add quarantine, alerting, historical monitoring, mart reconciliation, and ratio exception tests.
08 / Engineering Decisions
Engineering Decisions
01
Preserve raw JSON
- Decision
- Store original SEC responses before transformation.
- Rationale
- Raw preservation supports source traceability, reprocessing, validation, and future changes to normalization logic.
02
Use layered transformations
- Decision
- Separate raw, staging, core, and mart responsibilities.
- Rationale
- Layering prevents source irregularities from leaking directly into analytical outputs and supports targeted testing.
03
Retain source metadata
- Decision
- Keep taxonomy, source tags, accession numbers, filing forms, fiscal periods, frames, and filing dates.
- Rationale
- Financial values cannot be interpreted reliably without their reporting context.
04
Classify metric duration
- Decision
- Separate instant, quarter-duration, and other records.
- Rationale
- Balance-sheet values and income-statement values represent different time semantics.
05
Add quality validation early
- Decision
- Validate the raw layer before building downstream models.
- Rationale
- Early checks expose ingestion and structural problems before they become modeling defects.
06
Use a controlled initial scope
- Decision
- Begin with a limited 2024 Q1 and first-100-CIK processing scope.
- Rationale
- A controlled sample supports faster iteration on schema, validation, and failure handling before broader backfills.
09 / Trade-offs
Trade-offs
01
Breadth versus validation depth
The current build favors a smaller company and quarter scope so normalization and validation rules can be developed before broader ingestion.
02
Source fidelity versus analytical simplicity
Preserving SEC source tags and filing context increases schema complexity but avoids losing information needed for interpretation and traceability.
03
Python iteration versus distributed processing
Python and Pandas support rapid staging development, while Spark remains a target option for larger historical workloads; no scale improvement is assumed without benchmarking.
04
Fixed mappings versus extensible classification
Initial metric mappings are direct to implement but need a more extensible approach as issuer-specific tags and additional statements are added.
05
Strict quality gates versus ingestion continuity
Strict validation improves reliability but needs quarantine and retry behavior so isolated failures do not block an entire batch.
06
Current visibility versus future automation
Manual inspection remains useful during development, while Airflow, dbt tests, and monitoring are intended to reduce manual intervention later.
10 / Outputs
Outputs
Raw SEC company-facts JSON and S3 landing objects
Ingestion summary JSON with 100 requested, 95 successful, and 5 unsuccessful records
Normalized staging records with statement and duration classifications
Raw-layer Great Expectations validation workflow
Validated staging dataset and Snowflake raw / staging layers
Core financial metric models, anomaly rules, and orchestration logs
Trend marts, ratio indicators, Power BI monitoring, alerts, and expanded backfills
11 / Current Progress
Current Progress
Controlled SEC company-facts ingestion and Lambda-compatible entry point
First 100 CIK scope with 95 successful and 5 unsuccessful requests
Raw JSON preservation, AWS S3 upload, and ingestion_summary.json
Python/Pandas staging, metric mappings, statement classification, and duration classification
Raw S3 Great Expectations validation workflow
Second Great Expectations layer and deeper staging validation
Anomaly rules, Airflow DAG, dbt models, and Snowflake layers
Expanded quarterly coverage, automated marts, Power BI monitoring, alerting, and historical backfills
12 / Next Improvements
Next Improvements
13 / Technology Stack
Technology Stack
- Ingestion
- PythonImplemented
- SEC Company Facts APIImplemented
- AWS Lambda handlerImplemented
- Processing
- PandasImplemented
- SparkPlanned
- Storage
- AWS S3Implemented
- Warehouse
- SnowflakeIn Progress
- Transformation
- dbtIn Progress
- SQLIn Progress
- Orchestration
- AirflowIn Progress
- Data Quality
- Great ExpectationsImplemented
- Runtime & Output
- DockerPlanned
- Power BIPlanned