Data Architect interview questions

I would use the bus matrix to map each business process to reusable conformed dimensions before building marts.

• Rows represent processes such as orders, shipments, and invoices, while columns represent dimensions such as customer, product, and date.
• A marked intersection commits a process to the same dimension meaning, keys, and attribute rules used by other marts.
• I would deliver one process at a time, but review every new dimension against the matrix so incremental work still forms an integrated warehouse.

I would define one governed customer identity and publish mappings from each regional source to it.

• The conformed dimension needs common surrogate keys, attribute semantics, history policy, and unknown-member handling across both marts.
• Region-specific attributes can remain optional extensions, but shared fields such as customer status must use one definition and code set.
• I would version the contract and reconcile old reports before replacing local dimensions, because identical column names do not prove conformance.

I would declare the grain in one sentence before choosing measures, usually one row per order line per shipment split.

• That grain preserves partial shipments and supports shipment-level timing without mixing line and order facts.
• Order-level values such as order discount must be allocated by an agreed rule or stored in a separate order-grain fact table.
• Every dimension key and measure must describe exactly one row at that grain, and uniqueness tests should enforce it.

I would encode each measure's valid aggregation behavior in the model and semantic layer.

• Transaction amount is additive across account and time, so ordinary sums are valid.
• Account balance is additive across accounts but not across dates, so reports should use the closing snapshot for the selected period.
• Ratios such as margin percentage are non-additive, so I would store numerator and denominator and calculate the ratio after aggregation.

I would create a new dimension version for every tracked segment change and preserve the old version.

• Each row gets a surrogate key plus effective_from, effective_to, and current indicators, while the business customer key repeats across versions.
• The load expires the current row and inserts the new row in one atomic operation only when tracked attributes change.
• Facts resolve the surrogate key valid at the event timestamp, so historical sales retain the segment known at that time.

I would use Type 3 only when the business needs a bounded comparison between current and previous values, not full history.

• A sales territory dimension can keep current_region and previous_region when reports compare only the latest reorganization.
• It keeps one row per entity and makes that comparison simple, but a second change overwrites the older prior value.
• If auditors need every effective period, Type 2 is the correct model despite its extra rows and temporal joins.

Type 6 combines Type 2 version history, Type 1 propagation of the current value, and Type 3-style current-versus-previous comparison.

• Each customer version keeps effective dates, segment_at_time, current_segment, and previous_segment.
• When Gold changes to Platinum, the load inserts a Type 2 version, propagates current_segment = Platinum to all versions, and retains Gold in previous_segment.
• Analysts can group past sales by the segment at sale time, today's segment, or the latest current-versus-previous pair without maintaining separate dimensions.
• Loads cost more because one change inserts a version and updates current attributes on prior rows, so I use Type 6 only for an explicit reporting need.

I would create an inferred customer member immediately and enrich it when the full dimension record arrives.

• The inferred row uses the real business key, a stable surrogate key, and placeholders for unknown attributes.
• The fact references that surrogate key instead of the generic unknown member, preserving the relationship from the first load.
• Later processing updates the inferred row in place or applies the SCD policy, with an inferred flag ensuring the repair is traceable and idempotent.

I would use a weighted bridge between the encounter fact and diagnosis dimension.

• The bridge contains the encounter key, diagnosis key, allocation weight, and optionally the rule version that produced the weight.
• Weights for one encounter must sum to 1, enforced by a data quality test, so allocated cost is not double counted.
• I would expose both allocated and unallocated measures because counts of encounters should use distinct encounter keys rather than summing bridge rows.

I would choose the hierarchy representation from the dominant query pattern and keep its history separate from product identity.

• A parent-child table handles uneven depth and reparenting cleanly, while a flattened path or bridge speeds common ancestor queries.
• Type 2 versions on hierarchy relationships preserve where a product belonged during each effective period.
• I would materialize level labels only for stable reporting levels, because forcing every branch into fixed columns creates false members.

