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

> How to build data analytics software: ingestion, a warehouse or lakehouse, tested models, a semantic layer, secure embedded dashboards and APIs, in stages.

- URL: https://computese.com/building-data-analytics-software/
- Author: Duong Quan Nguyen, CEO, Computese
- Published: 2019-09-24
- Updated: 2026-09-25
- Topics: Data, Custom software

## In short
- Data analytics software is five layers: ingestion, a warehouse or lakehouse, tested transformations, a semantic layer that defines each metric once, and dashboards, embedded analytics or APIs. Access control and quality checks run through all of them.
- Buy the commodity layers and build what your customers see. Keep metric definitions below the BI tool, so you can change the front end later without re-deriving every number.
- Choose the engine by who queries, how many at once and how fresh the answer must be: a cloud warehouse for internal BI, a real-time OLAP database such as ClickHouse or Apache Pinot for dashboards inside a product.
- In a multi-tenant product, enforce row-level security in the database, take the tenant only from the verified session or a signed token, and test isolation on every endpoint, export and cache.
- Deliver one question end to end first, with tests, data contracts and freshness checks, and reconcile the new numbers with the old reports before retiring them.

To build data analytics software, you assemble five layers: ingestion that copies data out of source systems, a warehouse or lakehouse that stores it, tested transformations that shape it, a semantic layer that defines each metric once, and dashboards, embedded analytics or APIs that serve it. Access control and data quality checks run through all five.

The same layers serve an internal reporting platform and an analytics feature inside a product you sell. What changes is who reads the numbers, how many of them query at once, and how strictly one customer's data must be kept from another's. This guide goes through the architecture layer by layer, then covers build versus buy, choosing an analytical engine, multi-tenant security, data quality, performance and cost, and the order to deliver it in.

## 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:

| Layer          | Its job                                               | Tools, as examples                              | What goes wrong without a check                    |
| -------------- | ----------------------------------------------------- | ----------------------------------------------- | -------------------------------------------------- |
| Ingestion      | Copy data from applications, SaaS APIs, files, events | Debezium, Apache Kafka, Airbyte, Fivetran       | A source changes shape or stops sending, unnoticed |
| Storage        | Keep raw and modelled data in one governed place      | Snowflake, BigQuery, Apache Iceberg, Delta Lake | Every query scans everything and the bill grows    |
| Transformation | Clean, join and model data into facts and dimensions  | dbt, SQL, Apache Spark                          | A change quietly breaks a total                    |
| Semantic layer | Define each metric once, with its filters and grain   | dbt Semantic Layer, LookML, Cube                | Two dashboards compute revenue two ways            |
| Serving        | Dashboards, embedded analytics and data APIs          | Power BI, Looker, Metabase, your own front end  | One customer sees another customer's rows          |

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:

| Option                                                               | Fits when                                                                   | You still own                                                         | Trade-off                                                                      |
| -------------------------------------------------------------------- | --------------------------------------------------------------------------- | --------------------------------------------------------------------- | ------------------------------------------------------------------------------ |
| A BI tool for internal users (Power BI, Looker, Metabase)            | Employees ask new questions every week and a standard look is fine          | The data model, metric definitions and access rules                   | Licences per user, and logic drifts into the tool unless metrics live below it |
| Embedded analytics from a vendor (Power BI Embedded, Metabase, Cube) | Customers need standard dashboards inside your product soon                 | Tenant isolation, the tokens your server signs, styling within limits | Capacity or usage pricing, and the vendor decides what the charts can do       |
| Custom charts over your own data API                                 | Analytics is the product: your interactions, high concurrency, special data | Everything: API, caching, permissions, exports, accessibility         | The most engineering, and it needs an owner and a budget after launch          |

