OneLake Shortcuts: Query Amazon S3 Data from Fabric Without Copying It

A common situation in delegated authority, and one I know well: bordereaux are processed on AWS with Glue and land in Amazon S3, while the business reports in Power BI and is moving towards Microsoft Fabric. Other data sits in S3 too. A third-party claims administrator (TPA) drops claims bordereaux there, a bordereaux-management platform exports cleansed risk and premium data there, or a coverholder group shares its own data lake. The usual answer is a pipeline that copies it across every night. That means one more job to monitor, one more copy of policyholder data to secure, and a lag between S3 and the report.

OneLake shortcuts offer another way. A shortcut is a pointer inside a Lakehouse that makes an S3 location appear as if it were a folder or table in OneLake. No data moves until something reads it.

What a shortcut is (and isn't)

• It's a virtual reference, similar to a symbolic link. The files stay in S3.
• Any Fabric engine can read through it: Spark, the SQL analytics endpoint, and Power BI via the Lakehouse.
• For external sources like S3, shortcuts are read-only. You can't write back to S3 through a shortcut.
• Shortcuts can sit in the Files area (any format) or the Tables area (needs Delta format to be recognised as a table).

Step 1: Prepare an IAM identity in AWS

Fabric connects to S3 with an access key and secret for an IAM user. Ask the bucket owner to create a dedicated user, give it the minimum it needs, and scope it to the bucket and prefix:

 1: {
 2:   "Version": "2012-10-17",
 3:   "Statement": [
 4:     {
 5:       "Effect": "Allow",
 6:       "Action": ["s3:GetBucketLocation", "s3:ListBucket"],
 7:       "Resource": "arn:aws:s3:::tpa-claims-share"
 8:     },
 9:     {
10:       "Effect": "Allow",
11:       "Action": ["s3:GetObject"],
12:       "Resource": "arn:aws:s3:::tpa-claims-share/curated/*"
13:     }
14:   ]
15: }

Treat this key like a database password: rotate it, keep it out of notebooks, and don't reuse it for anything else. Claims data includes claimant details, so this is also one for your data protection officer to know about.

Step 2: Create the shortcut in Fabric

1. Open your Lakehouse and, in the explorer, right-click Tables (or Files) and choose New shortcut.
2. Choose Amazon S3.
3. Enter the bucket URL, for example https://tpa-claims-share.s3.eu-west-2.amazonaws.com, and create a new connection with the access key and secret.
4. Browse to the folder, for example curated/claim_transaction, give the shortcut a name and create it.

If curated/claim_transaction is a Delta table (it has a _delta_log folder) and you created the shortcut under Tables, it appears as a table right away and the SQL analytics endpoint picks it up.

Step 3: Query it

From a notebook:

1: claims = spark.read.table("claim_transaction")          # Delta shortcut under Tables
2: claims.groupBy("claim_status").count().show()
3: 
4: # Raw monthly claims bordereaux (CSV) via a Files shortcut
5: raw = (spark.read.option("header", True)
6:          .csv("Files/s3_tpa_bordereaux/2024/01/*.csv"))

From the SQL analytics endpoint, joining the S3 claims data to the contract and coverholder dimensions, which live natively in OneLake:

 1: SELECT  ch.coverholder_name,
 2:         cs.umr,
 3:         cs.section_no,
 4:         cs.year_of_account,
 5:         SUM(c.paid_this_month_gbp)  AS paid_gbp,
 6:         SUM(c.outstanding_gbp)      AS outstanding_gbp,
 7:         SUM(c.paid_this_month_gbp + c.outstanding_gbp) AS incurred_gbp
 8: FROM    dbo.claim_transaction    AS c      -- shortcut to S3
 9: JOIN    dbo.dim_contract_section AS cs ON cs.umr = c.umr AND cs.section_no = c.section_no
10: JOIN    dbo.dim_coverholder      AS ch ON ch.coverholder_key = cs.coverholder_key
11: WHERE   c.bordereau_month = '2024-01-01'
12: GROUP   BY ch.coverholder_name, cs.umr, cs.section_no, cs.year_of_account;

From Power BI, the table shows up in the Lakehouse's semantic model like any other, so the claims view can sit next to the premium view without anyone copying files around.

What if the S3 data isn't Delta?

Most TPA and coverholder data in S3 is plain CSV, Excel converted to CSV, or Parquet. Put the shortcut under Files, then decide:

• Read it on the fly in Spark when the data is small or used rarely, such as a one-off review of a coverholder's historical claims.
• Convert it to Delta in a Lakehouse table when it's queried often. This is a copy, but a cheap and simple one, and you get Delta's speed, V-Order and Direct Lake support.

1: (spark.read.parquet("Files/s3_bdx_platform/risk_transaction/")
2:       .write.mode("overwrite")
3:       .format("delta")
4:       .saveAsTable("risk_transaction"))

Things to watch

• AWS egress charges. Every read through the shortcut pulls data out of AWS, and AWS bills for data transfer out. If the bucket belongs to a TPA, agree upfront who pays. A Power BI report refreshing every 15 minutes against a large S3 table can get expensive.
• Latency. Reading across clouds is slower than reading native OneLake data. For heavily used tables, materialising them in OneLake is usually worth it.
• Security goes through the connection. Anyone with access to the Lakehouse can read what the shortcut's IAM user can read. If the bucket holds several coverholders' data, design the IAM policy and workspace permissions together so people only see what they should.
• Delta versions. If the Delta table in S3 was written with newer table features than Fabric's readers support, reads can fail. Agree conservative writer settings with whoever produces the data.
• Region. Use the correct regional bucket URL. A wrong region shows up as a confusing access error, not a clear one.

When to use shortcuts, and when not to

S3 shortcuts suit exploration, reference data, and data owned by another party, such as a TPA's claims feed that you don't want to copy and become responsible for. For core premium and claims facts that drive month-end reporting and Lloyd's returns, landing a copy in OneLake is still the safer choice. That isn't because shortcuts don't work. It's because the cost, latency and audit trail are more predictable.

Shortcuts change the default question from "how do I copy this?" to "do I need to copy this at all?" Often the answer is no.