Technical Stack and Infrastructure Questions

Addresses the technology stack and platform infrastructure choices that support a product. Topics include cloud architecture, deployment platforms, service and data layer design, observability and monitoring, scalability and performance considerations, tooling for build and release, and assessing technical debt or modernization needs in the stack. Also covers evaluation of BI tools, ETL platforms, and how infrastructure choices affect team velocity and operational cost.

HardTechnical

27 practiced

Explain strategies to optimize costs for cloud data warehouses such as Snowflake or BigQuery. Discuss partitioning/clustering, materialized views, storage vs compute trade-offs, reducing scanned bytes via query optimization, and governance controls like resource monitors and cost alerts.

Sample Answer

Start with goals: minimize billed compute and scanned bytes while preserving query latency and data freshness for dashboards.

Partitioning & clustering- Partition large tables by date (ingest or event date) so time-range filters scan few partitions. Example: daily-partitioned events table reduces scanned bytes for 30‑day dashboards by ~30× vs full-scan.- Use clustering (Snowflake) or BigQuery partition + clustering on high-cardinality filter columns (user_id, country) to eliminate micro-partitions during queries. Recluster/optimize periodically to avoid fragmentation.

Materialized views & aggregated tables- Create incremental materialized views or pre-aggregated summary tables for frequent dashboard queries (e.g., daily rollups by product). In Snowflake use materialized views; in BigQuery use scheduled queries to maintain summaries. Trade-off: storage + maintenance compute vs big reduction in repeated query cost and latency.

Storage vs compute trade-offs- Cold vs hot storage: keep raw detailed data in cheaper long-term storage (columnar/object) and expose curated marts for BI. In Snowflake, compress and use transient tables for intermediate work. In BigQuery, partitioned/tables with lower expiration can reduce storage costs.- Resize compute to match workload: use auto-scaling warehouses with smaller sizes during off-hours, suspend warehouses when idle (Snowflake), or schedule heavy ELT jobs in off-peak windows for lower pricing.

Reducing scanned bytes via query optimization- Push predicates to storage: ensure WHERE clauses use partition/cluster keys. Avoid SELECT *; project only needed columns.- Use LIMIT and TOP appropriately for preview queries; avoid large cross joins or unbounded window functions on raw tables.- Cache results in BI tool or use query result caching (Snowflake/BigQuery) to avoid re-execution. Encourage dashboard filters that narrow time ranges by default.- Rewrite expensive queries: replace repeated subqueries with WITH / temp tables, avoid correlated subqueries, and precompute expensive joins.

Governance & cost controls- Resource monitors / quotas: configure Snowflake resource monitors to suspend warehouses after thresholds; use BigQuery quota alerts and budget notifications.- Tagging and chargeback: enforce dataset/project tags to attribute cost to teams; use cost breakdowns nightly to stakeholders.- Cost-aware SLA & data product catalog: document expected freshness and query patterns; restrict interactive access to very large raw tables; require rationale for creating new wide tables.- Alerts & automated policies: set budgets with email/Slack alerts, auto-suspend ETL when anomalies detected (e.g., sudden increase in scanned bytes).

Measurement & continuous improvement- Track key metrics: scanned bytes per dashboard, average query latency, refresh times, cost per dashboard. Run quarterly reviews to identify heavy queries and convert them to materialized views or summaries.

These strategies balance performance, freshness, and cost—apply incrementally: start by partitioning + query fixes, add aggregates for heavy dashboards, then enforce governance and monitoring.

HardSystem Design

23 practiced

Design a CI/CD pipeline for dbt-driven data transformations. Include code review, unit and integration testing, ephemeral or dev environments, environment promotion, scheduling, secrets handling, rollback procedures, and coordination for schema migrations with downstream consumers.

Sample Answer

Requirements:- CI/CD for dbt projects delivering trustworthy models to analytics BI layers (Looker/Tableau).- Enforce code review, automated unit & integration tests, ephemeral/dev environments, safe promotion to prod, scheduled runs, secrets management, rollback, and coordinated schema migrations with consumers.

