Data Warehousing and Dimensional Modeling Questions

Direct answer

Do not force one pipeline to serve both speeds: build a fast path (streaming ingestion feeding a cache or pre-aggregated store) that answers "what is happening right now, approximately" in under a minute, and a separate slow path (capturing changes from the source systems and loading them into a partitioned star schema, with the customer dimension modeled as a Type 2 slowly changing dimension, SCD Type 2) that answers "what exactly happened, fully accurate, for as far back as we keep history." Let the two paths deliberately diverge on freshness and precision, and reconcile them on a schedule rather than trying to make the fast path fully correct or the slow path instantly fresh.

Structured elaboration

Streaming ingestion and the fast path. Events land in a stream, get aggregated in near-real time (windowed counts or sums), and are written to a low-latency store (an in-memory or key-value cache) that the operational dashboard reads directly. This path is allowed to be approximate: late-arriving events, minor double-counts during a brief window, or a slightly stale cache are acceptable trade-offs for staying under the latency budget.

Change-data-capture (CDC), the extract-transform-load / extract-load-transform (ETL/ELT) split, and the slow path. The same events (or a CDC stream off the systems of record) also flow into a batch ETL/ELT job that lands them in the warehouse's partitioned star schema. Because this path is not on the clock the way the fast path is, it can afford to do the work that produces a fully correct answer: deduplicate properly, apply the customer dimension's SCD Type 2 logic so a historical query joins to the customer attributes that were actually true at the time of the event, and handle late-arriving data by reprocessing the specific partitions it affects rather than the whole table.

Storage choices, partitioning, and materialized views. The warehouse side partitions the fact table by event date so a historical query only scans the date range it actually needs, and materialized views or pre-aggregated tables sit in front of the fact table for the recurring monthly/yearly rollups the business intelligence (BI) side asks for repeatedly, so those queries are not recomputed from raw rows every time.

Monitoring. Track two different signals for the two paths: freshness lag for the fast path (how far behind real-time is the cache), and reconciliation drift for the slow path (how much the batch-computed totals differ from what the fast path reported for the same window, once the batch numbers are final). A growing reconciliation drift is the signal that something in the fast path's approximation logic has drifted from reality, not just noise to suppress.

Where the two paths deliberately diverge. The fast path answers "roughly how many, right now" using data that has not been fully deduplicated or joined against the SCD Type 2 dimension as of the correct historical moment. The slow path answers "exactly how many, as of last night" using data that has. Trying to make the fast path fully accurate defeats its latency purpose (correct deduplication and point-in-time dimension joins take longer than the latency budget allows); trying to make the slow path instantly fresh defeats its correctness purpose (a batch job that reruns every few seconds to stay fresh does not have time to do the reconciliation work that makes it trustworthy). The two paths are allowed to disagree by a small, monitored, explainable amount at any given moment, and the dashboard should say so (for example, labeling near-real-time numbers as "preliminary, subject to revision") rather than presenting both as equally authoritative.

flowchart LR
  EVT[Event stream] --> FAST[Fast path: streaming aggregation and cache]
  EVT --> SLOW[Slow path: batch ETL into warehouse]
  FAST --> RTDASH[Near-real-time dashboard, under 60s]
  SLOW --> STARSCD[(Star schema with SCD Type 2 customer dim)]
  STARSCD --> HISTDASH[Historical BI, point-in-time accurate]
  STARSCD -->|reconciles counts with| FAST

Worked example

Suppose the 60-second latency budget for the fast path breaks down as: 5s for the event to reach the stream processor, 10s for a windowed aggregation to close, 5s for the result to land in the cache, and up to 40s of dashboard polling interval before a user's screen refreshes. That accounts for the full budget without leaving room for a point-in-time SCD Type 2 join, which typically costs tens of seconds to minutes at scale once you include locating the dimension row that was valid at the exact event timestamp; this is the concrete, numeric reason the fast path uses a simpler, pre-joined or denormalized "current customer state" lookup instead of the fully accurate historical join, and defers the fully accurate join to the slow path where there is no such budget.

