Reading and Understanding Data Schemas Questions

Be able to look at a data schema (table structure) and understand what data is available and how tables relate. At the start of the assessment, you'll be given a schema. Spend a minute understanding it before writing queries. Understand primary keys, foreign keys, and data types.

HardTechnical

33 practiced

In a warehouse using SCD Type 2 dimensions you notice queries joining facts to dimension tables by effective ranges are slow. Propose schema and query strategies to optimize common analytical patterns that need the 'dimension attributes at event time', including indexing, surrogate keys, and pre-computed snapshots.

Sample Answer

Clarify goal: fast analytical queries that return "dimension attribute values as of event time" (SCD Type 2). Propose schema + query patterns, indexing, and pre-computed options.

Schema strategies- Add effective_start/effective_end (timestamps) and is_current flag to dim table.- Add a surrogate_version_id (integer) as a synthetic surrogate for each version row. Keep natural_key + surrogate_version_id unique.- Optionally maintain a compact "dim_history" with (surrogate_version_id, natural_key, effective_start, effective_end, JSON(attributes)) for fast lookups and fewer columns.

Indexing and keys- Cluster/partition dim by natural_key and effective_start (or effective_start DESC) to speed range scans.- Create composite index: (natural_key, effective_start DESC) and/or (natural_key, effective_end) depending on DB.- Index surrogate_version_id for joins.- If using time-partitioning, partition facts by event_date to reduce scanned partitions.

Query patterns1) Version-lookup join (preferred over range join):- Precompute surrogate_version_id on fact load (see ETL section) and join by surrogate_version_id — exact equality join is fastest.2) Effective-range join (if no precompute):- Use lateral/partitioned window pick:

sql

-- pick latest dim version <= event_time
SELECT f.*, d.*
FROM fact f
JOIN LATERAL (
  SELECT * FROM dim
  WHERE natural_key = f.natural_key
    AND effective_start <= f.event_time
  ORDER BY effective_start DESC
  LIMIT 1
) d ON true;

- Use indexed filter columns to make this efficient.

ETL / Precompute (recommended)- During ETL, lookup the correct dim version and store surrogate_version_id on the fact record (or materialized column). This converts expensive range joins into equality joins.- Maintain backfills for historical facts when dims are corrected.

Pre-computed snapshots & materialized views- For common aggregations (daily cohorts, revenue by product-version), build daily materialized snapshots that join facts → dim versions at refresh time. Query these instead of raw joins.- Store denormalized reporting tables with required attributes (wide tables) for dashboards.

Trade-offs and guidance- Precomputing increases storage and ETL complexity but dramatically lowers query latency.- Range-join approaches keep normalization but rely on good indexing and may still be slow at large scale.- Use surrogate_version_id equality joins + partitioning + materialized snapshots as the primary strategy for analytics at scale.

HardTechnical

28 practiced

For a near-real-time dashboard that requires sub-second response for simple counts and sums, discuss schema trade-offs between normalized OLTP-style tables and denormalized pre-aggregated tables. Cover freshness, write amplification, storage cost, and maintenance complexity in your recommendation.

Sample Answer

Situation: You need sub-second counts/sums on a near-real-time dashboard. The schema decision (normalized OLTP vs denormalized pre-aggregates) directly affects latency, freshness, cost, and operational overhead.

Recommendation summary: For sub-second dashboard reads, use denormalized pre-aggregated tables (materialized aggregates) tailored to query patterns, with a small normalized source-of-truth for auditability. This hybrid gives the best read performance while controlling freshness and maintenance complexity.

Trade-offs:

- Freshness - Normalized OLTP: Always current (reads the canonical rows). But computing aggregates at query time adds latency and may require complex realtime OLAP scans. - Pre-aggregated: Freshness depends on update cadence (streaming or micro-batch). You can achieve near-real-time (millisecond–second) freshness with streaming updates, but there’s small lag between event and aggregate.