I would model stable business identities as hubs, relationships as links, and descriptive history as satellites.

• Customer and order become hubs keyed by their business keys, while a customer-order link records their association.
• Name, status, and source attributes belong in satellites with load timestamp and record source, allowing independent history and arrival rates.
• I would avoid putting descriptive attributes in hubs or links because that couples identity and relationship structures to volatile source fields.

A hub business key must identify the same business concept consistently across its intended scope.

• I prefer a source-issued durable identifier such as customer_number, not a name, email, or warehouse surrogate key.
• If several sources use different identifiers, a governed crosswalk or composite key must define whether they represent one identity or separate hubs.
• I retain the original key value and record source so collisions, normalization rules, and future remapping remain auditable.

Hash keys enable deterministic parallel loading, but they require one canonical input rule across every producer.

• The same normalized business key can generate the same hub key without a central sequence, so hubs, links, and satellites load independently.
• I specify field order, delimiter, null token, trimming, case handling, encoding, and hash algorithm in a shared contract.
• I retain raw business keys and test for collisions; changing canonicalization later requires controlled dual computation rather than silently rewriting history.

Hashdiff lets a satellite compare one canonical digest instead of every descriptive column.

• A new satellite row is inserted only when the incoming hashdiff differs from the latest row for the parent key.
• The digest must cover the intended payload columns in a fixed order with explicit null and type normalization.
• Adding a column changes the digest domain, so I version the satellite or backfill deliberately rather than treating every existing row as changed.

I would add PIT and bridge structures when repeated temporal assembly of raw vault data becomes a measured query bottleneck.

• A PIT table stores the relevant satellite row pointers for a hub at selected as-of dates, reducing repeated effective-date joins.
• A bridge precomputes paths across links for common relationship traversals such as customer to account to transaction.
• Both duplicate derivable state and need refresh rules, so I treat them as performance structures with lineage, not as new sources of truth.

I would place reusable governed derivations in the Business Vault and expose dimensional marts for consumption-friendly analytics.

• Business rules such as survivorship, calculated links, and standardized status mappings belong in traceable derived satellites or links.
• Keeping those rules out of the Raw Vault preserves source fidelity and allows them to evolve without reloading raw history.
• Star schemas still serve BI better because they offer declared grain and simpler joins, so the Business Vault should not become a direct reporting layer by default.

I would define each medallion layer by guarantees and allowed transformations, not by folder names.

• Bronze preserves replayable source records with ingestion metadata and minimal normalization.
• Silver deduplicates, validates, standardizes keys and types, and applies reusable domain rules while retaining rejected records separately.
• Gold publishes stable business-grain products such as dimensional marts and aggregates with contracts suited to dashboards or applications.

I would choose a warehouse for managed SQL analytics and a lakehouse when open files or multi-engine access are core requirements.

• Snowflake or BigQuery reduces operations and gives strong workload management for BI teams that mainly consume structured data.
• A lakehouse on object storage with Delta Lake or Iceberg supports Spark, streaming, and machine learning over the same governed tables.
• The lakehouse adds file compaction, catalog, and engine-compatibility work, so lower storage cost alone is not enough to justify it.

I would rely on the transaction log to provide atomic commits, snapshot isolation, and one table state for batch and streaming readers.

• Optimistic concurrency checks reject conflicting metadata or file changes instead of exposing partial output.
• Table versions support time travel and rollback for reproducible reads, while checkpoints keep log replay manageable.
• Schema enforcement blocks accidental incompatible writes, and controlled evolution admits intended new columns without replacing the table.

Iceberg decouples logical table metadata from physical file layout, so evolution does not require rewriting every consumer.

• Field IDs let columns be safely renamed, reordered, or added without relying only on names or positions.
• Hidden partitioning lets queries use logical columns while partition specs evolve from day to hour or from identity to bucket transforms.
• Metadata snapshots provide atomic commits, time travel, and rollback, while old and new files remain readable under their respective specs.