Ever hear the phrase "data rich, insight poor"? It describes a company that records thousands of orders, tickets and shipments a day and still can't say, on a Tuesday morning, which region's margin moved and why. If that's your company, the way out is a sequence, and the first step needs no new infrastructure.
Data visibility comes down to three answers you can give about any number in a report. Where did it come from, what was done to it on the way, and who is allowed to see it. Organizations that have good visibility get there in a predictable order. They stop reporting straight off the systems people work in, they gather the sources into one place with one set of definitions, and they record lineage automatically so "how was this transformed?" is a query rather than a meeting.
What poor visibility looks like from the inside
The month-end version is familiar. Finance pulls from the ERP, sales pulls from the CRM, customer service exports from its ticketing tool, and someone spends the first week of the month reconciling four spreadsheets that use three different definitions of "active customer". Each department ends up with its own numbers, built on its own assumptions, and the meeting where they're compared is spent arguing about whose is right.
None of that is a tooling failure in any one system. The ERP is doing its job. The problem is that nothing sits across the systems, so every cross-functional question gets answered by hand, late, and slightly differently each time.
There are two well-worn ways out, and one in between that's often skipped.
Route one: point a reporting tool at the database
The quickest start is connecting Power BI, Tableau or a similar tool straight to the application's own database. No new infrastructure, a short timeline, and the cost is the licence plus the report building. If you later outgrow it, the reports you built tell you exactly which questions people ask, which is the best possible brief for a warehouse.
The costs show up in four places.
Performance. The database is serving your application's reads and writes. A report that scans a year of orders competes with the checkout page for the same CPU, memory and locks. The symptom is intermittent slowness that engineers struggle to reproduce, because it only happens when somebody in finance hits refresh. You can see it directly. On PostgreSQL 13 or later, with the pg_stat_statements extension enabled, this lists the statements consuming the most time, and the reporting tool's queries are usually easy to spot by their shape or their database role:
SELECT calls,
round(total_exec_time::numeric / 1000, 1) AS total_seconds,
round(mean_exec_time::numeric, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
On SQL Server the equivalent starting point is sys.dm_exec_query_stats joined to sys.dm_exec_sql_text.
It only sees one system. If the database holds orders but not support tickets or marketing spend, organization-wide visibility isn't possible from it, however good the reports are.
Schema changes break reports. Application teams rename and split tables as the product grows. Every report written against those tables breaks with them, usually discovered by the person presenting the report.
Too many people near sensitive data. Reporting straight off the transactional database means more people need access to it, and more of the application's raw data passes through report builders' hands. For regulated data such as health records, that can conflict with who compliance says may see what.
The step in between: a read replica
Before building anything new, most managed databases will give you a read-only copy that stays in sync. AWS RDS and Aurora have read replicas, Azure SQL Database has readable secondaries you reach by adding ApplicationIntent=ReadOnly to the connection string on the tiers that support read scale-out, and PostgreSQL does it natively with streaming replication to a hot standby.
Pointing reports at the replica fixes the performance problem cleanly. The application's primary never sees a reporting query again.
It doesn't fix the other three. The replica has the same schema, so changes still break reports. It still holds one system's data. And it holds all of it, so the access question is unchanged. Replication is also asynchronous in most of these setups, which means reports can lag the primary by seconds, more under heavy write load.
One PostgreSQL detail catches teams out. A long reporting query on a hot standby can be cancelled with canceling statement due to conflict with recovery, because the standby needs to apply changes the query is still reading. Raising max_standby_streaming_delay or turning on hot_standby_feedback stops the cancellations, at the cost of more replica lag in the first case and table bloat on the primary in the second. Know which you're choosing before a board report fails at 8am.
A replica is the right answer when the pain is speed and the data lives in one system. It's a stopgap when the pain is the month-end reconciliation, because that pain comes from having several systems.
Route two: a warehouse or lakehouse, then reporting on top
The longer route copies data out of every source into one place built for analysis, reshapes it there into tables with agreed definitions, and points the reporting tools at that. Today that place is usually a cloud warehouse or lakehouse such as Databricks, Snowflake, BigQuery or Microsoft Fabric rather than a database server you size and run yourself.
What it gets you:
- Every source in one place. ERP, CRM, support, finance, partner and vendor feeds, joined on keys you control. "Active customer" gets defined once.
- Applications left alone. Reporting load lands on separate compute, so the application's database never sees it.
- Insulation from schema changes. The pipeline absorbs a renamed column in the source, and reports keep reading the tables you published.
- Access you can reason about. Fewer people need the raw data. Sensitive columns can be masked for most readers. Unity Catalog does it with column masks and row filters, Snowflake with masking policies.
What it costs:
- Time to build. You won't have reports in week one. If the business needs numbers now, start with route one or a replica and build the warehouse alongside.
- Load windows and freshness. Copying large volumes takes time and compute. Change data capture (CDC), which reads the source database's change log rather than re-copying whole tables, shortens the window, but it's more to set up and operate.
- Bad source data travels. A warehouse faithfully copies whatever the source got wrong, and it can do so for years before anyone notices. Freshness and quality checks on the tables people rely on are part of the build. Our post on a pipeline that ran fine on three-day-old data covers what to watch.
- Running cost. Pipelines, storage, compute and someone who owns them. Cloud platforms bill by use, so the cost tracks how much you load and query rather than a server you bought.
Seeing how data was transformed
Organizations get visibility into how data is transformed through lineage, recorded by the platform rather than drawn by hand.
Lineage is the record that table B was built from tables A and C by a particular job or query. When it's captured automatically, you can answer "where does this dashboard number come from?" and "what breaks if I change this column?" without asking the person who built the pipeline, who may have left.
On Databricks, Unity Catalog captures lineage for queries and jobs it governs and exposes it as system tables. system.access.table_lineage carries source_table_full_name and target_table_full_name for each read and write, so listing everything that feeds a given table is a short query:
SELECT DISTINCT source_table_full_name, entity_type, created_by
FROM system.access.table_lineage
WHERE target_table_full_name = 'finance.reporting.monthly_margin'
AND source_table_full_name IS NOT NULL
AND event_time > current_date() - INTERVAL 90 DAYS;
Swap the two column names and the same query answers the other question, which downstream tables and dashboards would feel a change. Column-level lineage lives alongside it in system.access.column_lineage. Outside Databricks, dbt generates a lineage graph from the ref() calls in its models, and OpenLineage is the open standard several orchestrators emit, so one tool can collect lineage from many.
Lineage only covers what runs on the platform. A spreadsheet someone downloads, edits and emails is invisible to it, which is one more reason to get reporting off exports and onto governed tables.
Choosing a route
Answer these in order.
- Is the pain speed, or disagreement? Slow apps when reports run point to a replica. Departments with different numbers point to a warehouse, because the disagreement comes from separate sources and separate definitions.
- How many systems does one important report need? One, and the replica may be enough for a long time. Two or more, and you're reconciling by hand until there's a shared place to join them.
- Who must not see what? If compliance limits access to fields, a warehouse with masking is far easier to defend than broad access to the production database.
- Can you live with yesterday's numbers? Nightly loads are cheap and simple. Minutes-fresh data needs CDC or streaming and costs more to run.
- Who will own it? A warehouse without an owner drifts. If nobody on the team can take it, plan for that before building it.
A reasonable path for many companies is a replica this quarter to stop reports slowing the application, and a warehouse for the two or three reports leadership argues about most, built properly with lineage and access controls from the first table. Our data engineering work starts there, and if you'd like a second opinion on which route fits, talk to us.