Blog › ICP guides

Data architect on retainer: data strategy, data mesh, dimensional modeling, and enterprise data governance advisory on monthly retainer

August 7, 2026 · ~20 min read

A 400-person e-commerce company is running its analytics on a three-year-old Redshift cluster built by a data engineer who left the company. The VP of Engineering has inherited a warehouse with 600 tables, no documentation, three overlapping fact tables for the same revenue metric that produce different numbers, and a data engineering team that spends 40 percent of its time answering ad hoc questions about which table to trust. The VP has approved a data platform modernization project — migrating from Redshift to Snowflake and rebuilding the core data models in dbt — but does not have the internal architectural expertise to make the foundational decisions: whether to use Kimball dimensional modeling or a data vault, how to design the customer and product master records, what data catalog and lineage tooling to implement, and how to structure data ownership across the five product engineering teams that contribute data to the warehouse. The company engages a fractional data architect on monthly retainer to own the architectural decision-making for the migration while the data engineering team owns the implementation.

In month one of the retainer: a current-state architecture assessment that documents the existing Redshift schema, identifies the three competing revenue fact table definitions and the business logic divergence behind each, maps the 15 source system schemas that feed the warehouse, and produces the architectural decision record (ADR) that specifies the target data platform architecture. The assessment output is a 12-page architecture document. The hours behind it — querying the Redshift information schema and pg_stat_user_tables to map table usage and identify the hot and cold tables, tracing the ETL SQL that populates each revenue fact table to understand the business logic differences, reviewing the Snowflake cost modeling documentation and Redshift-to-Snowflake migration guide, evaluating the dbt project structure and testing capabilities — are not visible in the 12 pages.

Data architects and enterprise data architects on monthly retainer — independent data strategy consultants, fractional chief data officers, and boutique data advisory firms — perform their highest-value work in the platform architecture evaluation, dimensional model design, data governance framework development, and lineage mapping that precedes and validates every visible deliverable: the source schema analysis behind the architecture ADR, the grain definition and SCD strategy design behind the star schema ERD, the stewardship role design behind the governance policy document, and the lineage tracing behind the data quality dashboard. This guide covers data platform architecture strategy, dimensional modeling and Data Vault 2.0, data governance framework design, master data management, and data lineage and observability — and how to structure a data architecture retainer that makes the hours behind each advisory function visible.

Data platform architecture strategy

The most consequential decision in a data platform modernization is the architectural pattern: whether to build a centralized data warehouse, a data lakehouse, or a federated data mesh. Each pattern makes different trade-offs in query performance, data freshness, team ownership model, total cost, and scalability. The data architect’s first major advisory function is helping the organization make this decision based on its actual analytical workload, team structure, data volume, and governance maturity — not based on the current industry marketing narrative.

Data warehouse vs. data lakehouse vs. data mesh

The centralized data warehouse (Snowflake, Google BigQuery, Amazon Redshift) stores structured analytical data in a managed columnar storage format optimized for SQL query performance. The warehouse architecture centralizes data ingestion, transformation, and serving in a single platform with strong governance controls, consistent performance SLAs, and a straightforward operational model. The trade-off: all data must be modeled and transformed before it is queryable, which creates a transformation bottleneck as data volume and variety grow; and centralized ownership of all data models creates an organizational scaling problem as the number of data-producing teams increases.

The data lakehouse (Databricks Lakehouse on Delta Lake, Apache Iceberg on Amazon S3 with Athena or Apache Spark, Apache Hudi) stores data in open file formats (Parquet, ORC) on cloud object storage and adds ACID transaction support, schema enforcement, and SQL query capability through a metadata layer. The lakehouse architecture supports both structured SQL analytical workloads and unstructured data science workloads (Python and Spark ML pipelines operating on raw event data) in a single storage tier, eliminating the data lake / data warehouse duplication pattern. The trade-off: higher operational complexity than a managed warehouse (cluster sizing, Spark tuning, file compaction management, and partition optimization require ongoing platform engineering attention) and typically higher latency for interactive SQL queries than a purpose-built warehouse at the same cost point.

