TL;DR
In 2026, data warehouse architecture should use a cloud warehouse when reporting is the main job and a lakehouse when machine learning matters too. Either way, split the design into clear layers for data intake, cleaned storage, shared business definitions and reports. Storage and computing power should grow separately, so heavy loads never slow the morning reports. Define each metric, such as revenue, once, so every report shows the same number. Regulated firms build audit trails first, while real-time teams add a live feed beside the nightly loads. Move an old warehouse one subject area at a time, and check totals against the old system before you switch.
Key Takeaways Data warehouse architecture covers how data is moved, modeled, governed and served, with the database as one component among several. Four layers do the work: source and ingestion, modeled storage, a semantic layer and consumption, while governance runs across all of them. Three-tier design still holds as a logical model, even though cloud platforms split storage from compute underneath it. Kimball, Inmon, Data Vault and medallion are not rivals, because history and reporting belong in different layers. Choose by workload mix, freshness, concurrency, governance and existing skills, then check how the billing model charges for that workload. Review an architecture before you scale it, since fixing a weak layer before go-live costs far less than fixing it under production load. Why Warehouse Architecture Decides What the Business Can Trust Picture a retailer at 8 a.m. on a Monday. Finance needs the exact weekend revenue for the board pack, while supply chain wants store inventory refreshed every 15 minutes so it can move stock before shelves run empty.
Both teams share one warehouse. When the inventory loads start colliding with the overnight finance batch, the finance job finishes late. Then the numbers arrive, and the finance and merchandising dashboards show two different revenue totals.
Nobody made a math error, though. One report subtracts returns but the other does not, because each team wrote its own revenue formula. So the board now sees two answers to the simplest question the business asks.
A Design Problem, Not a Server Problem Adding compute would have hidden the first problem for a few months. Even so, it would not have touched the second one at all. Both issues trace back to design choices: which loads share resources, where business rules live, and who owns each definition.
That is the real job of data warehouse architecture. It decides whether numbers arrive on time and whether two reports agree. It also decides whether a new team can use the data without breaking someone else’s work.
This guide first walks through the layers, the tier and deployment models, and the modeling patterns. After that, it covers what changed for 2026, a decision path, a worked example and the mistakes that sink most designs.
What Is Data Warehouse Architecture? Data warehouse architecture is the end-to-end design that turns data from operational systems into trusted answers. It covers collection, landing, cleaning and modeling. It also covers who can see the data and how reports and models consume it.
A useful test is to follow one sales order. The design describes every stop that order makes, from the point-of-sale system to the revenue chart, and the rules applied at each stop.
Architecture vs the Warehouse Itself A warehouse is the storage and query engine, such as Snowflake, BigQuery, Amazon Redshift or the Fabric Data Warehouse. Architecture, by contrast, is everything around that engine. That includes the pipelines that feed it, the models inside it, the access rules on top and the monitors that watch it.
So two companies can buy the same platform and still get very different results. One defines layers, owners and shared metrics before loading anything. Another loads tables straight from source systems, then lets every analyst build their own logic. Same engine, yet very different trust in the numbers.
Case Study
Fabric Architecture Review Before a Dynamics 365 Go-Live
Kanerika reviewed Forest River’s integrator-built Fabric platform in six weeks, scored it across five lenses and 18 dimensions, and sequenced about 136 findings before cutover.
Read the Case Study → Logical vs Physical Architecture Logical architecture names the responsibilities: ingest, store history, model for the business, define metrics, serve users and govern it all. It stays stable for years, since business questions change slowly.
Physical architecture names the services that carry out each responsibility, such as a pipeline tool, a storage account, a SQL engine and a BI tool. These change more often, especially when platforms add features or contracts end. Keeping the two views apart lets you swap a service without redesigning the whole flow.
In other words, the logical view is the building plan while the physical view is the materials. A good plan survives a change of supplier.
How Data Moves Through a Warehouse, Source to Report Every warehouse, old or new, moves data along the same basic path. Following one record through it is therefore the fastest way to see where problems start.
Take a single online order. It starts in the e-commerce platform and then gets copied, checked and joined to its customer and product. After that, it is stored with its history and finally counted in a revenue report. Each step has its own job, so each step also has its own way to fail.
Collect. Identify source systems such as the ERP, CRM, point of sale and web events, then agree who owns each one.Ingest and stage. Copy new and changed records into a raw landing area unchanged, so you can always replay a load.Transform and validate. Clean, deduplicate and join the records, then check row counts, keys and formats before anything moves on.Store history. Keep every version of key records, so you can answer what the data said last quarter, not only today.Serve BI and AI. Publish modeled tables and shared metrics to dashboards, notebooks and machine learning features.Monitor quality and cost. Track freshness, volume, failed checks and compute spend on every run, not only when someone complains.The exact order matters less than the habits behind it. Raw data is kept, checks run before anything is published, and monitoring watches the whole path. Our guide to data pipelines also covers the movement steps in more depth.
When a step is skipped, the damage usually shows up two steps later. For example, a missing validation check surfaces as a strange dashboard spike a day after the bad load ran.
Key Layers and Core Components of Data Warehouse Architecture A layer is a group of components that share one responsibility. Splitting the work this way means a change in one layer, such as a new source system, does not force a rewrite of every report.
The table below gives each layer’s job and the tools that usually fill it. It also lists the symptom you will notice when that layer is weak, since symptoms are often the quickest way to diagnose an existing design.
Layer Job Typical technology Symptom when weak Source and ingestion Copy data out of business systems on a schedule or when it changes Pipeline and connector tools, such as change data capture Late loads, missing deletes and full reloads that run all night Staging Hold raw data exactly as received Cloud object storage or raw schemas No way to replay a bad load, so audits stall Integration and modeled storage Clean, join and keep history, then build marts SQL engine, Spark and transformation frameworks Duplicate customers, overwritten history and slow joins Semantic layer Define metrics and business names once Semantic models and metric stores Two dashboards show two revenue totals Consumption Serve reports, apps, notebooks and models BI tools, SQL endpoints and feature stores Users export to spreadsheets, then rebuild logic by hand Metadata, governance, security, observability Catalog, lineage and access control, plus freshness and cost alerts Catalogs such as Microsoft Purview, policy engines and monitors Nobody knows where a number came from or who can see it
Source, Ingestion and Staging Ingestion decides how often and how much data moves. A full load copies a whole table every time, which is simple but slow once tables grow. An incremental load copies only new or changed rows, usually by watermark column or by change data capture. Our comparison of data ingestion vs data integration separates the two jobs.
Change data capture reads the source database log, so it also carries deletes. By contrast, a nightly load based on a “last modified” date will never see a canceled order that was removed from the source.
Staging is the raw landing zone. Data arrives here unchanged, together with a load timestamp, and stays long enough to replay any failed load. Teams that skip staging to save storage usually pay for it when a source first sends bad data.
Integration and Modeled Storage Here raw records become business data. Customer records from the CRM and the ERP are matched, and product codes are standardized. History is also kept, so a price change does not rewrite last year’s margin.
Teams choose between ETL and ELT at this point. With ETL, data is transformed on a separate engine before it is loaded. With ELT, raw data is loaded first and then transformed inside the warehouse, which suits cloud platforms with elastic compute.
Most modern designs use ELT for the bulk of the work, while an ETL framework keeps either approach consistent across hundreds of jobs. The output is a set of conformed tables, shared dimensions such as customer and date, and subject-area marts. Our data integration tools roundup compares the platforms that feed this layer.
Semantic Layer and Consumption The semantic layer is where “revenue” gets one definition. It holds metric formulas, business-friendly names, relationships and row-level rules, so every report that asks for revenue gets the same answer.
That layer would have solved the retailer’s two-totals problem. Finance and merchandising would both read “net revenue” from one model, because returns would be handled once.
Consumption sits on top, and it includes dashboards, SQL endpoints for analysts, embedded reports and feature tables for machine learning. Our piece on business intelligence architecture covers the reporting side in more detail.
Cross-Cutting: Metadata, Governance, Security, Observability Some responsibilities do not belong to one layer. Instead, they run across all of them, the way wiring runs through every floor of a building.
Metadata and lineage show where each column came from and what depends on it. Access control decides who sees which rows and columns, including masking for personal data. Catalogs such as Microsoft Purview, formerly Azure Purview, hold much of this in one place. Our data governance tools guide compares the main options.
Observability watches freshness, volume, schema changes and cost on every run. That way, the data team learns a load failed before the CFO does. Our explainer on data observability goes further into the signals worth tracking.
Checklist
Data Governance Checklist
Check catalog, lineage, access control and data quality ownership across every warehouse layer before new teams and AI workloads start using the data.
Get the Checklist → The Metadata Catalog in Practice A metadata catalog is the warehouse’s searchable inventory. It records what each table and column means, who owns it, where it came from and who may read it.
Databricks documents Unity Catalog as a governance layer that enforces access control on each query, tracks lineage and logs activity for auditing. Assets use a three-level name, catalog.schema.object, such as sales.gold.fact_orders. In Microsoft estates, Microsoft Purview fills the same catalog role.
Take the retailer’s two revenue totals. A catalog with column-level data lineage would have shown that one report read gross sales while the other subtracted returns.
Single-Tier, Two-Tier and Three-Tier Architecture Tier models describe how many separate levels sit between the source data and the user. They come from the on-premises era, but the terms still appear in vendor documents, interviews and audits.
A single-tier design is the retailer’s 8 a.m. problem in miniature, with loads and queries fighting over one store. Each extra tier exists to separate that work, as the table shows.
Model How it is built Where it still fits Main limit Single-tier One store that serves both loading and queries Small, single-purpose reporting databases Loads and queries compete for the same resources Two-tier Warehouse plus client tools that query it directly A department with a few dozen report users Concurrency suffers as users grow Three-tier Storage tier, analytical (OLAP) tier and presentation tier Most enterprise designs, especially as a logical model More parts to run and govern
Three-tier survives in cloud platforms as a logical model rather than a physical one. Storage sits in object storage, while the middle tier is elastic compute plus the semantic layer. Above both, the BI tool forms the top tier, so the tiers remain even though they no longer map to three servers.
Deployment Patterns: Centralized EDW, Hub-and-Spoke, Cloud-Native, Lakehouse Deployment patterns describe where data physically lives and who owns each part. That choice shapes cost and flexibility, as well as how hard it will be to change platforms later.
A centralized enterprise data warehouse (EDW) puts everything in one governed store, run by one team. Hub-and-spoke keeps a central hub for integration and then feeds departmental marts. Cloud-native designs use a managed service that bills storage and compute apart. Finally, a lakehouse stores data as open files and adds warehouse features on top.
Pattern How it works Best for Lock-in risk Watch-out Centralized EDW One integrated store, one team Strong central governance and stable reporting High The central team becomes a queue for every request Hub-and-spoke Central hub feeds department marts Large firms with distinct business units Medium to high Marts drift apart unless the hub’s dimensions are shared Cloud-native warehouse Managed service with separate storage and compute BI-led enterprises that want elastic scale Medium Costs grow quietly when workloads run without limits Lakehouse Open table formats on object storage, with a warehouse engine on top BI and machine learning on the same data Lower Needs stronger engineering skills and table maintenance
Many enterprises end up with a blend. A common 2026 shape pairs lakehouse-style storage with a cloud warehouse engine for BI. Fabric works this way, and so does Snowflake with Iceberg tables. Our data lake vs lakehouse comparison explains when a plain lake stops being enough.
Lock-in deserves a second look, too. When the files sit in an open format in your own storage, a second engine can read them later without an export project.
Cloud vs On-Premises vs Hybrid Hosting is a separate question from the pattern. For example, a hub-and-spoke design can run on-premises, in the cloud or across both, so each option changes who carries the operating work.
Factor Cloud On-premises Hybrid Scalability Elastic, so it scales in minutes Fixed by the hardware you buy Elastic for cloud workloads only Cost model Pay for use or reserve capacity Up-front hardware and licenses Both, plus transfer costs between them Security control Shared with the provider Fully in-house Split, so policies must match on both sides Operations effort Low for infrastructure High, due to patching, backups and upgrades Highest, since two estates must run
Hybrid is usually a stage rather than a destination. It suits firms with data that must stay in one location while the rest moves. Our guide to the cloud data warehouse covers the buying decisions in detail.
Data Modeling Patterns: Kimball, Inmon, Data Vault and Medallion A modeling pattern decides how tables are shaped inside the warehouse. The debate is often framed as one method against another, yet most working designs combine them by layer.
Kimball dimensional modeling starts from a business process and sets the grain. Then it builds fact tables surrounded by dimensions in a star schema , so it is fast to query and easy to read. Inmon instead builds a normalized enterprise model first and derives marts from it, which favors consistency over speed of delivery.
Data Vault splits data into hubs for business keys, links for relationships and satellites for changing attributes. It handles many sources and full history well, but at the cost of more tables and harder queries.
Medallion is different, since it is a layering pattern rather than a table shape. Microsoft’s documentation describes bronze, silver and gold layers for raw, validated and enriched data.
Pattern What it optimizes Best layer Trade-off Kimball Query speed and business readability Gold or presentation marts History and source changes need careful handling Inmon One consistent enterprise model Integration layer Slower first delivery than Kimball Data Vault Auditability, many sources and full history Silver or raw vault More joins, so it is not for direct reporting Medallion Clear quality stages on a lakehouse The whole flow Says nothing about table design within each layer
The practical combination is simple. Land raw data in bronze, then keep integrated history in silver with Data Vault or a normalized model. Finally, serve Kimball star schemas from gold. Our star schema vs snowflake schema guide covers the gold-layer choice, while the Databricks lakehouse architecture post shows medallion layers on one platform.
Slowly Changing Dimensions and SCD Type 2 Star schemas keep history in their dimension tables, using one of three common slowly changing dimension (SCD) types. Type 1 overwrites the row, and Type 2 inserts a new dated version. Type 3 keeps limited history in extra columns.
Say the retailer moves a customer from the online segment to loyalty in March. Under Type 1, last year’s sales jump to loyalty overnight. Under Type 2, past sales stay with the segment that was true at the time.
Microsoft’s Fabric dimension modeling guide keeps Type 1 for most attributes and reserves Type 2 for those that must preserve history. A surrogate key is required, since each version repeats the natural key. The columns below follow that guide.
CREATE TABLE d_Customer
(
Customer_SK INT NOT NULL, -- surrogate key
CustomerID VARCHAR(20) NOT NULL, -- natural key
Segment VARCHAR(20) NOT NULL, -- tracked attribute
RecValidFromKey INT NOT NULL, -- YYYYMMDD start
RecValidToKey INT NOT NULL, -- 99990101 if current
RecIsCurrent BIT NOT NULL -- 1 on current row
);On a change, the load closes the current row with an end date and a flag of 0. It then inserts the new version, flagged 1.
What Changed for 2026 The basic layers have not changed in decades, but the ground under them has. Four shifts shape almost every 2026 design.
First comes the split between storage and compute. Snowflake’s documentation describes separate database storage, compute and cloud services layers , and Fabric, BigQuery and Databricks follow the same idea. So the finance batch can get its own compute instead of waiting behind inventory loads.
Second, open table formats have spread. Apache Iceberg calls itself “the open table format for analytic datasets” , while Delta Lake plays the same role in Fabric and Databricks. Snowflake’s Iceberg tables also keep data in external cloud storage that you manage. As a practical result, your data is no longer tied to one vendor’s engine.
Third, streaming is a routine input in 2026 designs. In the retailer’s case, the 15-minute inventory feed runs on its own path into a hot table, while the finance batch stays nightly. Fraud and IoT feeds follow the same pattern, with a parallel streaming path inside the same architecture rather than a separate system.
Fourth, AI and machine learning read governed data too. Models and AI assistants query the same tables as reports, so lineage, masking and metric definitions protect model inputs too. For that reason, a warehouse governed only for dashboards is no longer enough.
Each shift turns into one design action for a new build.
Storage-compute split: give each heavy workload, such as the finance close, its own compute pool and a spend limit.Open formats: write silver and gold tables as Delta or Iceberg in storage your company controls.Streaming: send only the feeds that need minutes-old data down the live path, and leave the rest on batch.AI access: apply the same lineage, masking and metric rules to model inputs that already cover reports.Kanerika Service
Data Modernization Services
Move legacy warehouses to a layered cloud design with separate storage and compute, open table formats and governance that covers reports and AI models alike.
Explore Data Modernization How Fabric, Snowflake, Databricks, BigQuery and Redshift Implement the Layers Each major platform covers the same logical layers in its own way. The facts below come from vendor documentation, so they describe how each one works rather than which is better. Compare three things as you read: where the storage lives, how compute is rented, and whether the files stay open to other engines.
Microsoft’s Fabric Data Warehouse stores data as Delta tables in OneLake and separates storage from compute. Microsoft calls it a lake warehouse and positions it for star and snowflake schemas. BigQuery takes a different route, keeping data in managed columnar storage and running queries on slots, which Google describes as virtual CPUs .
Databricks keeps Delta tables in your own cloud storage, and a SQL warehouse is the compute resource that queries them. Amazon Redshift Serverless reads its managed storage plus external tables on Amazon S3, and it provisions and scales capacity automatically. Each platform rents compute on different terms, and the cost table further down compares those billing models.
The Five Platforms Side by Side Platform Storage format Compute model Billing unit Open-format support Microsoft Fabric Warehouse Delta tables (Parquet plus a log) in OneLake Shared Fabric capacity Capacity units (CUs) Delta natively Snowflake Snowflake-managed storage, or Iceberg tables in your storage Virtual warehouses, separate from storage Credits Iceberg tables Databricks SQL warehouse Delta tables in your cloud storage SQL warehouse compute: serverless, pro or classic Compute time while running Delta natively Google BigQuery Managed columnar storage Slots (virtual CPUs) Per TiB processed, or slot-hours External tables over lake files Amazon Redshift Serverless Managed storage, plus external tables on Amazon S3 Capacity provisioned and scaled automatically Capacity used while queries run External tables over lake files
Teams still on Azure Synapse dedicated SQL pools also have a documented path. Microsoft provides a Fabric Migration Assistant for moving those pools into a Fabric warehouse. It also accepts SQL Server sources and Teradata metadata. The page still listed Teradata support as a preview on October 25, 2026, and the tool serves the legacy warehouse moves described below.
Watch on YouTube
BigQuery vs Snowflake 2026: Which Cloud Data Warehouse Fits Your Enterprise?
A Kanerika walkthrough of two of the cloud warehouses in the table above, and how each one fits enterprise analytics workloads.
For a deeper look at one engine, read our breakdown of Snowflake architecture . Our data warehouse tools roundup then compares the wider market.
How to Choose the Right Data Warehouse Architecture Choosing an architecture is mostly about matching the design to the workload you will actually run. So start from the business questions and the people asking them, then pick the platform last.
The steps below turn that into a repeatable decision. They work for a new build as well as for a redesign of an existing warehouse.
Five Questions That Decide the Design What is the workload mix? Mostly dashboards and finance reporting points to a cloud warehouse, while heavy data science on raw files points to a lakehouse.How fresh must the data be? Next-morning data suits batch loads. When decisions need data within minutes, add a streaming or change data capture path.How many people query at once? High peak concurrency, such as Monday morning reporting, needs workload isolation and auto-scaling compute.What governance and residency rules apply? Regulated data needs enterprise data governance , with lineage, masking and region controls designed in before the first release.What do you already run and know? A team fluent in SQL Server and Power BI will deliver faster on Fabric than on an unfamiliar stack. Our Microsoft Fabric adoption guide covers that rollout.Recommendations by Scenario Five profiles cover the common cases, and each one puts its weight on a different layer. The layered design itself stays the same in every row.
Scenario Recommended architecture Why BI-led enterprise Cloud-native warehouse with a strong semantic layer Fast SQL, high concurrency and one metric definition ML-heavy organization Lakehouse on Delta or Iceberg with medallion layers Data scientists and BI share one copy of the data Real-time operations Warehouse plus a streaming path for hot data Minutes-old data where it matters, while batch handles the rest Regulated industry Governed warehouse with lineage and residency first Audit trails and masking come before any new use case Microsoft-centric estate Lake-based warehouse on one shared capacity, such as Fabric on OneLake with Power BI semantic models One capacity and one security model, since skills are already in place
Notice that every row keeps the same layers. What changes is which layer gets the most investment and which engine serves it.
What Drives Cost in Each Billing Model Architecture choices show up on the bill in different ways. The same query pattern can be cheap on one model but expensive on another, so match the model to your usage.
Billing model What you pay for What drives the bill up Main control Per TiB processed (BigQuery on-demand) Bytes each query reads Wide scans on unpartitioned tables Partitioning, clustering and selecting fewer columns Slot-hours (BigQuery capacity) Reserved or autoscaled virtual CPUs Idle reserved slots Right-sized reservations Credits (Snowflake) Time virtual warehouses run, by size Warehouses left running when idle Auto-suspend, plus one warehouse per workload Capacity units (Fabric) A capacity shared by all Fabric workloads One heavy job starving others Capacity sizing, scheduling and workload separation Serverless capacity (Redshift Serverless) Capacity used while queries run Long, frequent or unbounded queries Usage limits and query monitoring
We have left list prices out on purpose, because they vary by region, contract and edition. Run a two-week pilot on your own queries before you trust any estimate.
If You Already Run a Legacy Warehouse A Teradata warehouse with years of stored procedures inside it cannot move in one weekend. The same is true of the on-premises SQL Server, Netezza and Oracle warehouses that many enterprises still run.
Move it in waves, one subject area at a time, and start with an area that matters but is not the most complex. Run the old and new systems in parallel, then reconcile totals daily until they match. Once they do, retire the old reports for that area and begin the next wave.
A big-bang cutover puts every report at risk on the same weekend. Waves, on the other hand, turn that single risk into a series of small, reversible ones. Our data warehouse migration guide covers the runbook.
Migration ROI Calculator
What Would a Warehouse Migration Save You?
Estimate the cost and time savings of moving a legacy warehouse to a modern cloud platform before you plan the first wave.
Calculate Migration ROI → Worked Example: A Mid-Size Retailer Picks Its Architecture This example is illustrative, since it is built from a typical profile rather than one named client. It applies the five questions to the retailer from the opening section.
Assume 120 stores, an online shop, an ERP, a warehouse management system and about 400 report users. Most reporting serves finance and merchandising, while one data science team builds a weekly demand forecast.
Question Answer for this retailer Design consequence Workload mix About 90% BI, plus one forecasting model Warehouse engine for BI, while the model reads lake files Freshness Finance daily; inventory every 15 minutes, so 96 loads a day Batch for finance, then a separate streaming path for inventory Concurrency Peak on Monday from 8 to 10 a.m. Separate compute for finance and for ad hoc queries Governance Customer and payment data must be masked Column masking and lineage from day one Existing stack Power BI, SQL Server skills and Microsoft 365 Fabric warehouse on OneLake
So the chosen design is a Fabric warehouse on OneLake with medallion layers . Raw data lands in bronze and conformed history sits in silver. Kimball star schemas for sales, inventory and finance then live in gold. One Power BI semantic model defines net revenue once, which ends the two-totals problem.
Inventory runs on its own streaming path into a hot table. Because finance runs on separate compute, the 96 daily inventory loads no longer delay it. Forecasters, meanwhile, read silver tables directly from OneLake as Delta files. Once the design is set, our guide to data warehouse implementation covers the build phases.
Data Warehouse Architecture Best Practices (and the Mistakes They Prevent) Best practices are easier to follow when you can see the failure each one prevents. Every practice below exists because the opposite habit keeps breaking real warehouses.
Failed designs usually trade speed in the first month for pain over the next three years. A team skips staging, hard-codes metrics in reports and shares one compute pool. It all works until the third business unit joins.
Practice The mistake it prevents Keep a raw staging layer with load timestamps No way to replay or audit a bad load Define each metric once in a semantic layer Two reports, so two revenue numbers Isolate workloads on separate compute Ad hoc queries slowing the finance close Use change data capture for large or volatile tables Missing deletes and nightly full reloads Name an owner for every source and data product Broken loads that nobody fixes Monitor freshness, volume and cost on every run Users finding failures before the data team Store data in an open table format where possible An export project when the engine changes Review the design before go-live rather than after Rework under production load
Backup and disaster recovery deserve one firm line: test a restore at least once a quarter, because an untested backup is only a hope.
Scale is where weak designs give way. A design that holds for one team can still fail at twenty teams, unless layers, owners and workload limits were set up front.
Kanerika Service
Data Engineering Services
Build warehouse pipelines, lakehouse layers, DataOps and streaming on Snowflake, Databricks and Fabric.
Explore Data Engineering Warehouse, Lake, Lakehouse, Data Fabric or Data Mesh: Where Each Fits These terms describe different kinds of things, which is why comparisons between them often confuse. A warehouse and a lake are stores, while a lakehouse merges the two. A data fabric is neither; it is an integration layer, and a data mesh is an ownership model.
Approach What it is Fits best when Data warehouse Modeled, governed store for analytics Trusted reporting is the main goal Data lake Low-cost store for raw files of any type Data science needs raw and unstructured data Lakehouse Warehouse features on open files in a lake BI and machine learning share the same data Data fabric Metadata-driven layer that connects many stores Data is spread across systems that will not merge soon Data mesh Domain teams own and publish data products A central team has become a bottleneck
A warehouse often sits inside a fabric or a mesh rather than competing with it. For the detailed trade-offs, read data fabric vs data warehouse , data lake vs data warehouse and data mesh vs data lake .
How Kanerika Designs and Reviews Data Warehouse Architecture Kanerika starts most warehouse engagements with a review rather than a build. Checking a design against the layers and practices in this guide shows which parts to keep, which to fix first and which can wait.
Forest River, Inc., a North American maker of RVs and trailers, is a clear example. While the company moved from Dynamics AX to Dynamics 365, a systems integrator built its new Microsoft Fabric environment. Before go-live, Kanerika ran a six-week, read-only review of that environment.
The team scored the platform on a red-amber-green scorecard across five lenses and 18 dimensions. It then logged about 136 findings, each with an owner, an effort estimate and a sequence. Working parts stayed in place, such as the metadata-driven copy pattern, Key Vault usage and capacity scaling.
Pre-cutover actions came to about 30 days and three person-weeks of work. As a result, leadership made a conditional go-live decision on evidence rather than opinion. The full Forest River case study describes the scorecard in detail.
For SQL Server estates, Kanerika’s FLIP Migration Accelerator automates discovery and conversion during SQL Server to Fabric migration . Scope, timeline and cost are confirmed before any work begins. Simple estates with standard SSIS pipelines typically finish in four to eight weeks, and medium-complexity estates with custom SSIS logic take eight to fourteen.
That same method carries into new Fabric builds and into warehouse moves off legacy platforms. Kanerika is a Microsoft Fabric Featured Partner and a Microsoft Solutions Partner. It also holds the Data Warehouse Migration to Azure Specialization and the Analytics on Azure Advanced Specialization. Its certifications include ISO 9001, 27001 and 27701, plus SOC II and CMMI Level 3.
Kanerika Service
Data Architecture Services
Get a layered, governed warehouse design, or a scored review of the one you run, with findings sequenced by owner, effort and business risk.
Explore Data Architecture Bottom Line: What Enterprises Need in 2026 A warehouse is only as trustworthy as the design around it. Separate layers, shared metrics, isolated workloads and governance across every layer are what make reports agree and arrive on time.
So decide the layers and the workload profile first, then choose the engine. In 2026, enterprises need data warehouse architecture that is layered and governed, with storage and compute split. Build it as a cloud warehouse for BI-led work, and as a lakehouse when machine learning or open formats matter.
Frequently Asked Questions
What is data warehouse architecture? Data warehouse architecture is the end-to-end design that turns data from business systems into trusted reports and models. It covers how data is collected, staged, cleaned, modeled, governed and served. The warehouse engine is only one part. The architecture also includes the pipelines, the semantic layer, access controls and the monitoring that keeps every load accurate and on time.
What are the key layers in data warehouse architecture? The key layers are source and ingestion, staging, integration and modeled storage, a semantic layer, and consumption. Ingestion copies data out of business systems. Staging keeps it raw. The modeled layer cleans data and keeps history. The semantic layer defines metrics once, and consumption serves dashboards, notebooks and models. Governance and observability run across every layer.
Can a data warehouse support real-time analytics? Yes, with the right design. Most enterprises add a streaming or change data capture path beside the batch loads, so time-sensitive data such as inventory or fraud signals lands within minutes. The batch path still handles finance and history. Separate compute for the streaming workload keeps frequent small loads from slowing the scheduled reporting jobs.
What is the medallion architecture? Medallion architecture organizes a lakehouse into bronze, silver and gold layers. Bronze holds raw data as it arrived. Silver holds cleaned, validated and integrated data with history. Gold holds enriched, business-ready tables, often Kimball star schemas, for reports and models. It describes quality stages rather than table design, so teams pair it with a modeling method.
How is a data warehouse different from a database? An operational database runs day-to-day transactions, such as placing an order, and is tuned for many small reads and writes. A data warehouse combines data from many such systems, keeps its history and is tuned for large analytical queries. Running heavy reports on the operational database slows the business application, which is why warehouses exist.
How do you migrate a legacy data warehouse to the cloud? Migrate in waves, one subject area at a time, instead of one big cutover. Inventory the existing objects and reports, choose the target design, and move the first subject area. Run old and new systems in parallel and reconcile totals daily until they match. Then retire the old reports for that area and start the next wave.
What are the main components of data warehouse architecture? The main components are source systems, ingestion pipelines, a raw staging area, a storage and query engine, transformation logic, data marts, a semantic model and BI tools. Supporting components include a data catalog, lineage tracking, access control and monitoring for freshness and cost. Each component belongs to one layer, which keeps changes in one place from breaking others.
How do ETL and ELT fit into data warehouse architecture? ETL and ELT both sit in the integration layer. ETL transforms data on a separate engine before loading it into the warehouse. ELT loads raw data first and transforms it inside the warehouse using its own compute. Cloud platforms with elastic compute favor ELT for most work, while ETL still suits sensitive data that must be masked before it lands.
Why is data warehouse architecture important for enterprises? Architecture decides whether reports agree, arrive on time and stay affordable as usage grows. A weak design produces two revenue numbers, late finance reports and runaway compute bills. A strong design isolates workloads, defines metrics once and governs access across every layer. That lets new teams, new sources and AI models use the data without breaking existing reports.
What are the three tiers of data warehouse architecture? The three tiers are the bottom tier for storage, the middle tier for analytical processing and the top tier for users. The bottom tier holds the warehouse database. The middle tier runs OLAP processing and, in modern designs, the semantic layer. The top tier contains dashboards and query tools. Cloud platforms keep this model logically, spread across managed services.
What is the difference between Kimball and Inmon architecture? Kimball builds dimensional star schemas around business processes, so analysts get fast, readable marts early. Inmon builds a normalized enterprise model first and derives marts from it, which favors consistency over speed of delivery. Many enterprises combine them. They keep integrated history in a normalized or Data Vault layer and serve Kimball star schemas to report users.
What is modern data warehouse architecture? Modern data warehouse architecture is a cloud design that separates storage from compute, keeps data in open table formats where possible and feeds both batch and streaming data into the same layers. It adds a semantic layer for shared metrics and extends governance to AI and machine learning workloads. Fabric, Snowflake, Databricks, BigQuery and Redshift all support this pattern.
What is the difference between a data warehouse and a lakehouse? A data warehouse stores modeled, structured data in a format managed by its engine and is tuned for SQL reporting. A lakehouse stores data as open files, such as Delta or Iceberg tables, in object storage and adds warehouse features on top. The lakehouse lets BI and machine learning share one copy of the data without exports.
Which data warehouse architecture is best for a large enterprise? No single design wins for every enterprise. A BI-led company usually does best with a cloud-native warehouse and a strong semantic layer. A company with heavy machine learning work suits a lakehouse with medallion layers. Regulated firms should put lineage and residency controls first. All of them benefit from separate compute for each major workload.
