Data architect interview questions, with worked answers

How to use these questions

Answer with a design you made: the business problem, the model or platform you chose, the alternative you turned down and why, and what happened a year later. Interviewers trust an architect who can name a decision that went wrong and what they changed.

What a data architect is expected to know

• Modelling: data modellingConceptual, logical and physical models; dimensional models for reporting; naming and keys that last for years. from conceptual to physical, normal forms, dimensional models and data vault, tested with SQLRead and write SQL fluently enough to test a model, profile a source and review the queries teams run on it..
• Platforms: the data warehouseLayers from raw to curated, history kept on purpose, and a design that answers the business's questions fast., lakes and lakehouses, batch loads in ETLHow data moves from source to warehouse, loaded in full or by change, and how a failed load is repaired. and streams in KafkaEvent streams, topics and their contracts; when a stream suits the business better than a nightly load., on AWSObject storage, identity, managed databases and the analytics services teams build on, and their bills. depends on the provider, AzureStorage, identity and the data services teams build on in a Microsoft setting, and what each costs. depends on the provider or Google CloudBigQuery, storage and identity in Google Cloud, and how its pricing shapes a design. depends on the provider.
• Governance: owners, definitions and accessOwners and stewards for each domain, agreed definitions, access rules and a catalogue people actually use., data quality, master data, metadata and lineage, and integration between systems.
• Law and money: personal data under the EU's GDPRPersonal data in the data platform: purpose, minimisation, retention, access and deletion that can be proven. and local laws, security, and what each design costs to run.

Models: conceptual, logical, physical and dimensional

Walk us through conceptual, logical and physical data models: what is each for, and who reads it?

What they check: that you can move from the business's words to tables without losing either.

Short answer: "A conceptual model names the main things the business talks about, such as customer, contract and product, and how they relate; managers can read it. A logical model adds the attributes, keys and rules, still independent of any database; analysts and engineers agree on it. A physical model is how it is built in a given database: tables, types, indexes and partitions."

The full answer: The conceptual model is where most arguments should happen, because a word like customer often means three things in three departments. The logical model is the contract the teams build against. The physical model changes with the platform: a column store for reporting is laid out differently from a database behind an order screen. Keep the three in step, or the drawings stop being trusted.

A weak answer: "I go straight to the tables, the rest is paperwork." Follow-up: "Sales and finance count customers differently." Explain how the conceptual model makes the two meanings visible and gives each a name.

When do you normalise a model, and when do you denormalise it on purpose?

What they check: that you know why normal forms exist and when to break them.

Short answer: "I normalise where data is written, so each fact is stored once and cannot contradict itself: the order system, master data. I denormalise where data is read in bulk, such as reporting, because fewer joins make queries simpler and faster, and the copies are rebuilt from the normalised source."

The full answer: Normalising, usually to third normal form, removes repeated data and the update errors that come with it. Reporting models trade that for speed and readability, which is safe when the data is loaded, not edited by hand. Modern column stores make wide tables cheap, but a wide table nobody can explain is still a problem.

A weak answer: "Always normalise, it is the right way." Follow-up: "A report joins twelve tables and times out." Talk about a reporting layer and what it costs to keep in step.

Design a star schema for sales reporting. How do you keep history with slowly changing dimensions?

What they check: dimensional modelling and a clear grain.

Short answer: "A star schema has one fact table at a stated grain, here one row per order line, with measures such as quantity and amount, surrounded by dimension tables: date, customer, product, store. For a customer who moves region, a slowly changing dimension keeps a new row with valid-from and valid-to dates, so old sales stay with the old region."

The full answer: State the grain first, because every later argument comes back to it. Decide per attribute whether history matters: a typo fix overwrites, a change of region keeps history. Use surrogate keys so the dimension can hold several versions. Conformed dimensions, such as one date and one product table for every fact, let reports from different areas be compared.

A weak answer: "One big table with everything in it." Follow-up: "Finance wants sales by the region the customer has today." Explain how to offer both views from the same dimension.

When would you choose a data vault over a star schema?

What they check: that you know the patterns as tools for different jobs, not as camps.

Short answer: "A data vault splits data into hubs for business keys, links for relations and satellites for changing details, so new sources and changes are added without redesign and every change is kept with its source and time. I use it for the integration layer when there are many changing sources and audit needs. Users still read star schemas built on top."

The full answer: The vault is good at loading and keeping history, and poor at being read by people, so it rarely stands alone. It also brings many tables and needs automation to stay manageable. For a company with five stable sources a well-built warehouse with star schemas may be enough. Say what problem the extra layer solves before adding it.