The data mesh is an organizational and architectural pattern, not a specific technology — Zhamak Dehghani’s 2019 and 2022 framework describes a sociotechnical approach to scaling data platforms across large organizations where a single central data team cannot keep pace with the data production of multiple autonomous product engineering teams. Data mesh is built on four principles: domain-oriented decentralized data ownership (each product domain team owns, builds, and operates the data products that originate in their domain, rather than hand off raw data to a central data team for transformation); data as a product (each domain team treats the data they expose to other domains as a first-class product with a defined interface, SLA for freshness and quality, and a named product owner accountable for its reliability); self-serve data infrastructure (a central platform team provides the infrastructure — compute, storage, transformation tooling, catalog, governance automation — that enables domain teams to build and operate data products without requiring central data engineering support for each product); and federated computational governance (the central data governance function defines the policies, standards, and interoperability requirements that all domain data products must meet, enforced automatically through the self-serve platform rather than through manual review processes). Data mesh is not appropriate for organizations below 50 data producers; the organizational overhead of the product ownership model exceeds the benefit of decentralization at smaller scales.

Medallion architecture and storage layer design

Regardless of the platform technology chosen, most modern data architectures organize data into storage tiers by transformation maturity. The medallion architecture (popularized by Databricks and widely adopted across lakehouse and warehouse implementations) defines three tiers: Bronze (raw ingested data, stored as-landed with no transformation — the source of truth for reprocessing, audit, and debugging; schema-on-read in a lakehouse, raw staging tables in a warehouse); Silver (cleansed, conformed, and deduplicated data after initial transformation — data types enforced, null values handled, records deduplicated using the business key, and source system formatting standardized; the layer consumed by data analysts for exploratory analysis); and Gold (business-domain-level aggregated and modeled data optimized for specific analytical use cases — the Kimball-style star schema fact and dimension tables, the pre-computed aggregates for the BI tool consumption layer, and the ML feature tables consumed by machine learning pipelines). The data architect designs the medallion tier boundaries — what transformations happen at each layer, who owns each layer, what quality standards apply at each layer, and how data flows from Bronze through Silver to Gold — as the foundational data flow design that governs every subsequent data modeling and pipeline engineering decision.

Platform selection and vendor evaluation

When the organization is evaluating data platform technologies, the data architect conducts a structured vendor assessment that goes beyond the feature comparison matrix to the total cost of ownership, operational complexity, and ecosystem fit. Assessment dimensions include: Query performance against actual workloads (running benchmark queries against the organization’s existing analytical query patterns on both platforms — not the vendor-provided benchmark, but the organization’s actual slow queries and high-cardinality join patterns that stress the query engine); Cost modeling over a 3-year horizon (modeling the total platform cost including compute, storage, egress, and tooling licensing at projected data volume growth, query volume growth, and team size growth — the platforms that appear less expensive at current scale often invert at 10× scale); dbt and transformation tool compatibility (verifying that the organization’s preferred transformation tool — dbt Core, dbt Cloud, Spark Structured Streaming, or Airflow with SQL operators — is fully supported on the target platform with no significant capability gaps); and Governance and security model (evaluating the platform’s role-based access control model, column-level masking capabilities, row-level security, and audit logging against the organization’s compliance requirements). Platform evaluation typically requires 20 to 40 hours of benchmark design, data loading, query testing, and cost modeling before a recommendation is credible.

Dimensional modeling and Data Vault 2.0

Data modeling is the translation of business analytical requirements into a storage schema optimized for the query patterns and historical tracking needs of the organization’s analytical consumers. Two modeling methodologies dominate enterprise data warehousing: Kimball dimensional modeling (optimized for query performance and analytical usability) and Data Vault 2.0 (optimized for auditability, historization, and agile schema evolution).