High-level flow:Developer branch → PR + code review → CI runs (lint, unit tests, model compilation, static checks) → create ephemeral dev environment → run integration tests against a snapshot of raw data → if green, merge to main → CD deploys to staging (full run, data quality checks, contract tests) → stakeholder sign-off → promote to production with scheduled runs.

Key components:- Repo: git + PR workflow (require approvals, CLA)- CI: GitHub Actions/GitLab CI triggering dbt commands (dbt compile, dbt test, dbt source freshness)- Ephemeral dev environments: use namespaces/schemas per PR (dbt target profile with schema = dev_{pr_number}) or ephemeral cloud warehouses (Snowflake transient DBs) created by CI- Unit tests: dbt tests + schema tests + custom SQL tests; use dbt-utils for common macros- Integration tests: run dbt run on limited sample dataset (snapshotted via views or cloned schema) and verify end-to-end SQL output match golden files- Data quality & contract tests: Great Expectations or dbt-expectations in staging to assert row counts, nulls, type constraints- Scheduling: Airflow or dbt Cloud job scheduler to orchestrate runs with dependency graph; CI triggers immediate runs for deployments- Secrets: store DB credentials and API keys in Vault/AWS Secrets Manager/GCP Secret Manager; CI uses short-lived tokens; rotate regularly; least privilege roles- Environment promotion: promotion via git tags or release branches; CD maps target profile (staging → production) and runs dbt run --models +deploy flags; approvals required- Rollback: retain previous model versions via git tags and snapshot data in production (time travel in Snowflake or backup tables). To rollback, deploy prior git tag and run dbt run --full-refresh on affected models or restore from time travel for transient issues. Maintain change window and run backfill scripts if needed.- Schema migrations coordination: use explicit semantic versioning for models and column changes. For breaking changes: - Announce change in data contract doc + calendar; create deprecation path: add new column while keeping old one, backfill new column, update downstream mapping. - Use feature flags / dual-write period; update consumer teams and BI dashboards in a coordinated release window. - Automate contract tests that validate downstream schemas (e.g., column presence/types). Require consumer sign-off in staging before prod promotion.- Observability & alerts: dbt artifacts (run_results, catalog) pushed to monitoring (Prometheus/Datadog). Alert on test failures, freshness, SLA breaches.- Governance & access: enforce branch protection, role-based access to production secrets, and runbooks for incident response.

Example CI snippet (conceptual):

yaml

# GitHub Actions job steps
- name: Install deps
- name: dbt compile
- name: dbt test --models tag:unit
- name: Create ephemeral schema dev_${{ github.event.number }}
- name: dbt run --target dev_pr
- name: dbt test --models tag:integration
- name: Upload artifacts (run_results.json) to storage

Why this works:- Ephemeral schemas isolate PR work and prevent interference.- Multi-stage testing (unit → integration → staging) catches issues early.- Contracts + coordination prevent downstream breakage.- Secrets and short-lived creds reduce blast radius; rollback and time-travel/backups allow recovery.- Scheduling plus observability ensures reliable, monitored delivery suitable for BI needs.

HardTechnical

19 practiced

You rely on derived KPIs produced by transformation pipelines. Propose an architecture and process to detect data drift and regressions in those metrics (sudden changes or distribution shifts). Include automated tests, alerting thresholds, analyst-facing visualizations, and safe rollback strategies.

Sample Answer

Requirements:- Functional: detect sudden KPI changes (level shifts, spikes, drifts in distribution/variance) for derived metrics produced by transformation pipelines; notify analysts/stakeholders; allow quick investigation and safe rollback.- Non-functional: near-real-time detection (hourly/daily), low false positives, auditability, reproducible rollbacks.

