Saving Marketo Bulk Export CSV to Lakehouse using Fabric Pipeline
Hi Fabric Community, We are building a process in Microsoft Fabric Pipeline to retrieve Marketo Activity Logs using the Marketo Bulk Export Activities API and store them in a Fabric Lakehouse. We would like to avoid using a Notebook where possible and implement the process primarily with standard Fabric Pipeline activities such as Web Activity and Copy Activity. Currently, we call the Marketo Bulk Export Activities API in the following sequence, and we are encountering an issue at step 4. Create Export Activity Job → Successfully called directly from a Web Activity in Fabric Pipeline. Enqueue Export Activity Job → Successfully called directly from a Web Activity. Get Export Activity Job Status → Successfully called repeatedly from a Web Activity until the job reaches the Completed status. Get Export Activity File → When we call the API directly from a Web Activity in Fabric Pipeline, we have difficulty extracting the CSV text from the returned data and handling it so that it can be saved as a file or into a database. Therefore, we have currently implemented step 4 using a Notebook. The Pipeline calls the Notebook, which retrieves the export file and saves the CSV. This approach is working successfully. Our current implementation is: Fabric Pipeline Web Activity: Create Export Activity Job Web Activity: Enqueue Export Activity Job Web Activity: Get Export Activity Job Status Notebook: Get Export Activity File → retrieve/save CSV Bronze Lakehouse: Raw CSV Although this implementation works, for maintainability and operational simplicity, we would prefer to avoid the Notebook if possible and implement the process using only standard Pipeline activities, for example: Fabric Pipeline Web Activities: Create Job / Enqueue / Poll Status Copy Activity, etc.: Download and save Export File Bronze Lakehouse: Raw CSV Could you please advise on the following? Is there a recommended way to retrieve the CSV data from the HTTP response of Marketo's Get Export Activity File endpoint and save it directly as a CSV file in Lakehouse Files? If implementing this entirely with Pipeline activities is not practical, would the current approach of using a Notebook only for the file retrieval/storage step be considered appropriate? Our intended architecture is to first store the Activity Log as a raw CSV file in the Bronze Lakehouse, and then transform it into a Delta Table in the Silver layer. If anyone has implemented a similar API integration using Fabric Pipeline, examples or configuration details would be greatly appreciated. Thank you!
Microsoft Fabric Airflow DAG Queued for 30 Minutes on Starter Pool
Hi everyone, I am trying to run a simple data ingestion DAG using Apache Airflow Jobs in Microsoft Fabric. The purpose of the DAG is to import data into a Microsoft Fabric Lakehouse. The DAG is intentionally simple, and I am currently trying to understand an issue related to DAG execution and queuing. Environment: Orchestration: Apache Airflow Job in Fabric Target: Microsoft Fabric Lakehouse Schedule: Manual (schedule=None) Authentication: OneLake token through an environment variable Compute/Pool: Starter Pool Issue: When I manually trigger the DAG, the DAG run remains in the Queued state for approximately 30 minutes. During this time, the task does not appear to start executing. After approximately 30 minutes, the DAG run eventually changes to Failed.From Odoo & personal gateways to a Fabric Lakehouse: Medallion + Direct Lake, and F64 down to F16
We recently modernized the Power BI estate of a manufacturing client running Odoo while working on a project at DataToBiz Before: Gen1 dataflows pulling Odoo over XML-RPC, Python scripts running through personal gateways, and many reports nobody opened. All of it ran on an F64 capacity with slow refreshes (trial version) What we did (in about 60 working days): Built everything in a separate dev workspace on a small F8 capacity, so production was never touched Used report-usage metrics to pick only the reports worth migrating Moved Gen1 → Dataflows Gen2, and switched Odoo XML-RPC → JSON-RPC Moved the Python scripts into Fabric notebooks and removed the personal gateways Bronze/Silver/Gold medallion into a Fabric Warehouse, then one certified Direct Lake semantic model Rebound the Sales, Marketing and Production reports, ran UAT against the old workspace, then deleted the old content Result: Production capacity went from F64 to F16 with stable CU usage. Estimated saving is about $75K per year. Refresh time went up significantly. Question for the community: 1. When you downsize a capacity, how much CU headroom do you keep for spikes like month-end or bulk refreshes? 2. We used 75% peak utilisation on the Capacity Metrics app as our cut-off. Curious what thresholds others trust.Upsert implementation in Fabric Lakehouse
Hi Fabric Community, We are building a data platform in Microsoft Fabric using Salesforce and Marketo data, following the Medallion Architecture (Bronze / Silver / Gold). We are currently considering whether to use Replace or Upsert when writing data from Bronze to Silver and from Silver to Gold. Our current assumptions are: Bronze → Silver Salesforce Lead / Contact / Account / Opportunity, etc.: Upsert, because existing records can be updated Marketo Person, etc.: Upsert, because existing records can be updated Marketo Activity: Deduplicate and Append, since it is event/log data Silver → Gold Silver data is transformed and joined to create Gold tables optimized for AI use. Dataflow Gen2 with Replace is one option, but we are concerned about processing time and Capacity Unit (CU) consumption as the data volume grows in the future. My understanding from Microsoft Learn is that Dataflow Gen2 supports Replace / Append when writing to a Lakehouse, while Pipeline Copy Activity supports Upsert to a Lakehouse Table and allows key columns to be configured through the UI. Therefore, we would like to avoid using Notebook / Spark Delta MERGE where possible and are considering a low-code/no-code approach where Dataflow Gen2 handles transformations and Pipeline / Copy Activity handles the Upsert into the Lakehouse table. For example: Bronze → Silver Bronze → Dataflow Gen2 (filter/transform changed records) → temporary staging table → Copy Activity (Upsert) → Silver Silver → Gold Silver → Dataflow Gen2 (Gold transformations) → temporary staging table → Copy Activity (Upsert) → Gold However, we do not assume that a staging table is necessarily required. If there is a simpler no-code/low-code pattern for taking the output of Dataflow Gen2 and Upserting it with Copy Activity, we would prefer that approach. I would appreciate your advice on the following questions: Is using Dataflow Gen2 for transformation and Pipeline / Copy Activity for Upsert into a Lakehouse table a recommended pattern in Fabric? We are considering using a temporary staging table between them, but is there a simpler approach? Also, are there any important limitations or considerations compared with using Notebook / Delta MERGE? When the number of changed records per day is small compared with the total table size, can we generally expect Copy Activity Upsert to consume less processing time and fewer CUs than a full Replace? Since Upsert also has costs for key matching and updating Delta files, are there any guidelines or practical thresholds for choosing between Replace and Upsert based on table size or percentage of changed records? Would you also recommend the same Upsert approach for Silver → Gold? If a Gold table is created by joining multiple Silver tables, a change to one Silver record may affect multiple Gold rows. In such cases, is it generally better to Replace the Gold table rather than identify all affected rows and Upsert them? If the goal is to prioritize no-code/low-code implementation while also considering future data growth and CU consumption, which write method and Fabric feature would you recommend for Bronze → Silver and Silver → Gold? Any best practices or real-world experience with similar Fabric architectures would be greatly appreciated. Thanks in advance!𝐃𝐚𝐭𝐚𝐛𝐫𝐢𝐜𝐤𝐬 𝐭𝐨 𝐅𝐚𝐛𝐫𝐢𝐜: 𝐂𝐥𝐨𝐬𝐢𝐧𝐠 𝐭𝐡𝐞 𝐃𝐚𝐭𝐚 𝐈𝐧𝐭𝐞𝐠𝐫𝐚𝐭𝐢𝐨𝐧 𝐆𝐚𝐩
• 𝐓𝐡𝐞 𝐢𝐬𝐬𝐮𝐞: Data in Databricks currently needs to be physically ingested to support high granular Power BI semantic models in Fabric. This adds unnecessary cost and extra engineering effort. • 𝐓𝐡𝐞 𝐩𝐫𝐨𝐩𝐨𝐬𝐞𝐝 𝐟𝐢𝐱: As suggested by Patrick LeBlanc, Databricks writes data to cloud storage, and Fabric creates a shortcut to that data rather than copying it. Power BI then reads directly from that location. • 𝐓𝐡𝐞 𝐜𝐚𝐭𝐜𝐡: Power BI performs best when data is stored in the V-Order format. Databricks doesn't write data in this format, and shortcuts can't apply it retroactively — they simply point to the data as it exists. As a result, Power BI ends up reading at standard speed instead of its optimized speed. Why you can't just "fix" it outright: You could have Fabric reformat the data into V-Order, but that means writing changes directly to files that Databricks owns. If Databricks is also actively reorganizing those same files (which it often does automatically), the two systems can conflict and overwrite each other's work. Two ways to handle it: 1. If the data changes infrequently, it's reasonably safe to let Fabric reformat it — but make sure the data lives in the gold layer and is no longer actively managed by Databricks. 2. If Databricks is actively managing that data, maintain a separate copy in Fabric dedicated to reporting, and leave Databricks' original data untouched. Bottom line: Avoid having two systems rewrite the same underlying files. Either optimize safely in place, or keep a separate copy for each purpose. The Question: Has anyone in the community successfully implemented this approach? I would appreciate any insights into the challenges encountered during this process, particularly regarding the migration from traditional Power BI datasets to OneLake-based models.OAP for MLVs cross workspace
Hi all, I have a question regarding OAP implementation across multiple workspaces. My current setup consists of three layers: Bronze: OAP enabled Silver: OAP enabled Gold: OAP not enabled In the Bronze layer, I extract all source data using specific endpoints and gateways, with the required connection rules configured. In the Silver layer, I use shortcuts to access the source lakehouses in Bronze. This works as expected. Within Silver, I then create Materialized Lake Views (MLVs) for my entities. For example, I create a Person entity based on multiple source tables and store the result in LH_Silver. This also works correctly. The issue occurs in the next step. On top of the Silver entity, I want to create dim_Person. The MLV reads the entity from LH_Silver, but the output should be written to LH_Gold in the Gold workspace. When OAP is disabled on all workspaces, this cross-workspace setup works and I can successfully write the MLV to Gold. However, as soon as OAP is enabled on the Silver workspace, I can no longer write the MLV to the Gold workspace. I've already tried several connection rules, including: FabricMaterializedLakeView FabricSqlEndpointMetadata Fabric SQL Analytics Endpoint Lakehouse Notebook Web V2 I've also configured an MPE for the Gold (3000) workspace, but so far none of these options have resolved the issue. Does anyone know which connection rule, MPE, or configuration is required to allow an MLV running from an OAP-enabled Silver workspace to write to a Lakehouse in another workspace? Ideally, I would like to keep the notebooks and MLV logic in the Silver layer rather than moving them to the Gold workspace. Thanks in advance! Cel 1: %%pyspark spark.conf.set("spark.sql.caseSensitive", "true") workspace = spark.conf.get("trident.workspace.name") environment = "DEV" if "DEV" in workspace else "TEST" if "TEST" in workspace else "PRD" spark.sql(f"SET TargetEnvironment = 3000_CURATED ({environment})") Cel 2: CREATE OR REPLACE MATERIALIZED LAKE VIEW `${TargetEnvironment}`.LH_3000.Algemeen.dim_Persoon AS SELECT * FROM test.`02_entity`.Person
From SAP, FSM and SharePoint silos to a Fabric Lakehouse: Medallion + Direct Lake
We recently moved a building-services client at DataToBiz off a reporting setup where data sat across SAP S/4HANA, SAP FSM, SharePoint, and flat files. Pulling together a single report meant manual extraction and reconciliation, with some reporting cycles taking up to two weeks. We rebuilt this on Microsoft Fabric. Dataflows Gen2, Pipelines, and Notebooks handle ingestion from the four source types through automated batch workflows, landing in a single Lakehouse on OneLake with a Bronze/Silver/Gold structure. Spark notebooks and pipelines handle large-scale transformation, cleansing, and KPI standardization across the source systems. Power BI semantic models with DAX and RLS handle role-based access. Reports use Direct Lake mode to work with data in OneLake without the traditional import-based refresh approach. Result: reporting went from 10–14 days to under a day, with 20+ reports automated across roughly 60 users. The interesting part wasn't just bringing the data into Fabric. It was standardizing KPIs when different enterprise systems represented the same metric differently. Question for the group: how are you handling KPI standardization when multiple enterprise systems define the same metric differently? If anyone has a scalable, metadata-driven approach for that, please share.Red Flag in SQL Analytics EndPoint - User Identity Access Mode
We are seeing some red flags when switching the default access mode "Delegated" to ""User's Identity" in a SQL Analytics EndPoint of lakehouse, but seeing red flags with message not relevant to the user who were given access to downstream lakehouse, below is the details. Current Configuration: LH1 is in the hub workspace/WS1 with Onelake security role where users were added. In the user workspace/WS2, a shortcut to LH2 the source data is created, user can access through LH2’s SQL Analytics Endpoint, data access mode is switched to User’s Identity, so that source lakehouse's security role control access fully. In SQL End, there are some red flags as below, but user can access data without issue, which is confusing: error details: User principal is not supported. Issue: Error details is specifically for an AD group which is a workspace contributor role in the source workspace role (default reader of all tables in the source lakehouse), and it doesn't have access to the user's lakehouse. Question: Does anyone see the same? or it's a known issue?
Dataflow Gen2 can't see CSV files uploaded under Lakehouse Files
I am learning Microsoft Fabric Data Engineering and I'm having an issue with Dataflow Gen2 and Lakehouse Files. I created a Lakehouse called PracticeLakehouse and uploaded two CSV files under: Files > dp700_files The files are: departments.csv employees.csv I can see both files successfully in the Lakehouse Explorer, as shown in Screenshot 1. However, when I create a Dataflow Gen2 and try to select the Lakehouse as the data source, I cannot browse to: PracticeLakehouse > Files > dp700_files Thanks, Bhanu.
notebookutils.fs.cp() cannot copy from Lakehouse shortcut to Files in Python notebook
I've been playing around with notebookutils.fs.cp() and notebookutils.fs.fastcp() in Fabric Python 3.12 notebooks. This function seems to be unable to copy a file (e.g. PDF) from a SharePoint folder behind a Lakehouse shortcut to a directory in the Lakehouse Files. This produces a AzCopyFailedException. Somewhat strangely, it is possible to use fs.cp() and fs.fastcp() to copy files from the shortcut to local file storage in the notebook environment and to then copy from this local file storage to the Lakehouse Files. Has anyone come across a similar issue?