Trade-offs and pitfalls

The most damaging mistake is presenting the fast path's numbers with the same visual authority as the slow path's, so a user has no way to know a "live" revenue figure is preliminary and may be revised downward once the batch job reconciles it, which erodes trust in the dashboard the first time the two visibly disagree. A second common mistake is trying to eliminate the fast path entirely once the batch path exists, "because it is more accurate," without realizing that a 60-second latency requirement simply cannot be met by a pipeline whose correctness guarantees require minutes of processing; the two paths exist because they are solving genuinely different problems, not because one is a worse version of the other.

Direct answer

For a warehouse with a small number of very large tenants and many small ones, a shared schema with a tenant_id column on every fact and dimension is usually the right default, because schema-per-tenant and database-per-tenant both multiply your operational burden (migrations, backups, monitoring) by the tenant count. The one addition the shared-schema model needs is a sharding strategy for the largest tenant specifically: hash the tenant's own row keys into sub-buckets so its data spreads across many partitions instead of dominating one, which is what actually prevents the hotspot a single tenant_id partition would otherwise create.

Structured elaboration

Compare the three models directly:

Recommended approach and the matching partitioning/sharding strategy. Shared schema with tenant_id on every table wins on cost and operability, which dominate at 100k tenants where per-tenant provisioning of anything becomes its own maintenance job. To fix its weak point, the query-performance and hotspot risk from the skewed tenant, do not partition purely by tenant_id: for the dominant tenant, further hash its own primary or event key into a fixed number of sub-buckets (16, in the worked example below) so its rows spread across many partitions instead of piling into one. Small tenants do not need this treatment; a plain tenant_id partition is already small and well-isolated for them.

Isolation. Because the shared-schema model does not get isolation for free, enforce tenant scoping at the query layer (a mandatory tenant_id predicate that cannot be bypassed) or with native row-level security so a bug in one report cannot leak another tenant's rows, and treat any query missing that predicate as a defect, not a performance shortcut.

Operability: backups and schema migrations. One shared schema means one migration path and one backup/restore procedure regardless of tenant count, which is the model's biggest operability win over the other two; the corresponding risk is that a bad migration or a bad backup restore affects every tenant simultaneously, so the shared model raises the stakes of getting migrations right even as it lowers their frequency and cost.

Joins. In the shared-schema model, joining fact to dimension is a normal join with an extra tenant_id equality predicate on both sides, cheap and familiar. In schema-per-tenant or database-per-tenant, a per-tenant report joins normally within one tenant's schema, but any cross-tenant analytical join (an internal "which of our tenants behave like each other" analysis, for instance) has to reach across schema or database boundaries, which most engines make considerably more expensive or require moving the data into one place first.

Worked example

Simulate 100,000 rows where one tenant produces 90% of the traffic:

import duckdb
con = duckdb.connect()
con.execute('''
    CREATE TABLE fact_orders AS
    SELECT
        CASE WHEN i < 90000 THEN 'tenant_A' ELSE 'tenant_' || (2 + (i % 199)) END AS tenant_id,
        i AS order_id
    FROM range(100000) t(i)
''')

Grouping by tenant_id alone puts 90,000 of the 100,000 rows in a single partition:

$\frac{90{,}000}{100{,}000} = 90\%$

Now additionally hash-bucket only the dominant tenant into 16 sub-partitions:

SELECT *,
  CASE WHEN tenant_id = 'tenant_A'
       THEN tenant_id || '_shard' || (hash(order_id) % 16)
       ELSE tenant_id
  END AS partition_key
FROM fact_orders

Executed, the largest resulting partition holds 5,732 rows:

$\frac{5{,}732}{100{,}000} \approx 5.7\%$

That is the difference between one partition holding 90% of the table's traffic (a severe hotspot for both query concurrency and any partition-level maintenance job) and the largest partition holding under 6%, purely from adding a secondary hash key for the one tenant that needed it. Small tenants were left as plain tenant_id partitions because none of them are large enough to need sub-sharding.

