# AutoExport-BI — Resume & Interview Portfolio

## User-confirmed context — 2026-10-18

> Source: direct user answers in the portfolio review. These are user-confirmed statements, not independently rerun repository or production measurements.

- **Ownership:** independently built the entire application.
- **Operational adoption:** used daily by the whole team of more than 6 people (7+ users), with at least one export per day across the team.
- **Data volume:** exports range from roughly 100 rows through 10,000 rows to more than 1 million rows.
- **Workflow benefit:** users start an export and leave it running without further interaction; extraction and export-failure retries are automatic. The previous manual export process could take over an hour, especially during cube outages or heavy usage. This establishes reduced manual intervention, not a measured reduction in elapsed runtime.
- **Reconciliation:** compares exported totals with cube totals and reports differences in Excel. No mismatches have been observed so far; do not claim actual discrepancies caught or automatically corrected.
- **Unmeasured:** exact hours saved, throughput, elapsed-time improvements and reliability percentages.

> Existing draft variants and unanswered placeholders are retained. This confirmed context governs personal ownership, adoption and outcomes; unresolved draft claims are not newly verified.


> Everything here comes from the repository itself: source, configuration, CI workflows, the packaging spec, and the tests. Placeholders in `[brackets]` are business metrics that the code can't measure, so you need to fill them in with real numbers.

---

## 1. High-Level Project Overview

### One-Line Pitch

**AutoExport-BI is a Windows desktop data-extraction platform built with Python 3.13 and PyQt6. It connects to Power BI semantic models through ADOMD.NET and runs parameterized DAX queries. Every export is checked against the source data model, and the results are published as Parquet and Excel files. Finance and controlling teams use it for 12-month rolling, single-month, annual customs, and GL-account audit exports.**

### Problem & Solution

**Problem.** Finance analysts had to pull large month-level transactional datasets (billing cases, costs, transport orders) out of a Power BI semantic model by hand. Getting this data out of Power BI by hand has three problems:

- Power BI's UI row limits and the long-running XMLA/MSOLAP sessions make large exports fragile. Session expiry, timeouts, and memory pressure are common.
- There is no built-in way to prove that an exported file matches the model's totals. A silent truncation in a finance report is a compliance risk.
- Recurring exports need many filter combinations (legal entity × month, cost centers × countries × statuses), so manual work doesn't scale.
- Output often has to be Excel, which caps at 1,048,576 rows per sheet and can turn source text into executable formulas (formula injection).

**Solution (architecture).**

- **Direct semantic-model access.** The Python app calls the .NET `Microsoft.AnalysisServices.AdomdClient` library in-process through `pythonnet`, so DAX runs directly against the model without going through Power BI's UI ([src/data/connection.py](src/data/connection.py), [src/data/query_executor.py](src/data/query_executor.py)).
- **Layered pipeline.** The flow is: PyQt6 GUI → `ExportWorker` (on a `QThread`) → orchestrators → per-month processors → DAX → DataFrame → atomic Parquet/Excel publication → reconciliation → retry → execution ledger.
- **Built-in reconciliation.** After each month's extraction, a second aggregate DAX query (`total_per_month_query.dax`) recomputes the month's total on the model. That total is compared with the exported DataFrame's sum, and the run fails if they differ by more than **0.01%** ([src/validation/validator.py](src/validation/validator.py)).
- **Self-healing, stage-aware retries.** If only validation failed, the existing Parquet file is re-validated without re-extracting it. Otherwise the whole month is re-run, with backoff that depends on the error type ([src/validation/retry.py](src/validation/retry.py), [src/validation/retry_manager.py](src/validation/retry_manager.py)).
- **Background Excel conversion.** A single-worker producer/consumer queue converts Parquet to Excel while extraction of the next month continues ([src/export/background_manager.py](src/export/background_manager.py)).
- **Zero-admin distribution.** The app ships as a PyInstaller one-dir EXE with a per-user Inno Setup installer that needs no admin rights. Releases are versioned, validated, smoke-tested, and published with SHA-256 checksums by GitHub Actions.

---

## 2. Comprehensive Tech Stack & Tooling

### Languages & Runtimes

