FEC Election Data | Data Warehouse + Hadoop | Penn State Great Valley

Electoral Donation Data Insights
Integrated Data Systems for Electoral Donation Data in US States

A dual-architecture data engineering project that ingests, cleans, and analyzes 56 CSV files of Federal Election Commission contribution data spanning California, Florida, New York, and Pennsylvania. Two complete pipelines were built side-by-side: a PostgreSQL relational star-schema warehouse with KNIME ETL workflows, and a Hadoop HDFS implementation using Apache Pig, PySpark, and HiveSQL. Six business questions covering $721M in donation flows, party distributions, city-level breakdowns, top donor occupations, and monthly transaction trends were answered through both systems and compared.

●56 CSV files across CA, FL, NY, PA ●~5 million records processed ●$721M total donation data analyzed ●Dual pipeline: PostgreSQL + Hadoop Hive ●6 business questions answered ●2023 full year + 2024 Q1 coverage

PostgreSQL KNIME Hadoop HDFS Apache Hive PySpark Apache Pig Star Schema ETL Pipelines FEC Dataset SQL

Project Scale

Election Contribution Data at Scale

$721M

Total election contributions analyzed across CA ($359M), NY ($190M), FL ($127M), PA ($44M)

Source CSV files (16 per state) transferred to HDFS and loaded into PostgreSQL via KNIME ETL

~5M

Total transaction records processed across both relational and Hadoop implementations

Complete pipelines built and compared: PostgreSQL star schema and Hadoop Hive distributed architecture

Dual Architecture Overview: Relational Data Warehouse vs Hadoop

Architecture Design

Relational Star Schema: 7-Table Design

Entity Relationship Diagram: Star Schema for Election Contributions

Table Summary

Table	Type	Primary Key	Key Attributes	Role
transaction_main	Fact	train_id + composite UNIQUE	cmte_id, tran_date_id, address_id, personal_info_id, tran_amt, state	Central join point; all foreign keys + the donation amount
party_info	Dimension	cmte_id	party_nm, cmte_nm, cmte_tp, cmte_dsgn, cmte_filing_freq	Committee type and party designation (Democrat / Republican / Other)
person_info	Dimension	personal_info_id	name, employer, occupation	Donor identity: who gave, what they do, who they work for
address_informations	Dimension	address_id	city, state, zip_code	Donor location enabling city and state-level geographic aggregation
transaction_dates	Dimension	tran_date_id	tran_date, cvg_end_dt, dd, mm, yyyy	Normalized date dimension enabling monthly/quarterly time series analysis
transaction_information	Dimension	tran_id	tran_type (cash/check), memo_code, memo_text	Transaction method and memo records (proof of donation)

Data Preparation

KNIME ETL Workflows and Hadoop Pipeline

KNIME Node Workflow 1

Batch CSV Ingestion and Transformation

A CSV Reader node ingests either a single file or an entire batch of files, making it the entry point for all 56 source CSVs. The output connects to a transformation metanode that encapsulates all cleaning logic: converting uppercase column names to lowercase (to match PostgreSQL schema conventions), handling missing values, filtering unknown records, and correcting type mismatches before any data reaches the database.

KNIME Node Workflow 2

Duplicate-Safe Dimension Table Updates

A specialized sub-workflow handles inserts into each dimension table. A Column Filter removes irrelevant columns, a GroupBy node deduplicates, a DB Query Reader checks existing records in PostgreSQL, and a Reference Row Filter removes any rows already in the database before the final DB Writer node commits only new, non-duplicate records. The same metanode pattern is reused for each of the 5 dimension tables.

KNIME Node Workflow 3

Date Standardization (mmddyyyy Format)

Raw FEC date strings were often improperly formatted integers missing leading zeros (e.g., 1212023 instead of 01212023). This workflow uses String Manipulation nodes and a String to Date and Time converter to produce a consistent mmddyyyy format, then runs a GroupBy and Reference Row Filter before writing clean date records to the transaction_dates dimension table via DB Writer.

KNIME Node Workflow 4

Fact Table Assembly via Multi-Joiner

Multiple Joiner nodes concatenate all five dimension tables into a single combined row, which is then deduplicated with a Duplicate Row Filter, renamed with a Column Renamer, and cross-checked against the live PostgreSQL table using another DB Query Reader + Reference Row Filter. Only verified, non-duplicate records reach the final DB Writer that populates transaction_main.

Hadoop: PySpark ETL

Distributed Schema Creation and Null Handling