- Write amplification - Normalized: Low write amplification — write once to row tables. - Pre-aggregated: Higher write amplification — each event may update multiple aggregate rows (different time buckets, dimensions). With streaming systems (Kafka+Flink) this is expected and manageable.

- Storage cost - Normalized: Lower raw-data duplication; storage-efficient for detailed audits. - Pre-aggregated: Extra storage for aggregated shards (time × dimensions). Usually much smaller than raw logs but grows with cardinality and rollups. Prune or use TTLs to control cost.

- Maintenance complexity - Normalized: Simpler schema, easier ETL correctness, but queries are slower; you may need indexes and read replicas. - Pre-aggregated: More operational work—aggregation logic, idempotent updates, backfill, handling late-arriving data, and maintaining consistency. Testing and observability (lag metrics, reconciliation jobs) are essential.

Practical considerations for a Data Analyst:- Identify critical query patterns and dimensions; only pre-aggregate what matters.- Use time-bucketed aggregates (minute/hour) and hierarchical rollups to limit rows.- Implement streaming/upsert pipelines with exactly-once semantics where possible; include reconciliation jobs that compare aggregates to source-of-truth nightly.- Expose both: dashboard reads from aggregates for speed; analysts can query normalized tables for deep dives.

Conclusion: For strict sub-second SLA, pre-aggregated denormalized tables are the pragmatic choice, supplemented by a normalized source for auditability and periodic reconciliation to manage freshness and correctness.

MediumTechnical

23 practiced

You're given a denormalized reporting table that flattens customer, order, and product information for fast queries. Describe how you would verify that the flattened table is correct compared to normalized source tables, detect inconsistencies, and decide whether to rebuild or repair the denormalized table.

Sample Answer

Approach summary: treat this as a three-phase validation problem — verify, diagnose, decide — using reproducible SQL checks, sampling, and automated reconciliation so stakeholders can trust reports.

1) Verify (quick, high-level checks)- Row counts and cardinalities: compare counts in flattened table to expected joins from normalized tables. Example SQL:

sql

-- expected number of flattened rows
  SELECT COUNT(*) FROM orders o
  JOIN customers c ON o.customer_id = c.id
  JOIN order_items oi ON oi.order_id = o.id;

- Key existence: ensure every flattened.customer_id exists in customers, every order_id exists in orders.- Nullability & data types: check unexpected NULLs or type mismatches for important fields (price, dates).

2) Diagnose inconsistencies (narrow down differences)- Field-level aggregates: compare sums, min/max, counts by group (e.g., total_order_value per order).

sql

SELECT o.id, SUM(oi.qty*oi.price) AS expected_total
  FROM orders o JOIN order_items oi ON oi.order_id=o.id
  GROUP BY o.id
  EXCEPT
  SELECT order_id, order_total FROM flattened_table;

- Row-level hashes: create deterministic hash of source join for each logical record and compare to stored hash in flattened table to find changed rows efficiently.- Sample and audit: for a random sample of mismatched rows, pull full source records and examine history/timestamps to detect stale data vs transform bugs.- Check freshness: compare last_updated timestamps; detect lagging ETL runs.

3) Decide repair vs rebuild (criteria)- Scope & frequency: - If <1% rows affected, confined to certain columns, and source history available → targeted repair (update those rows via idempotent upsert). - If wide-spread corruption (schema drift, many columns inconsistent) or ETL logic changed → full rebuild.- Cost & downtime: if rebuild fits maintenance window and simpler/safer, prefer rebuild; if rebuild expensive and business needs continue, do phased repair + run rebuild asynchronously.- Root cause risk: if you can't quickly fix ETL bug, rebuild after fixing pipeline to avoid recurrence.

4) Execution best practices- Use transactional upserts, back up current flattened table, and log changes for audit.- Add automated daily reconciliation checks (row counts, checksums) and alerting.- Add test data and CI for ETL logic so future regressions are caught.

This approach balances fast detection, low-risk fixes, and clear criteria for when to fully rebuild so reporting remains accurate and trustable.

