Large Dataset Management and Technical Analysis Questions

Develop skills in working efficiently with large datasets: data cleaning and validation, efficient aggregation and manipulation, handling missing data, identifying and managing outliers. Master advanced Excel features or learn SQL for database queries. Practice data quality assessment. Learn efficient workflows that scale with dataset size. Understand data security and privacy considerations.

EasyTechnical

45 practiced

List and define five key data quality dimensions relevant to BI reporting (for example: accuracy, completeness, consistency, timeliness, uniqueness). For each dimension, give a concrete SQL check or metric you would implement to monitor it daily for a customer table with schema:

customers(customer_id, email, joined_at, country)

Explain assumptions for each check.

Sample Answer

1) Accuracy — data values reflect real worldCheck: percent of emails matching simple regex (proxy for valid emails)

sql

SELECT
  1.0 - COUNT(*)::float / NULLIF((SELECT COUNT(*) FROM customers),0) AS invalid_email_rate
FROM customers
WHERE email NOT ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$';

Assumptions: email format implies accuracy for contact info; some valid international TLDs might be flagged.

2) Completeness — required fields are presentCheck: percent non-null for required columns

sql

SELECT
  AVG((email IS NOT NULL)::int) AS pct_email_present,
  AVG((joined_at IS NOT NULL)::int) AS pct_joined_at_present,
  AVG((country IS NOT NULL)::int) AS pct_country_present
FROM customers;

Assumptions: customer_id is primary key so not checked; nulls indicate missing upstream capture.

3) Consistency — values conform to expected domain/rules across rowsCheck: fraction of country values matching ISO list (example table countries)

sql

SELECT
  1.0 - COUNT(*)::float / NULLIF((SELECT COUNT(*) FROM customers),0) AS inconsistent_country_rate
FROM customers c
LEFT JOIN countries ct ON c.country = ct.iso_code
WHERE ct.iso_code IS NULL;

Assumptions: countries.iso_code contains expected canonical codes; case and formatting must match or normalized before join.

4) Timeliness — data is up-to-date for reporting windowCheck: percent of customers with joined_at within last 30 days coming from recent load timestamp (assume ETL_LOAD_TIME table)

sql

SELECT
  COUNT(*) FILTER (WHERE joined_at >= current_date - INTERVAL '30 days')::float
  / NULLIF((SELECT COUNT(*) FROM customers),0) AS pct_joined_last_30d
FROM customers;

Assumptions: ETL runs daily; this metric checks recent user signups vs expectation — combine with ETL load time to detect lag.

5) Uniqueness — no duplicate logical entitiesCheck: duplicate customer_id or duplicate email

sql

-- duplicate customer_id
SELECT COUNT(*) - COUNT(DISTINCT customer_id) AS duplicate_customer_id_count FROM customers;

-- duplicate email (excluding NULLs)
SELECT email, COUNT(*) AS cnt FROM customers
WHERE email IS NOT NULL
GROUP BY email HAVING COUNT(*) > 1;

Assumptions: customer_id should be unique primary key; email should be unique-ish for customers but business rules may allow shared emails — monitor counts and investigate spikes.

For each check schedule daily runs, alert on thresholds (e.g., >1% invalid_email_rate, >0 duplicates), and log historical trends for SLA and root-cause.

MediumSystem Design

38 practiced

Design a star schema for an e-commerce analytics workload that supports fast reporting on sales by product, category, region, and time. Define the fact and dimension tables, surrogate keys, recommended SCD strategy for product price changes, and how to model category hierarchies to balance normalization and dashboard performance.

Sample Answer

Requirements:- Fast reporting of sales by product, category (multi-level), region, and time; support aggregations and point-in-time price analysis.

Grain:- One row per order_line (sale of QTY of a SKU) at transaction time.

High-level star:- Fact: fact_sales- Dimensions: dim_product, dim_category, dim_customer_region, dim_time

Fact table (fact_sales):- surrogate key: sales_id (bigint)- fk_product_sk, fk_category_sk (denormalized leaf category), fk_region_sk, fk_time_sk- measures: quantity, extended_amount, unit_price_at_sale, discount, cost_at_sale- metadata: order_id, order_line_id, created_at- partition by sale_date (time)

