ADF Logging and Auditing for Compliance

An auditor does not ask whether your pipelines work. They ask whether you can prove what happened: on a date eleven months ago, did this load run, who had changed it beforehand, which files did it move, and who could have altered the result. Azure Data Factory can answer all four, on one condition. Most of the evidence exists only if you configured it before the event. ADF logging and auditing is largely a matter of switching things on early.

Four questions, and where the answers live

The third column is the reason for this article.

What ran: run records

Data Factory stores pipeline run data for only 45 days (Microsoft Learn: monitor Azure Data Factory). Resource logs, the detailed records of pipeline, activity and trigger runs, are not collected and stored anywhere else until you create a diagnostic setting.

A diagnostic setting sends them to one or more of:

• A Log Analytics workspace, for querying. Retention is set on the workspace.
• A storage account, the economical choice for multi-year retention. Apply an immutability policy if the records must be tamper-evident.
• An event hub, to feed a security information system.

For audit purposes, send to both a workspace and storage: one to answer questions quickly, the other to keep. Create the setting when the factory is created. There is nothing to recover for the period before it existed. Monitoring and alerts covers the tables and queries.

Two choices affect what the record contains. Debug runs have their own log categories, separate from triggered runs. And a run record includes the parameters the pipeline was started with, so do not pass secrets as plain parameters.

Who changed it: two records

The Azure activity log records operations on Azure resources as seen from outside: a factory created, a pipeline written or deleted, a role assigned, a diagnostic setting removed. Azure retains these events for 90 days and then deletes them, and the activity log is the only place that stores who created a resource (Microsoft Learn: activity log). Export it to the same workspace and storage.

It tells you that a pipeline was changed and by whom. It does not tell you what the change was.

Git history does. Without Git, Microsoft notes, the service has no repository for your changes: Publish sends them straight to the live factory (Microsoft Learn: source control). With a repository configured, every change is a commit with an author, and with pull requests, an approver. Microsoft lists the ability to track and audit changes, and to revert them, first among the benefits.

Three details matter to an auditor:

• Changes made by PowerShell or an SDK bypass Git. They are published directly and are not entered into the repository. Restrict who can do that, and watch the activity log for it.
• Secrets are not stored in Git. The service keeps them out by design.
• Repository permissions are separate from factory permissions. A user with the Reader role and repository access can commit changes and cannot publish them (Microsoft Learn: roles and permissions). That is the basis of separation of duties: many can propose, few can release.

For production, the stronger arrangement is that nobody publishes by hand at all. Changes reach test and production only through a release pipeline, whose own history shows who approved each deployment. CI/CD for Azure Data Factory covers it.

What data moved

The run record says a Copy activity succeeded and gives totals: rows read, rows copied, rows skipped, files read and written. For many audits that is enough, provided it is retained.

When you need to show which files were copied, or prove that what arrived is what left, two settings exist. Both are off by default.

Data consistency verification. With it enabled, the copy checks each binary file's size, last-modified date and checksum between source and destination. For tabular data it checks that rows read equal rows copied plus rows skipped. It affects performance, and it is not supported with staged copy (Microsoft Learn: data consistency verification).

The session log. The copy writes a log file to storage you nominate, recording copied files, skipped files and skipped rows (Microsoft Learn: session log).

{
  "name": "CopyStatementsWithAudit",
  "type": "Copy",
  "typeProperties": {
    "source": { "type": "BinarySource", "storeSettings": { "type": "AzureBlobFSReadSettings", "recursive": true } },
    "sink": { "type": "BinarySink", "storeSettings": { "type": "AzureBlobFSWriteSettings" } },
    "validateDataConsistency": true,
    "skipErrorFile": { "dataInconsistency": true },
    "logSettings": {
      "enableCopyActivityLog": true,
      "copyActivityLogSettings": { "logLevel": "Info", "enableReliableLogging": true },
      "logLocationSettings": {
        "linkedServiceName": { "referenceName": "AuditLogStorage", "type": "LinkedServiceReference" },
        "path": "sessionlog/"
      }
    }
  }
}

Two settings in that block are audit decisions:

• Log level. The default, Warning, records only what was skipped. Info records every copied file as well. An audit trail of what moved needs Info.
• Reliable logging. Off by default: records are flushed in batches, and Microsoft warns that the last batch may be missing if the copy fails. On, each file's record is written immediately, at a cost in throughput, because every file means a second write.

Reading a session log

The log is a CSV file with five columns. The script below loads the example log printed in Microsoft's documentation into SQLite and asks the questions an audit would. The six events are Microsoft's example, not a copy run here.

import csv, json, sqlite3

# sessionlog.csv holds the example log printed in Microsoft's session log documentation: a header and six events
with open("sessionlog.csv", newline="", encoding="utf-8") as f:
    header, *events = csv.reader(f, skipinitialspace=True)
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE SessionLog (Timestamp TEXT, Level TEXT, OperationName TEXT, OperationItem TEXT, Message TEXT)")
db.executemany("INSERT INTO SessionLog VALUES (?, ?, ?, ?, ?)", events)
print("columns:", ", ".join(h.strip() for h in header), "| events:", len(events))

copied = [r[0] for r in db.execute(
    "SELECT OperationItem FROM SessionLog WHERE Message LIKE '%File is successfully copied%'")]
