What Services Are in the Data Warehouse Consulting Process?

Brian Chesky, Airbnb’s CEO, used to ask which city had booked the most nights in the previous week. Data Science and Finance sometimes handed him two different answers. Each team pulled the number from a different table, and the business logic behind each table differed slightly.

Airbnb’s data team wrote about the problem in 2021, after a multi-year rebuild of the warehouse resolved the disagreement. Few companies operate at Airbnb’s scale. However, almost all of them carry a smaller version of the same problem, and data warehouse consulting services exist to fix it.

The fix is a set of services delivered in order. Data warehouse consulting includes ten services, from strategy and architecture to migration and governance. Each service shows up at a specific step of a nine-step process, which runs from discovery to optimization.

For a plain definition of the role, our guide on what data warehouse consulting really is covers the basics. This article explains the nine steps of the process, the services each step draws on, and how firms price the work.

The data warehouse consulting process runs in nine steps. Each step produces something the next one needs.

1. Discovery: The consultant interviews the people who use the reports and learns what breaks today.
2. Current-State Assessment: The team documents the existing pipelines, costs, and query performance. The team also checks every source for quality, ownership, and schema stability.
3. Requirements Definition: Business owners sign off on KPIs, user groups, and data freshness targets.
4. Architecture Design: The consultant maps every layer of the future-state design, from ingestion through to reporting.
5. Platform Selection: The team scores two or three platforms against the documented workload. Concurrency and existing cloud commitments usually decide the result.
6. Development: Engineers build the data models, transformations, and data pipelines under version control. On modern platforms, transformation runs inside the warehouse, so most of this work is ELT.
7. Testing: The team validates row counts, financial totals, and permissions against the source systems.
8. Migration or Deployment: Workloads move into production in tracked waves, usually one subject area at a time.
9. Optimization and Support: The team reviews cost, performance, and reliability on a fixed cadence.

Assessment is the step teams cut when a deadline slips. The cost of the shortcut surfaces during testing, when the source data turns out dirtier than anyone assumed. Everything downstream inherits whatever the first three steps got wrong.

Each of the nine steps draws on at least one consulting service. The next section maps the ten services to the steps where they appear.

Data warehouse consulting services include ten offerings, and each one maps to part of the nine-step process. The table below shows what each service covers and where it fits.

Very few engagements buy (or need) all ten. A first milestone usually pairs strategy with architecture, and the roadmap decides what follows. Firms offering data warehousing services normally price these services as separate phases. Because of this, you can stop after the assessment if the numbers don’t support the build.

Scope a data warehouse consulting contract by naming which of five related services you’re buying. The five labels often get used interchangeably, but each one describes different work.

Consulting decides what to build and why. Development builds the warehouse. Data engineering keeps the pipelines running afterward, while data integration moves records between systems. Analytics then turns the finished tables into reports.

One firm often sells several of these services under one contract. The labels matter mainly when you write the statement of work and agree who owns the platform after launch.

The split between consulting and data engineering causes the most confusion. A consultant decides which platform fits a 12 TB workload with 200 concurrent BI users. An engineer writes the models and schedules the loads. The engineer is also the person who fixes a pipeline after an overnight failure.

The two roles overlap in practice. Most consultants write code, and most senior engineers have architecture opinions worth hearing. The difference shows up in what you buy. Consulting buys a decision and a roadmap, while engineering buys delivery capacity.

Data warehouse architecture consulting defines how data moves from your source systems to your reports. The path runs through six layers.

Sources → Ingestion → Transformation → Warehouse → Semantic Layer → BI and AI

Ingestion copies raw records out of the source systems, either on a schedule or as they change. Transformation cleans and joins the raw records into tables an analyst can read. The warehouse stores the result and answers queries against the stored tables.

The semantic layer sits above the warehouse and stores the metric definitions. Airbnb’s version is called Minerva, and the platform now carries more than 12,000 metrics and 4,000 dimensions maintained by over 200 people. A booking count then means the same thing in a dashboard, an experiment, and a board report.

Design comes down to how you shape the tables. Most analytical models use a star schema, with a central fact table for events and dimension tables for descriptive attributes. A snowflake schema normalizes the dimension tables further, which saves storage but adds joins to each query.

Three other choices matter even more on cloud platforms. Slowly changing dimensions decide whether you keep history when a customer changes address. Partitioning splits large tables so each query reads less. Clustering orders the data inside partitions, so filtered queries skip more blocks.

A modern data architecture makes these choices explicit and writes them down. Undocumented modeling decisions are the reason a warehouse becomes unmaintainable three years later.

Cloud data warehouse consulting helps you pick, size, and run an analytical platform the vendor hosts for you. Elasticity is the commercial argument here, because compute scales with demand and stops billing when idle.

Snowflake separates storage from compute and bills virtual warehouses per second while they run. Snowflake enables auto-suspend by default, so an idle warehouse stops consuming credits without anyone watching the console.

Amazon Redshift offers provisioned RA3 clusters alongside Redshift Serverless. Serverless measures capacity in Redshift Processing Units and bills RPU-hours on a per-second basis. Storage is billed separately.

Google BigQuery is serverless and charges on demand for data scanned, at $6.25 per tebibyte. Capacity pricing through BigQuery Editions replaces the on-demand model once query volume becomes predictable.

Microsoft Fabric stores every workload in OneLake and draws compute from one pool of capacity units. Teams already standardized on Power BI tend to shortlist Fabric first, since the reporting layer and the warehouse share a security model.

No platform wins every workload, so score each one against yours. The table maps common requirements to what you should test during an evaluation.