Trade-offs and pitfalls

The most common mistake is applying uniform sharding to every tenant "for consistency," which needlessly fragments small tenants' already-small data across many tiny partitions and makes their queries and maintenance jobs slower, not faster; sub-sharding should be applied selectively, to the tenants whose size actually warrants it. The second common mistake is treating tenant_id filtering as a query-writing convention rather than an enforced constraint: the shared-schema model's entire isolation story depends on that predicate being present on every single query and every ETL job that touches the table, with no exceptions, for the life of the table.

Direct answer

Do not rebuild the existing department's star schema from scratch: keep it running, and introduce a compatibility view layer between its existing dimensions and a newly-built conformed version, so its current reports keep working unmodified while every new mart is built against the conformed dimensions from day one. Sequence the three new departments' marts in whichever order matches the bus matrix's widest-touching dimensions first, exactly as you would for a greenfield build, since from their point of view they are joining an enterprise warehouse that already has its shared dimensions defined.

Structured elaboration

What to do with the existing star schema. The existing department's fact table and dimensions almost certainly still work correctly for that department's own reports; the problem is only that its dimensions were never designed to be reused by anyone else. Do not touch the fact table. Build new, properly conformed versions of whichever dimensions the other departments will also need (typically customer and date), migrate the existing department's fact table to reference the new conformed dimension's keys, and leave everything else about that department's schema alone.

Introducing conformed dimensions without breaking existing reports. The mechanism that makes this safe is a compatibility view: create a view with the OLD dimension's name and column names, backed by the NEW conformed dimension underneath, so any report or dashboard query that has not been touched keeps running against the old names while the underlying table is now the shared one. Reports get migrated to query the conformed dimension directly (picking up any new attributes it offers) on the team's own schedule, not as a synchronized cutover, and the compatibility view is retired only once nothing depends on it anymore.

Sequencing the migration. For the three new departments, treat this the same way you would size a bus matrix for a greenfield enterprise build (see the sales-orders-first reasoning used when a company plans from scratch): whichever new department's mart shares the most dimensions with the others should be built first against the now-conformed dimensions, so its build validates that the conformed dimensions actually generalize before a second and third department also depend on them.

Worked example

A minimal, runnable illustration of the compatibility-view mechanism:

CREATE TABLE dim_customer_conformed (
    customer_sk   BIGINT PRIMARY KEY,
    customer_id   VARCHAR,
    customer_name VARCHAR,
    region_code   VARCHAR      -- new attribute the conformed dimension adds
);
INSERT INTO dim_customer_conformed VALUES
  (1, 'CUST-100', 'Ada Lovelace', 'EMEA'),
  (2, 'CUST-200', 'Grace Hopper', 'AMER');

-- The original department's dimension name and column names, preserved as a view
-- backed by the new conformed table underneath:
CREATE VIEW dim_customer_v1 AS
SELECT customer_sk, customer_id AS cust_id, customer_name AS cust_nm
FROM dim_customer_conformed;

-- The department's existing report, unmodified, still returns correct results:
SELECT cust_id, cust_nm FROM dim_customer_v1 ORDER BY cust_id;

Executed, the unmodified report query returns [('CUST-100', 'Ada Lovelace'), ('CUST-200', 'Grace Hopper')], exactly what it returned before the migration, while a new report written directly against dim_customer_conformed can immediately use the new region_code attribute the old schema never had.

Trade-offs and pitfalls

The most damaging mistake is treating this as a single cutover weekend: forcing every existing report to move to the new conformed dimension at once maximizes the chance something breaks in production with no fallback. The compatibility view exists precisely so migration can happen gradually, dashboard by dashboard, with the old and new dimensions correct and consistent with each other for as long as both are in use. The second common mistake is skipping the conformance work for a "small" attribute mismatch (say, the old dimension calls it cust_nm and everyone assumes it obviously maps to customer_name), which is exactly the kind of undocumented assumption that produces the four-different-customer-dimensions problem a badly-managed enterprise rollout tends to create.