dim_product:- product_sk (surrogate, bigint, PK)- natural_key: product_id (business)- attributes: product_name, brand, current_sku, current_category_id- pricing: not store mutable price here for history (see SCD)- type: SCD Type 2 for price-sensitive attributes (see below)- effective_start_date, effective_end_date, is_current

SCD strategy for product price changes:- Use SCD Type 2 for price and other attributes that need historical correctness. On price change, insert new dim_product row with new product_sk, set previous row's effective_end_date. fact_sales stores fk_product_sk as of sale time so historical reports use correct price. Keep unit_price_at_sale in fact for fast reporting and to handle late-arriving facts.

dim_category:- category_sk (surrogate), category_id (natural), name, level (e.g., 1/2/3), parent_category_sk (nullable)- Model hierarchy with both normalized parent pointer AND precomputed flattened path columns: category_path (e.g., "Electronics > Phones > Smartphones") and ancestor_category_sks (array) or separate bridge table dim_category_ancestors for many-to-many queries.- Also maintain top_level_category_sk column in dim_product for very fast dashboard grouping.

dim_time:- time_sk (YYYYMMDD int), date, day, week, month, quarter, year, fiscal fields.

dim_customer_region:- region_sk, region_id, country, state, city, region_group

Performance and trade-offs:- Denormalize: store unit_price_at_sale and leaf category_sk in fact to avoid expensive joins for dashboard aggregates.- Maintain category_path and top_level_category_sk to support rollups without recursive joins.- Keep normalized parent link for maintainability; refresh flattened fields on category change (ETL).- Index/frequent aggregate strategies: create materialized views or aggregate tables (daily_product_category_rollup) for heavy dashboards.- Partition facts by date and cluster by fk_product_sk/fk_category_sk for range scans.

This design balances historical correctness (SCD2), normalization for maintainability, and denormalization for dashboard performance.

EasyTechnical

37 practiced

Explain table partitioning in a data warehouse and give three benefits for analytical queries on large tables (e.g., faster queries, easier maintenance, and efficient data retention). For a daily-partitioned events table, explain how partition pruning works and which partition key you'd choose for a user activity table that needs daily retention and frequent date-range queries.

Sample Answer

Table partitioning splits a large table into smaller, independent segments (partitions) based on a column value (commonly date, range, or hash). The database manages them as one logical table but stores/query them by partition.

Three benefits for analytical queries on large tables:- Faster queries: the planner scans only relevant partitions (partition pruning), reducing I/O and improving query time for date-filtered analyses.- Easier maintenance: you can back up, rebuild, or vacuum individual partitions instead of the whole table, and load data in parallel per partition.- Efficient data retention: drop or archive old partitions quickly (constant-time DROP PARTITION) without expensive DELETEs.

Partition pruning (daily partitions): when a query includes predicates on the partition key (e.g., WHERE event_date BETWEEN '2025-01-01' AND '2025-01-07'), the optimizer determines which daily partitions overlap that range and reads only those files/partitions.

Recommended partition key for a user activity table with daily retention and frequent date-range queries: use a DATE column such as event_date (or event_timestamp truncated to date) as the partition key (daily RANGE or LIST). Example:

sql

CREATE TABLE user_events (
  user_id BIGINT,
  event_timestamp TIMESTAMP,
  event_date DATE GENERATED ALWAYS AS (CAST(event_timestamp AS DATE)) STORED,
  event_type TEXT,
  payload JSONB
) PARTITION BY RANGE (event_date);

CREATE TABLE user_events_2025_01_01 PARTITION OF user_events
FOR VALUES FROM ('2025-01-01') TO ('2025-01-02');

This design maximizes pruning for date-range queries and makes dropping old days trivial for retention.

MediumSystem Design

75 practiced

You're designing an incremental ETL load for a daily fact table where the source provides a CDC stream with columns (operation_type, pk, updated_at, payload). Describe an ETL design that is idempotent, handles out-of-order events and deletes, supports safe backfills, and allows replaying. Include concepts like watermarking, ordering guarantees, deduplication, and use of MERGE/upsert semantics.

Sample Answer

