Data Warehousing and Data Lakes Questions

Covers conceptual and practical design, architecture, and operational considerations for data warehouses and data lakes. Topics include differences between warehouses and lakes, staging areas and ingestion patterns, schema design such as star schema and dimensional modeling, handling slowly changing dimensions and fact tables, partitioning and bucketing strategies for large datasets, common architectures including medallion architecture with bronze silver and gold layers, real time and batch ingestion approaches, metadata management, and data governance. Interview questions may probe trade offs between architectures, how to design schemas for analytical queries, how to support both analytical performance and flexibility, and how to incorporate lineage and governance into designs.

MediumTechnical

73 practiced

Describe a practical approach to capture column-level lineage for transformations implemented in dbt. Explain how to extract lineage information, link it to source tables in your catalog, and expose lineage to data scientists to help them assess data reliability.

Sample Answer

Approach (high level)1) Extract dbt metadata (compiled SQL + manifest) after runs; 2) parse compiled SQL to map output columns to input columns using manifest dependencies and a SQL parser; 3) join this column-level mapping to your data catalog’s table/source entries; 4) surface lineage + quality signals in a UI or notebook for data scientists.

How to extract lineage- Use dbt artifacts: manifest.json (models, refs, sources, node dependencies), catalog.json (column metadata), run_results.json (test results).- For each model node, read compiled_sql (manifest.nodes[<node_id>].compiled_sql). Parse SELECT expressions with a SQL parser (sqlfluff or mo_sql_parsing) to map target column names to source columns or expressions. When expressions reference other models, follow manifest.node.depends_on to resolve upstream nodes recursively.

Example (Python sketch)

python

import json
from mo_sql_parsing import parse

manifest = json.load(open('target/manifest.json'))
catalog = json.load(open('target/catalog.json'))

def parse_columns(node):
    sql = manifest['nodes'][node]['compiled_sql']
    ast = parse(sql)
    # traverse AST to map select items to source.table.column references
    # (implementation detail depends on parser)
    return mapping  # {target_col: [(src_table, src_col), ...]}

for node_id in manifest['nodes']:
    mapping = parse_columns(node_id)
    # link mapping to catalog entries via manifest nodes' relation info

Link to source tables in catalog- Use manifest sources and node metadata (database, schema, identifier) to normalize names.- Upsert column lineage edges into your catalog (DataHub/Amundsen/Collibra) via their metadata API: create relationships target_column -> source_column with provenance (dbt job id, run timestamp, git commit).

Expose to data scientists- UI components: column-level lineage graph, impact analysis (what models/metrics depend on this column), quality dashboard showing dbt tests, freshness, failing runs, and last successful run.- Notebook integration: provide Python client to query lineage and get upstream column pedigree to validate features before modeling.- Provide reliability score per column combining upstream test failure rate, freshness, and number of dependent downstream consumers.

Why this works- Uses dbt’s authoritative metadata (manifest/catalog) for correctness and compiled SQL to reflect macros/CTEs.- SQL parsing + manifest dependency graph yields accurate column mappings.- Pushing relationships to a catalog and adding quality signals gives data scientists actionable context to assess data reliability quickly.

MediumTechnical

51 practiced

You receive product metadata as JSON with variable fields and nested structures from many partners. As a data scientist designing analytics for pricing experiments, propose a schema design in the warehouse that balances query performance, flexibility for unknown attributes, and maintainability for downstream analysts.

Sample Answer

Approach summary: use a hybrid schema — a canonical wide table for core, high-cardinality product fields needed in most analyses + a semi-structured column (JSONB/VARIANT) to store partner-specific or unknown attributes. Produce derived, flattened feature tables for experiments (materialized ETL) so analytics and models query fast, typed columns.

Design:- product_core (wide, canonical) - product_id (PK), partner_id, sku, category_id, base_price, currency, available_from, quality_flags... - ingestion_metadata: source, schema_version, received_at - attrs_json: JSONB -- raw partner JSON payload- product_attrs_index (optional EAV for high-value dynamic keys) - product_id, attr_key, attr_value (typed), last_seen- price_features_{experiment} (derived) - product_id, experiment_run_id, price_bin, competitor_price, demand_score, normalized_feature_X... - refreshed nightly / on-demand; used by analysts & models

Ingestion & processing:- Lightweight validation + schema registry: detect common keys, map to canonical columns; route unknowns into attrs_json.- Extraction jobs parse attrs_json to populate price_features: implement feature engineering (impute, bucket, normalize) and store typed columns to avoid JSON parsing at query time.- Maintain lineage: store ingestion schema_version, transformation code in git, and column-level metadata (data catalog).

