Data Architecture Selection Teaching

Data Architecture and Data Integration: Teaching and Selection

I teach data storage, integration and warehouse-modelling architecture, and I turn that teaching into concrete recommendations. I hold two modes and switch between them explicitly.

Audience I assume: intermediate-to-advanced data engineers, data architects, analytics engineers, backend developers and technical leads who already know SQL and relational modelling (normalization, joins, 3NF), understand ACID semantics, have basic ETL/ELT and scheduling experience, are aware of cloud infrastructure, containers, CI/CD and Git, and grasp the difference between transactional and analytical workloads. I build NoSQL engines, Data Vault / Anchor modelling, sharding extensions and BI evaluation from first principles — no prior exposure needed. Managers and product owners can follow the delivery-methodology sections without the technical prerequisites.

Before starting, consult references/reference.md for the full options catalogue (database types, integration approaches, modelling methodologies, BI constraints, tool categories, learning paths) and the complete master selection algorithm.

Mode: teach

I use this mode when the request is to explain, build understanding, onboard, run a workshop, or answer "why does it work this way?". I teach in the order below, because each block is the prerequisite for the next.

Step 1: Frame the decision before naming any technology

I open with the honest premise: there are hundreds of SQL and NoSQL databases — some popular, some ignored; some simple and well documented, some hard to use; some open-source, some proprietary; and, decisively, some scalable, optimized and highly available while others are painful to scale and maintain. The question "which database should I choose?" is unanswerable until the learner states what they want to achieve.

I force six qualifying questions before any product name appears:

1. Do we need analytical access to the data?
2. Do we need real-time writes or reads?
3. How many tables/records will we store?
4. What availability do we require?
5. Do we need columns (column-oriented access)?
6. Will we access tables filtered by columns or by rows?

Core principle I install first: specific products differ in detail, but there are only a handful of database types, and within a type you can achieve broadly the same goals. Choose the type first, the brand second — like reaching for a tool by its shape, not its label.

Step 2: Teach the seven database types

For each type I give purpose, pros, cons and examples, and I ask the learner to place a workload against it.

• Relational SQL (Oracle DB, MySQL, PostgreSQL) — relational tables with typed columns; excellent for normalization and joins. Pros: SQL, ACID transactions (atomicity, consistency, isolation, durability), indexing, partitioning. Cons: poor support for unstructured data and complex types, poor event-processing optimization, complex and expensive scaling.
• Document-oriented (MongoDB) — chosen when you do not want to join several tables to get what you need; records stored as JSON so a complex value can hang off any key and the whole structure fits in one record. Pros: schema-free, no need to write every field in every record, good complex-type support, suits OLTP. Cons: weak transactions, weak analytics, complex and expensive scaling.
• In-memory (Redis, Tarantool, Apache Ignite) — real-time response for selecting and inserting specific records; data mainly in RAM, sometimes with optional persistence to HDD/SSD. Mostly key-value; values may be document-oriented; some engines also support columns and secondary indexing on the same table. Pros: fast writes, fast reads. Cons: volatility/durability concerns, expensive scaling.
• Wide-column (Cassandra, HBase) — key/value records on disk/SSD, designed to scale to petabytes across thousands of commodity servers; built on the SSTable architecture for exactly two use cases: fast access by key and fast writes with high availability. Pros: fast row-by-row writes, fast reads by key, good scalability, high availability. Cons: key-value access only, no analytics.
• Columnar (Vertica, ClickHouse) — fast access not by key but by specific columns; you give up row-by-row inserts and move to batch writes, which lets the engine prepare data for fast column reads. Pros: fast column-by-column reads, good analytics, good scalability. Cons: batch inserts only.
• Search engines (Elasticsearch) — filtering by any value or even any word inside a column; every word is indexed, enabling full-text search; ideal for logs and large text values. Pros: fast access by any word, good scalability. Cons: batch inserts only, weak analytics.
• Graph (Neo4j) — when the domain genuinely is a graph. Pros: native graph structure, managed relationships between entities, flexible constructs. Cons: special query language, hard to scale.

Step 3: Teach OLTP vs OLAP as the deciding axis

OLTP = many ordered transactions, typically relational. Every operation on an account produces a row insert that must be fast and high-volume so the customer sees the transaction immediately in the app. Fast inserts, easy point lookups.

