Building data analytics software: architecture, build vs buy and delivery

What data analytics software is made of

Whatever the product looks like on screen, the work underneath is the same five jobs. Each layer has a typical failure, and a check that catches it before a reader does:

There are two versions of this stack. Internal analytics serves employees, who read dashboards and explore data in a BI tool; a slow query is an annoyance. Analytics in a product, usually called embedded or customer-facing analytics, serves your customers inside your own application: many of them may open a dashboard at the same moment, a slow chart is a product defect, and one tenant seeing another tenant's data is a breach. Most of this guide applies to both. The sections on serving, engines and tenant security are where they part.

Build or buy: a BI tool, embedded analytics or custom

Decide per layer, not for the whole system. Connectors, the warehouse and the BI tool are commodity products that few teams should rebuild. The part worth building is the part your customers see and pay for. For the serving layer there are three realistic options:

Licensing often decides more than features do. Power BI, for example, has two embedding models. With embed for your customers (Microsoft also calls it "app owns data"), external users sign in to your application and need no Power BI licence; with embed for your organization, each user signs in with Microsoft Entra ID and needs a licence. Production embedding runs on a purchased capacity (as of September 2026, an A SKU under Power BI Embedded, a P or EM SKU under Power BI Premium, or an F SKU under Microsoft Fabric), so cost follows capacity rather than head count.

One rule keeps every option open: put metric definitions in a semantic layer or in version-controlled models below the BI tool, never only inside it. Then moving from a vendor's embedded dashboards to your own front end later means rebuilding charts, not re-deriving every number.

Get data in: batch loads, change data capture and streams

Ingestion copies data from where it is created to where it is analyzed, without slowing down the systems that created it. There are three patterns, and most platforms use all three:

• Batch loads with managed connectors, for SaaS applications, files and third-party APIs. Tools such as Airbyte and Fivetran extract and load on a schedule, and the transformation happens later in the warehouse (ELT). Airbyte's own documentation says this kind of replication is a poor fit when you care a lot about freshness and latency.
• Log-based change data capture (CDC), for your own databases. Debezium is a set of source connectors for Kafka Connect that reads each database's change log instead of polling its tables. Its documentation lists what that buys you: every change is captured, deletes included, no "last updated" column is needed, and for MySQL or PostgreSQL the delay is in the millisecond range.
• Event streams, for clicks, device readings and application events. Apache Kafka stores streams of events durably and routes them to other systems, so the warehouse and a real-time engine can read the same events.

CDC is worth the setup because the usual shortcut is worse. A nightly query for rows changed since yesterday misses deleted rows and rows whose timestamp nobody updated, and it loads the production database while it runs. The transaction log already records every committed change, because the database keeps it for recovery and replication; a CDC connector reads that record and turns each change into an event.

Land everything raw first. Keep an append-only copy of what each source sent, so a bug in a later layer can be fixed by replaying history instead of asking the source to send it again. Databricks calls this the bronze layer of a medallion architecture: bronze for raw ingested data, silver for cleaned and validated data, gold for modelled and aggregated data. Its documentation presents the pattern as a recommended practice, not a requirement.

Store it: a warehouse or a lakehouse

A data warehouse such as Snowflake or BigQuery stores data in its own managed format and provides SQL, security and elastic compute as one service. A lakehouse keeps data as open files in object storage such as Amazon S3, Azure Data Lake Storage or Google Cloud Storage, usually as Apache Parquet, a column-oriented file format with its own compression and encoding schemes (our guide to data compression algorithms explains how those encodings work). A table format on top makes the files behave like database tables.

Two open table formats are the common choices: Apache Iceberg and Delta Lake. Iceberg lets engines such as Spark, Trino, Flink, Presto, Hive and Impala work safely with the same tables at the same time, and its documentation lists schema changes that never rewrite the table, hidden partitioning, and time travel and rollback to earlier snapshots. Delta Lake adds ACID transactions, schema enforcement, time travel and merge, update and delete operations on top of data lakes.