Kimball dimensional modeling: star schema design

The Kimball dimensional model organizes analytical data into fact tables (which record business process events at a defined grain, with foreign key references to dimension tables and numeric measures) and dimension tables (which store the descriptive context attributes that answer the “who, what, where, when, why, and how” questions about each fact record). The most critical design decision in a Kimball model is the grain declaration: the precise definition of what one row in the fact table represents. The grain must be declared at the atomic level of the business process — one order line item, not one order; one daily account balance snapshot, not one account; one web session event, not one session — because a fact table that aggregates data before storage permanently destroys the dimensional flexibility that makes a star schema analytically useful.

Measure design categorizes each fact table measure by its additive properties across dimensions: additive measures (safely summed across all dimensions — order amount, unit quantity, duration in seconds — the easiest category); semi-additive measures (safely summed across some dimensions but not all — account balance can be summed across accounts but not across time periods; the correct aggregation across time is average or period-end snapshot, not sum); and non-additive measures (cannot be summed across any dimension — ratios, percentages, and per-unit rates must be recomputed from the underlying additive components rather than summed). Semi-additive and non-additive measures are the most common source of incorrect BI report calculations when the measure type is not properly documented and communicated to the analytics engineering and BI development teams.

Slowly Changing Dimension (SCD) design defines how the dimension table handles changes in descriptive attributes over time. SCD Type 1 (overwrite the existing record with the new attribute value — loses historical attribute values; appropriate for corrections and attributes where history has no analytical value); SCD Type 2 (add a new row for each change, with a surrogate key, effective date, expiration date, and current row flag — preserves the complete history of attribute changes; required for any attribute that changes over time and must be tracked for historical analytical accuracy, such as customer segment, product category, and employee department); and SCD Type 3 (add a “previous value” column for the most recent prior value only — appropriate for cases where only the immediate prior value is needed for analytics, such as a customer’s previous pricing tier). SCD Type 2 design for a dimension with 40 attributes that change independently requires careful analysis of which attributes require historical tracking and which can be overwritten — over-applying SCD Type 2 to all attributes creates a slowly changing dimension that grows rapidly and degrades query performance.

Data Vault 2.0: Hubs, Links, and Satellites

Data Vault 2.0 (developed by Dan Linstedt and Michael Olschimke, formalized in their 2016 book) is an alternative modeling methodology for enterprise data warehouses where audit trail completeness, regulatory compliance, and schema agility requirements make the Kimball model’s optimized-for-query design a poor fit. Data Vault 2.0 organizes data into three entity types: Hubs (contain the unique business keys for each core business entity — Customer Hub (CUSTOMER_BK), Product Hub (PRODUCT_BK), Order Hub (ORDER_BK) — with a surrogate hash key, load date, and record source; Hubs never change after initial load, making them the stable identity spine of the vault); Links (contain the relationships between Hubs — Order-LineItem Link (hash key, ORDER_HK, LINEITEM_HK, load date, record source); Link tables record the fact that a relationship existed at the point in time of the load, with no additional attributes; relationships in a Data Vault are first-class entities rather than foreign key constraints); and Satellites (contain the descriptive attributes and measures associated with a Hub or Link — Customer Contact Satellite (CUSTOMER_HK, load date, load end date, record source, email, phone, address); Satellite records are immutable after insert, creating a complete audit history of every attribute change for every entity without the surrogate key proliferation of SCD Type 2 dimension tables).

Data Vault 2.0 also defines Point-in-Time (PIT) tables and Bridge tables as query optimization structures. PIT tables pre-join the snapshot states of multiple Satellites for a Hub at a specific point in time, improving the query performance of historical analytical queries that would otherwise require complex join and filtering logic against the Satellite tables. Bridge tables pre-join related Hubs through their shared Links, improving the query performance of relationship traversal queries. These performance structures — and the logic for rebuilding them as Satellite data changes — require significant engineering design and implementation work: typically 15 to 30 hours of PIT and Bridge table design for a vault with 10 to 20 business entities, invisible in the finished query optimization layer that analysts experience as a fast dashboard.

