General Technical Tool Proficiency Questions

Practical fluency with the technical productivity, analysis, and reporting tools used in your day-to-day work: spreadsheet and reporting software (for example Excel or Google Sheets), scripting or query languages (for example SQL, Python, R), data visualization or BI platforms (for example Tableau, Power BI, Looker), and domain-specific applications (for example CRM, ERP, contract or case management, marketing automation) where relevant to the role. Candidates should be able to describe their depth of expertise, typical use cases and real world examples, automation or scripting practices, and how they choose the right tool for a given problem. This topic also covers reproducible workflows, data preparation and cleaning, presenting findings clearly, and integrating tools into cross functional projects.

EasyTechnical

71 practiced

In Tableau, explain the difference between filters and parameters. Provide two concrete scenarios where a parameter is preferred over a filter and vice versa. Also describe any performance implications of using many filters on large extract-based dashboards.

EasyTechnical

78 practiced

You receive a new reporting request from a sales manager who wants a dashboard to track quota attainment by sales rep. Describe your stakeholder discovery process using Jira or Azure DevOps to capture requirements, estimate effort, and prioritize the ticket. What fields and acceptance criteria would you include in the ticket to make implementation clear?

Sample Answer

Situation: A sales manager requests a quota-attainment dashboard for reps.

Discovery process (using Jira/Azure DevOps)1. Kickoff: Create an initial ticket/epic and schedule a 30–45 min discovery meeting to clarify scope, audience, cadence, and KPI definitions (quota, attainment %, period, territory, role).2. Capture requirements in the ticket: attach mockups or ask stakeholder to mark up an example (Excel or screenshot). Ask about filters, drill-downs, refresh frequency, security (row-level security), and downstream consumers (executives, managers).3. Data validation: Identify source systems (CRM, compensation system), required joins, and known data quality issues. Request sample data extracts if needed.4. Acceptance & UX: Agree on visualizations (bar/line, table), export options, and success metrics (e.g., within 2% of source totals).5. Follow-up: Convert discovery notes into detailed user stories and subtasks (data pipeline, model, visuals, QA).

Ticket fields to populate (Jira/Azure DevOps)- Title: "Quota attainment dashboard — Sales Reps (Monthly/Quarterly)"- Type: Story (or Feature/Epic if broader)- Priority: (set after WSJF)- Reporter / Assignee- Description: One-paragraph summary + business why- Acceptance Criteria (see below)- Data sources: list with owners & sample links- Mockups/attachments: annotated screenshots- Dependencies: downstream data readiness, API limits- Estimates: Story points or T-shirt size- Labels/Tags: powerbi, quota, sales- Security/RLS requirements- Due date/SLA- Test steps & QA owner

Estimation & prioritization- Estimation: Team does a quick grooming session; break into subtasks (ETL, model, visuals, QA). Use story points via planning poker; if unfamiliar, use T-shirt sizing then convert.- Prioritization: Use WSJF (Business value * Urgency / Effort) or simple priority matrix. Consider impact (number of users, revenue influence), confidence in requirements, and effort estimate.- Buffer: Add a small contingency for data cleanup work.

Example acceptance criteria (clear, testable)- Given production CRM and compensation feeds, the dashboard shows quota attainment % per rep for selected month and matches source totals within 2%.- Users can filter by time period, region, and manager; selecting a rep shows a drill-through to transaction-level records.- Data refreshes daily at 6:00 AM and reflects data loaded by 5:00 AM.- Row-level security enforces that managers see only their team; executives see all.- Exports to CSV preserve displayed filters and include timestamp.- Performance: initial load under 4 seconds for default view.- Tests: Unit tests for ETL, sample QA checklist attached.

Why this works- Captures business context, data constraints, and UX up front to reduce rework- Breaks work into measurable chunks for accurate estimation- Acceptance criteria are specific and testable so dev/QA know when done- Prioritization aligns team effort with business impact and urgency.

MediumTechnical

80 practiced

Given a 500GB transactions table that grows by 50GB per month, design a partitioning and indexing strategy to support fast monthly rollups and avoid full table scans. Explain partition key choice, index types to support common queries, and how you would manage partition lifecycle (retention and compaction).

