This project builds a small analytical data warehouse from publicly available FFIEC Call Report bulk data.
The project demonstrates an end-to-end data engineering and analytics workflow:
FFIEC Central Data Repository ↓ Python ingestion ↓ PostgreSQL raw layer ↓ dbt staging ↓ Analytical fact table ↓ SQL analysis
The pipeline is designed to be period-agnostic. A user supplies a Call Report quarter-end date, and the pipeline automatically loads that quarter plus the three preceding quarters.
For example:
python python/pipeline.py 2025-06-30automatically creates a four-quarter window:
2024-09-30 2024-12-31 2025-03-31 2025-06-30
The same workflow can be used with other supported FFIEC quarter-end dates.
This project was designed to demonstrate:
- Automated ingestion of publicly available financial data
- Separation of raw ingestion from analytical transformation
- Relational data warehouse design
- Multi-period financial analysis
- Data-quality testing
- Reproducible local development
- Parameterized ingestion
- Idempotent data loading
- Reusable analytical SQL
FFIEC Central Data Repository
|
v
Python Pipeline
|
+-----------------+-----------------+
| | |
v v v
POR RC RI
Institution Info Balance Sheet Income Statement
| | |
+-----------------+-----------------+
|
v
PostgreSQL
raw schema
|
+-----------------+-----------------+
| | |
v v v
raw.por raw.rc raw.ri
| | |
+-----------------+-----------------+
|
v
dbt
|
+-----------------+-----------------+
| | |
v v v
stg_por stg_rc stg_ri
\ | /
\ | /
+---------------+---------------+
|
v
fct_bank_financials
|
v
SQL analysis
- Python — downloading, extraction, parsing, and data loading
- pandas — parsing FFIEC text files
- PostgreSQL — raw and analytical data storage
- Docker — reproducible PostgreSQL environment
- dbt — SQL transformations and data-quality testing
- SQL — analytical queries
- Git/GitHub — source control and reproducibility
The project uses publicly available FFIEC Call Report bulk data from the:
FFIEC Central Data Repository
https://cdr.ffiec.gov/public/pws/downloadbulkdata.aspx
The project currently uses three Call Report sources.
Used for institution-level information such as:
- IDRSSD
- FDIC certificate number
- Financial institution name
- Address
- City
- State
- ZIP code
Used for:
- Total assets
- Total liabilities
- Total equity
Used for:
- Net income
Together, these sources provide enough information to demonstrate a complete ingestion, warehouse, transformation, and analytical workflow without loading every available Call Report schedule.
The pipeline supports the four quarterly Call Report reporting dates:
- March 31
- June 30
- September 30
- December 31
The user supplies the ending reporting period, and the pipeline automatically calculates the preceding three quarters.
For example:
python python/pipeline.py 2025-06-30loads:
2024-09-30
2024-12-31
2025-03-31
2025-06-30
The exact four-quarter window changes automatically based on the date supplied.
Before setting up the project, install the following:
- Git
- Python 3.12+
- Docker with WSL 2 installed
Docker Desktop provides the PostgreSQL environment used by the project.
After installing Docker Desktop, start Docker Desktop and verify that the Docker Engine is running:
docker info
### 6.1 Clone the Repository
```bash
git clone <repository-url>
cd FFIEC-CALL-REPORT-WAREHOUSECreate a local environment file from the example:
cp .env.example .envUpdate the PostgreSQL settings in .env.
Do not commit .env to Git.
The repository uses .env.example so that another user can provide their own local database configuration without exposing credentials.
Make sure Docker Desktop is running, then run:
docker compose up -dVerify the container is running:
docker pspip install -r requirements.txtSupply the desired ending FFIEC quarter-end date:
python python/pipeline.py 2025-06-30Replace 2025-06-30 with any supported FFIEC quarter-end date.
For example:
Input:
2025-06-30
Automatically loaded:
2024-09-30
2024-12-31
2025-03-31
2025-06-30
No Python source files need to be edited to change the reporting period.
Move into the dbt project:
cd dbtRun the transformations:
dbt runRun the data-quality tests:
dbt testA successful test run should finish with:
ERROR=0
The SQL files under sql/analysis/ are standalone, read-only analytical queries. They run against the PostgreSQL warehouse and do not modify the database.
To move back into the FFIEC-CALL-REPORT-WAREHOUSE directory run the following command:
cd ..Before running the analysis commands, load the PostgreSQL environment variables from .env into your shell:
set -a
source .env
set +aThe available analyses are described below.
Run:
docker exec -i ffiec-postgres psql \
-U "$POSTGRES_USER" \
-d "$POSTGRES_DB" \
< sql/analysis/quarterly_summary.sqlThis provides a quarter-by-quarter summary of:
- Reporting institutions
- Aggregate assets
- Aggregate liabilities
- Aggregate equity
- Aggregate reported net income
The results cover the reporting periods currently loaded into analytics.fct_bank_financials.
Run:
docker exec -i ffiec-postgres psql \
-U "$POSTGRES_USER" \
-d "$POSTGRES_DB" \
< sql/analysis/asset_growth_latest_period.sqlThis compares the latest loaded reporting period with the immediately preceding reporting period and calculates asset growth.
The query determines the latest available reporting date dynamically, so it does not depend on a specific year or quarter.
Run:
docker exec -i ffiec-postgres psql \
-U "$POSTGRES_USER" \
-d "$POSTGRES_DB" \
< sql/analysis/top_5_latest_quarter_four_quarter_trends.sqlThis analysis:
- Identifies the five largest institutions by total assets in the latest loaded quarter.
- Selects those same five institutions as the analysis cohort.
- Follows the cohort across the latest four reporting periods.
- Calculates quarter-over-quarter asset and equity growth.
- Reports net income and absolute asset changes.
The output contains:
bank_name
report_date
total_assets
asset_growth_pct
total_equity
equity_growth_pct
net_income
asset_change
Because the analysis dynamically identifies the latest reporting period, it is not tied to a specific calendar year.
Run:
docker exec -i ffiec-postgres psql \
-U "$POSTGRES_USER" \
-d "$POSTGRES_DB" \
< sql/analysis/top_10_asset_concentration.sql
This is the primary analytical finding for the project.
The analysis:
Identifies the ten largest institutions by total assets in the latest loaded quarter.
Holds that ten-bank cohort constant across the latest four reporting periods.
Calculates the total assets held by those institutions.
Calculates total reported assets across the analytical population.
Calculates the share of reported assets held by the ten largest institutions.
Measures the quarter-over-quarter change in that concentration.
The resulting output contains:
```text
report_date
top_10_bank_names
top_10_assets
total_banking_assets
top_10_asset_share_pct
quarter_change_pct_pointsThis analysis is intended to answer:
How concentrated are reported banking assets among the ten largest institutions, and how has that concentration changed over the latest four quarters?
The ten institutions are selected using the latest loaded reporting period and then held constant across all four quarters. This allows the analysis to measure changes in concentration without changing the composition of the comparison group.
Run:
docker exec -i ffiec-postgres psql \
-U "$POSTGRES_USER" \
-d "$POSTGRES_DB" \
< sql/analysis/aggregate_liability_to_asset_ratio.sql
This calculates the aggregate liability-to-asset ratio across all
institutions for each of the four most recent reporting periods loaded
into the warehouse.
The ratio is calculated as:
Liability-to-Asset Ratio = Total Liabilities / Total Assets
The analysis dynamically selects the latest four reporting periods, so
it is not tied to a specific year or quarter.
Total liabilities are used rather than a narrower definition of debt
because the analytical warehouse currently models total liabilities
from Schedule RC.
## 8. Warehouse Design
### Raw Layer
The raw schema contains:
```text
raw.rc
raw.por
raw.riEach table contains multiple reporting periods and includes a report_date column.
For example:
raw.rc
IDRSSD | report_date | ...
-------+-------------+-----
37 | 2024-12-31 | ...
37 | 2025-03-31 | ...
37 | 2025-06-30 | ...
The raw layer is intentionally kept close to the structure of the FFIEC source data.
dbt transforms the raw source tables into:
stg_rc
stg_por
stg_ri
These models provide analyst-friendly column names and standardized data types.
The primary analytical model is:
analytics.fct_bank_financials
The intended grain is:
One row per financial institution per reporting period.
The warehouse grain is therefore represented by:
bank_id + report_date
The fact table contains:
bank_id
report_date
bank_name
fdic_certificate
address
city
state
zip_code
total_assets
total_liabilities
total_equity
net_income
This allows multiple reporting periods for the same bank to coexist in one analytical table.
Example:
JPMorgan | 2024-12-31 | ...
JPMorgan | 2025-03-31 | ...
JPMorgan | 2025-06-30 | ...
JPMorgan | 2025-09-30 | ...
Main entry point for the ingestion workflow.
Responsibilities:
- Validate the reporting date
- Calculate the rolling four-quarter window
- Download required FFIEC files
- Extract required schedules
- Load raw data into PostgreSQL
- Skip already-loaded reporting periods
Downloads the FFIEC Call Report bulk ZIP for a requested reporting period.
Extracts POR, RC, and RI from the downloaded ZIP.
Loads Schedule RC into raw.rc.
Loads POR into raw.por.
Loads Schedule RI into raw.ri.
The project uses dbt tests to enforce basic warehouse quality.
Tests include:
bank_idmust not be nullreport_datemust not be nullbank_id + report_datemust be unique- The fact table must maintain the intended grain
Run:
cd dbt
dbt testA successful run should finish with:
ERROR=0
The ingestion pipeline is designed to be safely rerunnable.
Before processing a reporting period, the pipeline checks whether that period already exists in the raw PostgreSQL tables.
If the period has already been loaded, the corresponding ingestion steps are skipped.
For example:
python python/pipeline.py 2025-09-30can be safely rerun after the data has already been loaded.
This prevents intentional duplicate ingestion and makes the pipeline more robust for repeated use.
Database credentials are stored locally in .env and are not committed to Git.
The repository includes .env.example so another user can create their own environment configuration.
Raw FFIEC ZIP and text files are excluded from Git because they can be regenerated through the parameterized ingestion pipeline.
The warehouse can answer questions such as:
- Which institutions have the largest balance sheets?
- Which banks experienced the largest recent asset growth?
- How has bank equity changed across reporting periods?
- How have aggregate bank assets changed over time?
- How have the largest institutions changed across the latest four quarters?
This project intentionally uses a subset of the available FFIEC Call Report schedules.
It is designed to demonstrate:
- Data ingestion
- Relational storage
- Transformation
- Data-quality testing
- Warehouse grain design
- Longitudinal analysis
Call Report financial amounts are reported according to the units and reporting conventions of the source schedules. Selected monetary fields are converted to dollars in the dbt staging layer.
Income-statement values should be interpreted according to Call Report reporting conventions rather than automatically assumed to represent an isolated quarterly income statement.