Fabric SQL Audit Logs Predicate Filtering: Cutting Compliance Noise

Audit logging usually gets switched on for one of two reasons: an external auditor asked how you would prove who read the margin table, or a group security policy landed from a European or Japanese parent and someone in IT ticked the box that made the question go away. Both are reasonable.

What happens next is the problem. Nobody reviews it. Storage climbs, the query that reads the log takes minutes to return, and the control becomes an artefact you produce once a year for the auditor.

Predicate filtering in Microsoft Fabric addresses the part of this that is genuinely a technology problem: it drops known, expected activity at capture time rather than making you filter it out afterwards. It does not decide what matters — that judgement stays yours.

SQL audit logs in Microsoft Fabric record database-level events — authentication attempts, data access and modification, schema changes, permission and role changes — for Fabric Data Warehouse, the SQL analytics endpoint and SQL database in Fabric. Fabric writes them as encrypted .XEL files to an Audit folder in the item's OneLake storage.

Three things matter before you design anything around this. It is off by default in Fabric Data Warehouse — a user with the Audit queries permission enables it, and when they do, all actions are enabled and retained for nine years. It is not the same log as the Fabric activity log, which tells you who opened a report or shared a workspace. And events are grouped, not itemised: Fabric maps friendly portal names onto SQL Server audit action groups — BATCH_COMPLETED_GROUP, SCHEMA_OBJECT_ACCESS_GROUP, DATABASE_OBJECT_CHANGE_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP and others. Access is separately controlled — Workspace Admins hold Audit queries, and a GRANT lets someone query the log with T-SQL without changing the configuration.

Predicate filtering is an optional expression Fabric evaluates before an audit event is written. Microsoft is explicit about the consequence: filtering happens before the event is written, so excluded events are not available later for retrospective investigation. That single fact governs every design decision — filtering is irreversible.

The syntax is the predicate-expression clause from CREATE SERVER AUDIT, without the WHERE keyword. The rules that matter in practice: the field can be any column returned by sys.fn_get_audit_file except file_name, audit_file_offset and event_time — so server_principal_name, database_principal_name, object_name, schema_name, statement and succeeded; action_id and class_type are strings in the output but can only be compared to numeric values inside a predicate; the maximum length is 3,000 characters; and the predicate only evaluates events an enabled action group already generates, so to filter SELECTs on the statement field, BATCH_COMPLETED_GROUP must be enabled first.

Two practitioner notes. The published grammar combines factors with AND, OR and NOT and does not show parentheses, so keep predicates flat and test compound rules rather than trusting operator precedence. And you configure it in the portal or via the REST API as predicateExpression — on updates, omitting the property leaves the existing predicate unchanged, while an empty string removes it.

A typical mid-market manufacturer has one or two warehouses serving plant, quality and supply-chain reporting, plus a SQL database in Fabric behind an operational app. The filtering decision comes down to five categories.

The sequence: run unfiltered for one week first, then group the log by principal — in the estates I have worked in, the top three or four principals account for the overwhelming majority of rows, and every one is a machine identity you already know. Exclude by identity, not by statement text (for example, NOT server_principal_name = 'svc-fabric-refresh@yourcompany.com'). Protect classified data with item design, not predicate cleverness. And review the predicate on a schedule, because a new service account added next quarter will otherwise be captured as noise or, worse, a new human reader will be filtered by an over-broad rule.

Storage is the smaller half — for SQL database in Fabric, Microsoft states that writing audit logs to OneLake does not currently incur additional charges and counts within the capacity's OneLake storage limits. The larger half is review cost: a weekly exception review an IT or security lead can complete in 15–20 minutes gets done; a log that takes minutes to query and returns millions of rows does not.

The pattern that works: filter machine identities at capture, then land a small daily exceptions table in the Gold layer — access to classified objects, permission changes, failed authentications, and human reads outside a defined working window — in front of a named reviewer. Five to fifty rows a day is a control. A million rows a week is an archive nobody opens.

This is where vendor material tends to overstate the position, so be careful. In the United Kingdom, UK GDPR does not name audit logging as a requirement — Article 5(2) requires accountability and Article 32 appropriate security measures. Saudi Arabia's PDPL requires organisational, administrative and technical measures to protect personal data; the NCA's Essential Cybersecurity Controls go further on event logging, but neither prescribes auditing every SELECT. The UAE's federal data-protection law requires demonstrable control of processing.

None of these instruments tells you to audit every SELECT. They tell you to demonstrate control and detect misuse — which a targeted, reviewed exceptions log does far better than an unfiltered firehose nobody reads.

Microsoft Purview sensitivity labels classify the item so people know what they are handling and protection policies can restrict access — Fabric supports manual, default, mandatory and programmatic labelling with downstream inheritance, though inheritance from data sources has limits worth checking. Workspace and item design is what makes a narrow, defensible predicate possible: classified data in its own item and workspace means the noisy predicate never applies to it.

Retention runs on two clocks. Fabric stores SQL audit logs inside the item's folder in OneLake and scopes them to the item lifecycle — delete the warehouse and the audit files go with it. If you need independent retention (a regulator's floor, a group policy), copy the .XEL files to a separate Lakehouse or Azure Storage account with its own retention.

Filtering is irreversible — an event excluded by predicate is never written, so an over-broad rule is a permanent blind spot. Statement-text filtering is fragile — LIKE matching against the statement field breaks on parameterised queries, comments, whitespace and casing, and cannot distinguish a routine SELECT from a sensitive one, which is why you filter by identity, not text. Auditing is best-effort under load — Microsoft states the feature is optimised for the availability of the database, and under very high activity it might allow transactions to proceed without recording every event.

And the SQL analytics endpoint has real gaps — DML such as INSERT, UPDATE, DELETE and MERGE is not captured there, because Lakehouse data manipulation happens through the Lakehouse runtime. Configuration is per item and drifts, and is not supported in the default workspace or for warehouse snapshots. Above all, a log tells you what happened, not whether it was legitimate: the finance manager reading margin data at 22:00 may be closing the month or preparing to join a competitor — the log cannot tell you which.

Four questions to answer this week, before touching a configuration screen:

• Which tables would cause a problem if a departing employee copied them — and are they in their own item today?
• If you ran auditing unfiltered for seven days, which principals would generate the top 80% of events, and are they all machine identities you can name?
• Who reviews the log, how often, and how long does that review take? If the answer is "nobody", filtering is not your first problem
• What retention are you actually required to hold — a regulator floor, an ICO expectation, a group policy number — and does your Fabric setting match it?

We build governed Microsoft Fabric estates for manufacturers across the UAE, Saudi, the UK and India, with Microsoft Purview, workspace design and audit configuration treated as part of the build rather than a retrofit — because a predicate is only as defensible as the workspace design underneath it.