Articles

SSAS Tabular Interview Questions: Partitions, Processing, and Deployment Safety

Practice SSAS Tabular interview questions with a partition gap and overlap, TMSL processing choices, deployment safeguards, and row-count reconciliation.

An SSAS Tabular partition is not just a faster way to refresh a large table. It is a separate query definition whose rows become part of the same logical table. If adjacent definitions overlap, the model may count rows twice; if they leave a gap, it can omit a period altogether. A strong interview answer checks those boundaries before choosing a processing command or deploying metadata.

This guide uses an original four-partition incident to practice that answer. Evidence boundary: Microsoft Learn is the source for SSAS Tabular partition, TMSL refresh, and deployment behavior. The dates, counts, recovery sequence, and suggested interview rubric below are editorial exercises. No candidate report is used to claim a particular employer asks these questions.

What does a partition actually isolate?

Microsoft describes a tabular partition as a logical segment of a table, populated by a Power Query M expression or SQL query. By default a table has one partition. An administrator can process a deployed partition independently and manage it through SSMS, TMSL, or the Tabular Object Model. This is useful when a recent slice changes often while older slices are stable, but the partition name is only a label: the query expression defines which source rows enter the model.

That distinction is the first interview test. Naming a partition 2026-03 does not constrain it to March. A copied partition that still contains the February predicate can import February rows again. Microsoft's partition guidance explicitly warns that the copied or new expression must select a unique portion of data. The safe answer states the effective predicate, inclusivity of both endpoints, time zone, source column type, and source snapshot used for validation.

Keep SSAS administration distinct from Power BI incremental-refresh policy. Both can result in partitioned semantic models, but this exercise assumes manually managed SSAS Tabular partitions and explicit TMSL commands. Do not silently apply a Power BI policy's rolling-window behavior to a hand-built SSAS model. Check the deployment target and compatibility level before selecting a script; Microsoft's TMSL documentation applies to SQL Server 2016 and later tabular models, while older compatibility levels can require a different scripting surface.

Worked incident: find the overlap and the gap

Suppose FactOrders contains one row per order. The intended coverage is January 1 through May 1, 2026, expressed as a half-open interval [2026-01-01, 2026-05-01). The deployed partition manifest is:

Partition	Effective `OrderDate` predicate	Expected role
`orders_jan`	`>= 2026-01-01 AND < 2026-02-01`	January
`orders_feb`	`>= 2026-02-01 AND < 2026-03-02`	February
`orders_mar`	`>= 2026-03-01 AND < 2026-04-01`	March
`orders_apr`	`>= 2026-04-02 AND < 2026-05-01`	April

The names look normal, but February and March both select March 1. April 1 belongs to neither March nor April. Half-open intervals make the review mechanical: for ordered partitions, the prior upper bound must equal the next lower bound. The February upper bound 03-02 exceeds March's lower bound 03-01; March's upper bound 04-01 precedes April's lower bound 04-02. The repair is to end February at March 1 and begin April at April 1, after confirming that the intended business calendar and data type actually use those dates.

If OrderDate is a timestamp, do not “fix” this with BETWEEN '2026-03-01' AND '2026-03-31'. Depending on type conversion, that upper literal can omit almost all of March 31. Use >= start AND < next_start with correctly typed parameters. If the source stores UTC but reporting groups by a local business date, define which transformation determines partition membership and reconcile against that transformed key. A daylight-saving boundary can make a naive local-midnight conversion surprising. These are design decisions to state, not numbers to guess.

The manifest alone proves the predicates overlap and leave a gap. It does not prove how many rows are duplicated or missing; the source may have zero March 1 or April 1 orders. Query the source for those slices and compare model results at the same grain. If March 1 has 120 source order IDs and April 1 has 95, a fully processed copy of this faulty manifest would logically double count the first group and omit the second. Those counts are hypothetical for the exercise. Before claiming actual impact, inspect processing state, filters inside M or SQL, and data changes since the last refresh.

Which processing command would you choose?

Microsoft's TMSL refresh command accepts an object scope—database, table, or partition—and a refresh type. full refreshes data and recalculates dependents; dataOnly refreshes data but clears affected dependents without recalculating them; calculate recalculates where needed. automatic acts when an object needs refresh or recalculation. An interviewer may tempt you to say “process only the broken partition” without noticing that two partition definitions are wrong and dependent calculations may need rebuilding.

A minimal example for one partition, after its query definition has been corrected and reviewed, is:

{
  "refresh": {
    "type": "full",
    "objects": [{
      "database": "SalesTabular",
      "table": "FactOrders",
      "partition": "orders_feb"
    }]
  }
}

This is an illustrative TMSL command, not a universally safe production runbook. The exercise's corrected February and April definitions both need data refresh; March may also require a targeted check because March 1 was previously duplicated by February, while the partition-specific impact depends on actual processing state. full at partition scope is easier to defend for a bounded repair because Microsoft documents the data-and-dependent recalculation behavior. A two-step dataOnly plus calculate pattern can be useful when deliberately batching work, but skipping the calculate stage is not equivalent to a completed refresh. Document the chosen object list and processing order for the actual model.

