EasyTechnical

126 practiced

You're building an automated Excel report that pulls data from SQL Server and must refresh every morning at 06:00. Describe, step-by-step, how you would set up the data connection (Power Query), schedule the refresh (Windows Task Scheduler, Power Automate, or server-side option), manage credentials securely, and handle file saving/locking to avoid conflicts for users.

Sample Answer

1) Power Query connection- In Excel: Data → Get Data → From Database → From SQL Server Database. Enter server/database; choose Import (recommended) or DirectQuery-like (table load).- In Advanced options add a parameterized SQL query if needed. Set Load To → Table / Data Model as required.- In Query Editor: filter/transform, set Query Parameters for server, database, date range so you can change without editing the query.

2) Credentials & security- Prefer Windows Integrated Authentication (trusted) or a service account with least privileges for read-only access.- Store credentials securely: if using on a Windows machine, save to Windows Credential Manager under the account that will run the scheduled task. For cloud/server solutions use Azure Key Vault or SQL Agent credentials.- Avoid embedding plaintext credentials in the workbook.

3) Scheduling the refresh- Option A (Windows Task Scheduler + PowerShell): Create a PowerShell script that opens Excel COM, refreshes all queries, waits for completion, saves and closes. Schedule daily at 06:00 under a service account.- Option B (Power Automate): Use the “Refresh a dataset” / Excel Online actions if workbook is on OneDrive/SharePoint; schedule daily at 06:00.- Option C (Server-side): Use SQL Server Agent to export query results to CSV/Excel or refresh a Power BI dataset; then push to report location.- Include retry logic and email on failure.

Example PowerShell snippet:

powershell

$excel = New-Object -ComObject Excel.Application
$wb = $excel.Workbooks.Open("\\share\reports\Report.xlsx")
$wb.RefreshAll()
Start-Sleep -Seconds 30  # or poll for background refresh completion
$wb.Save()
$wb.Close($true)
$excel.Quit()

4) File saving & locking- Save outputs to a staging filename (Report_staging.xlsx). After successful refresh, move/rename atomically to Report.xlsx to avoid users opening a mid-refresh file.- Store on a network share or SharePoint. If SharePoint/OneDrive, use check-in/check-out or Microsoft Graph/Power Automate to manage versions.- Prevent conflicts: schedule refresh during off-hours, notify stakeholders, set share permissions read-only during replace, or maintain a read-only published copy and editable personal copies.- Log timestamps and outcomes to a small log file or monitoring system for audit/alerts.

5) Monitoring & maintenance- Add logging, error trapping, and alerting (email/Teams) on failure.- Rotate service account credentials per policy; test after network or DB changes.- Document the process and run periodic manual checks.

MediumTechnical

67 practiced

A scheduled Power BI dataset refresh intermittently fails with a gateway 'timeout' error. Describe your step-by-step troubleshooting approach: what logs and metrics you would inspect, tests to isolate network vs query performance vs credential issues, mitigation options, and how to notify stakeholders and track a fix.

Sample Answer

Situation: A scheduled Power BI dataset refresh intermittently fails with a gateway "timeout" error, impacting daily dashboards for stakeholders.

Approach — step-by-step troubleshooting:1. Reproduce & gather context- Confirm which dataset, gateway (personal vs enterprise), refresh schedule, frequency and timestamps of failures.- Note recent changes: dataset size/schema, gateway updates, network/SQL maintenance windows.

2. Inspect logs & metrics- Power BI Service refresh history (dataset settings → refresh history): check error messages, start/end times, duration.- On-premises data gateway logs: Gateway Performance Log and Trace logs (located on gateway machine) for network timeouts, throttling, or auth failures.- Gateway diagnostics in Portal: CPU/memory, connection pool, queue length, and number of concurrent requests.- Source DB metrics: SQL Server wait stats, long-running queries, locks, CPU, and network I/O in the same time window.- Network monitoring: firewall logs, VPN drops, packet loss during failure window.

3. Tests to isolate cause- Credential/auth: Manually trigger a refresh from Service using “Test connection” in dataset credentials; rotate/re-enter credentials and check.- Query performance: Run the dataset's underlying queries directly against source (in SQL Management Studio) with parameters and measure execution time; enable actual execution plans.- Gateway/network: From gateway server, run continuous ping/traceroute to DB and Power BI endpoints; perform a manual refresh from gateway machine to see if local execution succeeds.- Scale/Concurrency: Trigger multiple refreshes or check for concurrent gateway jobs that cause queueing.

4. Mitigation options- Short-term: Stagger refresh schedule to avoid concurrency, increase timeout settings if available, run incremental refresh or reduce dataset size (filter or remove heavy columns), temporarily switch to DirectQuery if acceptable.- Gateway tuning: Increase gateway machine resources, update gateway to latest version, enable high-availability gateway cluster.- Query optimization: Add indexes, rewrite expensive queries, materialize views or push transformations into source DB/ETL.- Credentials: Update service account to a dedicated, non-expiring account and ensure least-privilege access.