A weak answer: "Data vault is the modern way, star schemas are old." Follow-up: "The team has two engineers." Discuss whether they can keep a vault running and what you would do instead.

Warehouses, lakes, streams and platform choices

Data warehouse, data lake or lakehouse: how do you choose for a company?

What they check: a choice made from the data and the users, with its costs.

Short answer: "A data warehouse holds cleaned, modelled data for reporting and is easy for analysts. A data lake keeps raw files of any kind cheaply, which suits data science and large volumes, but turns into a swamp without rules. A lakehouse puts table formats and governance on top of the lake so one copy serves both. I choose by the users, the kinds of data, the skills of the team and the cloud already in place."

The full answer: Most companies end with layers: raw data landed as it came, cleaned data, and business-ready tables, whatever the product calls them. The question is less which word than where each layer lives, who may read it and how it is paid for. Moving between models later is costly, so write down why you chose.

A weak answer: "Put everything in the lake and sort it out later." Follow-up: "Analysts complain they cannot find anything." Talk about a catalogue, owners and curated tables.

Batch or streaming: how do you decide how fresh the data must be?

What they check: that freshness is a business requirement with a price, not a fashion.

Short answer: "I ask what decision uses the data and how late it may be: a monthly finance report can load at night, fraud checks need seconds. Batch is simpler and cheaper to run and repair; streaming, with events in KafkaEvent streams, topics and their contracts; when a stream suits the business better than a nightly load. or a cloud service, is worth it only where the delay costs money or risk."

The full answer: Many cases sit between: loads every fifteen minutes or change data capture from the source database give near real time without a full streaming design. Streams need contracts for each event, a plan for late and duplicate events, and people on call. Agree the freshness target with the owner and write it next to the data set.

A weak answer: "Real time for everything, users like it." Follow-up: "The stream delivered the same order twice." Explain how the design handles duplicates.

How do you compare data platforms such as Snowflake, Databricks, BigQuery and Microsoft Fabric, on fit and on cost, without favouring one?

What they check: a fair, written comparison against the company's own needs.

Short answer: "I write down the needs first: kinds of data and users, the cloud the company runs on, security and data residency, skills, and the expected load. Then I test each platform on a few real cases, measure speed and cost, and compare how each charges, by compute time, by data read or by capacity. The answer is the one that fits, with the risks written down."

The full answer: Snowflake, Databricks, BigQuery on Google Cloud and Microsoft Fabric on Azure, with the AWS services as another option, all store large data and run SQL; they differ in how they separate storage and compute, how they handle data science, and how their bills grow. Open table formats make leaving later less painful. Involve finance early, because a design that runs well can still surprise them.

A weak answer: "I use the one I know." Follow-up: "The bill doubled in three months." Explain how you would find the queries and jobs behind it.

Is data mesh right for this company, and how would you introduce it?

What they check: that you know what data mesh asks of an organisation, not only of technology.

Short answer: "Data mesh hands ownership of data to the business domains, which publish data products with an owner, a description and quality promises, on a shared self-service platform, under rules set together. It suits a large company where one central team has become a bottleneck. A small company with one data team usually gains little from it."

The full answer: The hard parts are people and money: domains need data skills and budget, and someone must keep the shared rules. Start with one or two willing domains, define what a data product must offer, and measure whether consumers find and trust them. Without a platform and governance, a mesh becomes many silos.

A weak answer: "Every team builds its own pipelines, that is a mesh." Follow-up: "Two domains publish different customer counts." Explain where the shared definition lives.

Governance, quality, master data, lineage and privacy

How do you set up data governance that people follow, with owners and stewards?

What they check: governance that helps work instead of slowing it down.

Short answer: "I name an owner in the business for each data domain, who decides what the data means and who may use it, and stewards who keep it in order day to day. We agree the key definitions in a shared glossary, put access rules into the platform, and keep a short list of standards. Decisions are written and easy to find."

The full answer: Governance fails when it is a committee that meets and decides nothing. Start with the data that hurts most, such as customer or revenue, show a quick win, and grow from there. Tools for catalogues and policies help, but only after owners exist.

A weak answer: "IT owns all the data." Follow-up: "No department wants to own the product data." Explain how you would find an owner and what you would offer them.

How do you measure and improve data quality?

What they check: quality as measured rules with owners, fixed at the source.