Which one fits depends on volume, variety and how many engines need the data:

• Start with a warehouse for structured reporting at modest volume, run by a team that knows SQL. It is the shorter path to a first dashboard.
• Choose a lakehouse when data is large or mixed, when machine learning and BI read the same tables, or when you want to switch query engines without copying data.

The choice is less permanent than it used to be: Snowflake and Google BigQuery both appear among Iceberg's listed integrations, so a warehouse can read open tables too. For when data actually outgrows one database, and what per-scan pricing does to the bill, see big data analytics explained.

Choose an analytical engine for the workload

Analytical queries read many rows and few columns: revenue by month and region scans every order but touches three fields. ClickHouse's documentation explains why that favours a column-oriented database. A row store reads whole blocks of rows from disk, including columns the query never uses; a column store keeps each column's values together and reads only the columns the query needs. The same page contrasts analytical (OLAP) queries, which routinely process billions of rows, with transactional (OLTP) queries that read and write a few rows each.

DuckDB is worth knowing about even if you run a warehouse. It was designed to run embedded in the host process, with no database server to install or maintain (it has since added a remote protocol, Quack, for use beyond one process). It uses a columnar, vectorized execution engine, reads Parquet, and through DuckDB-Wasm runs in a web browser. Its documentation contrasts that engine with systems such as PostgreSQL, MySQL and SQLite, which process each row in turn.

For dashboards inside a product, Apache Pinot describes the workload directly: user-facing analytics, where every user of an application gets personalized analytics and the query load grows with the number of active users. Pinot was built at LinkedIn for that case, and combines columnar storage with indexing and pre-aggregation.

Pick by three questions: who runs the queries (analysts or every customer), how many run at once, and how fresh the answer must be. Internal BI on hourly data is a warehouse job. A dashboard in your product that every customer opens on Monday morning is a real-time OLAP job, even when the warehouse still does the modelling behind it.

Model and test the transformations

Raw data is not ready for analysis: the same customer appears under three IDs, currencies differ, and cancelled orders sit next to paid ones. Transformations fix that, and the usual target is a star schema. Microsoft's star schema guidance describes fact tables that store events or observations, such as sales orders, with numeric measures, and dimension tables that describe business entities such as products, people, places and dates. The dimension key values set a fact table's grain: one row per order line, per day, or per month and product.

Write transformations as code, not as logic hidden in a BI tool. With dbt, each model is a SQL SELECT in version control, and its tests sit beside it in YAML. dbt ships four generic data tests: unique, not_null, accepted_values and relationships. Unit tests, available since dbt 1.8, check a model's logic against small fixed inputs before it runs on real data, and dbt Labs recommends running them only in development and CI, since their inputs never change.

sources:
  - name: app
    config:
      freshness:
        warn_after: { count: 2, period: hour }
        error_after: { count: 6, period: hour }
      loaded_at_field: _loaded_at
    tables:
      - name: orders

models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: tenant_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_tenants')
                field: tenant_id
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: [placed, shipped, completed, returned]

Run the whole set on every pull request against a copy of the data, so a change that breaks a join or doubles a total fails in review instead of in front of the people reading the dashboard. Other transformation tools work the same way. What matters is that the checks run automatically and block the merge.

Define every metric once in a semantic layer

A semantic layer holds the definition of each business metric in one place: what counts as revenue, which orders are excluded, which date it is reported by. Tools ask the semantic layer for a metric and it generates the SQL. Without one, each dashboard, notebook and API re-implements the formula, and the copies drift apart.

Three common ways to do it, as of September 2026:

• The dbt Semantic Layer, powered by MetricFlow. Metrics are defined in the dbt project, and a change to a definition is picked up everywhere the metric is used. The hosted Semantic Layer and its APIs (GraphQL, JDBC and a Python SDK) require a dbt Starter or Enterprise plan. MetricFlow itself is under the Apache 2.0 licence from version 0.209.0.
• LookML in Looker, a modelling language in which each SQL expression is written once and Looker generates the queries from it. LookML projects are typically kept in Git.
• Cube, a semantic layer that serves one model through SQL, REST and GraphQL APIs, with embedding APIs for customer-facing analytics.