| Technology | Evidence | Role |
|---|---|---|
| **Python 3.13** (pinned `>=3.13,<3.14`) | [pyproject.toml](pyproject.toml) | Application language. 3.14 is excluded because of interop constraints. |
| **.NET / CLR** via **pythonnet 3.2.0** | `import clr`, `clr.AddReference(...)` | Hosts ADOMD.NET in the Python process |
| **DAX** (Data Analysis Expressions) | [queries/](queries/): 7 `.dax` templates (330 lines), plus 3 snapshot queries embedded in [snapshot.py](src/processors/snapshot.py) | Query language for the Power BI / Analysis Services model |
| **PowerShell** | [scripts/build_release.ps1](scripts/build_release.ps1), workflow steps | Release staging, version calculation, checksums |
| **Inno Setup script** | [installer/AutoExport-BI.iss](installer/AutoExport-BI.iss) | Windows installer |

### Frontend / UI (Desktop)

| Technology | Usage |
|---|---|
| **PyQt6 6.11.0** | `QMainWindow` shell with a three-panel layout (navigation `QListWidget`, input `QStackedWidget`, and an execution panel with `QProgressBar` and `QTextBrowser` log) — [main_window.py](src/ui/qt/main_window.py) |
| **Qt signals/slots** for state propagation | 10 custom `pyqtSignal`s across the worker, tracker, and loader. This is the GUI's "state management": worker threads never touch widgets. |
| **QThread + `moveToThread`** worker pattern | `ExportWorker` and `LocationCountryListsLoadWorker` |
| **Qt Style Sheets (QSS) theming** | [theme.py](src/ui/qt/theme.py) (384 lines, 54 distinct hex colors). Dynamic `state` properties re-polish the status label for ready, running, complete, failed, and cancelled. |
| **Custom widgets** | `MultiSelectComboBox` (checkable combo with an event filter that keeps the popup open), a cost-center table with live search, "Select Visible Only", and manual entry |
| **Rich 15** | Console fallback tracker (`ProgressTracker`) implementing the same `TrackerProtocol` |

### Backend / Data Access & Processing

| Technology | Usage |
|---|---|
| **ADOMD.NET** (`Microsoft.AnalysisServices.AdomdClient.dll`, vendored in `lib/`) | `AdomdConnection`, `AdomdCommand`, `AdomdDataAdapter` over the **MSOLAP provider** (XMLA protocol to Power BI / Analysis Services) |
| **pandas 3.0.6 / NumPy 2.5.3** | Converting .NET `DataTable` → NumPy object array → DataFrame; reconciliation math; merges |
| **Polars 1.44.2** | Fast Parquet reads and `write_excel` in the converter; CAT database filtering |
| **PyArrow 25.0.1** | Parquet engine (Snappy compression) |
| **XlsxWriter 3.2.9 / openpyxl 3.1.5** | Streaming Excel writes (ZIP64); append-mode report updates |
| **python-dotenv** | `.env`-driven configuration (server, dataset, timeouts, retry counts, log levels) |
| **Concurrency primitives** | `ThreadPoolExecutor`, `threading.Condition`, `RLock`, `Event`, `queue.Queue`, `WeakSet` registry, `atexit` hook |

> There is no HTTP API. The protocol boundary is **XMLA/MSOLAP via ADOMD.NET**, and the inter-thread "API" is **Qt signals**.

### Database & Storage

| Layer | Details |
|---|---|
| **Source data store** | Power BI semantic model (Analysis Services tabular). The DAX touches **9 model tables**: `Billing Cases`, `Billing Cases - Costs`, `Billing Cases - Costs Status (selection)`, `Billing Cases - Status (selection)`, `Transport Orders`, `Loads`, `Legs (TOs on Loads)`, `Rating Units`, `Date (Finance)`. That's **68 distinct column references**. |
| **Columnar output** | Apache Parquet (Snappy) as the system of record for every extracted month |
| **Excel output** | Auto-split across sheets once 1,048,575 data rows is exceeded |
| **Reference data** | `Input_CAT.xlsx` with **5 sheets** used as lookup tables: `CostCenter-db`, `Leg-db`, `LocationType-db`, `CAT-db`, `ColumnOrder` |
| **Run state** | `execution_log.json`: a rolling ledger capped at **100 entries** ([state_manager.py](src/validation/state_manager.py)) |
| **File-system layout** | Per-user `%LOCALAPPDATA%\AutoExport-BI` with UUID-reserved run directories |