Data governance framework design

Data governance is the organizational framework that defines who is accountable for data quality, how data is catalogued and discoverable, what data quality standards are enforced, and how data access and usage is controlled. A data platform without governance produces fast analytics on unreliable data; governance without a data platform produces policies that nobody enforces. The data architect designs the governance framework that connects the data platform’s technical capabilities to the organization’s data quality, compliance, and discoverability requirements.

Data stewardship and domain ownership

Data stewardship defines the accountability model for data quality across the organization. The data architect designs: the data domain taxonomy (the organizational grouping of related data entities into coherent domains — Customer domain (customer profile, contact, preferences, segment), Product domain (catalog, pricing, inventory, attributes), Finance domain (revenue, invoicing, payment, accounting), and Operations domain (order, fulfillment, logistics, returns) — with clear boundaries between domains and a defined process for resolving cross-domain entity conflicts); data steward role design (the accountabilities and time commitment of the domain data steward — the individual in each domain who is accountable for defining data quality rules, approving schema changes, reviewing data quality incident reports, and representing the domain in cross-domain governance forums); data producer and consumer contracts (the formal agreement between a team that produces data (a product engineering team’s application database) and a team that consumes it (the data engineering team) — specifying the schema, data types, business key definition, SLA for data freshness and completeness, and the process for communicating breaking schema changes); and governance forum cadence (the regular governance review meetings where cross-domain data quality issues, schema change requests, and access control reviews are discussed and resolved). Data stewardship framework design typically requires 25 to 50 hours to produce a governance policy document that the organization’s leadership will actually enforce.

Data catalog implementation and metadata management

A data catalog is the discoverability layer that allows analysts, data scientists, and engineers to find, understand, and trust the data they need for their work. The data architect evaluates and selects the catalog platform that fits the organization’s data platform, team size, and budget: DataHub (open-source, LinkedIn-developed, graduated CNCF project; supports automated metadata ingestion from Snowflake, dbt, Airflow, Kafka, and 60+ sources via ingestion recipes; column-level lineage from dbt manifest files; business glossary and data domain management; tag-based access control; free for self-hosted deployment with commercial support from Acryl Data); Atlan (SaaS catalog built on Atlas; automated lineage from dbt, Snowflake, Fivetran, Looker, and Tableau; AI-assisted metadata enrichment; persona-based access control; pricing per user); Alation (enterprise SaaS catalog with strong governance and compliance features; behavioral analytics on query patterns; stewardship workflow management; pricing per user, typically higher than Atlan at enterprise scale); and Collibra (enterprise GRC-grade catalog with policy management, regulatory compliance documentation, and data lineage for regulated industries; used primarily at large financial services and healthcare organizations with formal data governance requirements). Catalog implementation — configuring automated ingestion connectors, validating lineage accuracy, seeding the business glossary, and training data stewards on metadata curation — typically requires 30 to 60 hours beyond the platform selection decision.

Data quality monitoring design

Data quality monitoring defines the automated checks that detect data quality failures before they reach the dashboards and reports that business users trust. The data architect designs: data quality rule taxonomy (the categories of rules applied to each data domain — completeness rules (no null values in required fields), uniqueness rules (no duplicate records on the business key), referential integrity rules (every foreign key has a matching primary key), format validation rules (phone numbers match the expected format, dates are within plausible ranges), distribution rules (the row count in today’s delivery falls within the expected range for this day of the week and month), and business logic rules (order amount is positive, invoice total equals the sum of line item amounts)); monitoring tool selection (Great Expectations, a Python-based open-source framework for defining expectation suites against Pandas DataFrames and SQL tables; Soda, a YAML-based data quality platform with native Snowflake and BigQuery integration and Soda Cloud for monitoring dashboard and alerting; or Monte Carlo, a ML-based data observability platform that automatically detects anomalies in row count, null rate, and distribution without requiring manual rule definition); and incident escalation and resolution workflow (the process for routing a data quality alert to the responsible data steward, the SLA for acknowledging and resolving data quality incidents, and the communication protocol for notifying downstream analytical consumers when a data quality incident affects their reports). Data quality monitoring design typically requires 15 to 30 hours of rule design, tool configuration, and alerting workflow definition.