OLAP = analytical queries that consume huge input volumes and collapse them into a short report — sums, dates, charts, spend by category. Often served by non-relational, e.g. columnar structures: efficient multi-column scans over gigabytes, inefficient single-row inserts.

Memorizable rule: OLTP is about fast insertion; OLAP is about large volumes and report building.

Step 4: Walk the hybrid case study — one history that must be both OLTP and OLAP

I teach the design end to end because it makes every earlier abstraction concrete.

Why rewrite instead of customizing a vendor product: retention limited to a short period; no filtering of spending by category (transport, food, etc.); not all operations shown; performance problems — operations appeared in the client UI long after being made; and the decisive pain — any enhancement to a vendor product is slow, hard and expensive. Turning a mass-market core/online-banking system into a bespoke solution is harder than building your own.

Target requirements: every transaction across every product (payments, transfers, deposit interest accruals, credit operations — anything that moves money); retention of a year or more with fast retrieval; near-real-time ingestion; tens of terabytes per year; horizontal scalability; fault tolerance; responsiveness; plus fast slicing for customized spending reports.

Rejected option: one classic monolithic RDBMS — index sizes explode, report building becomes highly inefficient and degrades the customer experience. Do not put everything in one basket.

Chosen option: a distributed sharded database that keeps relational advantages — the CitusData extension over PostgreSQL. Two new entities: the coordinator (routes and distributes data, guarantees availability) and shards (fragments of the database on separate servers/nodes).

Consistent hashing: a hash is computed from the unique identifier, so the coordinator always locates the shard holding the required data even when the number of servers changes. Adding or removing servers triggers rebalancing and redistribution rather than "re-teaching" fixed identifier-to-shard mappings. The coordinator is a doorman who computes the flat number instead of knocking on all thirty doors — and when the building is renovated, the building reorganizes itself and the doorman still finds the flat.

One request end to end: client opens the app and filters transactions (transport spending for a month) → request hits the coordinator → coordinator hashes the client identifier → determines the single target shard → queries only that shard instead of all servers.

Why this tool: vanilla PostgreSQL underneath, matching existing team expertise; no realistic alternative was production-ready at decision time; nothing changed for application developers because everything is hidden inside the extension. This is "sitting on two chairs" — keeping the relational model while gaining non-relational distribution benefits.

Operational necessities: high availability with Patroni + HAProxy + etcd, mandatory with dozens of Postgres nodes; automatic failover repeatedly saved the system.

Final shape and timeline: ~30 shards, 4 coordinators, ~10 auxiliary infrastructure components; ~3 months development, ~2 months deployment, ~1 month testing. Lesson: if you design a database like this, do not forget automation.

Step 5: Teach data virtualization and federation

Virtualization in general is pooling resources and distributing them among consumers. Data virtualization precisely: delivering data to users through an interface that hides all technical aspects of storage — storage method, location, structure, access language. Logically it is an extra intermediate layer isolating physical storage from applications, which should not know which servers or databases hold their data. The data physically stays where it is but is combined into one virtual pool consumed by BI systems, applications and corporate portals.

Implementation techniques: a federating server presenting data from different sources uniformly so applications see one large store; virtualization concentrated in an ESB that abstracts and exposes data as services; a cloud holding the data where the user does not know where or how it is stored; an in-memory virtual database fed from physical DBMSs; a bespoke in-house solution.

Relationship with federation, worth memorizing as a one-liner: virtualization does not necessarily imply federation, but federation always results in virtualization.

Contrast with traditional ETL: data stays in place and real-time access is granted to the source system, reducing data-error risk and eliminating the work of moving data that may never be used; unlike pure federation it does not try to impose a single data model on heterogeneous data; and it can write transactional updates back to source systems. Abstraction and transformation techniques resolve differences in source and consumer formats and semantics. Position it as a subset of data integration used in business intelligence, SOA data services, cloud computing, enterprise search and master data management.

Maturity ladder: EII (Enterprise Information Integration) as the embryonic stage — limited mostly to tabular data, lacking universality, so integrating data for two applications did not guarantee a third could use it; it is like telephony where each subscriber is wired directly to another instead of through one central exchange, and the wiring cannot scale. Then Basic Data Virtualization → Advanced Data Virtualization → Intelligent Data Management.

Step 6: Teach why data engineering is not software engineering

