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

42 practiced

Design a PII discovery and masking strategy for both raw data stored in the lake and curated tables in the warehouse. Cover detection techniques (pattern matching, ML classifiers), masking approaches (tokenization, reversible encryption, redaction), access controls, and audit logging to support compliance.

Sample Answer

High-level approach: treat PII discovery and masking as a data lifecycle capability spanning the raw data lake (ingest, landing zone) and curated warehouse (cleaned, modeled tables). Combine deterministic detection (fast, high-precision) with probabilistic/ML methods (higher recall) and enforce masking via policy-driven, role-aware controls plus thorough auditing.

1) Discovery- Deterministic rules: regexes, dictionaries, checksums (SSN, credit card Luhn, emails, phone formats) run as part of ingest and scheduled scans.- ML/NLP: Named Entity Recognition models (spaCy/transformers or vendor models) to find contextual PII (names in free text, addresses, job titles). Train/finetune on labeled samples to reduce false positives.- Hybrid pipeline: run regex → flag confident hits; run ML on remaining/unstructured fields; assign confidence scores; push medium-confidence to human-in-the-loop review.- Metadata/catalog: tag objects/columns in a data catalog (e.g., AWS Glue/Azure Purview/Collibra) with sensitivity labels and lineage.

2) Masking approaches (apply based on sensitivity, use-case, and retention policy)- Tokenization (format-preserving): for downstream analytics needing uniqueness but not raw value (customer tokens for cohorting). Store token map in a secure vault (HSM/KMS).- Reversible encryption: AES-GCM with per-field keys managed by KMS for users with legitimate need (support re-identification for legal/ops). Enforce key rotation and access logging.- Irreversible hashing/salting: salted SHA-256 for de-duplication where re-identification is not permitted.- Redaction/NULLing: for low-value fields or exported datasets.- Dynamic masking/views: implement at warehouse layer (Snowflake masking policies, Redshift views, SQL row/column-level functions) so analysts get masked values by default; privileged roles can view masked → clear via audited justification.- Differential masking: full PII to compliance/ops; pseudonymous for analysts; aggregated metrics for dashboards.

3) Access controls & governance- Principle of least privilege: RBAC + ABAC combining role, purpose, and dataset sensitivity. Enforce via DB roles, IAM policies, and data platform entitlements.- Purpose-based access: require ticketed justification (JIT access) and time-bound access tokens for re-identification.- Key management: central KMS/HSM, key separation for environments, rotation, split knowledge for sensitive keys.- Separation of duties: analysts use pseudonymized datasets; only approved privacy/ops can decrypt.

4) Audit logging & compliance- Capture detailed, tamper-evident logs for discovery runs, masking actions, access requests, decryption events, and exports. Include user, purpose, dataset, query, time, and rationale.- Immutable storage for logs (WORM or append-only) and integration with SIEM for alerting on anomalous patterns (large exports, repeated decryption).- Periodic reports: PII inventory, access reviews, key rotation reports, false-positive rates from ML review to prove compliance (GDPR DPIA readiness).- Retention & incident response: retention policies aligned to regulations; playbooks for breach detection/notification.

5) Operational notes & metrics- Automate scans at ingest + weekly full-lake recrawl. Track detection precision/recall, masking coverage, time-to-review for flagged items.- Validate with unit tests and synthetic PII test suites; maintain explainable ML models and human oversight.- Trade-offs: deterministic rules are fast but miss contextual PII; ML increases recall but needs governance to control false positives/negatives. Use hybrid and conservative default masking for analysts.

This strategy lets analysts work with high-utility, pseudonymized data for reporting while preserving the ability for controlled, auditable re-identification when legitimately required — meeting business needs and compliance obligations.

EasyTechnical

48 practiced

Explain schema-on-read versus schema-on-write. For analysts, how does each approach affect speed of exploration, data quality guarantees, and responsibility for schema validation?

MediumBehavioral

47 practiced

A product manager asks for a one-hour turnaround for a new dashboard that requires joining multiple slow tables. How do you manage expectations and deliver a useful result quickly? Describe the steps you would take, trade-offs you would propose, and how you'd document limitations.

MediumTechnical

58 practiced

Discuss strategies for schema evolution in a data lake used for analytics. How do you handle new columns, column renames, and incompatible type changes while preserving historical analysis and avoiding pipeline breaks?

EasyTechnical

56 practiced

What are Slowly Changing Dimensions (SCD)? Contrast SCD Type 1, Type 2, and Type 3, and give a concrete business example where using Type 2 is appropriate. Describe how SCD Type 2 preserves history.

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.