### DevOps, Testing & Tooling

| Tool | Usage |
|---|---|
| **uv** + `uv.lock` | Environment and dependency management; all deps pinned exactly |
| **GitHub Actions — CI** ([ci.yml](.github/workflows/ci.yml)) | 3 jobs: **Lint & Format** (Ruff on ubuntu), **Security Scan** (Bandit + Safety), **Tests** (windows-latest, gated on the other two). Uses concurrency groups with `cancel-in-progress`. |
| **GitHub Actions — Release** ([release.yml](.github/workflows/release.yml)) | On push to `main`: tests + 100% coverage gate → semver auto-bump from git tags → PyInstaller build → payload validation → **headless startup smoke test** (`QT_QPA_PLATFORM=offscreen`) → Inno Setup compile → SHA-256 manifest → GitHub Release. An optional live Power BI smoke test runs via `workflow_dispatch`, with `.env` injected from secrets. |
| **pytest 9 + pytest-qt 4.5** | 32 test modules, **231 test functions / 314 collected cases** (33 `parametrize` decorators), Qt tests with `qt_default_raising` |
| **coverage.py** | **Branch coverage enforced at `fail_under = 100`** |
| **Ruff 0.16** | 9 rule families (E, W, F, I, N, UP, B, SIM, RUF), formatter, line length 120 |
| **pre-commit** | Ruff fix/format, YAML check, large-file guard (1 MB), merge-conflict and debug-statement detection |
| **Bandit / Safety** | SAST over `src/` and a dependency vulnerability scan |
| **PyInstaller 6.22** | One-dir windowed build, Windows `VSVersionInfo` metadata, release-metadata JSON, Qt binding exclusion |
| **Inno Setup** | `PrivilegesRequired=lowest` per-user install; LZMA solid compression; seeds `.env`/inputs with `onlyifdoesntexist` |

---

## 3. Architecture & Technical Highlights

### 3.1 Cooperative + forced cancellation across Python, Qt, and .NET

**Files:** [src/infrastructure/cancellation.py](src/infrastructure/cancellation.py), [src/ui/qt/worker.py](src/ui/qt/worker.py), [src/ui/qt/main_window.py](src/ui/qt/main_window.py), [src/data/query_executor.py](src/data/query_executor.py)

- **The problem it solves.** A `adapter.Fill()` call inside .NET can block for up to the `COMMAND_TIMEOUT` (default **600 s**), and Python can't interrupt it.
- **`CancellationController` resource registry.** The controller is built on `threading.Event` and keeps a lock-protected registry of **live `AdomdCommand` and `AdomdConnection` objects**. These are registered through the `track_command()` and `track_connection()` context managers. `request_cancel()` calls `.Cancel()` on each command and `.Close()` + `.Dispose()` on each connection, which aborts the query server-side from another thread.
- **Race-free registration.** The cancel flag is checked **both before and after** an object is registered, so a cancel that arrives during registration is still caught.
- **Cancellation escalation ladder.**
  1. Cancellation is a typed exception (`ExportCancelled`) checked at dozens of yield points.
  2. `CancellationController.wait()` sleeps in **200 ms slices**, so retry backoff can be interrupted.
  3. The GUI calls `ExportWorker.cancel()` → `BackgroundExportManager.cancel_all()` → `QThread.requestInterruption()`.
  4. A **3,000 ms** `QTimer.singleShot` grace period follows, then `QThread.terminate()` with a 1,000 ms wait.
- **Idempotency.** Cancel is idempotent, and `_run_outcome_logged` makes sure the UI reports exactly **one** terminal outcome per run.

### 3.2 Thread-safe background export manager (producer/consumer state machine)

**File:** [src/export/background_manager.py](src/export/background_manager.py) (332 lines)