Avoid treating add as a generic incremental repair. Microsoft's reference defines it as an append to a regular partition; appending corrected source rows into a partition that already contains bad rows does not necessarily remove the old contents. Likewise, clearValues is destructive to object values and dependents; it is not a quick synonym for “refresh.” refresh normally runs parallel operations unless throttled with TMSL sequence, so “process four partitions” also raises memory and source-load questions. If the server is capacity constrained, choose a staged sequence and monitor it rather than assuming maximum parallelism is faster.

An interview answer should say how it will observe success. Capture the exact command, engine error if any, processing start/end, partition state and row counts, and a small set of report-level aggregates. A successful command response alone does not establish that the predicates form a complete non-overlapping time range or that model totals match the source. Microsoft's reference says a successful refresh response is empty; separately verify the result.

What can deployment change, and how do you protect production?

Deploying from Visual Studio sends model metadata to the configured server and database. Microsoft's deployment documentation describes Default, Do Not Process, and Full processing options. Do Not Process publishes metadata only, which can leave an object requiring subsequent processing; Full performs full processing; Default lets Analysis Services determine what needs processing. Transactional deployment is a separate property and its documented default is false. The answer should therefore identify the target, processing option, and transaction setting before a deployment, rather than saying “deploy is safe because it is metadata only.”

There is another trap: operational changes made directly to a deployed model can diverge from the source project. Microsoft's partition documentation says partitions in the authoring model are duplicated into the deployed model, and administrators can then manage deployed partitions separately. If a deployment replaces or changes deployed metadata, an unrecorded production-only partition fix may be lost or conflicted. Inventory the live model and source-control the intended partition definitions before redeploying. Use a model comparison or staging deployment to see the exact metadata changes. The decision is version and tool dependent; do not promise a preservation behavior without examining the actual deployment diff.

For the exercise, I would freeze the current manifest and source snapshot, record a restoration path for the metadata, and prepare corrected predicates in version control. I would test the deployment and refresh on staging with a representative data slice, compare the deployed partition list to the intended manifest, then perform a controlled production change. The cutover would be gated by row counts and key aggregates, not merely an SSMS success toast. If production reports cannot tolerate an intermediate unprocessed state, arrange the deployment/processing transaction or a separate-model cutover according to the platform and operational constraints, and test that rollback path beforehand.

Rollback needs a precise object. Reverting only metadata after processing may not restore the prior data snapshot; rerunning a prior script against a source that has changed can produce a new result. Preserve the prior model definition and know whether a server backup, scripted restoration, or a previous model endpoint is the operational recovery mechanism. If the source is mutable, pin or record an extraction watermark. State the time and data consistency the rollback is expected to provide.

Reconcile the repair at the right grain

Use two independent checks. First, validate the manifest: every partition's effective lower and upper bounds, no overlaps, no gaps, correct filter column and time zone, and a deliberate end boundary for the current period. This is a metadata-level proof. Second, validate the data: compare source distinct order IDs and row counts by business date to the processed model, especially March 1 and April 1. A grand total alone can hide one missing day and one duplicated day if their counts happen to match.

One practical query shape is to group source orders by CAST(OrderDate AS date) for the affected range, then compare that series with a model measure grouped by the same date key. Count rows and distinct order IDs separately: if two partitions load the same order ID, distinct count can appear right while row count or additive revenue is wrong. Check a monetary aggregate as well, but define whether the source and model apply the same currency and cancellation logic. A reconciliation that compares different semantics creates noise rather than confidence.

Refresh timing matters. Comparing today's source with yesterday's processed model can falsely suggest a partition error. Hold a consistent source snapshot or record extraction cutoffs. Test the edge dates and sample IDs on both sides of each boundary. Then reopen dependent reports and verify date filters and totals.

For an interview, narrate the evidence in order: “I found one overlapping day and one uncovered day in the definitions; I would measure affected source rows; I would correct the predicates, process the affected partitions with dependent calculations, then reconcile by day and key before exposing the new model.” This is stronger than memorizing every processing option because it ties the command to a falsifiable outcome.

Follow-up questions interviewers can ask

Why partition at all? Independent processing can limit the work needed for changing slices and help manage large tables. It adds metadata and operational complexity; if a table is small and fully refreshed cheaply, one partition may be easier to operate. Partitioning does not automatically improve every query, and a bad predicate can corrupt totals.

What if a merge succeeded but the next refresh lost data? Microsoft's guidance says merging does not update the resulting partition's query metadata. Inspect and revise the merged partition's M or SQL expression so a future process covers the complete merged range. A successful merge now is not proof that the next process will reproduce the same rows.

Does DirectQuery behave the same way? No. The TMSL partitions reference identifies restrictions for DirectQuery, including one partition per table. This exercise assumes imported data. Name the storage mode and compatibility level before transferring an import-processing plan to another model.

Would you process the entire table to be safe? Sometimes, if multiple partitions or relationships are uncertain and capacity allows. But “full” is not automatically the safest operational choice when it can create long unavailability or large source load. First determine the affected objects, dependencies, transaction behavior, service window, and restore option; then choose the smallest scope that can be validated confidently.

How do you answer under time pressure? Draw the half-open ranges, mark every boundary, pick the exact object scope and refresh type, then state one source-to-model reconciliation and one rollback condition. If you cannot remember a TMSL property name, explain the intended state transition and say you would verify syntax against Microsoft Learn before running it.