The semantic layer is also where a disagreement gets settled once. When finance and sales bring different revenue numbers to the same meeting, the fix is one agreed definition with a named owner, and the semantic layer makes it the only definition any dashboard, notebook or API can use. It is also what lets a language model answer data questions safely; our guide to generative AI for databases covers text-to-SQL built on top of it.

Serve it: dashboards, embedded analytics and data APIs

Internal users mostly need dashboards and a way to explore, which is what BI tools do well. Analytics inside a product is delivered in one of three ways:

• Signed iframe embeds. Your server signs a token that names the dashboard and the filter values the viewer is locked to, and the analytics tool renders the dashboard in an iframe. Metabase's static embedding works this way: Metabase loads the URL only with a JSON Web Token signed with a secret shared between your app and Metabase, and the token carries the locked parameter values. The same secret signs every static embed, so anyone who holds it can open all of them: keep it on your server. (As of September 2026, Metabase recommends its newer guest embedding over static embedding.)
• Vendor SDKs and embed APIs, such as Power BI's embed for your customers, where your back end requests an embed token for the report. When the token carries a user identity, that identity's row-level security applies to the data.
• Your own charts over a data API, when the design, the interactions or the speed have to be yours. The API can query the semantic layer (dbt's GraphQL and JDBC APIs, Cube's REST, GraphQL and SQL APIs) or read pre-aggregated tables directly.

Whichever you choose, treat the data API like any other product API: typed, versioned and paginated, with the tenant taken from the authenticated session. Never accept SQL, a table name or a tenant ID from the browser.

Keep tenants apart: row-level security and isolation

In a multi-tenant product, the worst analytics bug is not a wrong number. It is a right number shown to the wrong customer. The AWS SaaS Lens names three ways to lay out tenants:

• Silo: each tenant gets dedicated resources, such as its own database. Isolation is built into the layout, but every tenant adds resources to pay for and run.
• Pool: tenants share resources, which gives economies of scale; in shared tables every row carries a tenant ID, and isolation depends on a filter applied every single time.
• Bridge: a mix. The SaaS Lens gives a service's regulatory profile and noisy-neighbour behaviour as reasons to silo it while other parts stay pooled.

For a pooled design, enforce the filter in the database, not only in application code. PostgreSQL's row security policies do this: once row security is enabled on a table, every normal query must pass a policy, and with no policy at all the default is to deny every row. Two details catch teams out. Table owners bypass row security unless the table is set to FORCE ROW LEVEL SECURITY, and superusers and roles with BYPASSRLS always bypass it, so the application must connect as an ordinary role.

ALTER TABLE fct_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE fct_orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON fct_orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

/* Per request, inside a transaction, with the tenant from the verified session */
BEGIN;
SELECT set_config('app.tenant_id', '0b6d9f3e-4c1a-4e8b-9a57-2f1c8e7d6a10', true);
SELECT order_month, sum(amount) FROM fct_orders GROUP BY order_month;
COMMIT;

The last argument of set_config makes the setting apply only to the current transaction, which matters when a connection pool hands the same connection to the next request. Put together, each request carries the tenant from the verified session into its transaction, and the policy filters the shared table before any row leaves the database.

Warehouses and BI tools have the same control under other names. Snowflake uses row access policies, an Enterprise Edition feature as of September 2026. BigQuery's row-level access policies filter like a WHERE clause, and users who are not granted access see no rows at all. For Power BI, Microsoft's guidance to software vendors is one semantic model with dynamic row-level security for a moderate number of customers, and a separate workspace and semantic model per customer at the scale of thousands, with row-level security inside each.

