Data Architect interview questions
100 real questions with model answers and explanations for Data Modeler candidates.
See a Data Architect resume example →Practice with flashcards
Spaced repetition · Hunter Pass
Questions
A data architect defines how data is organized and governed, while engineers build the pipelines and analysts use the data to answer questions.
- The architect turns business needs into models, standards, and relationships between systems.
- The engineer implements ingestion, transformation, storage, and reliable operation.
- The analyst queries trusted datasets and presents findings through reports or dashboards.
Why interviewers ask this: The interviewer checks whether you understand the boundaries and collaboration between common data roles.
I would scope the first version around one named business process and the subject area it needs.
- For example, I would start with store sales and related customer and product details instead of modeling all retail data at once.
- I would list the source systems, business owner, expected outputs, and terms used by that process.
- I would mark inventory replenishment and supplier contracts as out of scope for this version.
- I would confirm the scope with sample reporting questions before designing entities and relationships.
Why interviewers ask this: The interviewer checks whether you can set practical process and subject-area boundaries before starting a model.
They describe the same domain at increasing levels of implementation detail.
- A conceptual model shows major business concepts such as Customer, Order, and Product.
- A logical model adds attributes, keys, relationships, and rules without choosing a database.
- A physical model defines tables, column types, constraints, and indexes for a specific platform such as PostgreSQL.
Why interviewers ask this: The interviewer evaluates whether you can move from business concepts to an implementable schema.
An entity is a distinct business object, while an attribute describes that object.
- Customer and Invoice are entities because the business tracks them independently.
- customer_email and invoice_date are attributes because they describe an entity.
- A useful check is whether the concept needs its own identity and relationships to other objects.
Why interviewers ask this: The interviewer checks whether you can identify the basic building blocks of a data model.
A relationship connects entities, and cardinality states how many records may participate on each side.
- One customer can place many orders, so Customer to Order is one-to-many.
- One order can contain many products and one product can appear in many orders, so that relationship is many-to-many.
- Optionality also matters: an order must have a customer, while a customer may have zero orders.
Why interviewers ask this: A strong answer names common cardinalities and includes whether a relationship is optional or required.
A primary key identifies a row, and a foreign key links another row to it while the database enforces valid references.
- customers.customer_id can uniquely identify each customer.
- orders.customer_id can reference that key so every order points to an existing customer.
- Referential integrity rejects invalid references and controls what happens when a parent row is updated or deleted.
Why interviewers ask this: The interviewer checks whether you understand identity, relationships, and database-enforced consistency.
I choose a stable natural key when one exists, a surrogate key when business identifiers can change, and a composite key when uniqueness depends on several fields.
- An ISO country code is a reasonable natural key because it is meaningful and controlled.
- A generated customer_id isolates internal relationships from a changeable email address.
- An order_items row may use order_id plus line_number as a composite key.
Why interviewers ask this: A strong answer distinguishes the key types through stability and a concrete uniqueness rule.
The first three normal forms remove repeating values and dependencies that do not belong to the whole key.
- First normal form requires atomic values and no repeating column groups.
- Second normal form removes dependencies on only part of a composite key.
- Third normal form removes dependencies between non-key attributes, such as storing postal_code details in a Customer table.
Why interviewers ask this: The interviewer wants a practical understanding of how normalization reduces duplication and update anomalies.
I would denormalize when simpler or faster reads justify controlled duplication.
- A reporting table may store customer_region beside each sale to avoid repeated joins.
- The cost is extra storage and a risk that duplicated values become inconsistent.
- I keep the normalized source of truth and document how the denormalized table is refreshed.
Why interviewers ask this: A strong answer presents denormalization as a measured read optimization rather than a default design.
OLTP systems run day-to-day transactions, while OLAP systems support analytical queries over large histories.
- An order service performs many short inserts and updates with strict consistency.
- A warehouse scans and aggregates millions of sales rows for trends and dashboards.
- OLTP schemas are often normalized, while OLAP models often use facts and dimensions for easier analysis.
Why interviewers ask this: The interviewer checks whether you can match data models to operational and analytical workloads.
A data warehouse integrates data across the organization, while a data mart serves a narrower team or subject area.
- A warehouse may combine sales, finance, support, and product data under shared definitions.
- A marketing mart may expose only campaign, customer, and conversion models.
- A mart can improve usability, but copied marts need conformed definitions to avoid conflicting metrics.
Why interviewers ask this: A strong answer distinguishes scope and explains the risk of isolated team datasets.
A data lake stores varied files cheaply, while a lakehouse adds table-like management and reliable analytics on those files.
- A lake can hold raw CSV, JSON, images, and Parquet in object storage.
- A lakehouse format such as Delta Lake or Apache Iceberg adds schemas, transactions, and table metadata.
- The added controls make curated lake data reliable for SQL queries and BI tools to use.
Why interviewers ask this: The interviewer checks whether you know why a lakehouse adds structure without requiring advanced internals.
ETL transforms data before loading it into the target, while ELT loads raw data first and transforms it inside the target platform.
- ETL can protect a constrained target by sending only cleaned and shaped data.
- ELT uses warehouse compute in platforms such as Snowflake or BigQuery for transformations.
- The choice affects where transformation logic runs, but both still need validation and lineage.
Why interviewers ask this: A strong answer explains the order of operations and where compute is used.
Batch processing fits data that can arrive on a schedule, while streaming fits events that must be available with low delay.
- A nightly finance report can load all of yesterday's transactions in one batch.
- Fraud alerts may need payment events within seconds, which favors streaming.
- Streaming adds operational complexity, so I would not choose it when hourly or daily freshness meets the business need.
Why interviewers ask this: The interviewer evaluates whether you choose processing modes from freshness needs rather than fashion.
Schema-on-write validates and shapes data before storage, while schema-on-read applies structure when data is queried.
- A warehouse table rejects rows that do not match its declared columns and types.
- A data lake may keep raw JSON and let each reader select and cast fields later.
- Schema-on-write improves consistency, while schema-on-read preserves flexibility but shifts work and risk to consumers.
Why interviewers ask this: A strong answer links each approach to validation timing, flexibility, and consumer effort.
These layers separate temporary ingestion, source-faithful history, and business-ready datasets.
- Staging holds short-lived files or tables while a load is being validated.
- Raw preserves source values with minimal changes so the team can replay or audit data.
- Curated applies types, quality rules, joins, and business definitions for trusted consumption.
Why interviewers ask this: The interviewer checks whether you understand how layers isolate responsibilities in a basic data platform.
A fact table records measurable business events, while dimensions describe the context around those events.
- Sales facts can contain quantity, revenue, date_key, product_key, and store_key.
- Product and Store dimensions contain labels, categories, and locations used to group facts.
- Fact tables are usually large and narrow, while dimensions are smaller and descriptive.
Why interviewers ask this: A strong answer identifies measures, descriptive context, and the keys connecting them.
A star schema keeps dimensions denormalized around a fact table, while a snowflake schema splits dimension details into related tables.
- A star may keep product category and department in the Product dimension for simple queries.
- A snowflake may separate Product, Category, and Department to reduce repeated attributes.
- Stars are easier for analysts, while snowflakes can save duplication at the cost of more joins.
Why interviewers ask this: The interviewer checks whether you understand the usability and normalization trade-off in dimensional models.
The declared grain defines exactly what one fact row represents and makes its measures interpretable.
- One row might represent one order line, not one order or one daily total.
- Every dimension key and measure must describe that same order-line event.
- Mixing grains can repeat order totals across lines and inflate sums.
Why interviewers ask this: A strong answer treats grain as the first design decision and connects it to correct aggregation.
A transaction fact table stores one row for each business event at its most useful atomic grain.
- Examples include one order line, one payment, or one website click.
- Rows are normally appended as events occur rather than overwritten as a current snapshot.
- Atomic events can later be aggregated by date, customer, product, or other dimensions.
Why interviewers ask this: The interviewer checks whether you recognize event-level facts and their analytical flexibility.
Locked questions
- 21
What is a periodic snapshot fact table?
modelingfact-tablessnapshot - 22
What is an accumulating snapshot fact table?
modelingfact-tablessnapshot - 23
What are additive, semi-additive, and non-additive measures?
- 24
What is a conformed dimension?
conformed-dimensions - 25
What is a role-playing dimension?
- 26
What is a degenerate dimension?
- 27
What is a junk dimension?
- 28
How does a Slowly Changing Dimension Type 1 handle changes?
scdslowly-changing-dimensions - 29
How does a Slowly Changing Dimension Type 2 preserve history?
scdslowly-changing-dimensions - 30
Why use a date dimension instead of only a timestamp column?
schema - 31
When is a bridge table useful in a dimensional model?
bridge-tables - 32
What is a factless fact table?
modelingfact-tablesfactless-fact-tables - 33
How do CSV, JSON, and Parquet differ as data formats?
parquet - 34
What is data partitioning and how can it help queries?
queriespartitioningdata-partitioning - 35
How is clustering different from partitioning?
data-partitioningdata-clusteringpartitioning - 36
Why does columnar storage suit analytical workloads?
schemacolumnar-storage - 37
What is a dbt model?
dbt - 38
What is a dbt source and why declare one?
dbt - 39
What basic tests would you add to a dbt model?
dbttesting - 40
What are a DAG, task, and dependency in Apache Airflow?
dependenciesairflow - 41
What are common dimensions of data quality?
quality - 42
Which simple data-quality checks would you run on a new dataset?
- 43
What is the difference between technical and business metadata?
metadata-management - 44
What is data lineage and why is it useful?
data-lineagelineage - 45
How do a data catalog and a business glossary differ?
data-catalogbusiness-glossary - 46
What is the difference between a data owner and a data steward?
- 47
What does it mean to classify PII in a data model?
modelingdata-modelingpii - 48
What does least privilege mean for data access?
least-privilegeaccess-control - 49
Why should a data model include retention rules?
modelingdata-modelingretention - 50
How should a team document and version a data model with Git?
modelingdata-modelinggit - 51
An ecommerce team wants sales reporting by day, product, customer, and channel. What grain and star schema would you propose?
schemamodelingstar-schema - 52
A source has one order header and several order lines, while analysts need both order counts and product sales. How would you model it?
- 53
Revenue doubles after a sales fact is joined to a table containing several promotions per order. How would you fix the query or model?
queries - 54
Some sales rows have a customer key that is null or absent from the customer dimension. How would you keep those facts reportable?
fundamentals - 55
A customer SCD2 table contains two current rows for the same customer and overlapping effective dates. What would you do?
- 56
Yesterday's sales fact arrives today after the daily warehouse load has finished. How would you process it?
warehouseconcurrency - 57
A sale arrives before its new product record is available in the product dimension. How would you handle the late dimension row?
- 58
An incremental load is rerun after a timeout and creates duplicate transactions. How would you make the rerun safe?
resiliencepipelinestransactions - 59
A daily source extract contains inserts and updates but silently omits records deleted at the source. How would you detect and represent deletes?
- 60
You have loaded a new sales model from three source tables. What basic reconciliations would you run before release?
react - 61
A report loses 8 percent of orders after joining orders to customers. How would you debug the loss?
- 62
A query intended to return daily product revenue produces several rows per product and day. What would you inspect?
queries - 63
A staging table receives multiple versions of the same customer in one batch. How would you select one row deterministically?
batch - 64
A source sends null country values and blank strings, but reports need a consistent customer country. How would you model them?
fundamentals - 65
A supplier adds a new column to a daily CSV file and the ingestion job fails. What is your local fix?
schema - 66
A JSON source changes customer.age from a number to a string for some records. How would you keep the load running safely?
- 67
You must store a daily 20 GB event extract for repeated analytical scans. Would you choose CSV or Parquet?
parquet - 68
A query on a five-year events table is slow even though the table is partitioned by event_date. What simple change would you check first?
queriespartitioning - 69
An hourly pipeline writes thousands of tiny Parquet files and analytical reads become slow. What modest fix would you propose?
ci-cdparquet - 70
You create a dbt customer dimension with customer_id as its business key. Which first tests would you add?
dbttestingbusiness-keys - 71
A dbt orders model references customer_id from the customer dimension. How would you test the reference?
dbt - 72
An order_status field should contain only pending, paid, shipped, or cancelled. How would you guard that rule in dbt?
dbt - 73
The daily payments source has not updated for 30 hours, but dbt models can still run. What would you configure?
configdbt - 74
A dbt staging model fails, and you need to rerun it with only its downstream models after fixing the error. How would you limit the run?
dbt - 75
An Airflow task calling an HTTP API receives status 429 and retries again almost immediately. How would you configure it?
configairflowhttp - 76
A corrected tax rule must be reapplied to the last seven days of sales. How would you backfill that window safely?
backfill - 77
A daily revenue task sometimes starts before the exchange-rate task finishes and produces null converted amounts. How would you fix the DAG?
fundamentals - 78
Two percent of incoming customer rows have invalid email or birth-date values. How would you keep good rows available without hiding the bad ones?
- 79
You are asked to map a source field order_total_text into the warehouse field net_amount. What would you put in a source-to-target mapping?
warehouse - 80
A hiring model mixes one row per application status event with milestone columns for the whole application. What would you raise in the design review?
milestonesdesignschema - 81
An ERwin model diff shows that customer.email was removed and customer.phone changed length. What would you do before applying it?
- 82
A new curated customer table is ready, but analysts cannot find or understand it. What catalog information would you register?
- 83
The catalog shows a dashboard connected directly to a raw orders table, but it actually uses a curated sales model. How would you repair the table-level lineage?
lineage - 84
Sales defines an active customer as someone who bought in 90 days, while Marketing uses a 30-day website visit. What would you do with the glossary conflict?
- 85
A support dashboard needs customer region and ticket counts, but the source table also contains names, emails, and phone numbers. How would you handle PII?
pii - 86
A contractor needs to validate one finance report for a week. What warehouse access would you grant?
warehousevalidation - 87
Policy requires raw application logs containing user identifiers to be deleted after 30 days. How would you implement and verify it?
- 88
A Snowflake warehouse used by a nightly job was left running all weekend. What local changes would you make?
warehousesnowflake - 89
A BigQuery dashboard uses SELECT * on a wide events table, but its chart displays only three columns. How would you reduce query cost?
queriesschemabigquery - 90
You and a teammate changed the same dbt model file and now there is a Git conflict. How would you resolve it safely?
gitdbt - 91
A stakeholder asks for an active customer table but gives no definition of active. What would you ask before modeling it?
stakeholder-managementcommunication - 92
An analyst says your monthly revenue is 5 percent higher than the finance report. How would you investigate?
- 93
You must deliver a model while the source owner has not confirmed whether refund amounts are negative. How would you document the assumption?
- 94
A team receives raw clickstream JSON and needs stable daily campaign summaries. Where would you place each dataset?
- 95
Analysts need sales by the customer's region at purchase time, but the customer table stores only the latest region. What would you change?
- 96
Two source systems both use customer_id 42 for different people. How would you prevent the warehouse from merging them?
warehousesystem-design - 97
A retailer sends one inventory file per store every night, and analysts need stock levels by day. What fact grain would you use?
fact-grain - 98
One product can belong to several campaigns, and one campaign contains many products. How would you model this for campaign filtering?
- 99
A training team needs to report which employees attended each required course, even though attendance has no amount. What table would you model?
- 100
A CSV load suddenly rejects dates because one supplier changed values from 2026-07-15 to 15/07/2026. How would you repair it?