- **Overlapped work.** While the extraction thread moves on to the next month's DAX query, Excel conversion runs on a dedicated `ThreadPoolExecutor(max_workers=1)`.
- **Explicit state machine.** States are `ACCEPTING → DRAINING → CANCELLING → STOPPED` (`StrEnum`), guarded by an `RLock`-backed `threading.Condition`.
- **Admission control.** Once draining starts, new jobs are rejected *and recorded* as failed or cancelled artifacts, never dropped silently.
- **Exactly-once terminal accounting.** `_finish_job` is idempotent (`if job.result is not None: return`). The worker's `finally` block guarantees that even a failing progress notification can't lose a job.
- **Timeouts.** `wait_for_excel_completion(timeout=300s)` marks queued and active work incomplete, then asks the worker to stop. It never claims the thread has already stopped.
- **Process-wide shutdown.** A `WeakSet` class registry plus an `atexit` hook let `shutdown_all()` / `cancel_all()` clean up from the GUI or at interpreter exit.
- **Test coverage.** These guarantees are covered by concurrency tests ([tests/test_background_accounting.py](tests/test_background_accounting.py)): slow active + queued jobs after a timeout, failures in notification handlers, and admission racing shutdown.

### 3.3 Data-integrity layer: reconciliation, stage-aware retries, and an authoritative outcome ledger

**Files:** [src/validation/validator.py](src/validation/validator.py), [src/validation/retry.py](src/validation/retry.py), [src/validation/retry_manager.py](src/validation/retry_manager.py), [src/processors/outcome.py](src/processors/outcome.py), [src/processors/pipeline.py](src/processors/pipeline.py)

- **Reconciliation.** Each month's model-side `SUM(AmountInEuro)` is compared with the exported DataFrame's sum.
  - Threshold: **0.01%**.
  - Zero tolerance: `1e-9`, with explicit handling for "model returns 0 but the export doesn't". Missing schema raises an error instead of passing as "0 = 0".
- **Error classification.** `RetryManager` sorts failures into 5 categories (`SESSION_EXPIRED`, `CONNECTION_TIMEOUT`, `MEMORY_ERROR`, `DATA_ERROR`, `UNKNOWN`), each with its own backoff:
  - session expiry: quick retry, at most 10 s
  - timeout: linear backoff, `30s × attempt`
  - memory: `60s × attempt`
- **Stage-aware recovery.** `EnhancedRetryManager` records *which stage* failed. If the export succeeded and only reconciliation failed, the retry re-reads the existing Parquet file instead of re-running an expensive DAX extraction (**validation-only retry**). The smart retry loop runs up to **3 global passes × 3 attempts per month**.
- **`RunOutcome` ledger.**
  - **Recovery replaces, never adds.** Retries never double-count rows or MB, and usable Parquet totals survive a failed reconciliation.
  - **Required deliverables affect status.** Excel files, the reconciliation report, and snapshots are tracked as *required artifacts*. Any failed deliverable downgrades the run from `COMPLETE` to `PARTIAL`. The four statuses are Complete, Partial, Failed, and Cancelled.

### 3.4 Crash-safe, atomic file publication and per-run isolation

**Files:** [src/export/workbook.py](src/export/workbook.py), [src/export/parquet.py](src/export/parquet.py), [src/infrastructure/directory_manager.py](src/infrastructure/directory_manager.py)

- **Atomic publication.** Every Parquet and Excel artifact is written to a uniquely named hidden sibling temp file (`NamedTemporaryFile(dir=dest.parent)`). The writer is closed and cancellation re-checked, and only then is the file moved into place with **`Path.replace()`, which is atomic on the same volume**. A failed or cancelled write leaves any earlier good file untouched.
- **Safe appends.** Append-mode workbooks (`preserve_existing=True`) copy the current file into the temp file first, so the published artifact is never changed in place.
- **Run-directory reservation.** Directories are reserved with **atomic `os.mkdir` and a UUID suffix on collision**, under a lock. Back-to-back runs, or two copies of the app, can't write into each other's folders.
- **Spreadsheet formula-injection defense.**
  - XlsxWriter runs with `strings_to_formulas=False` and `strings_to_urls=False`.
  - On openpyxl, cells that start with `=` are forced to `data_type = "s"`, so source text from the BI model can never run as a formula.

