4) Technical and Professional Questions (the real separators)
Technical rounds for ETL Developer roles in the US usually revolve around four themes: SQL depth, data modeling, pipeline reliability, and the specific stack. If you can explain tradeoffs—why you chose SCD2, why you used MERGE, why you partitioned that way—you’ll sound senior even if your title wasn’t.
Q: How do you design incremental loads vs. full refresh? Walk me through your decision.
Why they ask it: They’re testing cost, performance, and correctness thinking.
Answer framework: Tradeoff triangle — correctness constraints, compute cost, and operational complexity.
Example answer: I default to incremental loads when the source has reliable change tracking—like updated timestamps, CDC, or immutable event logs—because it reduces runtime and warehouse cost. If the source is small or change tracking is unreliable, I’ll use a full refresh with idempotent writes and clear cutoffs. For incrementals, I define a watermark strategy, handle late-arriving data, and build reconciliation checks against source counts or totals. The key is making reruns safe and predictable.
Common mistake: Saying “incremental is always better” and ignoring late data and backfills.
Q: Explain how you’d model slowly changing dimensions (SCD Type 2) and test it.
Why they ask it: They want to see if you can preserve history without corrupting reporting.
Answer framework: Design–Edge cases–Validation — schema, change detection, then tests.
Example answer: For SCD2, I store surrogate keys, effective_start, effective_end, and an is_current flag. I detect changes using a hash of tracked attributes or explicit comparisons, then close out the previous record and insert a new version. I test uniqueness of the current record per natural key, non-overlapping effective date ranges, and that updates don’t create duplicate “current” rows. I also validate row counts and sample diffs against source snapshots.
Common mistake: Implementing SCD2 logic without guarding against duplicate current rows.
Q: Write a SQL approach to deduplicate records when you have multiple updates per key per day.
Why they ask it: They’re testing practical SQL, window functions, and deterministic logic.
Answer framework: Explain then code — state the rule (latest by timestamp, tie-breaker), then use ROW_NUMBER.
Example answer: I’d define “latest” using an update timestamp and a deterministic tie-breaker like ingestion_time or a sequence. Then I’d use ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY update_ts DESC, ingestion_ts DESC) and filter to row_number = 1. If deletes exist, I’d include an operation flag and ensure tombstones win. I’d also add a test that no key has more than one surviving row.
Common mistake: Using DISTINCT and hoping it removes the right duplicates.
Now the stack questions. In US job posts, you’ll see SSIS Developer and Informatica Developer roles alongside cloud ELT stacks; interviewers will probe what you actually used and what you can learn fast.
Q: If you’ve worked as an SSIS Developer, how do you handle package configuration, environments, and deployments?
Why they ask it: They’re testing whether you can run SSIS like software, not like a one-off file.
Answer framework: Build–Deploy–Operate — configuration strategy, deployment method, monitoring.
Example answer: I separate configuration from code using parameters and environment-specific values, so the same package can run across dev/test/prod. I deploy via the SSIS Catalog with versioned artifacts and controlled permissions, and I schedule through SQL Server Agent with clear logging. Operationally, I monitor execution reports, capture row counts, and alert on failures and SLA breaches. That keeps deployments repeatable and reduces “works on my machine” issues.
Common mistake: Hardcoding connection strings or file paths inside packages.
Q: For an Informatica Developer role, how do you design mappings for performance and maintainability?
Why they ask it: They’re checking if you know the levers: pushdown, partitioning, and reusable components.
Answer framework: Performance checklist — reduce data early, push work to the database, parallelize safely.
Example answer: I filter and project as early as possible, and I use pushdown optimization when the database can do the heavy lifting. For large volumes, I apply partitioning with a stable key and avoid skew, and I’m careful with lookups—caching where appropriate and indexing lookup tables. For maintainability, I standardize naming, reuse mapplets for common logic, and keep transformations focused so troubleshooting is faster.
Common mistake: Building one giant mapping that’s impossible to debug under pressure.
Q: How do you handle schema drift from upstream sources?
Why they ask it: They want to know if you’ll prevent silent data corruption.
Answer framework: Detect–Decide–Document — detection mechanism, policy for breaking vs. non-breaking changes, and communication.
Example answer: I implement schema checks at ingestion—comparing expected vs. actual columns and types—and alert on changes. For additive columns, I can often allow them through and log the change; for breaking changes like type shifts, I fail fast and quarantine the batch. I document the contract and coordinate with the source team on versioning. The goal is to avoid “pipeline succeeded but data is wrong.”
Common mistake: Letting drift slide until dashboards look weird days later.
Q: What data quality checks do you add to an ETL/ELT pipeline, and where?
Why they ask it: They’re testing whether you build trust, not just movement.
Answer framework: Layered controls — source validation, staging checks, and curated model tests.
Example answer: At ingestion, I validate schema, row counts, and basic null thresholds. In staging, I check key uniqueness, referential integrity, and anomaly detection on critical measures. In curated layers, I add business-rule tests like “no negative revenue” or “status in allowed set,” plus reconciliation totals against the source when possible. I also make failures visible with alerts and a clear ownership path.
Common mistake: Only checking for job success/failure, not data correctness.
Here’s an insider question that shows up more than candidates expect: lineage and reproducibility.
Q: How do you make your pipelines idempotent and safe to rerun?
Why they ask it: Reruns happen constantly—late data, backfills, partial failures.
Answer framework: Idempotency recipe — deterministic inputs, atomic writes, and clear run boundaries.
Example answer: I define a run boundary like a date partition or a watermark window, and I write outputs in a way that can be replaced safely—often overwrite partition or merge with deterministic keys. I avoid side effects like appending duplicates without a key. I also store run metadata (start/end time, row counts, checksum where feasible) so reruns can be compared. That way a rerun is a routine operation, not a gamble.
Common mistake: “Just rerun it” without controlling duplicates or partial writes.
Q: How do you tune performance for large joins and aggregations in a warehouse?
Why they ask it: They’re testing whether you understand query planning and cost drivers.
Answer framework: Bottleneck-first — identify the expensive step, then apply targeted fixes.
Example answer: I start by checking the query plan and the size of intermediate results. Common fixes are pushing filters earlier, pre-aggregating, and ensuring joins use the right keys with matching data types. I’ll also leverage partitioning/clustering strategies and avoid cross joins or many-to-many joins unless they’re intentional. If the same heavy logic repeats, I materialize it into a modeled table.
Common mistake: Guessing at optimizations without looking at plans or row counts.
US companies—especially in finance, healthcare, and any public company—will probe compliance. You don’t need to be a lawyer, but you must show you understand constraints.
Q: How do you handle PII in pipelines to support US compliance expectations (e.g., SOC 2 controls, HIPAA where applicable)?
Why they ask it: They’re testing whether you’ll treat sensitive data responsibly.
Answer framework: Access–Encryption–Audit — least privilege, protection at rest/in transit, and traceability.
Example answer: I classify fields early and restrict access using role-based permissions and least privilege, so only approved roles can query raw PII. I ensure encryption in transit and at rest is enabled, and I avoid copying PII into unnecessary downstream tables. Where possible, I tokenize or hash identifiers for analytics use cases. I also make sure access and pipeline changes are logged for auditability, which aligns with SOC 2-style controls and, in healthcare contexts, HIPAA expectations.
Common mistake: Saying “we just mask it” without explaining access control and audit trails.
And yes, they’ll ask failure scenarios—because reliability is the job.
Q: A critical nightly load fails at 3 a.m. and the CFO dashboard is wrong at 8 a.m. What do you do?
Why they ask it: They want to see incident handling, communication, and pragmatic recovery.
Answer framework: Triage–Communicate–Recover–Prevent — prioritize business impact and safe data.
Example answer: First I confirm scope: which tables are stale and which dashboards depend on them. I communicate a short status update with an ETA and whether numbers should be considered unreliable. Then I attempt the fastest safe recovery—rerun the failed step, or load a known-good snapshot if the source is unavailable. After the immediate fix, I do a postmortem and add monitoring or contract checks so the same failure is caught earlier.
Common mistake: Going silent while you debug, leaving stakeholders to discover the issue themselves.