Technical Deep Dive: Bee Haven Azure Pipeline
The identity saga in full, the Aug 12 rebuild, and the two notebook bugs it fixed along the way
Result: ✅ Both resolved. Root-caused a two-identity RBAC gap on Aug 11, then rebuilt the pipeline from scratch in a new container on Aug 12 to fix the structural issue and complete the Silver → Gold layer.
Aug 11 — the identity saga, root-caused
The recurring symptom for the better part of two weeks: a notebook triggered manually in Synapse Studio worked fine. The exact same notebook, triggered by an ADF pipeline, failed every time with AuthorizationPermissionMismatch.
| Identity | Used for | Required role | Where to grant it |
|---|---|---|---|
| Your own user account | Manual testing in Synapse Studio | Owner (usually already true) | No action needed — this is why manual runs "just work" |
| Synapse Workspace Managed Identity | Letting Data Factory open a Spark session / read the workspace | Synapse Contributor | Synapse Studio → Manage → Access control |
| Data Factory's own Managed Identity | Actual notebook code execution when triggered by ADF | Storage Blob Data Contributor | Storage account → IAM → Add role assignment → search by the ADF resource's name under "Managed identity," not "User" |
The Data Factory identity specifically had never been granted the storage role directly — granting it to the Synapse workspace's identity did nothing for Data Factory's own identity, since they're separate principals with separate permission sets. This diagnosis came from a colleague, Andreas, whose notes identified the exact pattern after reviewing the project — including flagging the "Spark job stuck in Queued" quota symptom that came up again the same day, resolved by clearing the blocking session.
Confirmed fixed under the actual ADF-triggered execution context, not just manual Synapse Studio runs — bronze_to_silver_synapse succeeded end-to-end, 6m10s total, with weather integration (Bright Sky API, 29 monthly chunks covering the full 2017–2019 range) folded into the same notebook run for the first time.
Aug 12 — the rebuild
A structural mistake surfaced: the original folder diagram had been "just an idea, not final," and the actual intended structure differed in two ways from what had been built:
silver/processing/flipped meaning — from "post-cleaning staging" to "pre-cleaning landing zone." Raw CSVs now copy here first; the Synapse notebook reads from this folder, not directly frombronze/new.- Each sensor type and weather now gets its own final Silver subfolder (
silver/flow/,silver/weather/, etc.) instead of everything landing in one shared folder.
Rebuilt from scratch in a new container (lakecontainer1) rather than patching the existing structure in place, to avoid half-migrated state. New ADF pipelines: LC1_copy_to_bronze_new (raw copy) and LC1_bronze_to_processing (Get Metadata → ForEach → copy to staging, archive with timestamp, delete from bronze/new) — the same pattern as the original move pipeline, retargeted to the corrected layout.
Notebook bug — an archiving cell that would have silently emptied staging
The old in-notebook archiving cell used mssparkutils.fs.mv directly on DATA_DIR. Once DATA_DIR was repointed to silver/processing (the new pre-cleaning landing zone that ADF also writes to and reads from), that same move call would have silently emptied silver/processing after every run — a real bug that hadn't yet bitten because archiving used to happen elsewhere. Fixed by removing the cell entirely: archiving is now ADF's job (LC1_bronze_to_processing), not the notebook's.
New notebook: LC1_synapse_silver_gold
Loads silver/flow/flow.parquet and silver/weather/weather.json, pivots flow into flow_arrival/flow_departure columns, joins with weather, saves to gold/flow_weather.parquet.
Deliberate precision tradeoff, documented not ignored: weather data is hourly, flow data is per-minute. Flow timestamps are floored to the nearest hour before joining, so multiple flow readings within the same hour share one weather snapshot rather than each having precisely-matched conditions. Per the lesson's explicit guidance, exam-prep priority over analytical precision here.
Two bugs hit and fixed while building the Gold join
1. JSON read failure — spark.read.json() defaults to expecting JSON Lines (one object per line), but the Silver weather file was saved as a pretty-printed JSON array (pandas.to_json(indent=2)). Fixed by reading with .option("multiLine", "true").
2. Merge failure on mismatched timestamp types — one side of the join was timezone-naive (datetime64[us]), the other timezone-aware (datetime64[ns, UTC]). Fixed by forcing both sides to pd.to_datetime(..., utc=True) before flooring to the hour.
✅ Saved gold table → abfss://lakecontainer1@lake00bee.dfs.core.windows.net/gold/flow_weather.parquetFinal result
Full chain proven working end-to-end via the actual ADF pipeline at least once: LC1_copy_to_bronze_new → LC1_bronze_to_processing → bronze_to_silver_synapse, plus the new Gold notebook run manually to completion.
Left for the next session (and where the trigger bug entered)
- Chain all four stages into a
daily-processingorchestrator via Execute Pipeline activities, each with "Wait on completion" checked - Build the scheduled trigger, but per prior discussion, don't spend time trying to prove it fires live given the well-documented, long-standing ADF trigger bug — manual "Trigger now" / Debug is the reliable way to demonstrate the pipeline works
- Final validation with fresh data from the lesson
- Optional bonus challenges (automated daily weather, filename validation, Azure Functions, RBAC scenarios, star/snowflake schema) — explicitly optional, skipped for time
Full story: project overview. Compare against the open-source parallel build.