Legacy reporting means different things to different companies, but the pattern is almost always the same: a finance analyst maintains seventeen Excel files, each feeding another, none of them versioned; a developer runs a SQL query against the production database every Monday morning and pastes the output into a PowerPoint; the sales dashboard was built in Crystal Reports in 2014 and nobody knows how to change it. These are not edge cases. They are the norm for companies that grew before modern data tooling existed — and they describe an operational risk that is easy to underestimate until a key person leaves and takes the spreadsheet formulas with them.
Migrating to a modern BI stack does not mean ripping out everything at once. It means replacing fragile, person-dependent reporting with a structured, version-controlled, self-serve analytics layer that scales as your team grows. Here is how to do it without disrupting the business while you work.
Audit What You Have Before You Touch Anything
The migration instinct is to start building. Resist it. Spend two to three weeks cataloguing your existing reports first. You will almost certainly discover that 60–70% of the reports nobody has asked about in six months, and 20% of the remaining ones are duplicates of each other with slightly different numbers that nobody has reconciled.
A useful audit captures:
- What the report shows and which business decision it informs
- Who uses it, how often, and whether they could describe what it measures
- Where the underlying data comes from (which database, which API, which spreadsheet)
- How it is currently produced (manual SQL, Excel formula, Crystal Reports, etc.)
- Whether the output is trusted — does anyone verify it, or does it just get forwarded?
Group the findings into three buckets: keep and migrate (actively used, trusted, worth replicating), fix and migrate (used but broken or untrusted — the data model needs work before migration), and retire (no one will notice if it disappears). Most teams discover the retire bucket is larger than expected.
Choosing Your Modern BI Stack
A modern BI stack has three layers: a data warehouse (or lakehouse) for storage, a transformation layer for building clean data models, and a visualization layer for self-serve reporting. Each layer has well-established options; the goal is to pick a coherent set, not to optimize each layer independently.
| Layer | Common Choices | Good Default for Most Teams |
|---|---|---|
| Data warehouse | BigQuery, Snowflake, Redshift, DuckDB (small scale) | BigQuery (GCP) or Snowflake |
| Ingestion / ETL | Fivetran, Airbyte, dlt (open-source), custom pipelines | Fivetran for speed; Airbyte to reduce cost |
| Transformation | dbt Core (free), dbt Cloud, Dataform, Coalesce | dbt Core with a CI pipeline |
| Visualization | Metabase, Looker, Tableau, Power BI, Apache Superset | Metabase (fast, analyst-friendly, affordable) |
| Orchestration | Airflow, Prefect, Dagster, dbt Cloud Scheduler | dbt Cloud Scheduler for dbt-centric stacks |
The dbt + BigQuery/Snowflake + Metabase combination is the most commonly recommended starting stack for companies migrating from spreadsheet-based reporting. It is affordable ($500–$2,000/month for moderate usage), well-documented, and has the largest community of practitioners to draw support from. If your company is deeply invested in the Microsoft ecosystem, Power BI + Azure Synapse is the natural alternative.
Migration Strategy: Parallel Running, Not Big Bang
The worst BI migration approach is the big-bang cutover: build the new system in secret, then flip a switch and retire the old one on a Monday. Stakeholders do not trust numbers they have not seen build up over time, and the first discrepancy — even a legitimate one caused by a definition improvement — will create a crisis of confidence.
Run old and new systems in parallel for 4–8 weeks per report category. This means:
- Build the new version of a report in the modern stack
- Put both versions side by side for the team that uses it
- Investigate and document every discrepancy (some will be bugs; some will be intentional definition improvements)
- Get sign-off from the report owner that the new version is correct
- Retire the old version only after sign-off
This is slower but builds trust. The goal is for the stakeholders to feel like they chose to move to the new system, not that it was imposed on them.
Building Semantic Models: The Work That Makes Self-Serve Possible
The reason most legacy reporting systems require a developer to make changes is that the business logic is buried in SQL queries and spreadsheet formulas that only one person understands. The promise of modern BI is self-serve: an analyst or business user can build their own report without waiting for engineering. That only works if the semantic model — the layer that defines what "revenue," "active customer," and "churn" mean in SQL — is correct and accessible.
In dbt, semantic models are called metrics or semantic layer definitions. In Looker, they are LookML measures. In Metabase, they are segments and metrics defined by an admin. The specifics differ, but the principle is the same: encode business definitions once, in a version-controlled layer, so that every report built on top of it uses the same logic.
Getting these definitions right is the hardest part of the migration. Expect conversations with finance, sales, product, and operations teams to produce a single agreed definition of "monthly recurring revenue" or "30-day active user" that everyone will accept. These conversations surface assumptions that have been silently different across teams for years.
Self-Serve BI: What to Build vs What to Enable
A common mistake after migrating to a modern BI tool is building every existing report manually in the new tool. This recreates the same bottleneck with a different interface. The goal of self-serve BI is to give users the building blocks to answer their own questions.
What to build centrally (data team owns):
- Core marts (sales, product, finance) with clean, tested dbt models
- The 10–15 "source of truth" reports that the entire business references
- A well-documented data dictionary so users understand what each field means
What to enable users to build themselves:
- Ad-hoc exploration off the core marts
- Department-specific dashboards built by analysts embedded in each team
- Scheduled report exports for recurring operational needs
The transition to self-serve also requires training. A 2-hour internal workshop on how to use the BI tool, what data is available, and how to interpret the semantic model is often the difference between a tool that gets used and one that gets ignored.
Data Quality: The Migration Blocker Nobody Plans For
The most common reason BI migrations stall is data quality. When you start building warehouse models that aggregate data from multiple source systems, you discover inconsistencies that were hidden when each system was reported separately: customer IDs that do not match across CRM and billing, date formats that vary by region, currency fields without currency codes, and records that exist in one system but not another.
Budget explicitly for data quality work. A pragmatic approach:
- Add dbt tests (not_null, unique, accepted_values, referential integrity) to every core model from day one
- Create a data quality dashboard that surfaces failing tests and tracks improvement over time
- Assign ownership of data quality issues to the source system owners — it is their data, not the data team's
- Resist the temptation to patch data quality issues in transformation code rather than fixing the source
Teams that treat data quality as a technical afterthought find that stakeholders stop trusting the new BI system within months, and the migration fails socially even if it succeeded technically.
Frequently Asked Questions
How long does a BI migration typically take?
For a company with 20–100 existing reports across 3–5 source systems, a realistic timeline is 4–9 months for a full migration with parallel running and stakeholder sign-off at each stage. The bottleneck is rarely technical; it is the definition alignment and parallel validation process. Teams that try to rush this typically end up with a new tool that nobody trusts.
Should we migrate all reports at once or in phases?
Always in phases, starting with the reports that are least trusted or most painful to maintain. Winning a few quick wins in the first phase — delivering cleaner, faster versions of a few key reports — builds the internal support for the harder work in later phases. Finance and executive reporting usually come last because they are highest-stakes and require the most definition alignment.
Do we need a full-time data engineer to run a modern BI stack?
Not necessarily at the outset. A managed stack using Fivetran, dbt Cloud, and BigQuery or Snowflake can be operated by a senior data analyst with strong SQL skills. A data engineer becomes important for complex ingestion pipelines and performance tuning, but day-to-day maintenance of a well-built dbt project is within analyst skill range. Dedicated engineering headcount becomes worthwhile as data volume and pipeline complexity grow.
What if our source data is in an on-premise system that cannot connect to a cloud warehouse directly?
Fivetran has connectors for Oracle, SQL Server, SAP, and most on-premise databases. Airbyte (open-source) can be self-hosted inside your network, acting as the bridge between on-premise systems and your cloud warehouse. For more restrictive environments, an SFTP file export from the on-premise system can feed a cloud-side ingestion pipeline. It adds a step, but it is a solvable problem in virtually every enterprise network configuration.
Need a partner for this? Mexilet offers data engineering services and AI & analytics.
If your team is ready to retire the spreadsheets and manual SQL queries but is not sure where to start, book a free technical scoping call with Mexilet Technologies. We will map out your existing report inventory, recommend the right stack for your scale, and give you a realistic timeline and cost estimate before you commit to anything.