Direct answer

Kimball, Inmon, and Data Vault are three different bets about where risk lives in a warehouse build. Kimball (bottom-up: build conformed dimensions and business-process-scoped fact tables directly, tied together by a "bus" of shared dimensions) optimizes for fast delivery of business intelligence (BI)-ready data. Inmon (top-down: model the whole enterprise in a normalized model first, then derive dependent data marts from it) optimizes for enterprise-wide consistency. Data Vault (hub, link, and satellite tables, insert-only) optimizes for auditability and the ability to onboard volatile source systems without redesigning the model every time a source changes.

Structured elaboration

Kimball: bus architecture. You start from a business process (orders, shipments, support tickets) and build a star schema for it directly: a fact table at a declared grain, surrounded by dimensions. Cross-process consistency comes from conformed dimensions: the same dim_customer and dim_date are reused, unchanged, across every mart, so a customer or a date means the same thing whether you are looking at the sales mart or the support mart. The risk this reduces is slow time-to-value: business users get a usable, understandable star schema for one process quickly, without waiting for a full enterprise model.

Inmon: top-down, corporate information factory. You start by modeling the enterprise's core entities and their relationships in a normalized (typically 3NF) model, independent of any single reporting need. Departmental data marts are then built as dependent, derived views on top of that single source of truth. The risk this reduces is inconsistency at enterprise scale: because every mart derives from the same normalized core, you cannot end up with two departments quietly defining "customer" or "active order" differently. The cost is a longer time to the first delivered report, because the enterprise model has to exist before a mart can be derived from it.

Data Vault: hub, link, satellite. A hub stores just a business key (hashed) and where it came from. A link stores a relationship between hubs (an order-to-customer relationship, say). A satellite stores the descriptive, time-variant attributes for a hub or link, and it is insert-only: a change never overwrites a row, it appends a new one with a later load timestamp. This buys you two things a dimensional model does not give you as directly: a full, source-system-faithful audit trail (nothing is ever lost or rewritten, which matters for regulators), and structural resilience to source-system churn (a new source system for the same business entity just adds new satellites and links, it does not force you to redesign an existing star schema). The cost is that a Data Vault is not BI-friendly by itself: you still build a business vault or a dimensional layer on top of it before an analyst can use it comfortably.

When to use each. If the dominant risk is "the business needs a dashboard this quarter and cannot wait for an enterprise model," lean Kimball. If the dominant risk is "five departments will each build their own definition of the same entity if we let them," lean Inmon. If the dominant risk is "our source systems change constantly and auditors need to see exactly what we received and when," lean Data Vault. In practice the most common real-world answer is a hybrid: use Data Vault as the raw, auditable integration layer that absorbs source-system churn, then build Kimball-style conformed dimensional marts on top of it for BI consumption. That combination gets you Data Vault's auditability and onboarding agility underneath, and Kimball's BI-readiness on top, at the cost of an extra transformation layer between raw and consumption.

Worked example

A payments company onboards a new regional processor every few months, each with a slightly different schema for the same underlying "transaction" concept, and its regulator requires it to reproduce exactly what was received from each processor at any past date. A pure Kimball build would mean re-touching the transaction fact table's extract-transform-load (ETL) pipeline every time a new processor's schema differs even slightly. A pure Inmon build would require the enterprise model to anticipate every future processor's fields before onboarding even one of them, which is not realistic for a fast-growing integration surface. The Data Vault answer: model hub_transaction and hub_processor, link them, and give each processor's raw fields their own satellite. Onboarding processor six adds a new satellite, not a schema migration on the existing fact table, and the insert-only history satisfies the regulator's reproducibility requirement directly. A conformed fact_transaction star schema is then built on top of the vault for the finance team's dashboards, so analysts never see hub/link/satellite tables directly.