5. Communication & tracking- Notify stakeholders immediately: brief message (what failed, impact, workaround, expected ETA) via email/Slack and update dashboard status page.- Create an incident ticket in tracking system (Jira/ServiceNow) with logs, timestamps, root-cause hypothesis, and assigned owner.- Provide interim workarounds (e.g., manual refresh, cached snapshot).- After resolution: run post-mortem, document root cause, steps taken, and preventive actions; share a short summary and timelines with stakeholders.- Monitor for recurrence for several days and close the ticket after verification.

This ensures I isolate whether the timeout is due to network, gateway resource contention, slow source queries, or credential failures; apply both immediate mitigations and long-term fixes; and keep stakeholders informed throughout.

MediumTechnical

70 practiced

Compare using a live connection to a data source versus using an extracted snapshot for BI dashboards. Discuss trade-offs in freshness, concurrency support, performance, security, cost, and scalability, and give two example scenarios where each approach is preferred.

EasyTechnical

123 practiced

Given the transactions table schema: transactions(transaction_id INT PK, user_id INT, product_id INT, event_ts TIMESTAMP, event_type VARCHAR). Write an ANSI SQL query to compute Monthly Active Users (MAU) per product for the past 12 months. Output columns: month_start, product_id, mau. Assume event_type = 'usage' indicates activity and use months aligned to UTC.

Sample Answer

Approach: Filter to usage events in the last 12 months, bucket timestamps to month-start (UTC-aligned), then count distinct users per product per month.

sql

SELECT
  DATE_TRUNC('month', event_ts) AS month_start,  -- month aligned to UTC if event_ts is UTC
  product_id,
  COUNT(DISTINCT user_id) AS mau
FROM transactions
WHERE event_type = 'usage'
  AND event_ts >= DATE_TRUNC('month', CURRENT_TIMESTAMP) - INTERVAL '11' MONTH
  AND event_ts <  DATE_TRUNC('month', CURRENT_TIMESTAMP) + INTERVAL '1' MONTH
GROUP BY 1, product_id
ORDER BY month_start DESC, product_id;

Key points:- DATE_TRUNC('month', event_ts) groups events to the month start timestamp (e.g., 2025-06-01 00:00:00).- COUNT(DISTINCT user_id) yields unique active users per product per month (MAU).- The WHERE window covers the current month plus prior 11 months = last 12 months.- If event_ts is not stored in UTC, convert it to UTC before truncation (e.g., event_ts AT TIME ZONE 'UTC' in DBs that support it).Edge cases:- Months with zero activity for a product won’t appear; join against a months × products scaffold if you need zeros.- Large tables: consider partition pruning or pre-aggregations for performance.

EasyTechnical

92 practiced

Explain the differences between TRUNCATE and DELETE operations on a target analytics table in the context of refresh jobs: transactional semantics, logging/undo, performance characteristics, identity/sequences, and impacts on downstream processes such as replication or incremental change detection.

Sample Answer

Transactional semantics:- DELETE is a DML operation: it obeys transactions (can be COMMIT/ROLLBACK) and can be run with WHERE to remove subsets. TRUNCATE is a DDL-like operation in many RDBMSs: it typically cannot be filtered, and while it may be transaction-safe in some systems, in others it’s auto-committed or restricted inside transactions.

Logging / undo:- DELETE logs each row deletion (row-level logging/undo), enabling point-in-time recovery and fine-grained rollback. This increases log volume.- TRUNCATE is minimally logged in many engines (logs page deallocations or metadata changes), so it generates far less log/undo activity.

Performance characteristics:- DELETE is slower for large tables because it processes row-by-row (and fires triggers, maintains indexes, cascade constraints). TRUNCATE is much faster — deallocates pages and resets storage metadata.- For large refresh jobs, TRUNCATE + bulk INSERT is preferred for speed.

Identity / sequences:- DELETE preserves identity/sequence counters. TRUNCATE often resets identity columns to seed (behavior depends on DB; e.g., SQL Server resets, PostgreSQL can be configured).

Impacts on downstream processes:- Replication/change-data-capture: DELETE produces many change events (one per row), so CDC/replication sees individual deletes. TRUNCATE may be replicated as a single metadata event or may not be captured by some CDC tools — this can break downstream incremental loads or auditing.- Incremental change detection that relies on row-level tombstones or log-based CDC works with DELETE; TRUNCATE may require special handling (full reload) for consumers.

Recommendation for refresh jobs (Data Analyst perspective):- If you need fast full refresh and downstream consumers expect a full replace, TRUNCATE then bulk load is efficient — but confirm how replication/CDC and identity handling behave.- If downstream incremental processing, auditing, or transactional rollback is required, use DELETE (possibly batched deletes) or drop-and-recreate the table with coordination.

Automated Reporting & Report Development Questions

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Sample Answer

Unlock Full Question Bank