Short answer: "I agree quality rules with the owners: completeness, validity, uniqueness, timeliness and consistency between systems. The pipelines check them on each load and stop or flag bad data, and a dashboard shows the trend. Errors are fixed where they are made, in the source system, not patched in the warehouse forever."

The full answer: Not every field matters equally; start with the ones that feed money, regulators or customers. Give each rule a threshold and an owner, and decide what happens on failure. Report quality to the business in its own words, such as orders without a valid customer.

A weak answer: "We clean the data in the report." Follow-up: "The source team says they have no time." Show the cost of the errors and agree a plan.

How do you handle master data, such as one customer record across many systems?

What they check: matching, golden records and who may change what.

Short answer: "Master data is the shared core data, such as customers, products and suppliers. I decide which system leads for each attribute, how duplicates are matched and merged into one golden record, and how changes flow back to the other systems. An owner decides conflicts."

The full answer: The patterns range from a central hub that all systems read from to a registry that only links the records. The choice depends on how many systems write customer data and how clean they are. Matching is never perfect, so plan for review queues and for undoing a wrong merge.

A weak answer: "We pick the CRM as the truth for everything." Follow-up: "Billing and the CRM hold different addresses." Explain how each attribute gets its leading system.

How do you keep metadata and data lineage, so that people can trust a figure?

What they check: that a figure can be traced from report back to source.

Short answer: "Metadata describes the data: what a table holds, who owns it, how fresh it is. Data lineage shows where each figure comes from and what was done to it on the way. I keep both in a catalogue, collected from the pipelines automatically where possible, so an analyst can see the source of a revenue figure and an engineer can see what breaks if a column changes."

The full answer: Lineage pays off in audits, in incident analysis and before every change. Handwritten lineage goes stale within months, so prefer tools that read it from the code and the platform. Show it where people work, next to the report or the table.

A weak answer: "It is all in the code if someone needs it." Follow-up: "An auditor asks how this number in the annual report was made." Walk through how you would show it.

How do you design for security and privacy, including personal data under the EU's GDPR?

What they check: privacy and access built into the design from the start.

Short answer: "I classify the data, mark personal and sensitive fields, and give access by role, with masking for those who do not need the real values. Personal data is kept only for a stated purpose and time, and the design must be able to find and delete one person's data across every copy. Encryption and access logs are the default."

The full answer: Under the EU's GDPR deletion is the hard part, because data is copied into many layers and backups; design for it early. Other places have their own laws, such as California's CCPA in the US or India's DPDP Act, and data residency rules may limit which cloud region you can use. Work with the data protection officer; the architect designs, the lawyers decide what the law asks.

A weak answer: "Security is the security team's job." Follow-up: "A customer asks for all data you hold about them." Explain how the platform finds it.

Migration, stakeholders and the senior steps

Plan the migration of an old on-premises warehouse to the cloud.

What they check: a plan by stages, with low risk and an honest view of what to change.

Short answer: "I first map what exists: sources, loads, reports, users and the hidden rules in old code. Then we decide what moves as it is, what is rebuilt and what is switched off. We move by subject area, run old and new side by side, compare the figures, and switch the users only when they match."

The full answer: The comparison of figures is the part most plans underrate; budget time for it. Old warehouses carry business rules nobody wrote down, so involve the people who use the reports. Set a date to shut the old system down, or the company pays for two. Integration with systems that stay on site needs a network and security plan of its own.

A weak answer: "We copy all tables over a weekend." Follow-up: "The new revenue figure is off by a little." Explain how you would trace the difference.

Two departments want different definitions of revenue. As a senior data architect, how do you settle it with the stakeholders?

What they check: that you can lead people to a decision without owning the business question.

Short answer: "I make both definitions visible, with their effect on real figures, and bring the owners together. Often both are valid for different questions, so they get two names. If one definition is needed, the data owner or finance decides, and the decision is written in the glossary and built into the model."

The full answer: At principal data architect and head of data architecture level the questions widen to standards for many teams, a roadmap, the budget of the platform and who sits on the architecture board. Expect a case such as three warehouses after two acquisitions, and explain how you would bring them together while the business keeps running.

A weak answer: "I choose the right one and tell them." Follow-up: "The two heads still disagree." Explain who decides and how you escalate.

Your CV for these questions

Each design on your CV invites the questions above: which model, which platform, why, for how many teams, and what it changed. Give each design one line under the job where you made it, and the interviewer will ask you to tell it:

Before the interview, score your CV against a data architect job you want; the skills it marks as missing point to the designs you should be ready to talk about.