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.

HardTechnical

46 practiced

Debate the trade-offs between choosing a lakehouse (Delta/Iceberg) and a managed warehouse (Snowflake/BigQuery) for enterprise BI. Consider concurrency, ACID semantics, cost model (storage vs compute), performance for BI workloads, and support for streaming and batch ingestion.

Sample Answer

High-level summary: both lakehouses (Delta/Iceberg) and managed warehouses (Snowflake/BigQuery) can power enterprise BI but optimize different trade-offs. Choose based on concurrency needs, consistency guarantees, cost predictability, BI performance patterns, and ingestion modes.

Concurrency & BI UX- Managed warehouses: built for high-concurrency OLAP — many simultaneous dashboard users, ad-hoc queries, materialized views and automatic scaling avoid noisy-neighbor issues. Better SLAs for low-latency dashboards.- Lakehouses: concurrency improving (query engines like Presto/Trino, Databricks Photon) but often require more ops tuning (cluster sizing, workload isolation). Expect higher variance unless using managed lakehouse services.

ACID & Data Reliability- Delta/Iceberg: provide strong ACID at table level and time-travel/versioning — excellent for reproducible ETL and slowly evolving dimensions. Good for complicated merges/upserts from CDC.- Warehouses: transactional semantics are simpler (atomic load operations) and tightly integrated with query engine; easier for BI teams because ingestion + query behave predictably, but not as flexible for complex transactional merges.

Cost Model (storage vs compute)- Lakehouse: cheap object storage (S3/GCS) keeps storage costs low; compute is decoupled — pay for compute when processing. Great for large historical datasets you rarely query, but cost spikes for heavy interactive usage unless you reserve clusters.- Managed warehouse: charges often include compute in query pricing but provide auto-scaling and per-second billing. Predictable for steady BI workloads; can get expensive at scale for large scans, though clustering, partitioning, and materialized views mitigate cost.

Performance for BI workloads- Warehouses: optimized for small-latency, high-concurrency reporting queries, automatic query optimization, columnar storage, caching, and built-in BI connectors. Best for interactive dashboards.- Lakehouses: excel for massive analytical joins, custom ML pipelines, and ELT. With caching/compaction and tuned compute, they can approach warehouse performance but need engineering effort.

Streaming and Batch Ingestion- Lakehouse: first-class support for streaming CDC + batch (Delta/ICEBERG with Delta Lake/Apache Hudi patterns); enables near-real-time incremental views and complex merge logic.- Warehouses: increasingly support streaming ingestion (Snowpipe, BigQuery streaming) with lower latency for simple inserts; complex upsert/merge patterns can be clunkier or more costly.

Recommendation for BI Analyst- If primary need is reliable, low-latency dashboards for many concurrent users with minimal ops overhead: prefer managed warehouse.- If you need unified platform for streaming + batch ETL, advanced time-travel, and want to minimize storage cost on massive historical data while tolerating more engineering: lakehouse fits.- Hybrid approach: land raw events into a lakehouse for storage/ETL, then maintain curated, aggregated marts in a managed warehouse (or use managed lakehouse services) — gives best of both worlds.

Operational tips- For lakehouses: invest in compaction, Z-ordering/partitioning, and autoscaling; expose curated semantic layer to BI tools.- For warehouses: use clustering/materialized views and cost controls (query quotas, BI extracts) to contain costs.

EasyTechnical

40 practiced

Describe Slowly Changing Dimension Type 2 (SCD Type 2). As a BI Analyst, outline common implementation patterns in ETL/ELT (surrogate keys, valid_from/valid_to, current_flag), show a sample dimension schema, and explain how SCD Type 2 affects historical reporting and joins to fact tables.

MediumTechnical

50 practiced

Outline an architecture to implement CDC from MySQL OLTP into BigQuery using Debezium or a cloud-native CDC option. Address ordering, idempotency, schema evolution, how to apply updates in BigQuery, and how to backfill missing changes reliably.

Sample Answer

High-level approach:- Use Debezium (self-managed or cloud-native CDC like Cloud Dataflow with Datastream/Dataproc) to capture MySQL binlog events, stream to a durable messaging layer (Kafka or Pub/Sub), then process and apply to BigQuery with a consumer that enforces ordering, idempotency, and schema handling.

Architecture:- MySQL → Debezium connector → Kafka / PubSub (topic per DB/table, partitioned by primary key) → Stream processing job (Kafka Streams / Dataflow) → Staging (BigQuery streaming insert or GCS AVRO/Parquet load) → Final BigQuery tables (partitioned/clustering).

Ordering:- Preserve binlog order per primary key by partitioning topic by primary key; stream processor applies events in key order. For multi-row transactional guarantees, include transaction metadata (xid, commit order) from Debezium and process commit boundaries.

Idempotency:- Produce events with a unique event_id (binlog filename+pos+row) and include before/after payload + operation type. Consumer uses upsert semantics in BigQuery: - Buffer to staging table and run MERGE INTO final table using primary key and last_event_id or event_timestamp to avoid reapplying older events. - Create a deduplication key (event_id) and track last_seen_event per key.

Schema evolution:- Use schema registry (Confluent/Artifact Registry) or Debezium's schema in event. Stream processor handles field additions by: - Writing data to AVRO/Parquet with schema evolution support. - In BigQuery, use MERGE that tolerates new nullable columns. Apply periodic DDL to add new columns with default NULLs. Track schema versions and migrate downstream models.