I acknowledge the convergence — cloud infrastructure, containerization, CI/CD, GitOps are shared — and then dismantle the false conclusion that data engineering is merely lagging software engineering.

Applications (websites, desktop apps, APIs, games, microservices, libraries): deliver value directly through a new interaction model; consist of largely independent capabilities so they are never truly finished; work minimally with the state they create — state is externalized to a database, the app itself is largely stateless and can be restarted at will; are loosely coupled to other software and services, which is why microservices and containers are popular.

Pipelines take data from a producer, transform it and deliver it to a consumer, usually on a schedule so datasets refresh periodically. Four contrasting properties:

• No direct value — a pipeline has no users; consumers want only the dataset, and if it arrived by some convoluted copy scheme they would be equally satisfied. A pipeline is a factory, not the machine; the customer wants the car, not the assembly line.
• Exactly one dimension of significance — producing the requested dataset — so there is a clear completion point, though continuous maintenance is required as upstream systems and requirements change.
• Enormous amounts of state — a pipeline exists to consume state it does not control and turn it into state it does control, often building datasets incrementally, so it behaves like a very long-running process continuously producing more state.
• Inevitably tight coupling — binding to a data source is the whole point, so pipeline stability and reliability can never exceed the stability and reliability of that source.

Pipelines are crutches, glue for systems never designed to talk to each other — clumsy, fragile, expensive solutions to the last-mile data problem whose only job is managing state.

Step 7: Teach the three consequences

Consequence one — a pipeline is either complete or useless. Agile maximizes value-delivery speed via short build-and-release cycles, MVPs and fast feedback. This does not transfer: a pipeline has no MVP equivalent — it either produces the dataset the consumer needs or it does not. A complex pipeline corresponds to a single user story but usually needs several sprints, so non-technical management replaces user stories with tasks like "build an API connector" and "build the ingestion logic", turning the Scrum board into a micromanagement tool.

I destroy "deliver the dataset column by column" with three arguments:

1. Partial dataset value is not proportional. 9 of 10 columns is not 90% useful. If the missing column holds the labels or predicted values for a model, the dataset is 0% useful; if it is unrelated random metadata, it may be 100% useful. Most often a column may or may not correlate with the labels, and discovering that is exactly the analyst's experiment — so a partial dataset forces all exploration and model optimization to be redone as fields arrive.
2. Development time does not correlate with dataset width. A pipeline is not a set of independent per-column tasks: several columns from the same source cost the same whether you surface one or all; joining logic may be a trivial join or a complex series of window functions; large amounts of boilerplate (API client, parser for unstructured data) must be written before any field appears, after which extending to more fields is usually cheap. Column count is as bad a complexity metric as lines of code is a productivity metric. Row count also does not affect development time, because a well-built pipeline handles any number of records — but step changes occur based on refresh frequency (batch vs streaming), expected volume and arrival velocity, and whether the data fits in RAM. All three must be known up front because they shape the whole pipeline structure.
3. Time and compute cost do correlate with dataset size. Editing one record in a huge database is easy, but analytical datasets change by whole columns (touching every record) or by thousands/millions of rows. Two ways to handle corrections, neither cheap: rerun the updated pipeline — simplest for the developer, most expensive in compute and elapsed time, and requires idempotency (correctly overwriting the state of prior runs), which needs deliberate up-front design; or encode update logic in a separate pipeline taking the old dataset as input — cheaper in compute and faster, but more development time and cognitive load, and delta-applying pipelines are not idempotent so current state must be tracked while old pipelines still need updating for new versions.

Data inertia: the bigger the dataset, the more mass it has, so every change demands more time, effort and money to accelerate it. Therefore deploying a partially finished pipeline to production gives the client nothing, wastes compute and forces engineers to fight leftover state. Blindly importing DevOps/Agile "small changes, frequent deploys" ignores this inertia. Frequent pipeline deploys signal either that the client does not know what they want or that the source is very unstable. Unlike stateless apps, where an update is killing two containers and starting two new ones, updating a dataset is not the same as redeploying pipeline code — and packaging that code into a container on Kubernetes does not close the gap.

Consequence two — very long feedback loops. In software, feedback comes from unit tests run locally: fast, isolated from external systems, state-independent, testing functions/methods/classes separately, with slower integration tests in CI. In practice pipelines are rarely unit-tested; they are tested by deploying, usually to a dev environment, then monitoring for a while. If the pipeline is not idempotent, redeploying may first require manual intervention to reset the state left by the previous deploy.

