Skip to content
Tech Interview Prep
Technical interview guide

Data Modeling Standards

Organization-wide conventions for naming, structuring, and typing data so it's consistent across every pipeline and team.

Read
32 min
Practice MCQs
25
Interview Q&A
25
Edition
v4
Editorial status
Reviewed
Relevant for
Data Architect

Scope: PostgreSQL 18, W3C DCAT 3 and CSVW, Apache Avro and Parquet specifications, OpenLineage, and UK Data Standards Authority guidance current 2026-08-31.

Curated: · Written: · Reviewed:

25 questions

MCQ-8PostgreSQL 16, schema public. A migration creates a table with a quoted mixed-case name: ```sql CREATE TABLE "OrderItems" (id bigint primary key, qty int); INSERT INTO "OrderItems" VALUES (1, 5); ``` A hand-written report runs `SELECT qty FROM OrderItems;` and fails with `ERROR: relation "orderitems" does not exist`. In the same session, `\dt` lists OrderItems and `SELECT qty FROM "OrderItems";` returns 5. What explains the failure?
MCQ-6In Postgres 16, `orders.customer_id` is declared `REFERENCES customers(id)` with no `ON DELETE` clause. An operator runs `DELETE FROM customers WHERE id = 42;` and gets: ``` ERROR: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders" DETAIL: Key (id)=(42) is still referenced from table "orders". ``` Customer 42 has 17 rows in `orders`. What does this error establish?
MCQ-7Postgres 16. `customers(id)` holds 40,000 rows. `addresses(customer_id NOT NULL REFERENCES customers ON DELETE CASCADE)` and `payments(customer_id NOT NULL REFERENCES customers ON DELETE CASCADE)` both point at it. `payments` holds 900,000 rows that tax rules require you to keep for seven years, each still tied to the customer id it was written with. A support runbook runs `DELETE FROM customers WHERE id = 42;` and today it takes the customer's 3 addresses and its 1,900 payments rows with it. The runbook must keep working as that one statement, the addresses must still go, and the 1,900 payments rows must survive with `customer_id = 42` unchanged. Which change does that?
MCQ-21A catalogue lists `fact_orders` and `fact_order_lines` with identical columns: `order_id`, `region`, `order_total`. Only the grain field distinguishes them. What does that field settle for a consumer that the columns cannot?
MCQ-1`fact_order_line` holds one row per order line. A developer adds `customer_orders_to_date`, the number of orders the customer has placed so far, as a column on that table. Customer 42 has 3 orders of 2 lines each; customer 43 has 2 orders of 5 lines each. `SELECT SUM(customer_orders_to_date) FROM fact_order_line` returns 38, but between them the two customers have placed 5 orders. The table holds exactly 16 rows, one per order line. What is the correct fix?
MCQ-2A `dim_customer` table is loaded nightly from two source systems, CRM and billing. Every loaded row gets `customer_sk` from one sequence. Last night CRM sent 4,000 rows and billing sent 4,000 rows; the table grew to 8,000 rows with `customer_sk` running 1–8,000, no gaps or repeats. CRM `cust_id=1001` and billing `cust_id=1001` are the same company, and both are now in the table as separate rows. The team expected the surrogate key to keep one row per company. What does this load show?
MCQ-9A retailer's `fct_orders` joins a `dim_product` built from the ERP master, keyed on `product_sk`, where category `Outdoor` covers tents and patio furniture. Its `fct_returns` joins a separate `dim_product` built from the returns system, keyed on that system's product code, where `Outdoor` covers tents only. A report that groups both facts by category shows an `Outdoor` return rate of 340%. What change makes the report correct?
MCQ-10A retailer's warehouse uses SCD Type 2 for dim_customer. The table has customer_key (surrogate), customer_id (natural), city, valid_from, valid_to, is_current. On 2024-03-15 Ana (customer_id C-7) moves from Lisbon to Porto. The load closes the Lisbon row (customer_key 101, valid_to 2024-03-14, is_current false) and inserts a Porto row (customer_key 102, valid_from 2024-03-15, is_current true). fact_sales stores customer_id and sale_date; it contains sale 1 on 2024-02-10, customer_id C-7, amount 100, and sale 2 on 2024-04-02, customer_id C-7, amount 200. A report runs: SELECT d.city, SUM(f.amount) AS revenue FROM fact_sales f JOIN dim_customer d ON f.customer_id = d.customer_id WHERE d.is_current = true GROUP BY d.city; What does the report return?
MCQ-5A collector writes one row per minute into `latency(p99_ms)`; a minute with no requests gets NULL. Over a 10-minute window, eight minutes recorded p99_ms of 100, 110, 120, 130, 140, 100, 130 and 130 ms, and the other two minutes recorded NULL. A backfill runs `UPDATE latency SET p99_ms = 0 WHERE p99_ms IS NULL`, and the dashboard shows `AVG(p99_ms)` over the window. What does the dashboard report, and what is wrong with it?
MCQ-3A billing service stores each charge as an IEEE 754 binary64 dollar amount (Python `float`, Java `double`) and rounds to two decimal places with round-half-even. A customer's monthly usage computes to exactly $0.025. The service writes $0.03 to the invoice; the finance team's exact-decimal spreadsheet, applying the same round-half-even rule to $0.025, says $0.02. Which explanation accounts for the difference?
MCQ-16A Parquet column `event_time` is physical INT64 with logical type Timestamp(isAdjustedToUTC=true, unit=MILLIS). One row stores the value 1_700_000_000_000. A query engine honors the Timestamp annotation but ignores its `unit` field, always treating the INT64 as microseconds since the Unix epoch. What does it render for that row?
MCQ-4A payments service writes each event's `occurred_at` into a Postgres column declared `timestamp without time zone`, filled by the application with `datetime.now()`: the writing server's local wall clock. Four app servers run the service: three with `TZ=UTC`, one with `TZ=America/New_York`. A reconciliation job running in a UTC session selects rows with `occurred_at >= now() - interval '1 hour'`; the New York server's payments never appear in that window and surface 4 to 5 hours late. On 2024-11-03, between 01:00 and 02:00 local time, that server wrote 1,400 payments whose `occurred_at` values are indistinguishable from payments written an hour later. Which explanation accounts for both symptoms?
MCQ-11A bitemporal `orders` table has two timestamp columns: `placed_at` (when the customer placed the order) and `loaded_at` (when the row was written to the warehouse). A backfill on 2024-03-02 inserted 1,240 orders that were placed on 2024-02-20; every one of those rows has `loaded_at = 2024-03-02`. The 310 orders placed on 2024-02-20 that arrived normally have `loaded_at = 2024-02-20`. Two queries run for 2024-02-20: one filters `placed_at = '2024-02-20'`, the other filters `loaded_at = '2024-02-20'`. What do the `placed_at` and `loaded_at` queries return, in that order?
MCQ-12A payments ledger writes every amount into one `amount` BIGINT column, and neither the column name, the schema, nor the write path records a unit. Checkout writes cents. The refund service, added six months later, writes whole dollars and, like checkout, positive magnitudes. A nightly job scans every row and flags those where `amount > 50000`, comparing the stored number straight against 50000 with no conversion anywhere in the pipeline; last night's run flagged 340 rows. A $600.00 refund written by the refund service three days ago was not flagged. What explains the miss?
MCQ-15A team evolves an Avro schema: v2 adds a `region` field with default `"unknown"`, while v1 producers keep writing records that have no `region` field. What does that default actually do in this pipeline?
MCQ-13A payments service publishes JSON events validated against a JSON Schema with `additionalProperties: false`, and the registry subject is in BACKWARD compatibility mode. Three downstream teams consume the events with generated clients. A team adds an optional `coupon_code` property and deploys producers a week before consumers. What breaks in that window?
MCQ-14An `orders` topic carries Avro-encoded values registered under subject `orders-value` in Confluent Schema Registry 7.x, left at its default compatibility level. Schema v7 declares `user_id` as a string with no default. A developer renames it to `account_id` (same type, no default, no alias) and POSTs the result as v8. What does the registry return?
MCQ-17A dataset is refreshed nightly and used by 12 downstream teams. An incident occurs and no one knows who to contact to fix it. Which metadata element was missing from the catalogue entry?
MCQ-18An orchestration tool records table-level lineage only: each edge is (job, input table, output table). The recorded edges are: `build_stg` reads `raw.orders` and `raw.customers`, writes `stg.orders`; `build_mart` reads `stg.orders`, writes `mart.revenue`; `build_dim` reads `raw.customers`, writes `dim_customers`. An engineer plans to drop the `phone` column from `raw.customers` and queries the lineage graph to scope the impact. Which set of tables does the graph say must be checked for breakage?
MCQ-23`fct_orders` is partitioned by `order_date`. On 2024-03-04 a team fixes an FX-rate bug and backfills 2024-02-18 through 2024-03-03, 15 partitions, with a Spark SQL `INSERT OVERWRITE`, reading `raw_orders`, a Kafka-landed table with 7-day retention. The job logs `SUCCESS: 15 partitions written`. The next morning the dashboard shows revenue 0 for 2024-02-18 through 2024-02-25 and correct values from 2024-02-26 onward. Which explanation fits?
MCQ-24A retailer's finance and marketing dashboards report 41,200 and 38,900 active customers for the same month, both reading the same warehouse tables on the same nightly refresh. The company runs four BI tools, analysts in each need to slice the metric by any combination of about 40 customer dimensions at query time, and the definition changes most weeks. Which mechanism makes all four tools return the same number without per-tool rebuilds?
MCQ-25A platform team publishes a canonical `customer` model. Six months later, 19 of 22 teams have adopted it. A sample of 20 cross-team joins shows 17 complete without a field mapping or null coercion; the other 3 need a cast or null rule. Two dashboards silently undercount because one team changed `customer_id` from string to integer and consumers coerced nulls. Which conclusion about the standard is best supported?
MCQ-19A platform team asks the data governance council for an exception to the standard that all event timestamps be stored in UTC. Their request covers one table, names the platform lead as owner, cites an upstream producer that cannot be changed this quarter, offers a downstream view that converts the stored local times to UTC, sets a 12-month expiry, and gives a migration plan of "revisit after the producer upgrade". The council's policy requires every exception to expire within 6 months, to carry a migration plan with a date, and to have a compensating control that covers every consumer of the affected data. The view covers 4 of the table's 7 consumers; the other 3 read the raw table. What should the council do?
MCQ-20Your data platform team logged 62 production incidents last quarter. Postmortems attribute 38 to schema changes that broke downstream consumers, 14 to tables with no owner so no one was paged, 6 to missing classification tags, and 4 to other causes. You have 3 engineer-weeks to spend this quarter and can fund two controls. Hypothetical pilot results: A, blocking contract tests on schema changes, catches 80% of schema incidents and costs 3 engineer-weeks. B, auto-assigning owner from the pipeline's repo on merge, resolves 70% of ownerless-table incidents and costs 1 engineer-week. C, a nightly lint that flags missing tags, catches 50% of tag incidents and costs 1 engineer-week. D, a stricter table-creation UI, catches 10% of schema incidents because 31 of the 38 came from copied pipelines that bypass the UI, and costs 2 engineer-weeks. Which pair of controls gives the largest expected reduction in incidents within the 3 engineer-week budget?
MCQ-22A lab interface validates incoming results against a controlled vocabulary that an external standards body releases quarterly. The local table holds 4,200 active codes and 380 retired ones, and retired codes are still required to read 11 years of stored results. Clinicians read results from the coded field, not from free text. A partner upgrades ahead of the next release and starts sending codes the local table does not contain; the partner cannot resend, and the next release lands in six weeks. The validator currently drops those records and increments a counter, and 96 results have been lost this month. Which handling should replace it?