print("\ncopied: ", copied)

for item, message in db.execute("SELECT OperationItem, Message FROM SessionLog WHERE OperationName = 'FileSkip'"):
    print("skipped:", [item], "reason:", message.split("ErrorCode=")[1].split(",")[0])

print("\nwhat the source looked like when it was read:")
for item, message in db.execute("""SELECT OperationItem, Message FROM SessionLog
                                   WHERE OperationName = 'FileRead' AND Message LIKE 'Start to read file:%'"""):
    meta = json.loads(message.split("Start to read file: ")[1])
    print(f"  {item}  size {meta['Size']:,} bytes  last modified {meta['LastModified']}  MD5 {meta['ContentMD5']}")

print("\nseconds from first to last event, per file:")
for item, seconds in db.execute("""
        SELECT OperationItem,
               CAST(ROUND((julianday(substr(MAX(Timestamp), 1, 23)) - julianday(substr(MIN(Timestamp), 1, 23))) * 86400) AS INTEGER)
        FROM SessionLog GROUP BY OperationItem ORDER BY OperationItem"""):
    print(f"  {item}  {seconds}")

columns: Timestamp, Level, OperationName, OperationItem, Message | events: 6

copied:  ['sample2.csv']
skipped: ['sample1.csv'] reason: DataConsistencySourceDataChanged

what the source looked like when it was read:
  sample1.csv  size 104,857,620 bytes  last modified 2020-10-19T08:22:31Z  MD5 dGKVP8BVIy6AoTtKnt+aYQ==
  sample2.csv  size 104,857,620 bytes  last modified 2020-10-19T08:22:31Z  MD5 dGKVP8BVIy6AoTtKnt+aYQ==

seconds from first to last event, per file:
  sample1.csv  43
  sample2.csv  315

Four things an auditor can take from those few lines:

• One file was copied and one was not, and the log names both.
• The reason for the skip is recorded: the source file was changed by another client during the copy. Consistency verification caught it; without it, a half-changed file would have been delivered as a success.
• The state of each source file at the moment it was read is in the log: size, last-modified time and MD5. That is evidence of what was copied, even if the source file is later replaced.
• Timing per file falls out of the timestamps.

Who had access

Roles. Access to a factory is governed by Azure role-based access control. The Data Factory Contributor role allows creating, editing and deleting factories and their child resources; the Reader role allows viewing and monitoring. Custom roles can narrow this further, for example to monitoring only. Role assignments, and changes to them, appear in the activity log.

How the factory itself authenticates. A linked service that uses the factory's managed identity has no password to leak or share. Where a secret is unavoidable, it belongs in Azure Key Vault, which keeps its own access log. Azure Key Vault with Data Factory covers the set-up.

Policies that enforce it. Azure Policy has built-in definitions for Data Factory that turn good intentions into checks (Microsoft Learn: built-in policy definitions):

The last one closes the gap this article opened with: a policy that deploys the diagnostic setting means no factory can exist without its logs being kept. The Git policy's description is worth quoting for its instruction that only the development factory has Git integration, and that test and production are changed through CI/CD.

What the service itself holds

Two statements from Microsoft's security documentation are commonly requested in audits (Microsoft Learn: security considerations):

• Data Factory does not store temporary data, cache data or logs, except for linked service credentials for cloud data stores, which are encrypted.
• Certifications listed for the service include ISO 27001, SOC 1, 2 and 3, HIPAA BAA and HITRUST, with a pointer to Azure's compliance offerings for the current list.

A certified service does not make a pipeline compliant. The certification covers Microsoft's operation of the platform. Retention, access and change control on your factory remain yours to configure and to evidence.

Your own audit table

Platform logs describe activities. An auditor's question is usually about business objects: was the customer file for 30 September loaded, and how many records? A small table written by the pipeline answers that directly: batch, object, run ID, rows read and written, status. Building a metadata framework shows one. Keep the pipeline run ID in each row, so a business-level record can be traced to the platform's record of the same run.

For where data came from and went, a factory connected to Microsoft Purview can push lineage for its pipelines (Microsoft Learn: connect to Microsoft Purview). Data governance basics explains why lineage matters.

An audit-readiness checklist

1. Diagnostic setting on every factory, to a workspace and to storage, enforced by policy.
2. Activity log exported, with retention matching the run logs.
3. Retention periods written down and matched to the regulation that applies.
4. Git on the development factory; test and production changed only by release pipeline.
5. No direct publishing or SDK changes in production outside a documented emergency process.
6. Secrets in Key Vault; managed identity wherever supported.
7. Session log at Info level, and consistency verification, on copies where file-level evidence is needed.
8. An audit table with business keys and run IDs.
9. Role assignments reviewed on a schedule, with the review itself recorded.

Azure Data Factory and data governance are covered in the Data Engineering course.

Sources

• Monitor Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Activity Log in Azure Monitor (Microsoft Learn) — Microsoft Learn
• Session log in a Copy activity (Microsoft Learn) — Microsoft Learn
• Data consistency verification in copy activity (Microsoft Learn) — Microsoft Learn
• Source control in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Roles and permissions for Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Azure Policy built-in policy definitions for Data Factory (Microsoft Learn) — Microsoft Learn
• Security considerations for data movement in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Connect a Data Factory to Microsoft Purview (Microsoft Learn) — Microsoft Learn