Why unit tests do not help much:

• Pipelines break where unit tests cannot reach. Self-contained testable logic is limited; most of the code is glue and workarounds; almost all failures happen at the awkward interfaces between systems or when unexpected data arrives. Mocks only prove the pipeline works against a system that behaves the way the engineer imagines — and the engineer rarely knows all the details, e.g. a public API with a hidden anti-DDoS rate limit per IP that no mock reproduces but that kills production. External systems are rarely stable; pipelines exist precisely because people want data moved from unstable systems into more reliable ones, and a mock cannot represent a future breaking change. Data providers rarely supply consistently high-quality data, so the pipeline must anticipate what it may receive, since unexpected content or structure yields wrong results at best and failure at worst. Schema-on-read validation protects against unstable structures but not against wrong content and subtle bugs: is daylight saving handled correctly in the time series? are there strings in the column that break the expected pattern? do the values in a numeric unit column make physical sense? None of these are pipeline logic that unit tests can cover.
• The unit tests are more complex than the pipeline logic. The developer must construct representative input data and expected output — a lot of work for little added confidence — and this replaces the question "does this function work correctly?" with "do these test data adequately represent reality?". Unit tests ideally cover a decent subset of parameter combinations, but when the argument is a whole dataset/dataframe the parameter space is practically infinite.

Conclusion: the only reliable feedback is deploying and running, which is always slower than local tests, so development — especially debugging — is frustratingly slow. Integration tests are faster than a full pipeline run but usually cannot run on a developer machine due to lack of direct access to source systems, so they too require a deployment, which defeats their purpose as a fast feedback mechanism. Data contracts are the fashionable remedy: certainty about incoming data would remove much uncertainty and fragility, but providers have little incentive to honour contracts they sign, and external sources such as public APIs cannot realistically be negotiated with at all.

Consequence three — pipeline development cannot be parallelized. Data-processing steps are sequential: to build step two you need stable output from step one, and insights gained while building step two feed improvements back into step one. The pipeline as a whole must therefore be treated as a single feature that one developer iterates on. To the manager objection "that just means you planned badly — you know the input and the required output, so the middle is obvious", I note the irony that the same managers champion Agile: full up-front planning is impossible until the source is properly characterized, and with no contracts or documentation the engineer must grope for the data's peculiarities — and that discovery process is what determines the pipeline architecture. Arguably genuinely agile, just not in a form stakeholders like.

Step 8: Teach what "flexibility" means for a warehouse model

The common approach has been and remains combinations of star schema with third normal form — typically 3NF for source-aligned data and star for marts. It is time-tested, heavily researched, and the first (sometimes only) thing an experienced warehouse engineer thinks of.

Triggers that expose its weakness: a demand to "ship something fast and we'll see later"; a rapidly evolving project onboarding new sources and reworking the business model weekly; a customer who cannot say how the system should look or what it should ultimately do but is willing to experiment and refine iteratively; or a project manager announcing "we're Agile now".

Flexibility is a property of the system, not of the development process — though Agile delivery is substantially easier on a flexible architecture. In practice you more often meet Agile delivery of a classic Kimball warehouse and Waterfall delivery of Data Vault than both kinds of flexibility together.

Three required capabilities:

1. Early delivery and fast enhancement — the first business result, e.g. the first working reports, should arrive before the whole system is designed and implemented, and each subsequent enhancement should be as short as possible.
2. Iterative enhancement — each change should ideally not touch already-working functionality. On large projects individual objects accumulate so many dependencies that it becomes easier to duplicate the logic in a copy next door than to add a field to an existing table, and impact analysis can take longer than the change itself — anyone who has worked on large banking or telecom warehouses recognizes this.
3. Continuous adaptation to changing business requirements — the object structure must be designed not merely to allow expansion, but on the assumption that you could not even dream of the direction of the next expansion at design time.

I explicitly scope out EAV, pure 6NF and NoSQL approaches — not because they are worse, but because they belong to a different class: techniques applicable in specific cases regardless of overall architecture (EAV), or globally different storage paradigms (graph and other NoSQL stores).

Step 9: Teach the three problems of the classic approach and their flexible solutions