### 3.5 High-throughput .NET → pandas bridge with GIL yielding and Excel auto-sharding

**Files:** [src/data/processor.py](src/data/processor.py), [src/export/excel_converter.py](src/export/excel_converter.py), [src/processors/optimized_pipeline.py](src/processors/optimized_pipeline.py)

- **Typed conversion into a pre-allocated array.** The `DataTable` is copied into a pre-allocated `np.empty((rows, cols), dtype=object)` array. `DBNull` and `System.DateTime` types are cached, so .NET values map to `None` / `datetime` without repeated reflection. Rich progress updates are batched every `rows // 1000` rows.
- **GUI responsiveness.** pythonnet holds the GIL during interop. To keep the Qt event loop alive during multi-million-row conversions, the loop calls `time.sleep(0)` every `rows // 100` rows and checks for cancellation at each yield.
- **Memory hygiene.** .NET `DataTable`, `AdomdCommand`, and `AdomdDataAdapter` objects are explicitly `Dispose()`d in `finally` blocks. The month DataFrame is deleted followed by `gc.collect()`, and the rolling pipeline forces a GC every **3 months**.
- **Column-name normalization.** A small bracket-depth parser turns DAX column names like `Table[Col]` into `Table_Col`.
- **Excel sharding.** The Polars-based converter splits outputs that exceed **1,048,575 data rows** into `Data_1..N` sheets. It uses ZIP64 for files over 4 GB, strips timezones, and reports rows/sec throughput.

### 3.6 Security-minded query construction and release supply chain

**Files:** [src/data/query_executor.py](src/data/query_executor.py), [src/processors/customs_annual.py](src/processors/customs_annual.py), [scripts/release_validation.py](scripts/release_validation.py), [.github/workflows/](.github/workflows/)

- **DAX injection prevention.** User and Excel-sourced filter values (cost centers, countries, statuses, legal entities) are escaped by doubling quotes before they go into `IN { ... }` lists.
- **Safe templating.** Templates are filled with token-by-token `str.replace` instead of `str.format`, so DAX's literal `{ }` table constructors can't break or be hijacked.
- **Filesystem-safe file names.** Names built from legal entities are sanitized and suffixed with a **SHA-1 digest prefix** to avoid collisions.
- **Secrets hygiene.** Release validation **fails the build if any credential-bearing `.env` file is found in the payload**. Only `.env.example` ships, and the installer seeds it with `onlyifdoesntexist` so user configuration is never overwritten.
- **Supply chain.**
  - Pinned lockfile.
  - Bandit SAST and Safety dependency scan.
  - SHA-256 manifest for every release file.
  - Version checks that the semver fits Windows `VSVersionInfo` (components ≤ 65535) and that the built version matches the requested one.
  - Headless EXE smoke test before publishing.
- **UI hardening.** All log lines are `html.escape`d before reaching the rich-text log widget. Clickable output links open **only** if `url.isLocalFile()`.

---

## 4. Quantifiable Metrics & Scope (From the Codebase)

