Nightly Reporting Without a Data Warehouse: A Self-Hosted F-Pulse Use Case

Most teams reach for a cloud warehouse the moment someone asks for a daily metrics email. It is the default answer, and for a large analytics org it is often the right one. But a lot of teams buying Snowflake or BigQuery are not running an analytics org. They are running one production Postgres, they need eight numbers every morning, and the honest requirement is "a scheduled job that computes some aggregates and tells us when something looks wrong."

That job does not need a warehouse. This post walks through what it does need, using F-Pulse OSS, and is deliberately built from connectors marked Certified in the app — Postgres, S3/MinIO, Slack and SMTP — so nothing here depends on a Beta path.

The situation

A B2B product with a few thousand accounts. Production data lives in Postgres. Every morning the team wants:

• Yesterday's signups, activations and churn, split by plan
• Revenue recognised yesterday, and the running month-to-date figure
• A flag when any number moves more than it plausibly should

They have been getting this from a hand-written Python script on a cron. It works until it doesn't: nobody notices when it fails silently, the numbers occasionally disagree with the app, and the one person who understands it is on holiday.

The instinct is to buy a warehouse and a BI tool. The cheaper and more honest fix is to make the existing job visible, testable and safe to change.

Why not just query production directly

Because eventually you will run a six-way join with three window functions against the same database that serves your customers, and you will find out how that ends.

Separating the reporting workload from the serving workload is the actual reason to build a pipeline. You do not need a warehouse for that separation — you need somewhere to land a daily snapshot and something to compute against it.

The shape of the pipeline

The whole thing is one scheduled F-Pulse pipeline. Each step below is a node on the canvas, and you can click any of them mid-run to see the actual rows passing through.

1. Extract — Source (Postgres)

Three reads: accounts, subscriptions, events. Each is incremental, filtered on an updated-at watermark so a nightly run moves the day's changes rather than the whole table.

2. Land the snapshot — File Sink (Parquet) → S3 Sink (MinIO)

Write each extract straight to Parquet in your own MinIO bucket, partitioned by date. This is the bronze layer if you like the vocabulary, and it is the single most valuable step in the pipeline: once yesterday's raw data is on disk, every downstream mistake is replayable without touching production again.

MinIO here is just S3 you happen to run. If you are already on AWS, the S3 connector is the same node.

3. Clean — Deduplicate and Filter

Deduplicate on (account_id, event_id) keeping the latest row handles the duplicate delivery that any at-least-once event pipeline produces. Filter drops internal test accounts, which is the single most common cause of "why is the number different from the app."

4. Gate — Data Quality

This is the step the Python script never had. The Data Quality node is a declarative rule-based row validator that splits failing rows into a dead-letter output rather than silently letting them through. Rules worth having on day one:

• account_id is never null
• plan is one of the known set
• mrr_cents is non-negative
• yesterday's row count is within a sane band of the trailing average

There is also a Validate node for asserting expectations on the dataset as a whole, where the correct behaviour is to fail the run rather than quarantine rows. Use Data Quality when bad rows should be set aside and the run should continue; use Validate when a bad dataset means the whole run is wrong and should stop.

That distinction is the difference between a pipeline that degrades loudly and one that quietly publishes nonsense.

5. Model — Transform, Join, Aggregate

Transform holds the SQL. The expression editor is schema-aware, so it knows the columns coming out of the upstream node and will complete them. Join brings subscriptions onto accounts; Aggregate produces the group-by rollups per plan per day.

Execution is a vectorised DuckDB engine running in-process, which is why this is viable without a warehouse: for the data volumes described here, the aggregation is not the bottleneck and never was. If you want the mechanics of that engine, we wrote them up separately in Visual ETL with DuckDB.

6. Publish — DB Sink (Postgres)

Write the finished marts to a separate reporting schema — or a separate Postgres instance if you want real isolation. Small, wide, already-aggregated tables. Point Metabase, Superset, a spreadsheet or a psql session at them; they are just tables.

7. Tell someone — Slack Notify / Email Sink

Post the day's numbers to a channel on success. More importantly, say something when the quality gate quarantines rows or the run fails. A report nobody reads is a minor problem; a broken report nobody knows is broken is a much larger one.

Scheduling and the parts you stop thinking about

Schedule it with cron syntax and give it a retry policy. Backfill is supported, which matters the first time you change a definition and need to recompute the last sixty days.

Run history is kept, so "when did this number change and what did the run look like" is answerable. That is the question the Python script could never answer.

Above the individual run, the Steward reliability layer watches the workspace rather than the job. It is read-only by construction — it never edits or executes your pipelines. What it catches is structural: two pipelines reading the same source into different destinations, orphaned tables, schema drift, a join that suddenly explodes in row count. It also escalates findings you keep ignoring rather than letting them scroll away. It ships in OSS.

What stays on your infrastructure

Everything. F-Pulse binds to 127.0.0.1 by default, so a fresh install is not reachable from your LAN until you explicitly set FPULSE_ALLOW_LAN=1. There is no telemetry and no phone-home. The AI assistant runs against local Ollama by default; it will use a cloud model only if you configure one and supply your own key.

For this use case that matters twice over. The data in question is customer revenue and account activity — exactly the category that makes a security review slow — and none of it crosses your network boundary.

When you should buy the warehouse instead

Honestly:

• Your data does not fit on one machine. Single-node DuckDB has a ceiling. If you are genuinely at terabyte scale with concurrent analyst workloads, buy the warehouse.
• You have many analysts querying concurrently. Warehouses handle concurrency and workload isolation properly. A Postgres reporting schema does not.
• You need a semantic layer and governed self-service across dozens of people. That is a different product category.
• Nobody will own the infrastructure. Self-hosting is cheaper in dollars and more expensive in attention. If no one will patch it, a managed service is the correct call.

The point is not that warehouses are wrong. It is that "we need daily metrics" and "we need a warehouse" are different statements, and a surprising number of teams are paying for the second because they assumed it was the only way to get the first.

Building it

F-Pulse OSS is Apache 2.0 and free. The Postgres, S3/MinIO, Slack and SMTP connectors used above are all in the shipped connector catalog — 66 connectors in the picker — and the nodes are all in the default palette.

git clone https://github.com/hybridyn/fpulse.git
cd fpulse
docker compose up -d

Then open the canvas and build the seven steps above. Get the stack locally.

---

Related: Building a Medallion Architecture ETL Pipeline for the layering vocabulary, and Data Pipeline Monitoring for what to alert on once this is running.