Clear thinking on data and systems.

A warehouse is only useful when its metrics mean something.

Connecting a source system to a warehouse is often the easy part. The harder work is deciding what a customer, an active account, or net margin means across the business, and keeping those definitions consistent as new data arrives. Without that modeling layer, a warehouse can reproduce the same disagreements in a new place.

The Root Problem: Ingestion Without Semantic Modeling

Extracting data is only one part of data engineering. A warehouse becomes useful when technical records are translated into shared business definitions. If sales defines an "active customer" as anyone with an open invoice while finance uses cash received within 90 days, teams will struggle to trust a dashboard showing both as the same measure.

A useful separation of concerns

A practical warehouse often separates source cleanup, reusable business logic, and reporting models. The exact structure should fit the team's tools and operating needs; the underlying aim is to make each step understandable and maintainable.

• Layer 1
  Staging (Raw to Typed)

  Source tables are named consistently, cast to useful data types, and checked for issues such as duplicates. Keeping this layer close to the source makes later transformations easier to reason about.

• Layer 2
  Intermediate Marts (Entities & Logic)

  This is where sources can be connected into useful business entities, such as a customer model spanning commerce, CRM, and billing records. Metrics like gross margin or churn can then be defined in tested, reviewable logic.

• Layer 3
  Reporting Presentation (Decision Interfaces)

  Reporting models present the measures and dimensions needed by dashboards and recurring briefs. The goal is to give analysts and business users a stable, documented layer without requiring them to reconstruct logic from raw tables.

The Takeaway for Leadership

Before adding more dashboards, agree on the definitions behind the metrics people rely on. Clear models make visualisations easier to trust, change, and explain.

Search, retrieval, or RAG? Start with the question.

Retrieval-augmented generation (RAG) is useful when a system needs to find relevant material and synthesize an answer from it. But it is not the default choice for every search or knowledge problem. The right design depends on what users are asking, what the source material looks like, and how precise the answer needs to be.

What RAG Does Well (and Where It Fails)

RAG excels when your team needs to synthesize nuance across diverse, unstructured prose documents, such as summarizing customer dispute histories, comparing clauses across supplier contracts, or querying complex internal engineering wikis.

For quantitative questions (such as units sold by product and region), use a structured query against a well-defined data model. Vector retrieval finds semantically related material; it does not calculate reliable aggregates from transactional records by itself.

The Decision Heuristic

Before commissioning an AI retrieval pipeline, run your use case through this simple test:

• Option A
  Structured SQL / Relational Query

  If the answer depends on rows, dates, prices, or transaction states, use a structured query or dashboard. This keeps calculations explicit and reproducible, with data quality still dependent on the source and model.

• Option B
  Lexical / Full-Text Search (Postgres FTS / Elasticsearch)

  If users need exact part numbers, legal names, error codes, or phrases, lexical search can be a simpler and more predictable fit than vector retrieval.

• Option C
  Hybrid RAG (Vector + Keyword + LLM Synthesis)

  When a question calls for synthesis across policies or documents, a hybrid approach can retrieve relevant passages and draft a response with source references for review.

The Engineering Reality

Many useful systems combine these methods: structured queries for figures, lexical search for exact terms, and language models to help synthesize relevant text. Treat AI as one component of a system, not a substitute for choosing the right retrieval and data design.

Read more about our AI engineering practice