| Metric | Value | Source |
|---|---|---|
| Production source | **~6,230 LOC** in **44 Python modules** across 6 packages (`data`, `export`, `infrastructure`, `processors`, `ui`, `validation`) | `git ls-files src` |
| Test code | **~4,620 LOC** in **32 test modules** (test-to-source ratio ≈ **0.74**) | `tests/` |
| Classes | **48** top-level classes in `src/` | grep |
| Export modes / workflows | **4** modes (12-month rolling, single month, customs annual, GL account analysis) + optional CAT matching | [worker.py](src/ui/qt/worker.py) |
| "Endpoints" (query surface) | **0 HTTP routes**. **10 DAX queries**: 7 template files + 3 embedded snapshot queries (Total Invoiced, Total Accruals, Total Executed) | [queries/](queries/), [snapshot.py](src/processors/snapshot.py) |
| Data model touched | **9** Power BI tables, **68** distinct column references; **5** reference sheets in `Input_CAT.xlsx` | DAX grep |
| UI views | **5** navigation pages (4 modes + Settings) in a `QStackedWidget`; **3** custom Qt classes (`PowerBIExportWindow`, `MultiSelectComboBox`, `LocationCountryListsLoadWorker`) + tracker/progress adapters | [main_window.py](src/ui/qt/main_window.py) |
| Qt signals | **10** custom `pyqtSignal`s | grep |
| Automated tests | **231** test functions → **314** collected cases (33 parametrized), plus 1 opt-in live Power BI smoke test | pytest run |
| Coverage policy | **100% statement + branch coverage** enforced in CI (`fail_under = 100`, `branch = true`) | [pyproject.toml](pyproject.toml) |
| CI/CD | **2 workflows, 5 jobs** (lint, security, test, release-test, build-and-release) | `.github/workflows` |
| Reconciliation threshold | **0.01%** variance; zero tolerance `1e-9` | [validation/constants.py](src/validation/constants.py) |
| Retry policy | **3** attempts per month × **3** global retry passes; base backoff **30 s**; session-expiry retry capped at **10 s**; snapshot backoff `5 s × attempt`; GL query backoff `min(2·attempt, 10) s` | [infrastructure/constants.py](src/infrastructure/constants.py) |
| Timeouts | Connect **30 s**, command **600 s** (env-configurable); background Excel drain **300 s**; GUI cancel grace **3,000 ms** + **1,000 ms** terminate wait; country loader shutdown wait **1,500 ms** | config / UI |
| Throughput & memory controls | GIL yield every **1%** of rows; progress update every **0.1%**; `gc.collect()` every **3** months; Excel shard at **1,048,575** rows/sheet; DAX `TOPN(500000)` cap on snapshot queries | processors |
| Customs annual fan-out | `N legal entities × 12 months` jobs per run, with per-item failure isolation | [customs_annual.py](src/processors/customs_annual.py) |
| Execution history | Rolling log of the last **100** runs | [state_manager.py](src/validation/state_manager.py) |
| Project history | **69** commits; current version **2.0.3** | git |

> **Local verification note.** On this machine the suite ran **295/314 passing**. All 19 failures were Windows `MAX_PATH` (260-char) errors: the long pytest temp directory, the UUID run folder, and the temp file name together exceed the limit. This is an environment limitation (long paths not enabled), not a logic failure. CI on `windows-latest` enforces the 100% gate.

---

## 5. Ready-to-Use Resume Bullet Points (XYZ Format)

### Variation 1 — Full-Stack / General Software Engineering

1. **Independently built** a Python/PyQt6 desktop platform used daily by **7+ team members** for **at least one export per day**, across **4 business workflows**. Exports range from roughly **100 rows to over 1 million rows** and run unattended, with automatic export-failure retries. It replaces manual handling of exports that could take over an hour during cube outages or heavy usage; elapsed-time savings have not been measured.
2. **Engineered** a reconciliation check comparing exported monthly totals against a model-side aggregate within a **0.01% tolerance**, with differences reported in Excel. **No mismatches have been observed so far**; this is a validation/reporting capability, not a claim of discrepancies caught or automatically corrected.
3. **Established** a release pipeline with a **100% statement-and-branch coverage gate** across **300+ automated tests** (pytest, pytest-qt). The pipeline ships a versioned Windows installer through GitHub Actions with auto-semver, a headless EXE smoke test, Bandit/Safety scanning, and SHA-256 checksums.

### Variation 2 — Backend / Systems Architecture

