Login
GitHub - eznix86/laravel-analytics: Data Build Tool the eloquent way
At work, I kept writing the same thing: a few analytics tables, a cron job to rebuild them, and the same SQL copy-pasted into three models. Change one definition, forget to update one copy, and suddenly two dashboards quietly disagree.
There’s `dbt`, which solves this problem for data teams, but it means bringing Python and a second toolchain into a Laravel project. So I tried the same idea in PHP.
An analytics model is an Eloquent model with one query on it:
```php
class Revenue extends Model implements AnalyticsModel
{
use Analytics;
public function computes(): Query
{
return $this->from(Order::class)
->where('status', '<>', 'cancelled')
->per('customer_id')
->measure('total', 'sum(amount)');
}
}
```
Then `php artisan analytics:sync` works out what depends on what and builds everything in the right order.
After that, it’s just Eloquent:
```php
Revenue::query()->where('total', '>', 1000)->get();
```
A few things it does:
* The `GROUP BY` comes from the dimensions you declare, so you never have to write them twice.
* it has Incremental, microbatch, and snapshot buildsthe, same ideas as dbt.
* Runs on PostgreSQL, MySQL, and SQLite with the same commands.
### What it does not do
Every model in a dependency chain has to use the same connection.
That means you can’t, for example, import a SQLite query directly into a PostgreSQL query. This could be solved with an import mechanism, and I am still thinking about a better way to make that work in an Eloquent-like way.
### Why not just use a query class with dependency injection?
A query class, like the action pattern in `App\Queries`, that you inject wherever you need it is perfectly fine.
If the aggregate is fast, you need live numbers, and you only have one or two of them, write the class and skip this package.
The issue is that it computes on every read and you have zero indexes.
Cache the query? Now you’re stuck dealing with stale data.
There’s another problem: each layer (CTEs, subqueries, etc.) gets re-run instead of being reused.
You can use query classes can be composable by calling each other, but a shared subquery is still recomputed inside every caller. This package composes by reference.
For example, you can have a `StgOrder` model representing a transformed version of the `Order` table. It gets built once, and the models that depend on it simply select from the finished table.
This package can append the rows that arrived since the last run, rebuild one day at a time, or keep one row per version with `valid_from` and `valid_to`.
This package will make a built table that can carry the indexes your read patterns need.
A helper like `Revenue::isStale()` can tell you when the data has passed its freshness window.
### Why not just write a job that rebuilds the table?
That’s essentially what the package does.
The difference is that the queries are reusable, and dependencies are propagated through the entire chain of downstream aggregates.
TLDR; You write reusable queries as a data person but in PHP.
Repo: https://github.com/eznix86/laravel-analytics
Read more about DBT: https://en.wikipedia.org/wiki/Data\_build\_tool
the real dbt guys: https://github.com/dbt-labs/dbt-core (for the curious folks)
[link] [comments]
![[link]](https://i.redd.it/7eaumgg2kenh1.png){kind=link}
![[link]](https://i.redd.it/amo0t46scdnh1.png){kind=link}
![[link]](https://i.redd.it/pko96mch5dnh1.jpeg){kind=link}
![[link]](https://i.redd.it/lbtzckfrxcnh1.jpeg){kind=link}