Situation: You need a robust incremental ETL to populate a daily fact table from a CDC stream (operation_type, pk, updated_at, payload) that is idempotent, tolerates out-of-order events, supports deletes, safe backfills and replay.

Design (high-level):- Ingest: write raw CDC events to an append-only event table or object store partitioned by event_time (ingest_time). Keep full payload and metadata (op, pk, updated_at, source_offset).- Watermarking & ordering: maintain two timestamps — event_time (updated_at) and ingest_watermark (max offset/processing_time). For correctness, use event_time for ordering but only advance downstream watermark after you see a contiguous offset range or time threshold (e.g., hold window of N minutes) to allow late arrivals.- Deduplication & idempotency: assign a deterministic event_id (e.g., hash(pk, updated_at, operation_type, source_offset)). Before applying, dedupe by event_id against a processed_events table or use INSERT ... ON CONFLICT DO NOTHING.- Merge/upsert semantics: process batches ordered by (pk, updated_at, source_offset) and apply MERGE into the fact table using updated_at as the version key: - WHEN MATCHED AND incoming.updated_at >= existing.updated_at THEN UPDATE - WHEN NOT MATCHED AND op != 'delete' THEN INSERT - WHEN MATCHED AND op == 'delete' AND incoming.updated_at >= existing.updated_at THEN DELETE or set tombstone flagExample MERGE (pseudo-SQL):

sql

MERGE INTO fact f
USING (SELECT * FROM staging_batch) s
ON f.pk = s.pk
WHEN MATCHED AND s.updated_at >= f.updated_at AND s.op != 'delete' THEN
  UPDATE SET <columns> = s.payload, updated_at = s.updated_at
WHEN MATCHED AND s.updated_at >= f.updated_at AND s.op = 'delete' THEN
  DELETE
WHEN NOT MATCHED AND s.op != 'delete' THEN
  INSERT (...)

- Handling out-of-order events: because MERGE uses updated_at (and source_offset tie-breaker) you always keep the latest state. For late events older than current persisted updated_at, MERGE will be no-op.- Deletes: represent deletes explicitly (op='delete') and apply via MERGE. Optionally set a tombstone flag instead of physical delete for audit and backfill safety.- Backfills & replay: keep raw event store immutable with partitions by day. Replay by reprocessing a date range into a fresh staging table and applying MERGE idempotently (dedupe prevents double-applying). For destructive backfills, snapshot existing rows for the range, run replay in a sandbox, validate, then swap or apply.- Exactly-once / offsets: track source offsets in a checkpoint table per pipeline shard. Only advance checkpoint after batch commit to downstream DB.Operational notes:- Use small bounded reordering window (e.g., 5–30 min) to trade latency vs correctness.- Monitor late-event rates and maintain metrics for watermark lag.- Keep processed_events compact (TTL) or use bloom filters for performance.This design yields idempotent, replayable, out-of-order-tolerant incremental loads suitable for BI fact tables.

EasyTechnical

75 practiced

Describe INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN and provide a short SQL example for a common BI need: "Retrieve all customers and their last order date, including customers with no orders." Use schemas:

orders(order_id, customer_id, order_date)
customers(customer_id, name)

Explain which join you used and why.

Sample Answer

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN — brief:- INNER JOIN: returns only rows with matching keys in both tables.- LEFT JOIN: returns all rows from the left table; matched rows from the right; NULL when no match.- RIGHT JOIN: symmetric of LEFT JOIN (all rows from right table).- FULL OUTER JOIN: returns all rows from both tables; unmatched sides are NULL.

BI need: "Retrieve all customers and their last order date, including customers with no orders." We want every customer even if they have no orders → use LEFT JOIN (customers is the left table) and aggregate orders with MAX(order_date).

sql

SELECT
  c.customer_id,
  c.name,
  MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY last_order_date NULLS FIRST; -- optional: show customers with no orders at top

Why LEFT JOIN: it preserves all customers; orders are optional. INNER JOIN would drop customers without orders. RIGHT JOIN could work if you reversed tables, and FULL OUTER JOIN is unnecessary here and less efficient for this specific requirement.

Unlock Full Question Bank

Get access to hundreds of Large Dataset Management and Technical Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.