The tool is almost never the thing that is missing
A company gets to forty or eighty people and the reporting situation becomes untenable. Revenue lives in the billing system, signups live in the application database, pipeline lives in the CRM, and the weekly numbers come from a spreadsheet one person maintains by hand on Sunday night. Somebody proposes a warehouse, somebody else starts a trial of a business intelligence product, and six weeks later there are three dashboards, two of which disagree about how many customers the company has. The tooling was never the constraint. The constraint was that nobody decided who owns a number, and nothing in the stack forced that decision.
We build these for companies with real engineers and no data engineers. The engineers are good. They write SQL, run a container, read an API doc. What they do not have is fifteen hours a week for plumbing, and that fact should drive every choice here. A stack that suits a company with four analysts is wrong for this one: it gets abandoned in month five and the spreadsheet comes back.
You are probably here because
- Two dashboards disagree about how many customers you have, and both queries look correct when you read them.
- The weekly numbers still come out of a spreadsheet one person rebuilds by hand on Sunday night.
- Nobody can say what “active customer” means without an argument, so every review debates the number instead of the decision.
All three usually share one root cause — no number has a written definition with a person's name on it — which is what the section on the metrics layer below is about.
Budget the hours before you budget the dollars
Ask the question out loud, in the room, before any tool gets chosen: in twelve months, when this is boring, who spends how many hours a week keeping it running? Our planning figure for a company without a data team is four to six hours a week from one engineer who has other responsibilities. Not a full day. Not "we'll see." Four to six hours, and that person's name is on it.
That number is not pulled from nowhere. It is the maintenance a working engineer will absorb without resenting it or dropping it, and every steady-state cost has to fit inside it: credential rotations, upstream schema changes, a failed overnight load, one new question a week from finance, and the occasional "this number looks wrong" investigation. The last one is expensive; a single mismatch eats three hours if the lineage is unclear.
Once the hour budget is fixed, most architecture arguments resolve themselves. Fewer moving parts wins. Boring infrastructure wins. Anything that needs babysitting wins nothing. And any component whose failure mode is "quietly produces wrong numbers" is disqualified, because the four hours get spent finding out rather than fixing.
Where the first-pass hours actually go — our planning split
Planning split we use for a first internal stack. The two biggest lines are organizational, not technical.
Half that effort is access and definitions, and neither is solved by buying anything. Teams that get this working treat "who can grant read access to the billing account" as a week-one engineering task.
Five layers, and the smallest version of each
An internal analytics stack is five things: storage, ingest, transform, metric definitions, delivery. Scheduling holds them together but is not a layer of its own at this size. Almost every over-built stack we are asked to rescue bought a large product for a layer that needed a small one.
| Layer | Smallest thing that works | Upgrade trigger | You own regardless |
|---|---|---|---|
| Storage | A read replica of the application database, with an analytics schema | Scans over hundreds of millions of rows, or three-plus large external sources | The schema, and a tested export |
| Ingest | Managed connectors for third-party APIs, incremental SQL for your own database | More than a dozen sources, or a connector you keep patching | Watermark state and the backfill command |
| Transform | dbt with three layers of models and key tests | Model runtimes past the batch window, or genuine dependency branching | Every model file, in your repository |
| Metrics | One reviewed definition file, in version control | Two teams need different cuts of the same number | The definitions and their change log |
| Delivery | Self-hosted Metabase or Superset, plus a scheduled digest | Non-technical staff writing their own queries daily | The saved queries, exported |
The right-hand column is the important one. Vendors get acquired and repriced. What keeps you free is that the schema, the models, the definitions and the queries are text files in your repository a new engineer can read on day one.
Storage: you probably do not need a warehouse yet
The default answer for a company under a few hundred people is a read replica of your existing PostgreSQL database with a separate analytics schema, and it is better than it sounds. You already run it, you already back it up, your engineers already know it, and the join keys are correct because it is the same data model the application uses. Point the analytics tool at the replica, never the primary, with its own read-only role so a runaway dashboard query cannot compete with customer traffic.
Postgres holds up further than most people expect here. Our practical ceiling before it gets uncomfortable is roughly fifty to two hundred gigabytes of analytic data with fact tables in the tens of millions of rows, assuming you index for the queries you actually run and use materialized views for the expensive aggregates. Refresh those concurrently so readers are not blocked, which requires a unique index on the view. Read the query plans rather than guessing.
What ends the Postgres era is not total size. It is repeated full scans over columns you cannot index your way out of, plus concurrency from a BI tool that fires eleven queries when someone loads a dashboard. DuckDB is the underrated next step: an in-process column store that reads Parquet directly off object storage and runs inside the same Python job that produced the files, turning a scan that crawled in a row store into something interactive on one machine. Point it at an export and measure. It costs an afternoon to find out.
The managed columnar warehouses, BigQuery, Snowflake, Redshift, ClickHouse Cloud, are the right call when you have several large external sources, more than a couple of people querying all day, or a volume no single machine should hold. Know the cost model first. Pricing per terabyte scanned is fair until a dashboard set to auto-refresh every five minutes scans a large table three hundred times a day, and nobody notices until the invoice. Partition the big tables, ban select * in anything scheduled, and cap query cost on the BI service account before you hand out logins.
| Storage option | Fits when | Breaks when | Weekly upkeep |
|---|---|---|---|
| Read replica of app database | One primary source, data in the tens of gigabytes, engineers already fluent | Repeated large scans, or BI concurrency contends with the app | Near zero, it rides your existing operations |
| DuckDB over Parquet on object storage | Batch analytics on one machine, Python already in the loop, cost sensitivity | Many concurrent interactive users, or a need for fine-grained access control | Low, but you own file layout and compaction |
| Managed columnar warehouse | Several large sources, daily query load, more than two heavy users | Nobody watches spend, or every model is a full refresh | Moderate, mostly cost and access review |
Ingest: three source classes, three different problems
Your own application database. The easiest source and the one people get wrong most subtly. Pull incrementally on an updated_at column, store the watermark in a table you can query and edit, and give the extract an explicit window so a re-run is one command with two dates. Three traps. Hard deletes vanish unless you diff keys or turn on logical replication. Rows that mutate long after creation, subscriptions and orders especially, make an append-only copy drift from truth within weeks. And a non-monotonic updated_at drops records silently, so your first test should compare source counts to landed counts over a recent window.
Third-party APIs. Billing, CRM, support, ads, product analytics. Buy the connectors. Airbyte, Fivetran and Meltano exist because maintaining a Stripe or Salesforce extractor is a permanent job and the API changes on the vendor's schedule. Hand-write one only for a single endpoint you fully understand. The subtle issue here is restatement: a payment can be refunded or disputed weeks after it settles, so a revenue table has to be re-derived over a trailing window rather than appended. Pick the window deliberately and write down why.
Events from your own application. Here the schema is your responsibility and nobody else's. Version the event names, keep the property set typed and small, and treat an emitted event as an immutable fact rather than a mutable record. The failure pattern is a free-form JSON payload that different teams fill differently over eighteen months, after which the only honest answer to "how many users did X" is a two-day archaeology project.
Two rules cover all three. Land raw first, in an append-only schema with a load timestamp, and transform from there, so a transform bug is fixed by re-running the model instead of re-pulling six months of history through a rate-limited API. And make every extract idempotent for a window, so the answer to an overnight failure is to run it again rather than reason about what partially happened.
Transform: layer the models even at small scale
Use dbt unless you have a specific reason not to. It gives you three things that are tedious to build and easy to skip: a dependency graph nobody has to maintain, tests that run as part of the build, and documentation generated from the code rather than from memory. The alternative, a folder of SQL scripts run in a hand-maintained order, works until the order is wrong once.
Three layers, and keep the discipline. Staging models map one to one onto source tables, rename columns, cast types, and do nothing else, no joins and no business logic. Core models are the conformed entities the company argues about: customer, subscription, session, order. Marts are shaped for one question and may be denormalized. When someone asks why the customer count moved, this layering turns a two-hour hunt into a five-minute one, because exactly one model decides customer identity.
Tests are not optional at this size. They are the substitute for the analyst you do not have. Uniqueness and not-null on every key, referential tests between core models, accepted values on status columns, freshness on every source. Add the one most teams skip: a row-count-change threshold, so a load bringing four percent of yesterday's volume fails the build instead of quietly repainting the dashboards. Run dbt build in continuous integration on every pull request and require it to pass before merge.
Name things for the person reading them at 8am next March. Suffix timestamps with the zone, store everything in UTC and convert only at the presentation edge, and never ship a column called status_2. This SQL is read far more often than it is written, usually by someone checking whether a number can be trusted.
The metrics layer is where these projects actually die
Everything above is engineering, which your team can already do. What kills internal analytics is that two people write two correct queries and get two different answers, because "active customer" means one thing to whoever built the retention dashboard and another to whoever built the board deck. Nobody is wrong. There was never a definition to be wrong about.
The fix is unglamorous and it works: one definition file in the repository, reviewed like code. dbt's metric syntax, a semantic layer like Cube, or a plain YAML file a human reads, the format matters far less than the review. Each metric carries a name, an owner who is a person rather than a team, the grain, the filters, whether it is measured on event time or ingest time, what it excludes, and a dated change log.
The exclusions line is the one that saves you. Internal accounts, test transactions, refunded orders, employees, the enterprise pilot that is technically free, the API keys your own monitoring uses. Every company has five of these and every one is a reason two dashboards disagree. Write them into the definition, not into a WHERE clause somebody copied.
The change log matters for a different reason. A metric that changes silently invalidates every screenshot ever taken of it. When activation moves from seven days to fourteen, that is an announcement with a date attached, and the chart should mark it rather than show a mysterious step.
Send it over and we will tell you what we would change.
Email the two queries that return different numbers for the same metric, plus the source tables they read, to contact@precisionfederal.com. You get back a short written note naming the three things we would change and why. One business day. No charge, no meeting, no deck.
contact@precisionfederal.comOwn these regardless of which products you buy
- The metric definitions and their change log, in version control
- Every transform model as a file, not as saved state inside a vendor UI
- The watermark table and a backfill command that takes two dates
- A tested export of the raw landing schema, run at least once
- The saved queries and dashboard definitions, exported on a schedule
- Credentials in a secret manager with owners and expiry dates recorded
- A one-page runbook naming what to do when the morning numbers are missing
Delivery: fewer dashboards, more answers that arrive
Self-hosted Metabase or Superset covers the internal case well, and Looker Studio is a reasonable free start if your sources already sit in a supported warehouse. Any of them is adequate. What is not adequate is assuming that building a dashboard means someone will look at it.
Dashboards decay. Ours and everyone's. One built for a specific decision gets opened for two weeks, then only when something looks wrong, then not at all, and it is still running its queries every morning. Expect a real fraction of the first quarter's output to go unopened after ninety days, and instrument the BI tool so you know which ones. Delete the dead ones. Every dashboard you keep is a maintenance obligation with a pretty front end.
Push beats pull for the numbers that matter. A short Monday digest to email or a chat channel, generated by a query, carrying eight to fifteen numbers with their week-over-week change, gets read by people who never log into the BI tool. It also applies useful pressure: a number not worth putting in the digest may not be worth a dashboard tile. Link every number back to its model so "where does this come from" has a one-click answer.
Self-serve is a later stage, not a launch feature. When you get there, expose a curated mart with human-readable column names. Handing non-engineers the raw landing schema is how you get the fourth incompatible definition of revenue.
What breaks a no-data-team stack in year one — our ranking by frequency
Ranked by how often we find each one when we are called into an existing internal stack.
The seven-way failure list
- Pointing the BI tool at the production primary because the replica "wasn't ready yet."
- Alerting on job failure instead of on data freshness, so a job that succeeds with zero rows pages nobody.
- Business logic in the dashboard tool, where it cannot be tested, diffed, or reused.
- A backfill path that is a different script from the scheduled path.
- Copying every column of every table, including the ones you will have to explain in a security review.
- Local timestamps stored without a zone, discovered in March when a daily count comes out to twenty-five hours.
- Full refreshes of everything nightly, which works beautifully until the day it does not finish before the digest goes out.
Scheduling and alerting inside a four-hour budget
Start with a scheduled GitHub Actions workflow or a small always-on scheduler running an ordered dbt build after the extracts. That is enough for dozens of models with a linear dependency shape. Two caveats: scheduled runs can be delayed when the platform is busy, so nothing should depend on firing at exactly 04:00, and repositories that go inactive can have their schedules disabled, which is a surprising way to lose your numbers. Reach for Dagster, Airflow or Prefect when you have branching dependencies or per-asset retries, not before.
Alert on the state of the data, not the state of the job. The useful alert is "the orders mart has no rows newer than nine hours," because that is what a human would notice. Job-failure alerts miss the two worst cases, a job that succeeds having loaded nothing and a job that hangs without failing, so put an execution timeout on everything and a freshness check on every mart that feeds a decision. One channel, and the exact re-run command in the alert body.
Access, personal data, and your first security review
The analytics store is a copy of your most sensitive data in a place with looser access than the application, which is exactly what an auditor will observe. Handle it at extract time: exclude the columns you do not need and pseudonymize the ones you need only as identifiers. No password hash, full card data or raw support-ticket text belongs in a BI tool by default.
Four things a SOC 2 or ISO 27001 reviewer will ask about your warehouse
Who has read access, and when was that list last reviewed. What personal data is in there, under what retention. Whether a deletion request under GDPR or a customer contract propagates to the analytics copy and its backups, which is the one most internal stacks fail. And whether access is by named account rather than a shared credential in an environment file. Three roles answer most of it structurally: a loader that writes only to raw, a transformer that reads raw and writes marts, and a read-only role for the BI tool.
If you handle health data under HIPAA or card data under PCI, scope the design at the start: pulling a regulated column into the warehouse pulls the warehouse into scope with it. The cheapest control is not copying the data. The second cheapest is tokenizing at the boundary so the analytics side holds a reference rather than a value.
A thirty-day build that leaves something running
First Internal Stack — Thirty Days
Step five gets cut under time pressure, and it is the step that decides whether anybody trusts the result. Reconciliation means sitting with the person who maintains the spreadsheet today and explaining every difference between their number and yours until you agree which is right. Sometimes the spreadsheet wins, which is a good outcome: you found a bug before it reached a board deck. Skip it and the stack launches into an argument it never recovers from.
What this costs
The infrastructure is the small part. A read replica you already pay for, a BI tool that is free to self-host, a scheduler that ships with your source control, object storage measured in dollars. Managed connectors are usually the largest recurring line at this size, priced by row volume, and they are cheaper than an engineer maintaining extractors. On a managed warehouse, treat the first two months of spend as measurement and set a budget alert before the first login goes out.
The real cost is engineering time, and it is front-loaded: sixty to a hundred and twenty hours from nothing to a trustworthy first set of numbers, then the four to six hours a week you already budgeted. Anyone quoting substantially less has either not counted the access work or is planning to skip the definitions, and both bills arrive later with interest.
When to stop doing this yourself
Four signals say the no-data-team stage is over. Maintenance has quietly grown past a full day a week. Requests queue for more than a week and people are building their own extracts to route around the queue. You need experimentation or forecasting rather than reporting, which is a different skill set. Or analytics has become customer-facing, at which point it is a product with uptime commitments and per-tenant isolation and should be resourced like one.
Until then, a small stack maintained with discipline beats a large one maintained occasionally. We have taken over both kinds. The small ones we fix in a week.
Bottom line
Size the stack to the hours, not the ambition. Keep the data in the database you already run until it demonstrably hurts. Land raw, transform in layers, test the keys. Write the metric definitions down and review changes like code. Deliver a short digest people read instead of a wall of dashboards they do not. Alert on freshness, not on jobs. Do that and a company with no data team gets numbers it can defend, in a stack one engineer can carry.
Frequently asked questions
Usually not at first. A read replica of your application database with a dedicated analytics schema handles a surprising amount, roughly to the tens of gigabytes with fact tables in the tens of millions of rows. Move when repeated large scans or BI concurrency start hurting, and evaluate DuckDB on Parquet before assuming a managed warehouse is the only next step.
Plan on four to six hours a week from one engineer once it is running, plus sixty to a hundred and twenty hours to build the first trustworthy version. A design that cannot fit inside that weekly budget gets abandoned, and the spreadsheet it replaced comes back.
Almost always because there is no single written definition, so each query author made reasonable choices about exclusions, grain and time basis. Fix it with one metric definition file in version control carrying an owner, the exclusions, the time basis and a dated change log, then reconcile every number against its current source once before launch.
Buy them for third-party APIs. Maintaining an extractor for a billing or CRM platform is a permanent job, because the API changes on the vendor's schedule. Write your own only for your own database, where you control the schema, or for a single well-understood endpoint.
Data freshness and correctness rather than job status. A job that succeeds having loaded zero rows and a job that hangs without failing are both invisible to failure alerts. Put a freshness check on every table that feeds a decision, an execution timeout on every task, a row-count-change threshold in the build, and the exact re-run command in the alert body.