A custom schema was defined in PySpark and applied across all 56 files at once. Target columns were selected and filtered from the original data frames. Null values in zip_code, occupation, and employer were replaced with 'Unknown' rather than dropping records, preserving transaction counts. Dates were normalized to mm-dd-yyyy and the full dataset was loaded into the HiveSQL table election.main5 with 14 columns.

Hadoop: Apache Pig

MapReduce Data Flow Across All States

Apache Pig (Latin Pig) was used as a high-level platform to create the data flow, extracting all CSV files at once and transforming the data across multiple nodes using mappers and reducers. This approach handled the full 56-file batch significantly faster than the PostgreSQL ETL, which experienced bottlenecks during large batch updates. The transformed output was loaded directly to the Hive server schema.

Relational Data Warehouse vs Hadoop: Architecture Comparison

Dimension	PostgreSQL Data Warehouse	Hadoop (HDFS + Hive)
Storage model	Structured star schema, ~2 GB per table, single node	Distributed file system across compute cluster nodes
ETL tool	KNIME (visual node workflows)	PySpark + Apache Pig (code-based), then KNIME for reporting
Batch performance	Slow on large batches; significant delays on 56-file ingestion	Fast: mappers/reducers parallelize across nodes
Schema flexibility	Rigid star schema, predefined types and relations	Schema-on-read, flexible column definitions
Data quality tooling	KNIME detects type mismatches, missing values visually	Manual null-checks in PySpark code
Reporting	KNIME reporting service, rich chart types	KNIME via Hive Connector (same result quality)
Re-ingestion cost	Incremental via Reference Row Filter	Must clean and normalize data from scratch per batch
Best use case	Structured BI queries, role-based access, audit trail	Large-scale distributed analytics, growing data volumes

Reporting System

Six Business Question Analysis Results

BQ1: Total Donation Contributed by Each State (2023 + 2024 Q1)

BQ2: Top City Contributions by State

State	Top City (share)	2nd City	3rd City	Distribution pattern
CA	San Francisco (21.75%)	Los Angeles (18.74%)	San Jose (9.27%)	Distributed: 30+ cities each contributing; top 2 hold 40%
NY	New York City (74.31%)	Albany (3.09%)	Brooklyn (varies)	Highly concentrated: NYC holds nearly 3 in 4 NY donation dollars
FL	Tampa (18.88%)	Miami (10.73%)	West Palm Beach (10.6%)	Balanced: 3 cities combined hold 40%; rest widely spread
PA	Philadelphia (39.09%)	Newtown Square (15.55%)	Pittsburgh (varies)	Moderate concentration: Philadelphia holds 2 in 5 PA dollars

BQ3: Top Donor Occupations by State

BQ4: Donor Amount by Party (Democrats / Republicans / Other)

FL stands out: it is the only state where Democrats do NOT hold a majority of donation dollars. Republican and Other-affiliated committees together account for 61.45% of FL contributions, reflecting its competitive swing-state political landscape. CA, NY, and PA all show strong Democratic donation majorities (62-67%), consistent with their registration patterns.

BQ5 and BQ6: Transaction Volume Per Month (2023 full year vs 2024 Q1) and by State

Total transactions per month (all states, 2023 and 2024 Q1)

Transactions per month by state (2023)

Transaction volumes in 2023 show a strong pre-election surge from July onward, peaking in December at 470,086 total transactions. CA drives most of this surge, jumping from 125K in January to over 220K per month from August through December. The 2024 Q1 data shows a March peak (256,753) followed by a significant April drop (84,882), consistent with post-primary reporting deadlines.

Outcomes and Insights

Design Decisions and Comparative Findings

Hadoop Wins on Scale

The PostgreSQL ETL pipeline experienced significant slowdowns when updating dimension tables during the 56-file batch load, particularly for the transaction_main fact table with ~5 million rows. Hadoop's distributed MapReduce across multiple nodes handled the same batch considerably faster, confirming the scalability advantage of distributed architectures for large-scale election data at the FEC's reporting volume.

Star Schema vs Flat Hive Table

The relational star schema (7 normalized tables, composite primary keys, 5 foreign keys) required significant KNIME engineering to prevent duplicates across all dimension tables. The Hive flat table (election.main5) with 14 denormalized columns was far simpler to query for business questions but sacrifices referential integrity and makes complex multi-dimensional joins harder to maintain.

FL Is the Political Outlier

Among the four states analyzed, Florida is the only state where Democrats did not control the majority of donation dollars. Republicans and Other-affiliated committees combined for 61.45% of FL contributions, compared to 33-37% in CA and NY. The transaction count data confirms this: FL Republicans logged 370,183 transactions, outnumbering FL Democrats (331,542), the only such reversal in the dataset.

KNIME as Dual-Use Tool