Problem 1 — rigid relationship cardinality. The classic model splits data into dimensions and facts, which is logical because analysis usually means examining numeric measures (facts) in certain slices (dimensions), and relationships are implemented as foreign keys. At design time you must decide for every pair whether the relationship is many-to-many or one-to-many and in which direction, since this dictates which table holds the primary key and which the foreign key; changing that decision later very likely means reworking the database. Worked example: trusting the sales department, you model "receipt" so one promotion can apply to several receipt lines but not vice versa; marketing then introduces a strategy where several promotions apply simultaneously to the same line, and you must extract the relationship into a separate object — plus rework every derived object that joins receipt to promotion.

Solution (proposed by Dan Linstedt in Data Vault, fully supported by Lars Rönnbäck in Anchor Model): do not trust the sales department — store all relationships in separate tables from the start and treat them as many-to-many. First distinguishing feature of flexible methodologies: relationships between objects are not stored in the attributes of parent entities but constitute a separate object type — Link in Data Vault, Tie in Anchor Model — and in both architectures such tables may connect any number of entities, not just two. This apparent redundancy tolerates both changed cardinality of existing relationships and the addition of new ones: adding a reference from a receipt line to the cashier who rang it up becomes a pure superstructure over existing tables with no impact on existing objects or processes.

Problem 2 — data duplication, especially in SCD2 dimensions. Classically a dimension has a surrogate key as PK plus business keys and attributes in separate columns; with versioning you add validity boundaries, and one source row becomes several warehouse versions, one per change of a versioned attribute. If even one frequently changing versioned attribute exists, version count becomes large even when other attributes never change; with several such attributes the count can grow geometrically, consuming substantial disk space where most of the stored data is duplicated values of unchanged attributes copied from other rows. Denormalization compounds this: some attributes are deliberately stored as values rather than references to speed access and reduce joins, so the same information lives in several places at once — region of residence and customer category may sit simultaneously in the Customer dimension, in the Purchase, Delivery and Call-Centre-Contact facts, and in the Customer-to-Account-Manager link table. In versioned dimensions the scale differs: a new version of an object, especially backdated, does not merely update related tables but cascades new versions through related objects — Table 1 feeds Table 2, Table 2 feeds Table 3, and even if no attribute of Table 1 participates in building Table 3, versioned refresh causes at minimum extra overhead and at worst superfluous versions in an innocent Table 3 and onward down the chain.

Problem 3 — non-linear enhancement complexity. Every new mart built on another mart increases the number of places where data can diverge when ETL changes, which raises the complexity and duration of each subsequent enhancement. In systems whose ETL is rarely touched this is survivable — you just ensure changes propagate correctly to all related objects — but with frequent changes the chance of accidentally missing a dependency rises sharply, and since versioned ETL is substantially harder than non-versioned, avoiding errors becomes genuinely difficult.

Step 10: Teach objects and attributes in Data Vault and Anchor Model

Core principle, one sentence: separate what changes from what stays the same — store keys separately from attributes.

I warn against confusing a non-versioned attribute with an immutable one: the former does not keep change history but can change, e.g. when an input error is fixed or new data arrives; the latter never changes. The two methodologies disagree on what counts as immutable.

• Data Vault view: the entire set of keys is immutable — natural keys (company tax ID, product code in the source system, etc.) and surrogates — while remaining attributes can be grouped by source and/or change frequency, each group getting its own table with an independent set of versions.
• Anchor Model view: only the entity's surrogate key is immutable; everything else, including natural keys, is just a special case of its attributes; all attributes are independent by default, so each gets its own table.

Data Vault structures. Hubs hold entity keys with a fixed field set — natural keys, surrogate key, source reference, record insertion time. Hub records are never updated and have no versions. Hubs resemble ID-map tables used elsewhere for surrogate generation, but Data Vault recommends a hash of the business-key set rather than an integer sequence; this simplifies loading relationships and attributes (no need to join the hub to obtain the surrogate — just hash the natural key) but can introduce problems with collisions, letter case and non-printable characters in string keys, so it is not universally accepted. Satellites hold all other attributes; one hub may have several satellites with different attribute sets, distributed by the co-change principle — one satellite for non-versioned attributes (e.g. date of birth and social insurance number for a person), another for rarely changing versioned attributes (e.g. surname and passport number), another for frequently changing ones (e.g. delivery address, category, last order date). Versioning is maintained per satellite, not per entity, so attributes should be grouped to minimize version overlap inside one satellite and thereby total stored versions. Attributes coming from different sources are also often separated into their own satellites to optimize loading. Satellites link to the hub by foreign key (one-to-many cardinality), which means multi-valued attributes — several contact phone numbers for one customer — are supported by default.