Master data management strategy

Master data management (MDM) addresses the most persistent and expensive data quality problem in enterprise organizations: multiple systems maintaining conflicting versions of the same shared business entity. A customer who appears in the CRM as “Acme Corp”, in the ERP as “Acme Corporation”, and in the support system as “Acme” (with three different address records and four different contact email addresses) is not three customers — but without a master data management system that maintains the golden record for the Customer entity and synchronizes it to all consuming systems, every analytical report that joins customer data across systems will produce different results.

Golden record design and match-merge strategy

The golden record is the authoritative, deduplicated, and enriched version of a shared entity maintained by the MDM system and distributed to consuming systems. The data architect designs: entity scope prioritization (which shared entities to tackle first based on business impact and deduplication complexity — Customer is almost always the highest-impact entity for commercial organizations; Product is the highest-impact entity for multi-channel retailers; Location is critical for organizations with physical service delivery); attribute survivorship rules (the logic that determines which source system’s attribute value wins when multiple systems provide conflicting values for the same attribute — the CRM wins for account name and primary contact; the ERP wins for billing address and payment terms; the most recently updated system wins for phone number); match-merge algorithm design (the probabilistic or deterministic record linkage approach that identifies records referring to the same entity across source systems — deterministic matching on exact email address match, probabilistic matching using weighted scoring across name similarity (Jaro-Winkler or Levenshtein distance), address similarity (USPS address standardization match), and phone number match; the match threshold above which two records are automatically merged, the range below which they are automatically treated as distinct entities, and the review queue for uncertain matches); and MDM system integration architecture (the bidirectional integration between the MDM system and each consuming system that keeps the golden record current when source system records change and distributes golden record updates back to the consuming systems).

Data lineage and observability

Data lineage is the documentation of how each piece of data flows from its source system through every transformation, join, and aggregation step to its final analytical consumption point. When an analyst reports that the Q3 revenue number in the executive dashboard differs from the Q3 revenue number in the finance report by $240,000, the data architect uses the lineage graph to trace both numbers back to their source and identify the transformation divergence that explains the discrepancy. Without lineage documentation, this investigation takes days; with complete column-level lineage, it takes hours.

Column-level lineage implementation

Column-level lineage traces the origin, transformation, and consumption of each individual column value through the data platform. The data architect evaluates lineage implementation approaches: dbt native lineage (dbt Cloud and dbt Core generate a directed acyclic graph (DAG) of model dependencies from the ref() and source() functions in dbt SQL files, providing table-level lineage automatically and column-level lineage through the column-level lineage feature in dbt Cloud or through dbt-column-lineage open-source package analysis); OpenLineage and Marquez (OpenLineage is an open standard for lineage metadata collection, maintained by the Linux Foundation; Marquez is the open-source reference implementation of the OpenLineage metadata server; Airflow, Spark, and dbt emit lineage events in the OpenLineage format, which Marquez collects and serves through a lineage API that the data catalog integrates with); and native catalog lineage (DataHub, Atlan, and Collibra provide their own lineage ingestion from dbt manifest files, SQL parser-based lineage extraction from query logs, and direct API ingestion from pipeline orchestrators, enabling lineage display within the catalog UI without a separate lineage server). Column-level lineage implementation — configuring the lineage collection pipeline, validating lineage accuracy against known transformation logic, and integrating lineage display into the data catalog — typically requires 15 to 30 hours of configuration and validation work invisible in the finished lineage graph that analysts use to debug metric discrepancies.