MediumTechnical

31 practiced

A BigQuery table events(event_id STRING, user_id STRING, event_ts TIMESTAMP, payload JSON) stores nested JSON in payload with structure like:

{"product": {"id": "p1", "category": "c1"}, "price": 19.99, "attributes": [{"key":"color","value":"red"}, ...]}

Write a BigQuery SQL snippet that extracts product_id, product_category, price and explodes attributes into rows as attribute_key, attribute_value.

Sample Answer

Approach: parse the JSON payload fields with BigQuery JSON functions, extract scalars for product and price, and UNNEST the attributes array to produce one row per attribute.

sql

SELECT
  event_id,
  user_id,
  JSON_VALUE(payload, '$.product.id')        AS product_id,
  JSON_VALUE(payload, '$.product.category')  AS product_category,
  CAST(JSON_VALUE(payload, '$.price') AS FLOAT64) AS price,
  JSON_VALUE(attr, '$.key')   AS attribute_key,
  JSON_VALUE(attr, '$.value') AS attribute_value,
  event_ts
FROM `project.dataset.events`,
UNNEST(JSON_EXTRACT_ARRAY(payload, '$.attributes')) AS attr

Key points:- JSON_VALUE returns a scalar (string) from a JSON column; CAST to FLOAT64 for numeric price.- JSON_EXTRACT_ARRAY(payload, '$.attributes') returns an array of JSON objects which we UNNEST to explode into rows.- If payload is stored as STRING rather than JSON type, use JSON_EXTRACT_SCALAR/JSON_EXTRACT_ARRAY similarly: - JSON_EXTRACT_SCALAR(payload, '$.product.id') etc.Edge cases:- Missing fields: JSON_VALUE returns NULL if path absent — handle with COALESCE or WHERE to filter.- Empty or null attributes: UNNEST on NULL returns no rows; to keep one row per event even when attributes missing, LEFT JOIN UNNEST pattern can be used:

sql

FROM `...events`
LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(payload,'$.attributes')) AS attr

This yields NULL attribute_key/value for events without attributes.

EasyTechnical

25 practiced

You receive a schema where customer.email and customer.phone_number are nullable. You're asked to prepare a dashboard showing active users by preferred contact channel (email, phone, none). How would you interpret the nullability in the schema, handle nulls during analysis, and implement the logic in SQL for reporting counts per channel?

Sample Answer

Interpretation and assumptions:- Nullable customer.email and customer.phone_number mean some records lack those values. Null can mean "unknown" or "not provided" — confirm with stakeholders if null = opted-out, not collected, or unavailable. Also check for empty strings or placeholders like 'N/A'.

Handling nulls & mapping logic:- Define preferred channel per business rule: if email is present use "email", else if phone present use "phone", else "none". If stakeholders want preference order reversed or a true user preference field, adapt accordingly.- Normalize values: trim, lower-case emails, remove non-digits from phone, treat empty string/whitespace as NULL.

SQL implementation (example, Postgres):

sql

WITH cleaned AS (
  SELECT
    user_id,
    NULLIF(trim(lower(email)), '') AS email,
    NULLIF(regexp_replace(phone_number, '\D','','g'), '') AS phone
  FROM customers
  WHERE status = 'active'  -- or join to active users table
)
SELECT
  channel,
  COUNT(*) AS user_count
FROM (
  SELECT
    user_id,
    CASE
      WHEN email IS NOT NULL THEN 'email'
      WHEN phone IS NOT NULL THEN 'phone'
      ELSE 'none'
    END AS channel
  FROM cleaned
) t
GROUP BY channel
ORDER BY user_count DESC;

Notes:- Include date filters for active timeframe.- Add deduplication if multiple accounts per person.- Document assumptions and validate with stakeholders before dashboarding.

Unlock Full Question Bank

Get access to hundreds of Reading and Understanding Data Schemas interview questions and detailed answers.

Join thousands of developers preparing for their dream job.