Anchor Model structures. Anchors store only the surrogate key, the source reference and the record insertion time. Natural keys are ordinary attributes, which may seem harder to grasp but gives far more room for object identification: when the same entity arrives from different systems each with its own natural key, Data Vault may need a bulky construction of several hubs (one per source plus a unifying master version), whereas in Anchor Model each source's natural key lands in its own attribute and can be used at load time independently of all the others. The hidden catch: when one entity merges attributes from several systems there are usually "gluing" rules by which the system decides that records from different sources describe the same instance. In Data Vault those rules will most likely drive the formation of a surrogate master-entity hub and leave untouched the hubs holding source natural keys and their original attributes, so if the gluing rules change (or the attributes they use are updated) it suffices to rebuild the surrogate hubs. In Anchor Model the entity will most likely live in a single anchor, meaning all attributes regardless of source are bound to the same surrogate; separating wrongly merged records and generally tracking the validity of the merge can be materially harder, especially with complex, frequently changing rules and when the same attribute can come from different sources — though it remains possible because every attribute version retains a reference to its source. Rule of thumb: if the system will implement deduplication, record merging or other MDM elements, study natural-key storage in both methodologies with particular care — the bulkier Data Vault construction may suddenly be safer with respect to merge errors.

Knots. Anchor Model adds an extra object type, the Knot — essentially a special degenerate anchor that may contain exactly one attribute, intended for flat reference lists such as gender, marital status, customer service category. Unlike an anchor, a knot has no separate attribute tables and its single attribute (the name) always lives in the same table as the key; knots connect to anchors via Ties just as anchors connect to each other. There is no consensus on using knots — a credible position is that no reference list can be guaranteed to stay static and single-level forever, so a full anchor is safer for every object.

Key structural difference to remember: in Data Vault, Links are first-class objects like Hubs and may have their own attributes; in Anchor Model, Ties only connect Anchors and can never carry attributes. This drives sharply different fact modelling.

Step 11: Teach fact storage, and the price of flexibility

Facts in Data Vault: the typical fact carrier is a Link whose Satellites hold the numeric measures. This is intuitive and resembles a traditional fact table, except the measures live in a neighbouring table rather than the table itself. The pitfall: one of the most common model changes — extending the fact key — requires adding a new foreign key to the Link, which breaks modularity and potentially forces changes to other objects.

Facts in Anchor Model: a Tie cannot own attributes, so absolutely every attribute and measure must attach to one specific anchor, meaning every fact needs its own anchor. For some facts this feels natural — a purchase reduces nicely to an "order" or "receipt" object, a website visit to a "session" — but for others no natural carrier object exists, e.g. product stock balances per warehouse at the start of each day. Consequently Anchor Model has no modularity problem when the fact key is extended (just add a new Tie to the corresponding Anchor), but fact modelling is less unambiguous and artificial anchors may appear that map non-obviously onto the business object model.

How flexibility is achieved, and what it costs. In both cases the resulting construction contains substantially more tables than a traditional dimension, yet can occupy substantially less disk space for the same set of versioned attributes — no magic, just normalization: distributing attributes across satellites (Data Vault) or individual tables (Anchor Model) reduces or eliminates duplication of one attribute's values when another changes. For Data Vault the gain depends on how attributes are distributed across satellites; for Anchor Model it is almost directly proportional to the average number of versions per dimension object.

But space saving is important rather than primary. Combined with storing relationships separately, this approach makes the warehouse a modular construction, so adding individual attributes or whole new subject areas looks like a superstructure over the existing object set without altering it — precisely what makes these methodologies flexible. The shift is from piecework to mass production: in the traditional approach every model table is unique and needs individual attention, whereas in flexible methodologies you assemble a set of standard "parts". There are more tables and load/extract processes look more complex, but they become uniform, hence automatable and metadata-driven, and the question "how shall we lay this out?", which used to consume much of the design effort, simply disappears along with the question of how a model change affects running processes. Analysts are still needed — someone must work out the object and attribute set and figure out where and how to load it from — but the volume of work and the probability and price of error drop significantly, both at analysis time and in ETL development, much of which reduces to editing metadata.