Then test isolation like a feature: an automated test signs in as one tenant and asserts that every analytics endpoint, export and cached result returns nothing that belongs to another. Caches and pre-aggregations need the tenant in their key; Cube, for example, takes the tenant from a security context in its API token and can keep pre-aggregations per tenant. Broken access control is the first category in the OWASP Top 10:2025, and our secure coding checklist covers how to catch it in review.

Keep the numbers right: tests, contracts and freshness

Tests catch bad logic. Contracts and freshness checks catch bad inputs. Data quality needs all three:

• Data contracts are agreements between the team that produces a dataset and the teams that consume it: schema, quality rules, service levels and owners, written down where tools can check them. The Open Data Contract Standard (ODCS) defines such a contract in YAML. It is maintained by Bitol under the LF AI & Data Foundation, licensed Apache 2.0, and at version 3.2.0 as of September 2026.
• Model contracts protect the tables your dashboards read. In dbt, an enforced contract makes the build fail when a model's column names or data types differ from what is declared. Constraints are uneven across platforms: dbt's documentation shows Snowflake and BigQuery enforcing not_null but treating a primary key as metadata only, so uniqueness still needs a test.
• Freshness checks compare the newest load time with limits you set, warning after one age and failing after another (warn_after and error_after in dbt's freshness config). A dbt build does not run them, so they need their own schedule, and dbt Labs suggests checking at least twice as often as your tightest service level: every 30 minutes for data promised within an hour.

Lineage completes the picture: when a check fails, you need to know which dashboards read that table. OpenLineage is an open standard for collecting lineage metadata from running jobs, with integrations for Apache Airflow, Apache Spark, dbt and others.

Before retiring any old report, run old and new side by side and reconcile the key numbers with each metric's owner. A new platform that disagrees with the spreadsheet everyone trusts will be ignored, even when it is right.

Control performance and cost

Analytics cost comes from how much data each query reads and how long compute stays on. Both can be controlled:

On BigQuery's on-demand model, a query is billed by the bytes processed in the columns it selects, even when it has a LIMIT. Results served from the cache are not charged, and as of September 2026 the first 1 TiB each month is free. Partitioning lets BigQuery skip the partitions a filter excludes, which it calls pruning, and clustering sorts storage blocks so that filters on the clustered columns scan less.

Snowflake bills warehouses per second, with a 60-second minimum each time a warehouse starts or resumes. Auto-suspend is on by default, and a suspended warehouse uses no credits. Give dashboard queries and batch transformations separate warehouses, so neither waits for the other and each is sized for its own load.

Pre-aggregation moves work from read time to write time. ClickHouse's incremental materialized views run a query on each block of rows as it is inserted and write the result to a second, usually much smaller table, so dashboards read the summary instead of the raw events. Cube's pre-aggregations are materialized query results that Cube picks automatically when an incoming query matches one.

Deliver it in stages

Analytics projects stall when they try to model the whole company before anyone sees a number. Deliver one question end to end, then widen:

1. Pick one decision and its metrics. Name the people who will use it, the few metrics they need, and one owner for each metric.
2. Inventory the sources. For each metric, find the systems it comes from, who owns them and how fresh the data must be.
3. Land raw data. Set up ingestion into a raw layer, with freshness checks from the first day.
4. Model and test. Build the fact and dimension tables for that one area, with tests running in CI.
5. Define the metrics once. Put them in the semantic layer and reconcile them against today's reports with the metric owners.
6. Serve a first audience. Ship one dashboard or one embedded view to a pilot group. For a product, tenant-isolation tests pass before any customer sees it.
7. Widen and harden. Add the next subject area, cost alerts and lineage, and write the runbook for the team that will operate it.

Each stage ends with something a person can check, which keeps the project honest about its progress and lets you stop or change course with a working system in hand.

The two halves of this work map to two of our services. Our data platform service builds the ingestion, a governed warehouse or lakehouse and a semantic layer, engineered with tests, lineage and access control, so your own staff can run the platform afterwards. When the analytics sit inside a product you sell, custom software development covers the application side: the customer portal, the APIs behind it and tenant isolation. The same modelled data is also where intelligent applications start.