HourTab for data architecture retainers

Data architecture retainer work produces deliverables — architecture decision records, dimensional model ERDs, governance policy documents, and data catalog configurations — that are visible to the VP of Engineering and the data engineering team. The hours behind each deliverable — the source schema analysis, the grain definition and SCD strategy design, the data quality rule taxonomy, and the lineage implementation configuration — are not visible unless the data architect logs them with enough specificity to connect the hours to the architectural function performed.

HourTab gives data architects a retainer dashboard that their engineering clients can bookmark without creating an account: the month’s committed hours, the hours consumed to date, and the work log entries that connect each hour block to the deliverable, the analysis performed, and the architectural decision produced. When the VP of Engineering can see that 24 of the month’s 60 retainer hours went to the customer analytics dimensional model design and 18 went to the data catalog implementation, the retainer usage discussion is grounded in the actual distribution of architectural work rather than a generalized sense that the data architect is or is not worth the monthly engagement fee.

The retainer model works for data architecture because architectural advisory workload is not linear — a platform migration decision week may consume 40 hours; a steady-state implementation support month may consume 15. A monthly hour commitment smooths this variance for both the data architect and the engineering organization, ensuring architectural leadership is available for the high-stakes decisions that shape the data platform for the next three to five years without the overhead of a full-time hire.

Frequently asked questions

What does a data architect on retainer typically do?

A data architect or enterprise data architect on monthly retainer provides ongoing data strategy and architecture advisory across data platform architecture (warehouse vs. lakehouse vs. data mesh), dimensional modeling (Kimball star schema or Data Vault 2.0), data governance framework design (stewardship roles, data catalog, data quality monitoring), and master data management strategy (golden record design, match-merge algorithm, and MDM integration architecture).

What data architecture work is most commonly underlogged?

The most systematically underlogged categories are platform architecture evaluation (20 to 40 hours of benchmark and cost modeling invisible in the architecture ADR), dimensional model design (15 to 35 hours of grain definition, measure classification, and SCD strategy design invisible in the star schema ERD), data governance framework design (25 to 50 hours of stewardship role design and data contract template development invisible in the governance policy document), and data lineage mapping (10 to 25 hours of transformation tracing invisible in the lineage diagram). Detailed work log entries that capture the specific deliverable and analysis work behind each hour block make this invisible architectural work visible.

What should a data architecture retainer agreement include?

Retainer agreements should specify: scope of advisory (strategy and design only, or also implementation review); monthly hour commitment and activity types; IP ownership for architectural deliverables; platform access requirements; and escalation process for time-sensitive architecture decisions. Monthly retainer amounts for fractional data architecture advisory typically range from $12,000 to $30,000 per month covering 50 to 100 hours of architectural analysis, design, and advisory.

What are typical retainer rates for data architects?

Independent data architects with 8 to 14 years of experience typically bill $175 to $325 per hour. Principal data architects and enterprise data architects with 14 to 20 years of experience typically bill $275 to $500 per hour. Data architects at boutique data strategy consulting firms typically bill $225 to $400 per hour. Monthly retainer amounts range from $12,000 to $25,000 per month for part-time engagements, increasing to $20,000 to $40,000 per month for comprehensive retainers during major data platform migrations or governance transformations.

How should data architect retainer hours be logged?

Work log entries should capture: the architectural domain, the specific deliverable, the analysis performed, and the output. Example: “Dimensional Modeling — Customer Analytics Star Schema. Task: design the star schema fact and dimension tables for the customer analytics subject area. Work: source schema review of CRM and product database (6 hr); grain declaration and measure classification (4 hr); SCD strategy design for Customer Dimension (5 hr); conformed dimension design for Date, Product, and Channel (6 hr); team validation session (3 hr). Total 24 hours. Output: star schema ERD, dimensional model specification, SCD decision record.” Entries that connect hours to specific design decisions give engineering leadership a clear record of the architectural investment.