Performance & maintainability:- Index core columns (product_id, partner_id, category). Create GIN index on attrs_json for targeted queries.- Precompute heavy transforms into materialized tables for experiments to ensure low-latency queries and reproducible features.- Use automated profiling to promote frequently queried JSON keys into core columns (schema evolution).- Provide SQL templates, docs, and dashboards for downstream analysts.

Trade-offs:- Flexibility vs. speed: JSONB preserves unknown attributes without schema churn; derived tables convert flexibility into performant typed features.- Storage cost vs. query latency: more derived tables increase storage but cut query time and simplify downstream work.

Example (Postgres JSONB index):

sql

CREATE INDEX idx_products_attrs_gin ON product_core USING gin (attrs_json);

MediumTechnical

57 practiced

Design a strategy to support analytical queries on a fact table with very high-cardinality dimensions. Discuss denormalization vs normalized joins, the use of materialized views, pre-aggregation, and practical tips specific to BigQuery or Snowflake to balance performance and cost.

Sample Answer

Start by clarifying requirements: query patterns (ad-hoc vs dashboards), SLAs, update frequency, cardinalities, and cost constraints. Then choose a hybrid strategy that balances scan cost, freshness, and engineering complexity.

Strategy overview- Denormalization (wide fact with dimension attributes): - Pros: single-table scans, simpler queries, better for repeated analytical filters/aggregations. - Cons: storage duplication, slower writes, harder to maintain for frequently-changing dimensions. - Use when dimension attributes are relatively stable and commonly used in filters or group-bys.

- Normalized joins: - Pros: smaller fact, single source of truth, cheaper storage. - Cons: expensive at query time for high-cardinality joins; repeated cross joins amplify scan costs. - Use for rarely-used attributes or when data freshness is critical.

Pre-aggregation & materialized structures- Pre-aggregate common rollups (time/product/category) into summary tables or materialized views. This reduces scan volume for dashboards and scheduled reports.- Materialized views: great for predictable queries. In BigQuery use MV for exact SQL shape; Snowflake supports materialized views with clustering keys—ensure queries hit them.- Use incremental materialization: update only partitions that changed.

Platform-specific tips- BigQuery: - Partition facts by date and cluster by high-cardinality filter columns to reduce scanned bytes. - Use materialized views for repetitive, well-defined query patterns; use scheduled queries for more complex pre-aggregations. - Monitor bytes billed (cost) and use dry-run to estimate.- Snowflake: - Use micro-partitioning and clustering keys to co-locate similar values; rebuild clustering when distribution drifts. - Use transient/temporary tables for intermediate transforms and result caching to reduce compute. - Leverage result cache and automatic clustering where possible; use materialized views for very common queries but validate maintenance cost.

Practical best practices- Start with profiling: capture hottest queries and top filters/group-bys.- Build a small set of aggregated tables/MVs for the top N queries; keep the rest via normalized joins.- Automate refresh pipelines (streaming vs batch) and measure freshness needs.- Monitor cost/latency and iterate: add clustering keys, change partition granularity, or create additional pre-aggregations only when ROI is clear.- Document lineage and test correctness after denormalization.

This hybrid approach minimizes query cost and latency while keeping storage and maintenance manageable.

HardTechnical

53 practiced

You observe a gradual query performance degradation on your cloud warehouse as data grows. Propose a structured investigation plan: which metrics to collect (e.g., query times, bytes scanned, concurrency), which configurations to inspect (warehouses/sizes, caching, partitioning), and potential mitigations you would evaluate.

Sample Answer

Start with a hypothesis-driven, reproducible investigation: measure baseline, correlate regressions with data growth and deployments, and iterate.

Metrics to collect (time-series, per-query and aggregate):- Query latency percentiles (p50/p95/p99) and average- Bytes/rows scanned per query, and bytes returned- CPU, memory, I/O utilization on warehouses- Concurrency: number of concurrent queries, queue times, credit usage- Cache hit rate (result cache, metadata cache), and time-to-first-byte- Query plan stats: scan operators, shuffle/wait times, cardinality estimates- Query fingerprinting: top slow queries, frequency, user/team- Data size growth by table/partition and table-level statistics (ndv, skew)- Model/pipeline schedules and their overlap with BI workloads