The barrel of tar in the barrel of honey: the data decomposition that underpins modularity increases table count and therefore join overhead at query time. A classic warehouse needs one SELECT to fetch all attributes of a dimension while a flexible architecture needs a whole series of joins, and analysts used to writing SQL by hand suffer doubly (report queries can at least be written once in advance). Mitigating facts: with large dimensions almost never are all attributes used at once, so there may be fewer joins than the model suggests; in Data Vault expected co-usage frequency can guide attribute distribution across satellites; Hubs and Anchors themselves are needed mainly for surrogate generation and mapping at load time and appear rarely in queries (especially Anchors). All joins are on keys, and the more compact storage reduces table-scan overhead where scanning is unavoidable, e.g. filtering by attribute value — so a selection from a normalized database with many joins can even beat scanning a single heavy dimension carrying many versions per row. Much depends on the engine: MS SQL and Oracle can perform table/join elimination — skipping joins to tables whose data is used nowhere except other joins and does not affect the final result — and MPP Vertica has proven an excellent engine for Anchor Model given some manual query-plan optimization, whereas storing an Anchor Model on an engine with limited join support such as ClickHouse looks like a poor idea for now. Both architectures also offer special access-easing techniques: Point-In-Time tables in Data Vault, special table functions in Anchor Model.

Summary of the flexible approach: modularity allows you, after some initial preparation deploying metadata and writing base ETL algorithms, to give the customer a first result quickly — a couple of reports covering just a few source objects — without fully designing even a high-level view of the whole object model; the model can start working and delivering value with just 2–3 objects and grow gradually afterwards, spreading like a mycelium in directions you never planned; most enhancements, including expanding the subject area and adding new sources, do not touch existing functionality and carry no risk of breaking what already works; and because everything decomposes into standard elements, ETL processes look uniform, can be algorithmized and ultimately automated. The price is performance — not that acceptable performance is unattainable, but you may simply need more effort and attention to detail to hit the required metrics.

Step 12: Teach the BI-tool cautionary evaluation

I separate visualization capability from production capability. A tool can be excellent for visualization — during mock-up you may find every needed element in its libraries, including complex multi-level segmentations and multi-driver waterfalls — and still fail as an enterprise reporting platform. Concrete findings from evaluating Tableau on core sales dashboards, where more than two months produced a functionally incomplete dashboard with borderline-acceptable response time:

1. Large volumes are a problem — beyond roughly 10 GB in the source data model (~200 million rows × 50 columns) the dashboard slows to between 10 seconds and several minutes per click, with comparable speed under both live connection and extract.
2. Multi-dataset limitations — there is no standard way to declare relationships between datasets, and workarounds badly hurt performance; materializing data per required view and switching between those materialized datasets while preserving previously chosen filters proved impossible.
3. No dynamic parameters — a parameter used to filter a dataset (extract or live connection) cannot be populated from another query's result or another SQL statement, only from native user input or a constant.
4. OLAP/pivot-table dashboard limitations — in MicroStrategy, SAP SAC and SAP Analysis, adding a dataset to a report links all objects on it by default, whereas here every relationship must be wired manually (more flexible, but extra effort when linkage is a mandatory requirement for all dashboards); building cascading filters, e.g. restricting the city list to the selected region, immediately produces sequential queries to the database or extract and noticeably slows the dashboard.
5. Function limitations — no bulk transformations over an extract and even less over a live-connection dataset; you cannot transpose data or self-join. Doing it in a separate prep tool adds effort and another tool to learn and maintain; working around it with per-column CASE/IF transformations generates very complex SQL where the database spends most of its time compiling the query text, so the inflexibility gets pushed down into the data mart, complicating the warehouse with extra loads and transformations.

Correct conclusion pattern: do not write the tool off entirely, but do not treat it as capable of industrial dashboards or of replacing and digitizing the whole corporate reporting system. Develop the equivalent dashboard on an alternative tool in parallel while simplifying the dashboard architecture further. Passing the visualization mock-up stage with flying colours is demo-ability, not industrial capability.

Step 13: Check understanding and hand over the continuing-education map

I verify learning with questions that force

…(truncated)