Sample Answer

Situation: We need fast monthly rollups on a large and growing transactions table while preventing full table scans.

Partitioning strategy:- Partition by transaction_date using monthly RANGE partitions (e.g., 2025-01, 2025-02). Monthly partitions align directly with your rollups and limit scans to a single partition or a small set.- If query patterns often filter by tenant/customer, use SUBPARTITION (HASH or LIST on customer_id) inside each month to reduce IO for high-cardinality tenants.

Indexing strategy:- Use partitioned indexes so each partition has its own index; this allows partition pruning and avoids global index rebuilds.- For point/lookup queries: B-tree composite index on (transaction_date, customer_id, transaction_id) or (customer_id, transaction_date) depending on filter order.- For low-cardinality analytics / many-similar-values: consider bitmap indexes (on OLAP DBs) or rely on columnar storage with zone maps (e.g., Parquet/Redshift Spectrum, BigQuery).- Create covering indexes/materialized views for common monthly rollups (e.g., SUM(amount) GROUP BY product_category) updated nightly or incrementally.

Partition lifecycle & maintenance:- Retention: keep N months in hot table (e.g., 24 months). Automate DROP/ARCHIVE of old partitions to cheaper storage (move partition files to archive schema or object storage).- Compaction/maintenance: run periodic VACUUM/REORG/OPTIMIZE on recent partitions to compact files and update statistics; merge small files after ETL (especially in file-based lakes).- Rebuild stats after major loads; maintain incremental refresh for materialized views.- Automation: scheduled jobs to create the next month partition, rotate retention, and validate partition pruning.

Trade-offs:- Monthly range partitions minimize scanned data for rollups; subpartitioning reduces per-tenant IO but adds complexity.- Use columnar storage + materialized rollups for fastest analytical queries; use indexes for low-latency point lookups.

This combination gives fast monthly aggregations, predictable maintenance, and scalable growth handling.

HardSystem Design

68 practiced

You must design a robust incremental data pipeline using dbt for modeling, Airflow for orchestration, and a cloud warehouse. Requirements: 10M rows ingested daily, schema evolution allowed, near real-time alerts on test failures, and ability to rollback a bad deployment. Provide architecture, deployment flow, testing strategy, and failure recovery steps in detail.

Sample Answer

Requirements clarification:- Ingest ~10M rows/day, support schema evolution, near real-time alerts on test failures, and safe rollback of bad deployments. Cloud warehouse: Snowflake (or BigQuery/Redshift) + dbt + Airflow.

Architecture:- Ingestion: Streaming (Kafka / Kinesis) into raw landing tables in cloud storage or warehouse staging (micro-batches). Use partitioning by ingestion_time, source_id.- Orchestration: Airflow DAGs trigger ingestion validation, run incremental dbt models, and post-run QA.- Modeling: dbt incremental models (is_incremental() logic) layered: staging → core (deduped, conformed) → marts. Use snapshots for slowly changing or event-sourced tables.- Catalog & metadata: dbt docs + a simple metadata table recording model version, git commit, schema hashes.- Monitoring/alerting: Airflow + dbt-run results feed into Alerting (PagerDuty/Slack) via webhook; send near real-time alerts for test failures.

Deployment flow:1. Dev feature branch; write dbt models/tests.2. CI (GitHub Actions): lint, run unit dbt tests on sample dataset, run schema-change checks (compare expected schema vs current), static analysis.3. Merge to main triggers staging deployment: run dbt on a staging dataset in warehouse.4. Run full integration tests and data quality tests; if green, promote to production.5. Production deploy via Airflow CI/CD operator that records git commit id and applies migration tags.

Testing strategy:- dbt tests: schema tests, unique/not_null, relationships, custom data tests (variance checks, row-count delta thresholds).- Incremental validation: before running incremental load, run pre-hook that checks source watermark and schema; use dry-run on new columns.- Backfill/snapshot tests: run on representative slices.- Canary promotion: run models for a small partition (last hour/day) then full.

