After a few years of live operations, a game or app company can be running tens of thousands of SQL reports. Every team wrote its own version of "paying users in the last 30 days." The subqueries look almost identical, sit in different files, and each one triggers its own scan of years of detail data. The cost compounds with every copy.
Nobody has the full inventory either. Which reports exist, which are still in use, which are duplicates of each other: that knowledge lives in the memory of two or three senior engineers.
This is where data pipeline automation earns its place. An agent scans the reporting assets, groups the repeated logic, ranks what is worth merging, and drafts the shared tables and task flows. A data engineer reviews the definitions, approves the changes, and owns the cutover.
Why does duplicate SQL get expensive quietly?
The same mechanism creates two different bills.
The first is compute. When a dozen reports each carry their own copy of the paying-user subquery, the warehouse evaluates that logic a dozen times. Every open triggers a full scan of the detail layer, so reports that once returned in seconds degrade to minutes, and some time out. Your dashboard users feel this months before anyone traces it to a line item.
The second is drift. Each copy makes its own small choices about event filters, time zones, refund handling, and which user key to join on. Two reports share a label while counting different populations. Cross-project analysis then compares numbers that match by name and disagree by SQL.
Sprawl grows quietly because no single person owns it. A team knows its own dashboards. It has no view of what four other teams built last quarter, so the duplicate computation stays spread across files, schedules, and individual memory.
How does data pipeline automation build an inventory you did not write by hand?
The first useful move is an asset scan across every SQL report definition in the project. It starts with one sentence in a team channel: scan the SQL reports, find duplicate query logic, and suggest shared intermediate tables. The agent pulls the definitions, detects repeated CTE and subquery patterns, groups the related ones, and hands back candidates.
That changes the unit of work. Your engineer reviews groups of repeated logic in one pass, with reference counts and computation characteristics attached, and decides whether the grouped queries actually mean the same thing.
Take the paying-user example. The scan finds a dozen reports carrying versions of the same subquery and groups them. It surfaces neighboring patterns, such as daily active users, as their own groups. Then the engineer works through the questions that a machine should not answer alone:
- Which payment events qualify?
- Which date field defines the 30-day window?
- Which user identifier joins to downstream tables?
- Are refunds, test accounts, and regional filters handled the same way in each copy?
Those questions are the governance control. A repeated pattern is sometimes one shared metric and sometimes three metrics that happen to rhyme. The scan produces the candidate set. The engineer owns the interpretation.
Merge candidates get ranked by how many reports reference the pattern and how heavy the computation is, so the expensive and widely used logic surfaces first. The scan also leaves behind something the team never had: an inventory showing which reports carry repeated logic, which intermediate tables already exist, and where two teams built the same thing without knowing.
How does data pipeline automation merge repeated logic into shared tables?
Once the definition is settled, the repeated logic moves into an intermediate table that is built once and read by many.
The shape is three layers. A cleaned detail layer holds event and business records. A shared intermediate layer holds the reusable result, in this case the 30-day paying-user table. The reporting and tag layer reads that prepared output, so a dozen report definitions stop carrying their own copy of the subquery.
The agent drafts the table SQL, prepares the task flow that connects the layers, and lists the report definitions whose source needs to change. Your engineer reads the generated SQL, checks row-level results against the current reports, confirms the date and payment rules, and approves. The table gets created under that approval, and the engineer owns the cutover to the new source.
The approval step is doing real work here. One shared table can sit under many downstream assets, so joins, filters, refresh timing, and field meaning all need a human read before business teams are told to switch. A shared table becomes a governed definition only after it passes that review.
Done by hand, this same sequence means finding the duplicate reports, reading every query, building tables, configuring flows, and chasing teams to change their source. It takes days to weeks, and it runs on whichever senior engineer remembers how the assets connect.
Why should nightly tag jobs stop recomputing history?
User tags, things like cumulative spend or active days, are usually computed as a full refresh: recalculate from launch day through today, every night. The job grows with the title.
One title in its third year saw daily tag computation grow from 30 minutes to 4 hours. Nothing was wrong with the SQL. The job was simply rereading a longer history every night to produce a number it already had most of.
Cumulative spend shows why that is avoidable. Yesterday's cumulative value already contains the history. Add today's new payments and you have today's total:
yesterday's cumulative total + today's increment = today's cumulative total
So the agent reads the existing tag SQL and drafts an incremental design that starts from the prior output, adds the current day, and comes with the generated SQL and a scheduled job for review. Active days follow the same shape, appending the current day to the existing user state.
Your engineer reviews the edge cases before any of it ships: late-arriving payments, duplicate events, corrections, and the first run that establishes the starting state. They confirm the schedule and the downstream dependencies, then approve.
What the agent drafts, and what your engineer owns
The split is the whole point, so it is worth stating plainly.
The agent scans report definitions, detects repeated patterns, groups and ranks merge candidates, drafts intermediate-table SQL and the task flow between layers, designs incremental computation for tags that suit it, and flags new duplicate logic as it appears.
The engineer decides whether grouped queries share a business definition, checks joins, filters, keys, time windows, and refresh behavior, approves table creation and SQL changes, validates results against current outputs, and owns the cutover and the rollback call.
This addresses a specific risk. When several people build tables independently, metric definitions drift. An agent is good at finding assets that look alike. Establishing what a metric means is a judgment that needs an accountable owner, which is why high-risk operations sit in a confirmation queue, and why lineage and change logs exist to show what was approved and what depends on it.
How does the loop stay useful after the first cleanup?
The first scan produces an inventory. Repeated scans keep it true.
A report written three months from now will carry a familiar payer CTE. The agent flags the repeated pattern, points at the existing intermediate table, and the engineer decides whether the new report should read the shared asset. When two teams build similar intermediate tables, lineage exposes the dependency paths so the merge can be reviewed properly.
That turns governance into an operating cycle: scan, group, review, draft, approve, validate, rescan. Automation supplies the coverage and the drafts. Engineering supplies the approval and the ownership.
Get the whole white paper
This article is one use case pulled out of the Agentic Engine white paper, where data development and governance is chapter 2. The chapter carries what a blog post has to leave out: the merge-priority ranking method, the implementation thresholds, and the production cutover workflow, plus a real customer case with what changed and what it cost.
Fourteen chapters cover the same ground for user acquisition, live operations, monetization, data engineering, and player support teams. Every one of them keeps a person at the approval step.
