At Carbon Arc, we ingest data from dozens of external vendors. Some push files to S3 buckets while others expose APIs we poll on a schedule. Over time, this grew organically. A new vendor meant a new:
- Repository
- Dockerfile
- Airflow DAG
- Set of conventions to remember
We called these repos ingest-* (one for each vendor codename), and before long we had dozens of them, each with its own quirks.
This post describes how we consolidated that sprawl into a single, reusable ingestion framework built on Apache Iceberg, Apache Spark, and PostgreSQL. The result is a unified “staging” table that serves as our bronze layer, with file-level tracking, per-vendor retention policies, and a dev environment that mirrors production exactly.
The problem with organic growth
When we started, each vendor got its own repository. The pattern looked reasonable at first:
ingest-firefly/
├── src/
│ ├── main.py
│ └── sync_s3.py
├── dags/
│ └── ingest_firefly_dag.py
├── Dockerfile
├── requirements.txt
└── ...
But as vendor count grew, so did the maintenance burden. One repo had 30+ Python files (one per dataset) with duplicated boilerplate across all of them. CI/CD pipelines had diverged and dependency versions drifted. When we needed to add observability or change how we ingested data, we had to touch every repo.
The tracking mechanism was particularly painful. We stored a JSON file in S3 that listed which files had already been processed. This worked, but it was brittle: concurrent writes could corrupt state, there was no query interface, and debugging “why didn’t this file get processed?” meant downloading and parsing JSON.
Design goals for the new architecture
We set out with four objectives:
- One staging table. All vendor data lands in a single Iceberg table before downstream transformations touch it. This creates a clear bronze layer with consistent schema.
- Reusable ingestion patterns. Most of our feeds are file-based (S3 drops). We wanted a base class that handles folder discovery, incremental processing, and staging writes. API-based vendors get their own base class sharing the same write path.
- Queryable file tracking. Replace the S3 JSON log with a PostgreSQL table. This gives us SQL access for debugging, proper transactional semantics, and the ability to answer questions like “which files were processed in the last hour?”
- Dev/prod parity. Engineers should be able to test ingestion end-to-end without touching production data. That means a dev staging table and a dev tracking table that behave identically to their prod counterparts.
The unified staging table
At the heart of the new architecture is a single Iceberg table that receives all ingested data. The schema is intentionally simple:
| Column | Type | Description |
|---|---|---|
raw | STRING | The source payload as JSON |
ingest_id | STRING | Identifies the vendor and dataset |
vendor | STRING | Codename for the vendor |
dataset | STRING | Dataset identifier within that vendor |
drop_id | STRING | Batch or folder identifier for this ingestion run |
filename | STRING | Source file path (for S3-based ingests) or NULL for APIs |
other | STRING | Vendor-specific metadata as JSON |
processing_ts | TIMESTAMP | When we processed this record |
dt | STRING | Date |
The table is partitioned by ingest_id and drop_id. This partitioning scheme is critical: it lets us efficiently query or delete data for a specific vendor without scanning unrelated partitions, and it keeps time-travel and replay operations fast even as the table grows to billions of rows.
Reusable base classes
We built a small class hierarchy that captures the two dominant ingestion patterns we see:
S3-based ingestion. The majority of our vendors drop files into S3 buckets on a schedule. The base class handles folder discovery (“what new folders appeared since last run?”), incremental processing via the PostgreSQL tracking table, and writing to staging. A concrete implementation only needs to declare the bucket, path pattern, and how to derive a batch identifier from the folder path. Most new S3 vendors are onboarded in under 50 lines of configuration.
API-based ingestion. Some vendors expose REST APIs that we poll. The base class handles authentication, pagination, rate limiting, and concurrent fetching across entities. Subclasses define the endpoints and any vendor-specific parameters.
Both patterns share the same write path to the staging table. This means every vendor, whether file-based or API-based, produces rows with identical schema. Downstream transforms don’t need to know where the data came from; they just query the staging table by ingest_id.
PostgreSQL for file tracking
The old JSON-in-S3 approach had no concurrency guarantees and no query interface. We replaced it with a PostgreSQL table:
CREATE TABLE ingest.files_processed (
filename TEXT NOT NULL,
ingest_id TEXT NOT NULL,
vendor TEXT,
dataset TEXT,
drop_id TEXT,
rows_written INTEGER,
ingested_at TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (filename, ingest_id)
);
Before processing a folder, the ingestor queries this table to see what’s already been done. After a successful write to staging, it inserts a tracking record. The ON CONFLICT DO NOTHING clause makes the operation idempotent: if a job restarts mid-run, it won’t duplicate work.
This table has already come in handy! When a downstream job reported missing data, we ran a simple query to confirm the source files had landed and identify exactly when. Previously, that debugging session would have involved downloading JSON files and writing ad-hoc scripts.
Dev environment that mirrors production
We maintain a dev staging table with identical schema and partitioning to production. The PostgreSQL tracking table also has a corresponding dev variant.
The ingestors accept a flag that switches both the target table and the tracking table atomically. Engineers can run a full ingestion against dev, inspect the results, and only flip to production once they’re confident. This removed a significant source of anxiety from the onboarding process. Nobody wants their change to accidentally corrupt production data.
Retention as a first-class concept
Different datasets have different retention needs. We built retention into the framework from the start. Each ingest_id can specify its own retention period, and a scheduled maintenance job deletes expired partitions:
cleanup_staging_data(
vendor="financials_provider",
retention_days={
"balance_sheet": 7,
"income_statement": 14,
"kpis": 30,
}
)
Partitioning by ingest_id makes these deletes efficient since we’re not scanning the entire table to find rows to remove.
What we learned
Standardization reduces operational burden. With one ingestion pattern and one staging contract, we have one runbook. New team members learn the framework once; new vendors are a configuration exercise, not a greenfield project.
Metadata tables are part of the product. The PostgreSQL tracking table isn’t just for deduplication. It’s an observability surface. When something goes wrong, the first question is usually “did the data land?” The tracking table answers that instantly.
Dev parity removes fear. Maintaining parallel dev tables sounds like overhead, but it changed how engineers approach onboarding. They experiment freely in dev, iterate on transforms, and only promote to production once they’ve seen real data flow end-to-end. That confidence is worth the extra table.
Iceberg’s partitioning enables scale. Partitioning by ingest_id and drop_id keeps queries fast even as row counts grow. Time-travel lets us debug historical states without maintaining manual snapshots. And Spark handles concurrent appends from multiple jobs without conflict.
Before and after
| Aspect | Before | After |
|---|---|---|
| Repositories | Dozens of separate ingest-* repos | 1 shared ingest module in monorepo |
| Lines of code | ~500-800 per repo, heavily duplicated | ~200 per vendor, mostly configuration |
| File tracking | JSON in S3 (no concurrency, no query interface) | PostgreSQL table (ACID, SQL queryable) |
| Staging tables | Per-vendor landing zones | Single unified Iceberg table |
| Retention | Manual, inconsistent | Per-ingest-id policies, automated |
| Dev testing | N/A or ad-hoc | Full dev environment mirroring prod |
What’s next
We’re continuing to migrate legacy ingest-* repos into the new framework. Each migration follows the same pattern: implement the S3 config, verify in dev, run parallel ingestion to compare outputs, then cut over. The framework has handled every vendor we’ve thrown at it so far.
On the schema side, we’re evaluating Iceberg’s VARIANT type for the raw column. Today we store the payload as a JSON string, which works but requires parsing at query time. VARIANT provides native semi-structured data support with better query performance and the ability to push down predicates into the JSON structure. As our staging table grows, that efficiency gain becomes increasingly attractive.
If your team is dealing with similar ingestion sprawl, the core insight is that the staging layer should be a product, not an afterthought. Invest in the schema, the tracking, the retention model, and the dev/prod parity early. It pays rent every time you onboard a new data source.
- iceberg
- ingestion
- data-platform
- postgresql
- spark