A proof of concept against a sample of your largest table answers more questions than a vendor benchmark. A Snowflake consulting engagement often starts with the same test.

Data warehouse migration consulting moves data and workloads from one platform to another without breaking existing reports. Most of the work is mapping legacy schemas, stored procedures, and business rules to the target platform. The team then decides what gets rewritten, migrated, or retired.

Validation carries as much weight as the mapping. The old and new warehouses usually run in parallel while the team compares row counts, financial totals, and report outputs. Cutover happens only after the results match. A rollback plan stays in place until the new warehouse is stable.

Common projects include moving an on-premises warehouse to the cloud and replacing Teradata or Netezza. Other projects migrate between two cloud warehouses or build a first warehouse from operational databases.

Most migration failures begin during planning. That’s why sequencing and validation sit at the center of any data migration consulting engagement.

Modernization replaces the parts of an old warehouse that now cost more than they return. Legacy ETL servers become ELT inside the warehouse. Hand-scheduled scripts become orchestrated pipelines with alerting on failure.

Optimization keeps the new platform from drifting back. Most optimization work answers one of seven complaints, and the table pairs each complaint with the usual fix.

No fix works for every workload. Clustering a rarely filtered table adds maintenance cost and returns nothing. So, an optimization engagement should start with the query history.

A warehouse is only as good as the systems feeding the tables. Integration work connects the CRM, the ERP, and whatever SaaS tools the business uses. Three patterns cover most of this work. 

Batch loads run on a schedule and suit daily reporting. Change data capture streams inserts and updates as they happen, which keeps latency low without a full reload. Streaming ingestion handles continuous event data.

Pattern choice follows the freshness the business asks for. Real-time delivery costs more to build and more to run, so confirm somebody acts on hourly numbers before you engineer for them. Data integration is where most warehouse projects lose their schedule. Source system owners control access and rarely share your deadline.

Data governance sets the quality rules, access controls, and metric definitions that analytics and AI depend on. The service shows up at two steps of the process, requirements definition and testing.

A dashboard inherits whatever the underlying tables say, including a metric with two conflicting definitions. Trust in a chart comes from the tests running underneath the model.

On the BI side, consultants spend most of their time on definitions and models. Governed metrics and tested transformations make self-service data analytics safe to hand to a business team. Documented ownership matters as much, since somebody has to answer when a number looks wrong.

AI workloads read from the same governed tables. Forecasting models and churn scoring both need clean historical data. Retrieval pipelines for internal assistants also need the tables joined and documented.

Not every AI project needs a warehouse, though. A use case with one data source can read straight from the application database. Adding a warehouse first would only slow the project down.

A data warehouse stores curated data for reporting. A data lake stores raw data of any shape for exploration.

Most mid-size companies run both. Raw event data lands in the lake, and the warehouse stores the modeled tables finance depends on. A data lake implementation and a warehouse build often happen inside the same program.

Company size decides how much of the nine-step process a data warehouse consulting engagement needs. For a small business, right-sizing is the whole job. A 50-person company with five SaaS sources rarely needs a multi-layer enterprise design.

A managed cloud warehouse, a hosted ingestion tool, and a small transformation project usually cover the requirement. The build takes weeks. The monthly platform bill often stays below the cost of the manual reporting the warehouse replaces.

Enterprise work adds governance. The tables are rarely the hard part. Several business units bring competing metric definitions, and regulated industries add access controls and audit trails on top.

Architecture standards keep an enterprise platform from fragmenting again two years later. Without standards, each business unit builds a separate set of marts. The organization then ends up back at the start with faster hardware.

Company size shapes the scope, but the trigger for hiring is usually a symptom. Our guide on when you need data warehouse consulting walks through the common warning signs.

Published day rates tell you little, because the same job varies by an order of magnitude depending on scope. Four factors move the number most.

The count of source systems sets the integration effort. The state of the historical data sets the cleanup effort. A migration from a legacy platform adds schema conversion and a period of parallel running. Governance and compliance requirements add design and documentation time on top of everything else.

The engagement type then decides how a firm structures the fee.

Most firms bill assessments as a fixed fee and implementation by phase or by month. Ask which model a firm prefers before the first call, since the answer tells you how the firm handles risk. Platform cost sits alongside the fee, and our Snowflake cost guide shows how to model platform spend before you commit.

A data warehouse consulting statement of work should turn each step of the process into something you can check. Six clauses cover most of the risk.

• Deliverables Per Phase: Each phase ends with a named output, such as the assessment report or the target architecture. Payment follows the output, not the calendar.
• Acceptance Tests: The contract defines how the new numbers must match the old ones. Row counts and financial totals are the usual tests.
• Migration Waves: The contract lists which subject area moves first and when the legacy system retires.
• Platform Ownership: Accounts, vendor contracts, and admin access sit in your name from day one.
• Cloud Cost Ownership: The contract names who watches the cloud bill after go-live. The contract also sets the spend level that triggers a review.
• Handover and Support: The contract states what happens in the first 90 days after cutover and who answers the phone.

Data warehouse consulting firms differ most in method. Two firms can both propose Snowflake and run the project in very different ways. The statement of work is where the difference becomes visible, so ask each firm to draft one before you sign.

Airbnb ended the two-answer problem by rebuilding its core tables. The team defined each metric once and served the definition to every reporting tool. The rebuild combined two of the ten services above, data modeling and data governance.

Few companies need all ten services at once. The nine steps decide the order, starting with an honest look at the source data.

Everything above assumes somebody has the time to run the process end to end, and most internal teams don’t. Data Prism runs the process for those teams, and we’re happy to look at your current setup on a free 30-minute call.