The workflow before#
44 Farms sells premium beef direct to customers, and its business runs on a Ruby on Rails application hosted on Heroku. Until this project, one Postgres database did two jobs. It took customer orders, and it answered every report, dashboard and ad hoc question the business asked. Those jobs don't mix well. A heavy sales or inventory report competed with live checkouts for the same database, so the team thought twice before running analysis during busy hours. Engineers fielded requests for one-off queries, and nobody wanted to be the person whose report slowed the store. The result was a ceiling on how much the business could learn from its own data.
What we built#
We split the two jobs apart. A pipeline now copies data from the store's Postgres database into a dedicated cloud data warehouse, and every report and analysis runs there. The store's database goes back to doing one thing: taking orders. Analysts get a place to ask any question, as often as they like, without a second thought about checkout.
A warehouse built for questions
Replication jobs copy the store's tables into the warehouse on a schedule, so analytics never touches the transactional database. We chose a separate warehouse over a read replica because a warehouse can be modeled and tuned for reporting, while a replica inherits the app's schema and its limits. Analysts point their tools at the warehouse and stop worrying about load.
Clean, tested reporting tables
Raw application tables aren't much use to a business user, so dbt models turn them into reporting tables for sales, customers and inventory. Each model carries tests that check the data before anyone reads it. We kept the models in version control so a change to a metric gets reviewed the same way a change to code does.
Pipelines that fail safely
Airflow runs the replication and modeling jobs in order and retries them when something breaks. We built every job to rerun cleanly without manual cleanup. A pipeline that needs an engineer to untangle a half-finished load before the morning report doesn't stay trusted for long.
How we delivered it
Two of our data engineers joined the 44 Farms engineering team and shipped alongside their developers, rather than handing over a finished system at the end. They put working practices in place along the way: version-controlled dbt models, review for data changes and CI/CD for deployment. The pipelines, conventions and deployment process belong to 44 Farms, and the in-house team runs them day to day.
Before and after#
| Measure | Before | After |
|---|---|---|
| Where reporting runs | Production Postgres database | Dedicated cloud warehouse |
| Reporting load on the store's database | Every report and ad hoc query | None |
| Data modeling | Ad hoc queries in the app database | Version-controlled dbt models with CI/CD |
What's next#
With clean, modeled data in a warehouse, 44 Farms is ready for the next layer: an analytics assistant that answers sales and inventory questions in plain English, straight from the warehouse. A merchandiser could ask which cuts sold best last month and get an answer without filing a request with engineering.