4. **Designed** a thread-safe producer/consumer export manager (`ThreadPoolExecutor`, `Condition`-guarded state machine, exactly-once job accounting) that overlaps Excel conversion with DAX extraction, cutting end-to-end 12-month run time by **[X%]** (from **[A] min** to **[B] min**).
5. **Implemented** cross-runtime cancellation that aborts in-flight .NET `AdomdCommand` queries from a second thread. It reduced cancel latency from up to the **600 s** command timeout to **under [Y] s**, using a lock-protected registry of live commands and connections plus a 3-second cooperative grace period before forced thread termination.
6. **Built** a fault-tolerant retry subsystem that classifies failures into **5 error types** (session expiry, timeout, memory, data, unknown) with type-specific backoff, raising unattended run success from **[X%]** to **[Y%]** on a flaky XMLA endpoint.
7. **Hardened** data publication with atomic temp-file-plus-`replace` writes, UUID-reserved run directories, and DAX/Excel formula-injection escaping. Together these eliminated **[N] partial-file and overwrite incidents** and kept untrusted BI text from running as spreadsheet formulas.
8. **Optimized** a .NET `DataTable` → pandas bridge (pre-allocated NumPy arrays, cached type checks, GIL yields every 1% of rows, explicit `Dispose()`), processing **roughly 100 to over 1 million rows per export**, with monthly volume **[N million]** and throughput **[X] rows/s** still unmeasured while keeping the GUI responsive. Outputs larger than Excel's 1,048,576-row limit are split across sheets automatically.

### Variation 3 — Frontend / Product Engineering

9. **Delivered** a PyQt6 desktop UX with real-time progress, structured logs, clickable output folders, and one-click cancellation, used daily by **7+ team members**, with support-request reduction still **[X%]** (unmeasured). Background work runs on `QThread`/signal-slot workers so the UI never freezes during multi-minute queries.
10. **Built** a GL-account analysis workflow with multi-select country pickers (populated asynchronously from Power BI), a searchable cost-center table, status filters, and optional wildcard-aware CAT matching. It reduced audit preparation from **[A] hours to [B] minutes** while blocking invalid exports through input validation.

---

## 6. Interview Prep: Deep-Dive Talking Points

### Talking Point 1 — "How do you cancel a blocking .NET call from Python?"

**Where:** [src/infrastructure/cancellation.py](src/infrastructure/cancellation.py), [src/data/query_executor.py](src/data/query_executor.py), [src/data/connection.py](src/data/connection.py), [src/ui/qt/worker.py](src/ui/qt/worker.py), [src/ui/qt/main_window.py](src/ui/qt/main_window.py)

- **Situation.** Exports run DAX queries through ADOMD.NET. `AdomdDataAdapter.Fill()` is a blocking native call that can run for up to the 600 s command timeout. Users needed a Cancel button that actually works, but Python can't interrupt a thread stuck inside the CLR.
- **Task.** Make cancellation fast and safe without leaking connections, corrupting output files, or leaving the UI in a confusing state.
- **Action.**
  - Built a `CancellationController` that combines a `threading.Event` flag with a **lock-protected registry of live .NET commands and connections**, registered through `track_command` / `track_connection` context managers.
  - On cancel, another thread calls `AdomdCommand.Cancel()` and `Connection.Close()/Dispose()` best-effort. That makes the blocked `Fill()` throw, so the worker unwinds normally.
  - Closed the race window by re-checking the flag *after* registration.
  - Added typed `ExportCancelled` checkpoints throughout the pipeline, and made sleeps cancellable in 200 ms slices.
  - In the GUI, cancel goes through cooperative steps first (`worker.cancel()`, `BackgroundExportManager.cancel_all()`, `requestInterruption()`). Only after a **3 s `QTimer` grace period** does it fall back to `QThread.terminate()`.
  - Atomic file publication means a forced stop never leaves a half-written final artifact.
- **Result.** Cancel stops server-side work instead of waiting out the timeout. Cleanup is idempotent and the UI shows exactly one terminal outcome per run; this is covered by `test_cancellation.py` and the worker/window tests.
- **Trade-off to discuss.** `QThread.terminate()` is unsafe in general (locks may be held). It is used only as a last resort, after cooperative cancellation, and is acceptable here because every output write is atomic.

### Talking Point 2 — "How do you guarantee exported financial data is correct and complete?"

**Where:** [src/processors/month.py](src/processors/month.py), [src/validation/validator.py](src/validation/validator.py), [src/validation/retry.py](src/validation/retry.py), [src/processors/outcome.py](src/processors/outcome.py), [src/processors/pipeline.py](src/processors/pipeline.py)