Schema evolution:- Use explicit column mapping and a column registry in dbt sources. When new columns arrive: - Auto-detect via pre-hook; if additive (new nullable column) allow and add to models via dbt exposures with feature flag. - For breaking changes (rename/remove), fail CI and require migration PR with transformation logic; maintain column aliases in SQL to support backward compatibility.- Use views for marts to decouple physical column changes from downstream BI.

Failure recovery & rollback:- Atomic deploy metadata: record commit id and model versions.- If a bad deployment: use Airflow to pause downstream DAGs, revert to previous commit tag, run dbt seed and models with --full-refresh for affected models (or use time-travel in Snowflake/BigQuery to restore affected partitions).- For incremental corruption, restore from snapshots/warehouse time-travel for affected partitions then re-run dbt incremental/full-refresh.- Post-rollback: run full test suite and compare row counts + checksums against pre-deploy baselines.

Operational practices:- Maintain SLA: monitor ingestion lag, model run time, test failure rate.- Alerts: immediate Slack + paging for failures that fail critical tests (schema drift, row-count drop > X%).- Documentation: dbt docs, runbook for rollback with clear owner and run commands.

This design balances incremental performance for 10M/day, safe schema evolution, rapid alerts, and deterministic rollback for BI consumers.

EasyTechnical

119 practiced

You are responsible for daily ETL that loads sales data. List a lightweight checklist of automated data quality checks you would run after each load to ensure completeness and freshness. Include SQL examples or brief descriptions for checks like row count, null ratios on key fields, and referential integrity, and describe how you would notify stakeholders when checks fail.

Sample Answer

Checklist (run after each ETL load):

1) Row count / load success- Purpose: detect missing/partial loads.- SQL:

sql

SELECT COUNT(*) AS rows_loaded FROM sales_staging WHERE load_date = CURRENT_DATE;

Compare to yesterday or expected count; alert if < threshold (e.g., < 95% of expected or diff > X%).

2) Delta count / freshness- Purpose: ensure new data arrived.- SQL:

sql

SELECT MAX(sale_timestamp) AS latest_ts FROM sales_fact;

Alert if latest_ts < (CURRENT_TIMESTAMP - INTERVAL '24 hours').

3) Null ratio on key fields- Purpose: critical fields must be populated.- SQL:

sql

SELECT
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS pct_null_customer
FROM sales_fact
WHERE load_date = CURRENT_DATE;

Alert if pct_null_customer > 0.01 (tunable).

4) Referential integrity (lookup keys)- Purpose: detect orphaned foreign keys.- SQL:

sql

SELECT COUNT(*) AS orphan_count
FROM sales_fact f
LEFT JOIN customers c ON f.customer_id = c.customer_id
WHERE f.load_date = CURRENT_DATE AND c.customer_id IS NULL;

Alert if orphan_count > 0.

5) Aggregate sanity checks- Purpose: catch major value shifts.- SQL:

sql

SELECT SUM(amount) AS total_sales, COUNT(*) AS txns FROM sales_fact WHERE load_date = CURRENT_DATE;

Compare to moving average (7-day); alert if outside nσ threshold.

6) Duplicate detection- Purpose: prevent double-counting.- SQL:

sql

SELECT sale_id, COUNT(*) FROM sales_fact WHERE load_date = CURRENT_DATE GROUP BY sale_id HAVING COUNT(*) > 1;

7) Schema / row-level validation- Purpose: verify types/ranges.- Example: check negative amounts:

sql

SELECT COUNT(*) FROM sales_fact WHERE amount < 0 AND load_date = CURRENT_DATE;

Notification & workflow:- Automate checks via scheduler (Airflow/cron) that runs SQL and captures results.- On failure: create alert with context (failed check, query result, sample rows) sent to: - PagerDuty/Slack channel #data-alerts for on-call BI/ETL engineer - Email to data owners and dashboard stakeholders with remediation steps and link to runbook- Include automatic ticket creation (Jira) if failure persists >1 run.- Provide dashboards showing check history and MTTR metrics so stakeholders see reliability trends.

Thresholds, sample sizes and alerting severity should be tuned with stakeholders to avoid noise.

Unlock Full Question Bank

Get access to hundreds of General Technical Tool Proficiency interview questions and detailed answers.

Join thousands of developers preparing for their dream job.