Licensing often decides more than features do. Power BI, for example, has [two embedding models](https://learn.microsoft.com/en-us/power-bi/developer/embedded/embedded-analytics-power-bi). 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](https://docs.airbyte.com/platform) and [Fivetran](https://fivetran.com/docs/getting-started) 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](https://debezium.io/documentation/reference/stable/features.html) 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](https://kafka.apache.org/intro/) 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](https://computese.com/change-data-capture/) 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.

![An application server writes to a database. A connector reads the database's orange transaction log and sends change cards, one marking a deleted row, along a stream into a warehouse.](https://computese.com/images/blog/building-data-analytics-software/cdc.16b16a7efb-1536.webp)

*Reading the log captures every change, deletes included, without querying the tables the application depends on.*

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](https://computese.com/medallion-architecture/) instead of asking the source to send it again. Databricks calls this the bronze layer of a [medallion architecture](https://docs.databricks.com/aws/en/lakehouse/medallion): 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](https://parquet.apache.org/docs/overview/), a column-oriented file format with its own compression and encoding schemes (our guide to [data compression algorithms](https://computese.com/revolutionary-data-compression-algorithm/) 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](https://iceberg.apache.org/) 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](https://docs.delta.io/index.html) 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](https://computese.com/the-power-of-big-data-analytics/).

## 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](https://clickhouse.com/docs/get-started/about/intro) 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.

| Engine type                        | Examples                                         | Fits when                                                                   | Watch for                                                                  |
| ---------------------------------- | ------------------------------------------------ | --------------------------------------------------------------------------- | -------------------------------------------------------------------------- |
| Row-store database you already run | PostgreSQL                                       | Modest volumes and internal reports, run on a replica                       | Large scans compete with the application for the same database             |
| In-process columnar engine         | DuckDB                                           | Analysis inside a service, a job or a notebook, straight from Parquet files | Built to run inside one application; plan how the data is shared beyond it |
| Cloud data warehouse               | Snowflake, BigQuery                              | Many analysts and mixed workloads that need elastic compute                 | Cost follows query habits: compute time or bytes scanned                   |
| Real-time OLAP database            | ClickHouse, Apache Pinot                         | Dashboards inside a product: many users at once, fast answers, fresh events | Tables are designed around the queries, often with pre-aggregation         |
| Lakehouse query engines            | Spark, Trino or Flink over Iceberg or Delta Lake | Large or mixed data, several engines and machine learning on one copy       | More components to run and tune                                            |

[DuckDB](https://duckdb.org/why_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](https://docs.pinot.apache.org/) 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](https://computese.com/star-schema/). Microsoft's [star schema guidance](https://learn.microsoft.com/en-us/power-bi/guidance/star-schema) 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](https://docs.getdbt.com/docs/build/data-tests): `unique`, `not_null`, `accepted_values` and `relationships`. [Unit tests](https://docs.getdbt.com/docs/build/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.

```yaml
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](https://computese.com/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](https://docs.getdbt.com/docs/use-dbt-semantic-layer/dbt-sl)**, 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](https://docs.getdbt.com/docs/dbt-apis/sl-api-overview) (GraphQL, JDBC and a Python SDK) require a dbt Starter or Enterprise plan. [MetricFlow](https://github.com/dbt-labs/metricflow) itself is under the Apache 2.0 licence from version 0.209.0.
- **[LookML](https://docs.cloud.google.com/looker/docs/what-is-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](https://docs.cube.dev/reference)**, 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](https://computese.com/generative-ai-for-databases/) covers text-to-SQL built on top of it.

![One orange definition card sits on a stack of data tables and feeds arrows to a monitor, a laptop and a phone, and all three screens show the same bar chart.](https://computese.com/images/blog/building-data-analytics-software/metric.44d0a01f3a-1536.webp)

*When every tool asks the semantic layer, a definition changed once is changed everywhere.*

## 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](https://www.metabase.com/docs/latest/embedding/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](https://learn.microsoft.com/en-us/power-bi/developer/embedded/embedded-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](https://docs.aws.amazon.com/wellarchitected/latest/saas-lens/silo-pool-and-bridge-models.html) 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](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) 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.

```sql
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`](https://www.postgresql.org/docs/current/functions-admin.html) 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.

![Three laptops marked with a circle, a triangle and a square send queries through a server and an orange gate to one shared table; returning rows reach only the laptop with the matching shape.](https://computese.com/images/blog/building-data-analytics-software/tenants.9b4533f390-1536.webp)

*The database applies the tenant filter, so a forgotten WHERE clause in one query cannot leak another customer's rows.*

Warehouses and BI tools have the same control under other names. Snowflake uses [row access policies](https://docs.snowflake.com/en/user-guide/security-row-intro), an Enterprise Edition feature as of September 2026. BigQuery's [row-level access policies](https://docs.cloud.google.com/bigquery/docs/row-level-security-intro) 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.

> [!WARNING]
> Take the tenant ID only from the authenticated session or a token your server signed, never from a URL parameter, a request body or a filter value the browser sends. Metabase's documentation describes exactly this attack: a visitor edits an embed URL's filter to reach data they should not see, which is why the value is locked inside a signed token.

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](https://docs.cube.dev/embedding/multitenancy), 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](https://top10.owasp.org/2025/A01_2025-Broken_Access_Control/), and our [secure coding checklist](https://computese.com/best-practices-for-secure-coding/) 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](https://bitol-io.github.io/open-data-contract-standard/latest/home/) (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](https://docs.getdbt.com/docs/mesh/govern/model-contracts) 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](https://docs.getdbt.com/reference/resource-configs/freshness)). A `dbt build` does not run them, so they need their own schedule, and dbt Labs suggests [checking at least twice as often](https://docs.getdbt.com/docs/deploy/source-freshness) 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](https://openlineage.io/docs/) 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:

| Symptom                                  | Usual cause                                               | Fix                                                                          |
| ---------------------------------------- | --------------------------------------------------------- | ---------------------------------------------------------------------------- |
| The warehouse bill grows faster than use | Queries scan whole tables and every column                | Partition by date, cluster on common filters, select only the needed columns |
| Dashboards in the product slow at peak   | Every page view recomputes aggregates from raw events     | Pre-aggregate with materialized views or rollups the engine matches itself   |
| Compute billed overnight with no users   | Warehouses left running, batch and dashboards sharing one | Auto-suspend, and separate compute for batch jobs and user queries           |
| A cached chart shows the wrong numbers   | Cache keys without the tenant or the model version        | Include the tenant and model version in every cache key                      |

On BigQuery's on-demand model, a query is [billed by the bytes processed](https://cloud.google.com/bigquery/pricing) 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](https://docs.cloud.google.com/bigquery/docs/partitioned-tables) lets BigQuery skip the partitions a filter excludes, which it calls pruning, and [clustering](https://docs.cloud.google.com/bigquery/docs/clustered-tables) sorts storage blocks so that filters on the clustered columns scan less.

Snowflake [bills warehouses per second](https://docs.snowflake.com/en/user-guide/warehouses-overview), 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](https://clickhouse.com/docs/concepts/features/materialized-views/incremental-materialized-view) 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](https://docs.cube.dev/docs/pre-aggregations/using-pre-aggregations) are materialized query results that Cube picks automatically when an incoming query matches one.

> [!TIP]
> Set a budget alert on the warehouse on the first day. Analytics costs grow quietly, one scheduled query or new dashboard at a time, and an alert finds the problem while it is still small.

## 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](https://computese.com/services/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](https://computese.com/services/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](https://computese.com/artificial-intelligence-and-intelligent-apps/) start.

## Key terms
- **OLAP (online analytical processing)**: Queries that aggregate many rows, such as revenue by month and region, as opposed to OLTP (transactional) queries that read or write a few rows at a time.
- **Columnar storage**: Storing the values of each column together, so a query reads only the columns it uses. ClickHouse, BigQuery and Apache Parquet files store data this way.
- **Change data capture (CDC)**: Copying every insert, update and delete from a database as it happens. Log-based CDC, as in Debezium, reads the database's transaction log instead of querying its tables.
- **Lakehouse**: Data kept as open files in object storage, with a table format such as Apache Iceberg or Delta Lake that adds transactions, schema changes and time travel so the files behave like database tables.
- **Star schema**: A model of fact tables, which record events such as orders with numeric measures, joined to dimension tables that describe products, customers, places and dates.
- **Semantic layer**: The single place where business metrics are defined, with their filters and grain. Tools ask it for a metric, and it generates the SQL, so every dashboard computes the number the same way.
- **Embedded analytics**: Dashboards, charts and reports shown inside your own application, often to your customers, instead of in a separate BI tool.
- **Row-level security (RLS)**: A policy in the database or analytics tool that filters which rows each user or tenant can read, applied to every query rather than written into each one.
- **Data contract**: An agreement between the team that produces a dataset and the teams that use it: schema, quality rules, service levels and owners, written in a form tools can check.
- **Data freshness**: How old the newest data in a table is. A freshness check warns or fails when the last load is older than the limit the consumers were promised.

## Common questions

### What is data analytics software?

Software that turns operational data into answers. It collects data from applications and other sources, stores and models it, and serves metrics through dashboards, embedded views or APIs. It can be an internal platform for employees or an analytics feature inside a product your customers use.

### Should we build or buy our analytics?

Buy the layers that are commodities, such as connectors, the warehouse and often the BI tool, and build the part customers see when analytics is part of your product. A vendor's embedded analytics is the fastest way to show dashboards in your app; custom charts over your own data API give you full control of design, speed and cost, at the price of building and maintaining them.

### What is embedded analytics?

Embedded analytics puts dashboards and reports inside your own application, so users see their data without opening a separate BI tool. It is delivered as a signed iframe, a vendor SDK or your own charts over a data API, and in a multi-tenant product every query must be filtered to the viewer's tenant.

### Do we need a semantic layer?

You need one as soon as two tools, teams or customers read the same metric. A semantic layer such as the dbt Semantic Layer, LookML or Cube defines each metric once and generates the SQL, so dashboards, notebooks and APIs cannot drift apart.

### Is PostgreSQL enough for analytics?

For modest volumes and internal reports, often yes, ideally on a replica so reporting does not slow the application. When scans grow large or many customers open dashboards at once, move analytics to a column-oriented engine such as a cloud warehouse, ClickHouse or DuckDB, and keep PostgreSQL for transactions.

### How do we keep one customer from seeing another customer's data?

Store the tenant ID on every row, enforce row-level security in the database or analytics tool, and take the tenant only from the authenticated session or a token your server signs. Then test it: an automated check for every endpoint, export and cache that one tenant sees nothing of another's.

## Sources
1. [Power BI embedded analytics overview](https://learn.microsoft.com/en-us/power-bi/developer/embedded/embedded-analytics-power-bi), Microsoft Learn
2. [Data replication platform](https://docs.airbyte.com/platform), Airbyte
3. [Fivetran Documentation](https://fivetran.com/docs/getting-started), Fivetran
4. [Debezium Features](https://debezium.io/documentation/reference/stable/features.html), Debezium
5. [Introduction](https://kafka.apache.org/intro/), Apache Kafka
6. [What is the medallion lakehouse architecture?](https://docs.databricks.com/aws/en/lakehouse/medallion), Databricks
7. [Overview](https://parquet.apache.org/docs/overview/), Apache Parquet
8. [Apache Iceberg: the open table format for analytic datasets](https://iceberg.apache.org/), Apache Iceberg
9. [Welcome to the Delta Lake documentation](https://docs.delta.io/index.html), Delta Lake
10. [What is ClickHouse?](https://clickhouse.com/docs/get-started/about/intro), ClickHouse
11. [Why DuckDB](https://duckdb.org/why_duckdb), DuckDB
12. [Introduction](https://docs.pinot.apache.org/), Apache Pinot
13. [Understand star schema and the importance for Power BI](https://learn.microsoft.com/en-us/power-bi/guidance/star-schema), Microsoft Learn
14. [Add data tests to your DAG](https://docs.getdbt.com/docs/build/data-tests), dbt Labs
15. [Unit tests](https://docs.getdbt.com/docs/build/unit-tests), dbt Labs
16. [dbt Semantic Layer](https://docs.getdbt.com/docs/use-dbt-semantic-layer/dbt-sl), dbt Labs
17. [Semantic Layer APIs](https://docs.getdbt.com/docs/dbt-apis/sl-api-overview), dbt Labs
18. [MetricFlow](https://github.com/dbt-labs/metricflow), dbt Labs (GitHub)
19. [Introduction to LookML](https://docs.cloud.google.com/looker/docs/what-is-lookml), Google Cloud
20. [API reference](https://docs.cube.dev/reference), Cube
21. [Static embedding](https://www.metabase.com/docs/latest/embedding/static-embedding), Metabase
22. [Security in Power BI embedded analytics](https://learn.microsoft.com/en-us/power-bi/developer/embedded/embedded-row-level-security), Microsoft Learn
23. [Silo, Pool, and Bridge Models (SaaS Lens)](https://docs.aws.amazon.com/wellarchitected/latest/saas-lens/silo-pool-and-bridge-models.html), Amazon Web Services
24. [Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html), PostgreSQL Global Development Group
25. [System Administration Functions](https://www.postgresql.org/docs/current/functions-admin.html), PostgreSQL Global Development Group
26. [Understanding row access policies](https://docs.snowflake.com/en/user-guide/security-row-intro), Snowflake
27. [Introduction to BigQuery row-level security](https://docs.cloud.google.com/bigquery/docs/row-level-security-intro), Google Cloud
28. [Multitenancy](https://docs.cube.dev/embedding/multitenancy), Cube
29. [A01:2025 Broken Access Control](https://top10.owasp.org/2025/A01_2025-Broken_Access_Control/), OWASP Foundation
30. [Open Data Contract Standard (ODCS)](https://bitol-io.github.io/open-data-contract-standard/latest/home/), Bitol (LF AI & Data)
31. [Model contracts](https://docs.getdbt.com/docs/mesh/govern/model-contracts), dbt Labs
32. [freshness](https://docs.getdbt.com/reference/resource-configs/freshness), dbt Labs
33. [Source freshness](https://docs.getdbt.com/docs/deploy/source-freshness), dbt Labs
34. [About OpenLineage](https://openlineage.io/docs/), OpenLineage (LF AI & Data)
35. [BigQuery pricing](https://cloud.google.com/bigquery/pricing), Google Cloud
36. [Introduction to partitioned tables](https://docs.cloud.google.com/bigquery/docs/partitioned-tables), Google Cloud
37. [Introduction to clustered tables](https://docs.cloud.google.com/bigquery/docs/clustered-tables), Google Cloud
38. [Overview of warehouses](https://docs.snowflake.com/en/user-guide/warehouses-overview), Snowflake
39. [Incremental materialized view](https://clickhouse.com/docs/concepts/features/materialized-views/incremental-materialized-view), ClickHouse
40. [Using pre-aggregations](https://docs.cube.dev/docs/pre-aggregations/using-pre-aggregations), Cube