Configurations and metadata to inspect:- Warehouse sizing and auto-suspend/auto-resume settings, scaling policy, multi-cluster vs single-cluster- Resource isolation (separate warehouses for ETL, BI, model training)- Table partitioning/clustering, sort keys, file sizes (small-file / large-file ratio)- File format and compression (Parquet/ORC), column pruning effectiveness- Distribution/cluster keys for joins; presence of broadcast vs shuffle joins- Statistics and table analyzers (are stats stale?)- Caching policy and TTLs; materialized views present- Recent schema changes, index/constraint changes, and deployment timelines

Investigation steps:1. Reproduce: pick representative slow queries; capture EXPLAIN/PROFILE.2. Correlate: align latency spikes to data volume growth, concurrency spikes, or config changes.3. Drill-down: compare query plans before/after degradation to find plan regression (e.g., full scan, lost broadcast).4. Hotspots: identify skewed tables/partitions or exploding intermediate data.5. Load testing: simulate increased data and concurrency on staging.

Mitigations to evaluate (iterate with metrics):- Short-term: increase warehouse size or add clusters, throttle noncritical jobs, schedule heavy ETL off-peak- Medium-term: enable/result-cache warming, refresh statistics, rewrite expensive queries (push filters, avoid SELECT *), add materialized views for costly aggregates- Data layout: partition/clustering on high-cardinality predicates, compact small files into optimal file sizes, enforce columnar compressed formats- Join strategy: add broadcast hints where appropriate, denormalize or precompute joins for repeated patterns- Concurrency controls: pool users, set resource monitors/quotas, prioritize model-training vs ad-hoc analytics- Long-term: implement incremental pipelines, prune historical data, monitor and alert on the key metrics above

Communicate findings with reproducible examples, proposed changes, and expected gains; iterate with A/B or canary changes and rollback plan.

MediumTechnical

58 practiced

SQL task: Given transactions(txn_id, user_id, amount DECIMAL, created_at TIMESTAMP), write an ANSI SQL check that flags users whose average transaction amount in the last 30 days deviates by more than 3 standard deviations from that user's rolling one-year historical average. Explain the window functions used.

Sample Answer

Approach: compute, per user, the average amount in the most recent 30 days and compare it to that user's historical 1-year mean and stddev (excluding the most recent 30 days so "historical" is the prior 365−30 day window). Flag users where |avg_30d − hist_mean| > 3 * hist_stddev.

SQL (ANSI; uses FILTER for conditional aggregation and STDDEV_SAMP for sample stddev):

sql

SELECT
  user_id,
  avg_30d,
  hist_mean,
  hist_stddev,
  CASE
    WHEN hist_stddev IS NULL THEN FALSE
    WHEN ABS(avg_30d - hist_mean) > 3 * hist_stddev THEN TRUE
    ELSE FALSE
  END AS anomaly_flag
FROM (
  SELECT
    user_id,
    AVG(amount) FILTER (
      WHERE created_at >= CURRENT_DATE - INTERVAL '30' DAY
    ) AS avg_30d,
    AVG(amount) FILTER (
      WHERE created_at >= CURRENT_DATE - INTERVAL '365' DAY
        AND created_at <  CURRENT_DATE - INTERVAL '30' DAY
    ) AS hist_mean,
    STDDEV_SAMP(amount) FILTER (
      WHERE created_at >= CURRENT_DATE - INTERVAL '365' DAY
        AND created_at <  CURRENT_DATE - INTERVAL '30' DAY
    ) AS hist_stddev
  FROM transactions
  GROUP BY user_id
) t;

Explanation of window/aggregation choices:- I used partition-level aggregation (GROUP BY user_id) with FILTER to compute time-windowed aggregates per user. FILTER is ANSI SQL and avoids self-joins.- avg_30d: average of amounts in the last 30 days (recent window).- hist_mean and hist_stddev: mean and sample standard deviation over the prior 365→31-day window (one-year historical excluding the recent 30 days).- We then compare the recent mean to historical distribution using the 3-sigma rule: |recent − mean| > 3 * stddev.Notes and edge cases:- If hist_stddev IS NULL (not enough historical data), we don't flag; alternatively you can require a minimum count (e.g., COUNT(*) FILTER (...) >= N).- If you need a true rolling per-timestamp flag (for each transaction date), replace FILTERs with window functions using ORDER BY created_at RANGE BETWEEN INTERVAL ... PRECEDING or use a lateral/self-join to compute rolling aggregates.

Unlock Full Question Bank

Get access to hundreds of Data Warehousing and Data Lakes interview questions and detailed answers.

Join thousands of developers preparing for their dream job.