High-level architecture:- Ingestion/Transform (ETL/DBT) → Metrics Store (e.g., aggregate tables, Metrics Layer like dbt + metrics layer or a metrics DB) → Monitoring Service (drift/regression detectors) → Alerting & Runbook → Observability/UI (Looker/Tableau + anomaly dashboard) → CI/CD for transformations.

Detection & automated tests:- Unit/regression tests in CI for dbt models: schema, row-count ranges, assertion tests (non-null, monotonic where applicable), and historical expectations.- Pre-deploy data tests: smoke-run on sample partition; compare key aggregates against baseline using tolerance windows.- Runtime monitoring: - Point anomaly detection: seasonal-HoltWinters or median absolute deviation (MAD) & z-score on residuals to capture spikes. - Distribution drift: population-level tests (Kolmogorov-Smirnov, PSI) on features feeding KPI and on KPI distributions over rolling windows (7/28/90 days). - Change point detection (CUSUM/PELT) for persistent shifts.- Combine statistical signals with business rules (absolute thresholds, % change relative to same period last week/month).

Alerting thresholds & workflow:- Multi-tier alerts: informational (<10% change or small PSI), warning (10–30% or PSI 0.1–0.25), critical (>30% or PSI>0.25 or failed assertions).- Alert contain: metric name, magnitude, affected partitions, contributing upstream models/tables, data sample, recent commits.- Delivery: Slack/email + ticket creation (Jira) with runbook link.

Analyst-facing visualizations:- Anomaly dashboard per KPI: time series with detected anomalies/change points, distribution histograms (baseline vs current), PSI/K-S trend, contributing upstream dimensional breakdown, data lineage links to dbt models and last successful runs, and “Investigation” panel with sample rows and SQL to reproduce aggregates.- Executive monitoring panel: health summary, counts of warnings/criticals, SLA impact estimates.

Safe rollback & remediation:- Maintain immutable snapshots of derived aggregates per run and organization of transformation code in git + CI artifacts for each deploy.- Automated canary deployments: run new transformations in parallel for a window, compare metrics to production baseline; block deploy if tests fail.- Rollback strategies: - Soft rollback — switch downstream dashboards to prior snapshot of aggregated metrics (feature flag / config switch) allowing quick user-facing revert. - Code rollback — revert dbt commit and re-run affected models; CI gate prevents re-deploy until tests pass.- Postmortem & corrective action: require root-cause, corrective dbt tests added, and closure in ticketing system.

Why this works:- Mix of statistical detection and business rules reduces false positives.- CI tests + canary runs prevent many regressions pre-deploy.- Dashboards and lineage speed triage; immutable snapshots and feature-flagged rollbacks minimize user impact.

HardTechnical

33 practiced

Design controls and processes to ensure GDPR compliance for a BI platform that stores and analyzes personal data. Cover data classification, minimization, pseudonymization or aggregation, access control, audit logging, consent management, retention/deletion workflows, and vendor assessments.

Sample Answer

Situation: As the BI analyst responsible for dashboards that surface customer metrics, I proposed a controls framework to ensure GDPR compliance across our BI platform that stores and analyzes personal data.

Controls & Processes:

1. Data classification- Enforce a schema-level classification: Sensitive PII (e.g., national ID), Personal Data, Business Identifiers, Non-Personal.- Tag datasets/columns in the data catalog (Looker/Power BI metadata) with classification and legal basis for processing.

2. Data minimization- Require BI requirements to include explicit purpose and fields needed; reviewers block requests that include full PII if not required.- Automate ETL filters to drop unnecessary fields before landing in analytics zones.

3. Pseudonymization / aggregation- Apply field-level pseudonymization (hashing with per-environment salts + pepper in HSM) for identifiers used in analysis.- Use irreversible tokenization for sensitive IDs; provide re-identification only via a controlled vault and legal process.- Enforce aggregation thresholds (e.g., min 10 records) and k-anonymity checks for dashboards to prevent singling out.

4. Access control- RBAC mapped to job roles; row-level security and column-level masking in BI tools and DBs.- Just-in-time access requests with manager approval and automatic expiry; quarterly access attestation.