Applying updates in BigQuery:- Prefer micro-batches: write events to GCS as files (Avro/Parquet) then use BigQuery load + MERGE for transactional correctness and cost control. For low-latency, use streaming inserts into a staging table and regularly MERGE into the serving table: MERGE final USING staging ON pk WHEN MATCHED AND staging.event_ts > final.event_ts THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...

Backfill & missed changes:- Use binlog snapshot and offset management: Debezium supports snapshotting and resume from offsets stored in Kafka/connector. For gaps: - Re-run a consistent snapshot of affected tables (via mysqldump or Debezium snapshot) and load to a temporary table, then MERGE with source precedence using last_update_ts or a synthetic sequence to ensure missing events overwrite older rows. - For point-in-time backfill, replay binlog segments from stored offsets into Kafka and reprocess.Monitoring & validation:- Track connector offsets, consumer lag, and use checksums/row counts for reconciliation. Implement alerting and a reconciliation job comparing source counts/hashes to BigQuery to detect drift and trigger re-snapshot or replay.

MediumTechnical

54 practiced

Design an auditability plan for business reports: include a central metric definitions registry, mapping from dashboard widgets to SQL queries/tables, versioning of metric logic, and a verification workflow so executives can trace reported numbers to source data.

Sample Answer

Requirements:- Executives must trace any dashboard number to the exact SQL, transformation logic, and source rows.- Central single source of truth for metric definitions, with versioning, approvals, and access control.- Mapping from visualization widgets to the metric definition and the SQL/tables used.- Verification workflow with automated tests, lineage, and human sign-off.

Design — components and flow:1. Metric Definitions Registry- Catalog each metric (name, description, business owner, formula, canonical SQL, dimensions/time grain, tags).- Store in a versioned metadata store (e.g., db-backed registry + Git for definitions; LookML/DBT model files or a tool like Amundsen/Marquez).- Expose API and UI for search and read/compare versions.

2. Widget → Query Mapping- Enforce that dashboards reference metric IDs (not ad-hoc SQL).- Persist widget metadata: metric_id, parameters, applied filters, and the resolved SQL the BI tool runs.- For ad-hoc queries, require registration as a derived metric with lineage.

3. Versioning & CI- Keep metric logic in source control (dbt models or SQL files) with semantic versioning.- PR workflow: tests (row counts, aggregation sanity, nulls), preview diff of metric outputs vs previous version.- Auto-deploy after approvals; tag releases.

4. Verification & Traceability Workflow- Lineage engine captures table/column lineage (dbt + data lineage tool).- “Trace” button on dashboard: shows metric definition version, SQL, upstream source tables, sample source rows, and transformation steps.- Automated data tests run on schedule; failing tests alert owners and mark affected reports as stale.- Executive sign-off queue for major metric changes; change notes visible in the registry.

5. Governance, Access & Audit Logs- Role-based access for editing definitions; read-only for consumers.- Immutable audit log: who changed what, when, with diffs.- Retention for compliance.

Example:- KPI: Monthly Active Users (MAU) - Registry entry: MAU v1.2 — SQL referencing events.user_id where event_date between... - Dashboard widget references metric_id MAU:v1.2 - Trace shows source table events, sample rows, last test run (pass), and approval timestamp.

Operational practices:- Quarterly metric review cadence with business owners- Onboarding docs and training for analysts to register metrics- Lightweight SLA: data freshness and test coverage goals

Why this works:- Single source of truth reduces divergence, versioning provides safe evolution, automated tests prevent regressions, and trace UI + lineage give executives immediate, auditable paths from KPI to raw data.

EasyTechnical

56 practiced

Compare ETL and ELT for modern cloud analytics. As a BI Analyst, explain where transformations occur, implications for traceability and lineage, cost and scalability trade-offs, and which approach better supports ad-hoc analyst queries.

Sample Answer

Brief definitions: ETL (Extract → Transform → Load) performs data transformations before loading into the target; ELT (Extract → Load → Transform) loads raw data into the cloud warehouse/lake and transforms there.

Where transformations occur:- ETL: transformations happen in an ETL tool/engine (on-prem or cloud ETL service) before data lands in the warehouse. Resulting tables are curated and ready for BI.- ELT: raw data lands in the cloud data warehouse (BigQuery, Snowflake, Redshift) and SQL-based transformations (views, materialized tables, dbt jobs) run inside the warehouse.

Traceability and lineage:- ETL: lineage is tied to ETL job metadata — good if the tool provides versioning, but transformations can be a black box if scripts aren’t well-documented.- ELT: lineage is often clearer when using SQL-based transform tooling (dbt) that lives with code, enabling granular version control, test coverage, and easy mapping from raw columns to metrics — better for analyst traceability.

Cost and scalability trade-offs:- ETL: compute costs occur on the ETL engine; can be cheaper for heavy pre-processing if you control resources, but it’s less elastic. Scaling requires ETL tool capacity.- ELT: leverages cloud warehouse elasticity; transforms use warehouse compute (can be cost-efficient at scale but may increase storage + compute bills if heavy processing or repeated queries occur). ELT simplifies pipeline ops and scales well for large volumes.

Support for ad-hoc analyst queries:- ELT is preferable for ad-hoc exploration: raw data and SQL-transforms in the warehouse let analysts spin up experiments, create derived tables, and iterate quickly. ETL’s curated outputs are cleaner for production dashboards but limit exploratory flexibility.

Practical BI recommendation: adopt ELT with disciplined transform practices (dbt, tests, documented models) to balance agility, lineage, and scalable cost control; use ETL-style pre-filtering only when data must be standardized or privacy-filtered before landing.

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.