- **Situation.** Finance teams used the exports for monthly close. A truncated query, a session drop partway through extraction, or a type-conversion bug could silently produce a file that looks fine but is missing rows. A successful file write doesn't prove the data is complete.
- **Task.** Prove each month's export matches the source model, recover from transient failures automatically, and report an honest overall status.
- **Action.**
  - After extraction, the pipeline runs a separate aggregate DAX query (`total_per_month_query.dax`) and compares the model total with the DataFrame sum. The tolerance is 0.01%, with an explicit 1e-9 zero band and a schema check so "missing column" can't pass as 0 = 0.
  - Failures are recorded with their **processing stage**. If the export succeeded but reconciliation failed, the **validation-only retry** re-reads the existing Parquet instead of re-running a multi-minute extraction. Other failures get a full retry with backoff based on the error type.
  - A `RunOutcome` ledger keys months by `(year, month)`, so recovery *replaces* earlier attempts instead of adding to them, and row/MB totals are never double-counted.
  - Requested deliverables (Excel files, reconciliation workbook, snapshot sheets) are *required artifacts*: if any fails, the run is `PARTIAL`, not `COMPLETE`.
  - The outcome is persisted to a rolling JSON execution log.
- **Result.** Every run produces a reconciliation report (per-month model vs. export totals) plus a machine-readable outcome. A run is never reported Complete while a requested output failed. The guarantees are covered by `test_monthly_recovery.py`, `test_combined_report.py`, and `test_pipeline_coverage.py`.
- **Trade-off to discuss.** Reconciliation costs one extra query per month. That was chosen deliberately over trusting row counts, because sums catch both missing rows and conversion errors in the money column.

### Talking Point 3 — "How did you keep a GUI responsive while moving millions of rows through .NET interop, and parallelize Excel output safely?"

**Where:** [src/data/processor.py](src/data/processor.py), [src/export/background_manager.py](src/export/background_manager.py), [src/processors/optimized_pipeline.py](src/processors/optimized_pipeline.py), [src/export/excel_converter.py](src/export/excel_converter.py), [src/ui/qt/tracker.py](src/ui/qt/tracker.py)

- **Situation.** The 12-month rolling export pulls a large `DataTable` per month. pythonnet holds the GIL while iterating .NET rows, so the PyQt6 window froze. Excel conversion of large Parquet files was slow and blocked the next month's extraction. Excel also hard-caps sheets at 1,048,576 rows.
- **Task.** Keep the UI responsive, overlap I/O-heavy conversion with extraction, and make the concurrency provably lossless.
- **Action.**
  - **UI isolation.** All work runs in a `QObject` moved to a `QThread`. Processors only talk to the UI through a `TrackerProtocol`, implemented by `QtProgressTracker` with Qt signals. A Rich-compatible `QtProgressContext` adapter means processors work unchanged in both the console and the GUI.
  - **GIL management.** The row-conversion loop writes into a pre-allocated NumPy object array, caches .NET type objects, and calls `time.sleep(0)` every 1% of rows to hand control back to the Qt event loop. Native resources are disposed explicitly, and the pipeline forces a GC every 3 months.
  - **Background Excel.** A single-worker `ThreadPoolExecutor` drains a queue under a `Condition`-guarded state machine (`ACCEPTING/DRAINING/CANCELLING/STOPPED`):
    - Admission closes before the final wait.
    - Every accepted job gets exactly one terminal result.
    - A 300 s drain timeout marks work incomplete instead of hanging.
    - One manager is owned for the whole run, including retry passes, so a timed-out worker never overlaps a replacement.
  - **Sharding.** The Polars/XlsxWriter converter splits outputs into `Data_1..N` sheets and enables ZIP64.
- **Result.** The GUI stays interactive and cancellable during long extractions, and Excel conversion of month *N* overlaps extraction of month *N+1* (**[X%]** faster wall-clock). Lossless accounting is enforced by dedicated race-condition tests: timeouts with active and queued jobs, notification-handler failures, and admission racing shutdown.
- **Trade-off to discuss.** One worker instead of a pool. Excel writing is memory-heavy and the extraction thread already holds a full month in memory, so one serialized converter limits peak RAM and keeps the ordering deterministic. That was worth giving up some parallelism for.

---

*Generated from static analysis of the `auto-exporter-bi` repository (v2.0.3) and a local test run.*