5. Audit logging & monitoring- Immutable audit logs for data access, exports, dashboard views, and dataset changes (central SIEM).- Alert on bulk exports, unusual query patterns, or attempts to access masked columns.

6. Consent & legal basis- Data catalog links each dataset to consent status / lawful basis stored in a consent registry.- Dashboards that include consentable events expose the consent status and block processing when consent revoked.

7. Retention & deletion workflows- Enforce retention policies per classification; automated purge jobs in data warehouse; archived data encrypted.- Maintain deletion requests pipeline: map DSAR to datasets, confirm deletions, log proof of erasure.

8. Vendor assessments- DPIA for high-risk processing; vendor checklist: GDPR DPA, subprocessors, data residency, breach notification SLAs, audits, security posture (ISO27001/CIS), encryption at rest/in transit.- Annual vendor reviews and contractual right-to-audit.

Operational governance- BI change control for new datasets, quarterly privacy reviews, stakeholder sign-off, and mandatory GDPR training for BI team.

Why this works: It ties technical controls to legal basis, limits exposure through minimization/pseudonymization, enforces principle of least privilege, and provides auditable evidence for compliance and DSAR/erasures — all practical within BI tooling and warehouse workflows.

MediumTechnical

22 practiced

A company reports dashboard load times around 5–10 seconds when queries hit large fact tables. Propose a prioritized list of practical solutions to improve perceived and actual performance for end users, covering immediate quick wins and longer-term architectural changes.

Sample Answer

Situation: Dashboards loading in 5–10s when queries scan large fact tables — both perceived and real performance suffer. Below is a prioritized, practical list targeting quick wins first, then longer-term architectural changes, tailored for a BI analyst.

Immediate quick wins (0–2 weeks)- Add BI-tool caching / extracts: switch heavy dashboards to Tableau/Power BI extracts or Looker persistent derived tables refreshed hourly. Quick perceived improvement for users.- Reduce query scope with dashboard design: default to top-level summaries, use “apply filters” or “Run” buttons, limit default date ranges (e.g., last 30 days).- Enable query/result caching in DB and BI tool; set appropriate TTL for low-change reports.- Optimize slow visualizations: replace highly-cardinal charts (granular tables) with aggregated summaries or pre-filtered views.

Short-term (2–6 weeks)- Create aggregate tables / materialized views for common rollups (daily/customer/product). Schedule incremental refreshes.- Add appropriate indexes and partitioning on fact table (date, customer_id) and ensure stats are up to date.- Rewrite expensive queries: remove SELECT *, push filters before joins, pre-aggregate in SQL.- Implement incremental extracts for ETL to avoid full reloads.

Medium/Long-term (6 weeks+)- Build a semantic layer / curated marts: create star-schema OLAP-like marts to serve BI with denormalized, query-ready structures.- Adopt pre-aggregation / cube tooling (e.g., dbt + materializations, ClickHouse/BigQuery BI Engine, Druid) for sub-second analytical queries.- Consider a columnar or cloud data warehouse if OLTP DB is used (e.g., BigQuery, Snowflake, Redshift Spectrum).- Implement monitoring and SLAs: query performance dashboards, slow-query alerts, and periodic audits.

Measurement & governance- Baseline key metrics (median load time, 95th pct) and measure after each change.- Prioritize aggregates and indexes by usage: instrument dashboards to log most-frequent queries and users.- Balance freshness vs. speed: document acceptable staleness per report and set refresh policies.

Result-focused rationale- Quick UX fixes + caching deliver immediate perceived speed.- Aggregates, indexing, and semantic layers reduce scan volume and deliver consistent long-term performance.- Monitoring ensures changes target actual user pain points and measure ROI.

Unlock Full Question Bank

Get access to hundreds of Technical Stack and Infrastructure interview questions and detailed answers.

Join thousands of developers preparing for their dream job.