Trade-offs and pitfalls

A common mistake is treating this as a purely technical choice; it is at least as much an organizational one. Inmon's enterprise model requires enterprise-wide agreement on entity definitions before anyone gets value, which stalls in organizations without the authority to enforce that agreement. Kimball's bus architecture requires genuine discipline about NOT letting a "just this once" un-conformed dimension leak into a mart, or you end up with the exact inconsistency Inmon was designed to prevent. Data Vault's insert-only satellites grow fast: without a defined retention or archiving policy, the raw vault becomes large purely from history that few queries ever touch, so a business vault or a materialized "current state" view over the vault is not optional in practice, it is what makes the vault usable at all.

Direct answer

Move to a dedicated warehouse when ad-hoc analytical queries start measurably hurting the production database's transactional performance, when the same numbers are being computed slightly differently in different spreadsheets, or when reporting needs data joined across sources the production database does not have (a payments processor, a support tool, a marketing platform). Start with the simplest useful version: a small set of tables that are periodically copied out of production into a separate database or a managed cloud warehouse, denormalized just enough to answer the handful of reports people actually run today, not a fully modeled Kimball bus architecture with conformed dimensions across every future business process.

Structured elaboration

Signal one: production impact. A heavy analytical query (a full table scan for a monthly report, say) run directly against the database serving live user traffic can degrade transactional latency for real users; if analysts are being asked to "only run reports at night" or engineers are seeing production incidents traced to a report someone ran, that is a concrete, observable signal, not a vague sense that things feel slow.

Signal two: inconsistent numbers. Once more than one person is computing the same metric independently (one analyst's spreadsheet formula, another's ad-hoc query), small differences in filtering or date handling silently produce different answers to "what was our revenue last month," and nobody notices until two answers are compared in the same meeting. This is the earliest, cheapest form of the exact conformance problem later covered by dimension conflicts across marts; catching it before it compounds is far cheaper than the reconciliation project.

Signal three: joining across sources. Once a report needs to combine production order data with a separate support tool's ticket data and a third-party payment processor's transaction data, there is no single production database to query against anymore; some place has to receive copies of all three and let them be joined together, which is the core job a warehouse exists to do.

The simplest version to build first. Do not start with a full dimensional model. Start with a small, straightforward extract-and-load process (even a scheduled job that copies a handful of production tables into a separate database or a managed cloud warehouse on a nightly cadence) and let analysts query those copies directly, denormalized or lightly modeled, for exactly the reports people already run. Introduce actual dimensional modeling (declared grain, a real date dimension, slowly-changing-dimension handling) only once a second or third report reveals that ungoverned ad-hoc structure is producing inconsistent answers or is too slow to maintain by hand, which is the point at which the methodology and system-design questions the rest of this topic covers actually become relevant.

Worked example

A single unindexed analytical query scanning a 10-million-row production orders table for a monthly report can hold a lock or consume enough I/O bandwidth to add hundreds of milliseconds to unrelated transactional queries hitting the same table concurrently; at a company processing customer-facing checkout requests against that same table, a delay large enough for customers to notice during checkout is the concrete, business-visible cost of skipping a warehouse, not an abstract inefficiency. That single observation, "a report degraded checkout latency," is usually the moment a 20-person startup's engineering leadership actually approves the investment, well before any of the modeling-methodology questions in this topic become the operative concern.

Trade-offs and pitfalls

The most common mistake at this stage is over-building: reaching for a full Kimball-style bus architecture, multiple conformed dimensions, and Type 2 slowly changing dimension (SCD) history tracking before there is more than one or two reports that need any of it wastes engineering effort the startup does not have to spare, and most of that early investment will be redesigned anyway once real reporting needs are better understood. The opposite mistake, waiting until the production database is visibly struggling before doing anything, is also common and more expensive to unwind, since by then inconsistent numbers have usually already reached several audiences and eroded trust in whichever spreadsheet or dashboard people were relying on.