Resume Grilling: Worked Example

Structure the answer as sources → layers → consumers → operations: “Data lands from [N] source systems (CDC from operational databases, files, APIs) into bronze via AutoLoader/streaming. We have six layers: landing, bronze, silver (conformed entities), a GDPR layer where direct identifiers are pseudonymised and consent-filtered, gold domain marts, and a secure serving layer with ABAC policies. Transformations are dbt on Databricks, organised per domain, with a shared macro package. Consumers are [BI tool] dashboards for ~1,000 users across [regions], plus data science notebooks. Orchestration is [Workflows/Airflow]; CI runs slim dbt builds on every PR.” Then pause and let them choose where to go deeper.

Each extra layer has a distinct, enforceable contract: “nothing downstream of the GDPR layer contains direct identifiers” and “everything consumers query goes through ABAC policies”. That makes compliance auditable by construction instead of by convention. Trade-off: more storage and more models to maintain. We mitigated with views where a layer is thin and with macros that generate the boilerplate.

A versioned internal dbt package with ~40 macros: surrogate keys, audit columns, GDPR masking, ghost/unknown-member records for dimensions, dynamic schema resolution per environment, and generic data quality tests. Domains pin a version; changes go through PR review by the platform team; breaking changes get a new major version. This turned standards from wiki pages into code.

Incremental models for large facts (merge with lookback windows for late data), slim CI (state:modified+ with prod deferral), tagging/selectors per domain so jobs run independently, model contracts on public gold models, tests on every primary/foreign key, and [run time before → after] from moving full refreshes to incremental. Mention one concrete incident that a test caught before production.

Pick a real pattern. Example: upstream schema changes and late-arriving source data. Fixes: schema evolution handling with rescue columns in bronze and contracts in silver, freshness checks with alerts routed to the owning domain, and a backfill runbook with idempotent partition overwrites. Show you measured improvement: [incidents/month before vs after].

A strong answer names 1–2 real weaknesses: e.g. “I’d introduce a semantic/metrics layer earlier, because metric definitions drifted between domains”, or “I’d use liquid clustering from day one instead of partitioning some tables by high-cardinality columns.” Senior signal: honest self-critique with a reason.

Unity Catalog row filters and column masks are SQL functions bound to tables and evaluated with the querying user’s identity. Columns tagged as PII are masked unless the user is in an approved group; row filters restrict rows by attributes such as [country/business unit] mapped from IdP groups. Policies are tag-driven, so a new table with correctly tagged columns is protected without new grants.

Deletion requests table → identify all keys for the subject → batched deletes/anonymisation per table found via tags/lineage → physical removal (deletion vectors purged, VACUUM within the legal window) → verification queries → audit record. Downstream layers only hold pseudonymous keys, so most deletions are a mapping-table delete plus a few silver tables. Mention your actual SLA ([30] days) and how you prove completion.

Automated PII detection on new columns, tagging enforced in CI for gold models, audit logs reviewed for access to sensitive tags, and lineage to find derived copies. Be honest about gaps (e.g. ad-hoc notebooks) and how you mitigated them (personal schemas with TTLs, no PII allowed without approval).

Give the business reason first: [freshness requirement] for [use case]. AutoLoader also gives incremental file discovery with exactly-once tracking, so even “batch-like” sources benefit from availableNow triggers. Be precise about which pipelines truly run continuously vs incremental batch, since interviewers respect that nuance.

AutoLoader with schema location and addNewColumns (stream restarts once on new columns, by design) or rescue mode for strict tables; unexpected data goes to _rescued_data; silver enforces types and quarantines violations; breaking changes go through a contract process with the producer.

Separate transient failures (cloud storage throttling, cluster loss → job retries with backoff; checkpoints make restarts safe) from data errors (malformed records → rescue column/quarantine table, alert if rate exceeds threshold). Exactly-once comes from checkpoints + idempotent Delta writes; foreachBatch sinks use MERGE so replays don’t duplicate.

Check input rate vs processing rate, batch duration vs trigger, skewed partitions, small-file explosion in the source, state store growth (missing watermark), and slow sinks (MERGE scanning too many files). Short-term: scale up, limit maxFilesPerTrigger to stabilise. Long-term: fix the root cause and add lag alerting.

“The input is legacy [dialect] SQL. A classifier routes simple statements to deterministic translation; complex ones go to an agent pipeline: one agent analyses the statement and dependencies, retrieval pulls similar already-migrated examples and dialect rules from a vector store (RAG with [ChromaDB]), a generator produces Databricks SQL, and a validator compiles it and compares results against the legacy system on sample data. Failures loop back with the error message for repair, up to N attempts; low-confidence cases go to human review, and every reviewed fix is added to the knowledge base.”

Separation of concerns makes each step testable and cheaper (smaller contexts, smaller models for simple steps) and makes failures diagnosable (“generation was fine, validation failed on NULL semantics”). Trade-off: more orchestration complexity and latency. Use a single prompt when the task is simple enough.

The honest answer interviewers want: you don’t trust the LLM, you trust validation. Compile/EXPLAIN on the target, then result-set equivalence on representative data (row counts, column checksums, row diffs on keys with tolerance rules for floats and timestamps), plus generated tests. Report the auto-validated rate and the human-review rate instead of claiming “fully automated”.

Have 2–3 concrete examples ready: NULL handling in string concatenation, integer division, date functions and week numbering, implicit casts, procedural constructs (loops, temp tables) that must become set-based SQL or dbt models. Interviewers love specific examples.

Use real numbers only: objects migrated, % auto-validated, average human minutes per object before vs after, defects found post-migration. If numbers are confidential, give ratios (“roughly a third of the manual effort”).

STAR with numbers: Situation (job/table, size, runtime/cost), Task (target), Action (diagnosis via Spark UI: skewed join, small files, no data skipping; fixes: liquid clustering on filter keys, OPTIMIZE, broadcast hint/AQE skew join, removing a Python UDF), Result ([runtime before → after], [cost saved]). Say how you verified it and kept it from regressing.

Liquid clustering for new and evolving tables (incremental, keys changeable, no partition small-file issues); Z-order on older partitioned tables until migration. Explain how you chose keys from query patterns (system tables / query history).

Inventory + dependency order, migrate in waves, parallel runs with automated reconciliation (counts and aggregates per key), business sign-off per wave, cutover and decommission. Mention one thing that went wrong and how you handled it.

Define the baseline (incidents/tickets per month or failed checks reaching consumers), the measurement window, and what the framework did (profiling, row-level validation rules, quarantine, alerting). Be ready to explain the denominator. Unclear metrics invite skepticism.

Concrete mechanisms: coding standards and PR review checklist, pairing on complex models, design reviews for new domains, internal talks, onboarding guides. Evidence: [faster onboarding, fewer production incidents, promotions]. Senior signal: you scaled yourself through systems, not heroics.

Pick a real one (e.g. partitioning strategy, adopting DLT vs dbt). Show you listened, framed options with data (benchmarks, cost), agreed on decision criteria, and committed to the outcome even if not your preference.