<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Marta Castrillo on Medium]]></title>
        <description><![CDATA[Stories by Marta Castrillo on Medium]]></description>
        <link>https://medium.com/@martacasdelg?source=rss-30b331a2e646------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*sUApaRqWqAj5uiYpnNHvdw.jpeg</url>
            <title>Stories by Marta Castrillo on Medium</title>
            <link>https://medium.com/@martacasdelg?source=rss-30b331a2e646------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 21:12:01 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@martacasdelg/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Cómo construir una Arquitectura Medallón Metadata-Driven en Microsoft Fabric (Parte 2)]]></title>
            <link>https://medium.com/@martacasdelg/c%C3%B3mo-construir-una-arquitectura-medall%C3%B3n-metadata-driven-en-microsoft-fabric-parte-2-04eb13aefb93?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/04eb13aefb93</guid>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[metadata]]></category>
            <category><![CDATA[medallion-architecture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[pyspark]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Mon, 09 Nov 2026 17:55:40 GMT</pubDate>
            <atom:updated>2026-11-09T17:55:40.357Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ItUuH36ReaulmhaVWsKLQ.png" /><figcaption>diagrama de arquitectura (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/architecture_diagram.png">Ver en GitHub</a>)</figcaption></figure><p>En la<strong> primera parte </strong>de esta serie se establecieron los cimientos de la arquitectura en Microsoft Fabric: el diseño general de la solución, la configuración del warehouse de metadatos y la ingesta incremental en la capa Bronze.</p><p>En esta <strong>segunda parte</strong> se completará el ciclo de vida del dato hasta su consumo visual.</p><p>A lo largo del artículo se abordará:</p><ul><li><strong>Transformación de datos en <em>Silver</em></strong><em> </em>mediante la aplicación de esquemas, eliminación de duplicados y ejecución de operaciones <em>UPSERT</em> en tablas Delta, junto con la consulta del histórico de operaciones para la extracción de estadísticas.</li><li><strong>Creación de tablas de hechos y dimensiones en <em>Gold </em></strong>a través de cuadernos dedicados, cuyos IDs se obtienen por medio de la API de Fabric para su invocación dinámica.</li><li><strong>Configuración de un pipeline principal </strong>para la ejecución secuencial de las capas.</li><li><strong>Despliegue en Power BI</strong> del modelo semántico en modo <a href="https://learn.microsoft.com/es-es/fabric/fundamentals/direct-lake-overview"><em>Direct Lake</em></a> sobre la capa <em>Gold</em> y la construcción del reporte visual.</li></ul><h3>Capa Silver</h3><p>En la capa <strong><em>Silver</em></strong>, el objetivo principal es transformar los archivos JSON procedentes de <em>Bronze </em>en <strong>tablas Delta</strong> optimizadas, libres de duplicados y con nombres de columnas y tipos de datos estrictamente definidos.</p><p>Si recordamos la estructura de almacenamiento de la capa <em>Bronze</em>, esta no es casual: los datos se organizan en carpetas principales según su temática, conteniendo en su interior subcarpetas específicas para cada indicador de ESIOS (es decir, una carpeta por cada indicador).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/1*7nGaX5eQ5qwLlKhW4TDsTQ.png" /></figure><p>En <em>Silver</em>, la tabla de control (<em>silver_control</em>) especifica que el <strong>origen </strong>de los datos abarca las cuatro carpetas temáticas principales — <em>Demand, Generation, Generation Forecast</em> y <em>Market</em> — , mientras que el <strong>destino </strong>es una tabla Delta que comparte el mismo nombre que su respectiva categoría.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c5WUYa0mm1l3YQVM9Vaprw.png" /><figcaption>silver_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/silver_control.png">Ver en GitHub</a>)</figcaption></figure><p>De este modo, los distintos indicadores se agrupan directamente en sus tablas correspondientes, evitando la necesidad de hardcodear los identificadores individuales ni en la tabla de control ni en los notebooks de PySpark.</p><p>Para orquestar este proceso, el pipeline <em>PL_Silver </em>ejecuta las siguientes actividades:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/821/1*qnIgt2LWe0EPrXhUlZ74_A.png" /></figure><ul><li>La actividad <strong>LookUp </strong>recupera todos los registros activos de la tabla control, de la misma manera que se empleó en <em>Bronze</em>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/698/1*jT34ZdOtL9VkP4v-W0YLDw.png" /></figure><ul><li>El bucle <strong>ForEach </strong>se encarga de iterar sobre cada una de las filas para ejecutar, en cada iteración, el cuaderno <em>NB_Silver </em>y el procedimiento almacenado <em>SP_update_silver_watermark </em>con los parámetros correspondientes.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/758/1*8-Tm_EQDGpp6AMCIp_bnIQ.png" /></figure><p>El <strong>notebook</strong>, el cual se puede ver al completo en <a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture">mi repositorio de GitHub</a>,<strong> </strong>inicia definiendo las variables que provienen del pipeline e invocando un cuaderno auxiliar donde se establecen tanto el esquema de los datos como las columnas clave necesarias para realizar el merge en la tabla <em>Silver</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/955/1*-wLwKiVaGGfTorjoGIgiGQ.png" /></figure><p>A diferencia del pipeline de <em>Bronze</em>, en esta ocasión <strong>no es necesario definir una variable <em>end_date</em></strong><em> </em>a nivel de pipeline. Las tablas <em>Silver </em>se actualizan buscando directamente todos los<strong> ficheros de <em>Bronze </em>cuya fecha de modificación sea posterior al último watermark</strong> <strong>registrado</strong>, por lo que la fecha de fin no es requerida.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/818/1*Pas2UQlDcanTu5D8_RAoAg.png" /></figure><p>En otras palabras, no se leen los ficheros para inspeccionar a qué fecha corresponden los datos, sino que se aprovechan los <strong>metadatos nativos del lakehouse</strong> de <em>Bronze </em>para identificar qué archivos aún no han sido procesados.</p><p>El notebook también incluye las transformaciones necesarias para eliminar duplicados, aplicar tipados estrictos, renombrar columnas y generar nuevos campos. No obstante, la parte más relevante de la lógica de cara a este post se encuentra en el bloque final, donde se ejecuta el proceso de merge hacia la tabla <em>Silver</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*v15XVrHAaL5g1nHR_CTi2A.png" /></figure><p>Primero, <strong>se comprueba si la tabla ya existe en el lakehouse de destino</strong>. Si no es así, se crea explícitamente utilizando el esquema predefinido en el cuaderno auxiliar, evitando que el motor infiera los tipos de datos de forma automática.</p><p>Luego, <strong>se insertan los registros nuevos y se actualizan los ya existentes</strong>. Las columnas que definen la clave de negocio se recogen dinámicamente de la variable <em>merge_keys</em>, también procedente del cuaderno auxiliar.</p><p>Por último, se recopilan las <strong>estadísticas de la operación </strong>(número de filas insertadas y actualizadas) consultando el historial de la tabla Delta (<em>delta_table.history(1)</em>) para extraer las métricas de la última ejecución.</p><p>Este historial se puede consultar directamente en los archivos de la carpeta <em>_delta_log </em>de cada tabla, donde se almacena un archivo JSON por cada commit realizado. Si abrimos cualquiera de ellos, encontraremos el campo <em>operationMetrics </em>del que provienen las estadísticas.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/614/1*kcspKwfTgpMIaNpQKv9bNg.png" /></figure><p>En la última celda del notebook, se calcula el <strong>nuevo watermark</strong> como la fecha máxima de la columna especificada como <em>watermark_column </em>dentro de la tabla de control — en este caso, una marca temporal de ingestión generada en el propio proceso — .</p><p>Estos tres valores <strong>se devuelven como</strong> <strong>output de la actividad del notebook </strong>mediante la librería <em>mssparkutils</em>, lo cual es necesario para que el procedimiento almacenado posterior pueda actualizar la tabla de control.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/739/1*LB4YFGl_5zN_JWXhmG55tA.png" /></figure><p>Una vez finalizada la ejecución de este pipeline, las tablas Delta aparecerán en el lakehouse de la capa <em>Silver</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*ZgNFf-6u4ICr5II2R6GkrQ.png" /></figure><p>Todas las tablas compartirán la misma estructura:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MmqJYlWUQtiE8Q_wAEuMzA.png" /></figure><p>Y los registros de <em>silver_control </em>se habrán actualizado con éxito:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/938/1*2sXwGRMg2732MKWM7W4nrg.png" /></figure><h3>Capa Gold</h3><p>En la última capa de la arquitectura <strong>se construyen las tablas de hechos y dimensiones</strong> destinadas a conformar el modelo dimensional que consumirán las<strong> herramientas de BI</strong>.</p><p>A partir de las cuatro tablas de la capa <em>Silver</em>, este proceso transforma la información para generar 5 dimensiones y 4 tablas de hechos.</p><p>Dado que no existe una relación directa de uno a uno entre las tablas de ambas capas, que cada una requiere transformaciones específicas y que la tabla de control exige una fila por cada tabla <em>Gold</em>, se optó por desarrollar un <strong>notebook específico para cada</strong> <strong>una de ellas</strong>, tal y como se puede observar en la tabla de control:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*byi0EoU3Yrvg_RMef0buAQ.png" /></figure><p>Esto reduce la complejidad del código en comparación con el uso de un único cuaderno genérico. Por otro lado, permite omitir la actualización de una tabla concreta sin necesidad de modificar ningún cuaderno, pues bastaría con cambiar el valor de <em>is_active </em>a 0 en la fila correspondiente.</p><p>Asimismo, de forma similar a lo visto en <em>Silver</em>, un cuaderno auxiliar almacena en un diccionario la estructura de las tablas y las claves necesarias para el merge en <em>Gold </em>— cuando aplica, ya que las tablas de dimensión se actualizan mediante una carga completa (full), mientras que las de hechos aplican una estrategia incremental — . Los cuadernos consultan este diccionario de manera dinámica durante su ejecución.</p><p>En el <strong>pipeline </strong>de la capa <em>Gold</em>, denominado <em>PL_Gold</em>, se configuran las siguientes actividades:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fwSWexnRdUEGgU2VNxSdLA.png" /></figure><p>La lógica del flujo es muy similar a la que ya empleamos en la capa <em>Silver</em>: la actividad Lookup realiza una consulta al warehouse de metadatos para obtener las filas activas de <em>gold_control</em>, iterando sobre ellas mediante un bucle ForEach que ejecuta secuencialmente el cuaderno correspondiente y el procedimiento almacenado encargado de actualizar el watermark y las estadísticas de la operación.</p><p>No obstante, en esta capa nos encontramos con un detalle técnico importante: la actividad de notebook en los pipelines de <strong>Fabric no permite referenciar dinámicamente un cuaderno por su nombre</strong>, sino que se requiere estrictamente su identificador único (ID).</p><p>Para solucionarlo, el flujo incorpora una acción previa que realiza una <strong>llamada directa a la API de Fabric con el fin de listar todos los cuadernos</strong> existentes en el área de trabajo, obteniendo así sus identificadores de forma dinámica.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/1*OTHPhhEvujgKSFXTw7xkaA.png" /></figure><p>Hecho esto, dentro del <strong>ForEach </strong>se ejecutan las siguientes acciones:</p><ul><li>Se asigna a la <strong>variable <em>notebook_name </em></strong>el valor de la columna <em>notebook </em>correspondiente a la fila de la iteración actual.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/510/1*iTHxQz0X35xrlmhPJxpm7Q.png" /></figure><ul><li>Se <strong>filtra </strong>el output de la actividad ‘Get Notebooks’ para seleccionar únicamente el elemento cuyo nombre coincide con el de la tabla de control.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/1*OiaCnWfX57HkfRVWYzhSWA.png" /></figure><ul><li>Se <strong>invoca el cuaderno </strong>extrayendo su ID directamente del resultado de la acción de filtrado.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/542/1*77dP_OgL42FMI8KicF1Slg.png" /></figure><ul><li>Se ejecuta el <strong>procedimiento almacenado</strong> con el output del notebook correspondiente en cada iteración, al igual que en la capa <em>Silver</em>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/693/1*HcOafxffUx6_pwFri1kDEw.png" /></figure><p>Al completar la ejecución de todo el pipeline, las tablas de hechos y dimensiones aparecerán en el lakehouse de la capa <em>Gold</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/357/1*SXHXitRNnKIqPPCKcoGMJw.png" /></figure><p>Por ejemplo, la tabla de hechos <em>fact_demand</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mOll2cvhDNqxNArV-1cV_Q.png" /></figure><p>Asimismo, la tabla de control <em>gold_control </em>se habrá actualizado:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/961/1*zL1W5bCGX65tWbRA1j4Jhw.png" /></figure><p>Cabe aclarar que la tabla <strong><em>_measure_table </em></strong>que se ve en la imagen del lakehouse <strong>no forma parte de la ejecución del pipeline</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/686/1*mYM-gqrV7-B9Rc7C3qrVQg.png" /></figure><p>Esta tabla, compuesta por una única columna, se utiliza como tabla de medidas en el modelo de <strong>Power BI</strong>, pues en el modo <em>Direct Lake </em>no es posible crear la tabla manualmente como se haría habitualmente con un modo <em>Import</em>.</p><p>Para crear esta tabla — un proceso que solo es necesario realizar una única vez — , se emplea el siguiente cuaderno:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/792/1*6yTNVh2M8gl1vc8J5zaBMw.png" /></figure><h3>Pipeline Principal</h3><p>Para garantizar que la ejecución de las tres capas se realice de forma secuencial, se configura un pipeline denominado <em>PL_Main</em>, el cual se encarga de invocar ordenadamente a los flujos individuales:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/865/1*I0LrQyY7c2e7AXCeEGK4Lw.png" /></figure><p>Este pipeline no solo asegura el orden de dependencias entre las capas del Medallón, sino que también actúa como el punto único de entrada donde se configura la ejecución programada o mediante eventos de todo el proceso.</p><h3>Modelo Direct Lake y Reporte</h3><p>Una vez completada la Arquitectura Medallón, ya es posible configurar el modelo semántico utilizando el modo <a href="https://learn.microsoft.com/es-es/fabric/fundamentals/direct-lake-overview"><em>Direct Lake</em></a>, tal como se muestra en la siguiente imagen:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*23yohgcwEg_rbYKx9I9xeQ.png" /></figure><p>El modelo consume las tablas del lakehouse de <em>Gold </em>e incluye métricas DAX organizadas por temáticas, un grupo de cálculo para las medidas de <em>time intelligence</em> y parámetros de campo para determinadas visualizaciones.</p><p>A continuación, os presento un breve recorrido visual por el reporte final:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/1*zKprCBDikJCEp6orIF0dYg.gif" /></figure><p>Espero que esta serie de artículos os haya servido de inspiración para vuestro propio proyecto. Igualmente, cualquier feedback es más que bienvenido.</p><p>Recordad que todos los notebooks y las consultas SQL para la creación de las tablas de control están disponibles en <a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture">mi repositorio de GitHub</a>.</p><p>¡Gracias por leerme!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=04eb13aefb93" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Build a Metadata-Driven Medallion Architecture in Microsoft Fabric (Part 2)]]></title>
            <link>https://medium.com/@martacasdelg/how-to-build-a-metadata-driven-medallion-architecture-in-microsoft-fabric-part-2-c2a3fbab4021?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/c2a3fbab4021</guid>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[medallion-architecture]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[metadata]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Mon, 09 Nov 2026 17:55:14 GMT</pubDate>
            <atom:updated>2026-11-09T17:55:14.418Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ItUuH36ReaulmhaVWsKLQ.png" /><figcaption>architecture diagram (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/architecture_diagram.png">See on GitHub</a>)</figcaption></figure><p>The <strong>first part </strong>of this series laid the foundations of the architecture in Microsoft Fabric: the solution design, the metadata warehouse setup, and the incremental ingestion into the <em>Bronze </em>layer.</p><p>In this <strong>second part</strong>, the data lifecycle will be completed up to its visual consumption.</p><p>Throughout the article, the following will be covered:</p><ul><li><strong>Data transformation in <em>Silver</em></strong><em> </em>through the application of schemas, deduplication, and execution of <em>UPSERT </em>operations in Delta tables, alongside querying the operation history for statistics extraction.</li><li><strong>Creation of fact and dimension tables in <em>Gold </em></strong>through dedicated notebooks, whose IDs are retrieved via the Fabric API for dynamic invocation.</li><li><strong>Configuration of a main pipeline</strong> for the sequential execution of the layers.</li><li><strong>Deployment in Power BI</strong> of the semantic model in <a href="https://learn.microsoft.com/es-es/fabric/fundamentals/direct-lake-overview"><em>Direct Lake</em></a> mode over the <em>Gold </em>layer and the construction of the visual report.</li></ul><h3>Silver Layer</h3><p>In the <strong><em>Silver </em></strong>layer, the primary objective is to transform the JSON files from <em>Bronze </em>into optimized <strong>Delta tables</strong>, free of duplicates, with strictly defined column names and data types.</p><p>Recalling the storage structure of the <em>Bronze </em>layer, this is not accidental: data is organized into main folders by subject matter, containing specific subfolders for each ESIOS indicator inside them (that is, one folder for each indicator).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/1*7nGaX5eQ5qwLlKhW4TDsTQ.png" /></figure><p>In <em>Silver</em>, the control table (<em>silver_control</em>) defines the data <strong>source </strong>as the four main subject folders — <em>Demand, Generation, Generation Forecast, </em>and <em>Market </em>— while the <strong>target </strong>is a Delta table matching each category’s name.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c5WUYa0mm1l3YQVM9Vaprw.png" /><figcaption>silver_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/silver_control.png">See on GitHub</a>)</figcaption></figure><p>In this way, the different indicators are grouped directly into their corresponding tables, avoiding the need to hardcode individual identifiers either in the control table or in the PySpark notebooks.</p><p>To orchestrate this process, the <em>PL_Silver </em>pipeline executes the following activities:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/821/1*qnIgt2LWe0EPrXhUlZ74_A.png" /></figure><ul><li>The <strong>LookUp </strong>activity retrieves all active records from the control table, in the same manner as it was used in <em>Bronze</em>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/698/1*jT34ZdOtL9VkP4v-W0YLDw.png" /></figure><ul><li>The <strong>ForEach </strong>loop iterates over each row to execute, in every iteration, the <em>NB_Silver </em>notebook and the <em>SP_update_silver_watermark </em>stored procedure with their corresponding parameters.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/758/1*8-Tm_EQDGpp6AMCIp_bnIQ.png" /></figure><p>The <strong>notebook</strong>, which can be viewed in full in the <a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture">GitHub repository</a>, begins by defining the variables passed from the pipeline and invoking an auxiliary notebook where both the data schema and the key columns required for performing the merge into the <em>Silver </em>table are defined.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/955/1*-wLwKiVaGGfTorjoGIgiGQ.png" /></figure><p>Unlike the <em>Bronze </em>pipeline, defining an <strong><em>end_date </em>variable at the pipeline level is not required</strong> in this case. The <em>Silver </em>tables are updated by scanning directly for all <strong><em>Bronze </em>files with a modification date later than the last recorded watermark</strong>, so an end date is unnecessary.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/818/1*Pas2UQlDcanTu5D8_RAoAg.png" /></figure><p>In other words, files are not read to inspect the internal date of the data; instead, <strong>native metadata from the <em>Bronze </em>lakehouse</strong> is leveraged to identify which files have not yet been processed.</p><p>The notebook also includes the necessary transformations to remove duplicates, apply strict typing, rename columns, and derive new fields. However, the most relevant part for this post lies in the final block, where the merge process into the <em>Silver </em>table is executed:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*v15XVrHAaL5g1nHR_CTi2A.png" /></figure><p>First, <strong>it is verified whether the table already exists</strong> in the target lakehouse. If not, it is explicitly created using the schema predefined in the auxiliary notebook, preventing the engine from automatically inferring data types.</p><p>Next, <strong>new records are inserted and existing ones are updated</strong>. The columns defining the business key are retrieved dynamically from the <em>merge_keys </em>variable, which also originates from the auxiliary notebook.</p><p>Finally, <strong>operation statistics</strong> (number of inserted and updated rows) are gathered by querying the Delta table history (<em>delta_table.history(1)</em>) to extract the metrics from the latest execution.</p><p>This history can be inspected directly in the files within the <em>_delta_log </em>directory of each table, where a JSON file is stored for every committed transaction. Opening any of these files reveals the <em>operationMetrics </em>field, which serves as the source for these statistics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/614/1*kcspKwfTgpMIaNpQKv9bNg.png" /></figure><p>In the notebook’s final cell, the <strong>new watermark</strong> is calculated as the maximum date of the column specified as <em>watermark_column </em>in the control table — in this case, an ingestion timestamp generated during the process itself.</p><p>These three values are <strong>returned as the notebook activity’s output</strong> via the <em>mssparkutils </em>library, which is necessary for the subsequent stored procedure to update the control table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/739/1*LB4YFGl_5zN_JWXhmG55tA.png" /></figure><p>Once the execution of this pipeline is completed, the Delta tables will appear in the <em>Silver </em>layer lakehouse:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*ZgNFf-6u4ICr5II2R6GkrQ.png" /></figure><p>All tables will share the same structure:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MmqJYlWUQtiE8Q_wAEuMzA.png" /></figure><p>And the records in <em>silver_control </em>will have been updated successfully:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/938/1*2sXwGRMg2732MKWM7W4nrg.png" /></figure><h3>Gold Layer</h3><p>In the final layer of the architecture, <strong>the fact and dimension tables are built</strong> to form the dimensional model for <strong>BI tools</strong> consumption.</p><p>Starting from the four tables in the <em>Silver </em>layer, this process transforms the data to generate five dimensions and four fact tables.</p><p>Since there is no direct one-to-one relationship between the tables across both layers, each target table requires specific transformations, and the control table demands a row for each <em>Gold </em>table, a <strong>dedicated notebook</strong> <strong>was developed for each one</strong>, as shown in the control table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*byi0EoU3Yrvg_RMef0buAQ.png" /></figure><p>This reduces code complexity compared to using a single generic notebook. Furthermore, it allows skipping the update of a specific table without modifying any notebook code, as simply setting <em>is_active </em>to 0 in the corresponding row suffices.</p><p>Similarly, much like in the <em>Silver </em>layer, an auxiliary notebook stores the table schemas and the required merge keys for <em>Gold </em>within a dictionary — where applicable, since dimension tables are updated via a full load while fact tables use an incremental strategy — . The notebooks query this dictionary dynamically during execution.</p><p>In the <em>Gold </em>layer <strong>pipeline</strong>, named <em>PL_Gold</em>, the following activities are configured:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fwSWexnRdUEGgU2VNxSdLA.png" /></figure><p>The flow logic is very similar to that used in the <em>Silver </em>layer: the Lookup activity queries the metadata warehouse to retrieve the active rows from <em>gold_control</em>, iterating over them via a ForEach loop that sequentially executes the corresponding notebook and the stored procedure responsible for updating the watermark and operation statistics.</p><p>However, an important technical detail arises in this layer: the notebook activity in <strong>Fabric pipelines does not allow referencing a notebook dynamically by name</strong>, strictly requiring its unique identifier (ID) instead.</p><p>To address this, the workflow incorporates a preceding action that makes a direct <strong>call to the Fabric API to list all existing notebooks in the workspace</strong>, thereby dynamically retrieving their identifiers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/1*OTHPhhEvujgKSFXTw7xkaA.png" /></figure><p>Once this is completed, the following actions are executed within the <strong>ForEach </strong>loop:</p><ul><li>The <strong><em>notebook_name </em>variable</strong> is assigned the value of the <em>notebook </em>column corresponding to the current iteration row.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/510/1*iTHxQz0X35xrlmhPJxpm7Q.png" /></figure><ul><li>The output of the ‘Get Notebooks’ activity is <strong>filtered </strong>to select only the element whose name matches that of the control table.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/1*OiaCnWfX57HkfRVWYzhSWA.png" /></figure><ul><li>The <strong>notebook is invoked</strong> by extracting its ID directly from the output of the filtering action.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/542/1*77dP_OgL42FMI8KicF1Slg.png" /></figure><ul><li>The <strong>stored procedure</strong> is executed using the output of the corresponding notebook in each iteration, just as in the <em>Silver </em>layer.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/693/1*HcOafxffUx6_pwFri1kDEw.png" /></figure><p>Once the execution of the entire pipeline is completed, the fact and dimension tables will appear in the <em>Gold </em>layer lakehouse:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/357/1*SXHXitRNnKIqPPCKcoGMJw.png" /></figure><p>For example, the <em>fact_demand </em>table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mOll2cvhDNqxNArV-1cV_Q.png" /></figure><p>Additionally, the <em>gold_control </em>control table will have been updated:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/961/1*zL1W5bCGX65tWbRA1j4Jhw.png" /></figure><p>It should be clarified that the <strong><em>_measure_table </em></strong>table visible in the lakehouse image <strong>is not part of the pipeline execution</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/686/1*mYM-gqrV7-B9Rc7C3qrVQg.png" /></figure><p>This table, consisting of a single column, is used as a measure table in the <strong>Power BI </strong>model, since in <em>Direct Lake</em> mode it is not possible to create a table manually as is typically done in <em>Import </em>mode.</p><p>To create this table — a process that only needs to be performed once — the following notebook is used:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/792/1*6yTNVh2M8gl1vc8J5zaBMw.png" /></figure><h3>Main Pipeline</h3><p>To ensure that the execution of the three layers is carried out sequentially, a pipeline named <em>PL_Main </em>is configured, which is responsible for orderly invoking the individual flows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/865/1*I0LrQyY7c2e7AXCeEGK4Lw.png" /></figure><p>This pipeline not only ensures the order of dependencies between the Medallion layers, but also acts as the single entry point where scheduled or event-driven execution of the entire process is configured.</p><h3>Direct Lake Model and Report</h3><p>Once the Medallion Architecture is completed, it is already possible to configure the semantic model using <a href="https://learn.microsoft.com/es-es/fabric/fundamentals/direct-lake-overview"><em>Direct Lake</em></a> mode, as shown in the following image:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*23yohgcwEg_rbYKx9I9xeQ.png" /></figure><p>The model consumes the tables from the <em>Gold </em>lakehouse and includes DAX metrics organized by theme, a calculation group for <em>time intelligence</em> measures, and field parameters for specific visualizations.</p><p>Below is a brief visual tour of the final report:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/1*zKprCBDikJCEp6orIF0dYg.gif" /></figure><p>I hope this series of articles has inspired your own project. Likewise, any feedback is more than welcome.</p><p>Remember that all notebooks and the SQL queries for creating the control tables are available in <a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture">my GitHub repository</a>.</p><p>Thanks for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c2a3fbab4021" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cómo construir una Arquitectura Medallón Metadata-Driven en Microsoft Fabric (Parte 1)]]></title>
            <link>https://medium.com/@martacasdelg/c%C3%B3mo-construir-una-arquitectura-medall%C3%B3n-metadata-driven-en-microsoft-fabric-parte-1-f5a6dbfa6f20?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/f5a6dbfa6f20</guid>
            <category><![CDATA[medallion-architecture]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[metadata]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Sun, 08 Nov 2026 11:40:31 GMT</pubDate>
            <atom:updated>2026-11-09T18:00:02.831Z</atom:updated>
            <content:encoded><![CDATA[<p>Implementar una <a href="https://learn.microsoft.com/es-es/azure/databricks/lakehouse/medallion"><strong>arquitectura Medallón</strong></a> permite gestionar la calidad de los datos de forma clara y escalonada, desde la ingesta en bruto hasta la creación de modelos dimensionales listos para su consumo por herramientas de BI.</p><p>Combinar este patrón con un <strong>enfoque guiado por metadatos</strong> optimiza significativamente la flexibilidad y escalabilidad de los pipelines. En lugar de depender de valores estáticos y hardcodeados, son las<strong> tablas de control</strong> las que gobiernan de manera dinámica los parámetros, los watermarks y los rangos de ejecución.</p><p>En esta serie de artículos explico cómo llevar a la práctica esta arquitectura en <strong>Microsoft Fabric</strong>, empleando datos reales del mercado eléctrico español procedentes de la <a href="https://www.esios.ree.es/es/pagina/api">API de ESIOS</a>.</p><p>Mostraré cómo:</p><ul><li>Separar la lógica del flujo mediante un warehouse de metadatos.</li><li>Procesar datos a través de las capas <em>Bronze</em>, <em>Silver </em>y <em>Gold </em>con cuadernos de PySpark.</li><li>Entregar un producto de datos en Power BI utilizando el modo <a href="https://learn.microsoft.com/es-es/fabric/fundamentals/direct-lake-overview"><em>Direct Lake</em></a>.</li></ul><p>Esta <strong>primera parte</strong> se centra en el diseño de la solución, la creación del warehouse de metadatos y la ingesta incremental en la capa <em>Bronze</em>; la <strong>segunda parte</strong> abordará el resto de las etapas hasta el informe final.</p><p>Conviene señalar que no existe una única forma correcta de diseñar este tipo de arquitectura. Estos artículos no pretenden ser una guía definitiva, sino una solución práctica que sirva de inspiración y pueda adaptarse a las necesidades de vuestros propios proyectos.</p><h3>Arquitectura</h3><p>A continuación, se muestra el diagrama de la arquitectura de este proyecto:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ItUuH36ReaulmhaVWsKLQ.png" /><figcaption>Diagrama de arquitectura (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/architecture_diagram.png">Ver en GitHub</a>)</figcaption></figure><p>En los siguientes apartados se explicará la solución paso por paso.</p><h3>Warehouse de Metadatos</h3><p>El control del proceso recae en un <strong>warehouse de metadatos</strong> llamado <em>WH_Metadata</em>, donde se almacenan tablas de control que gobiernan la extracción de datos, la carga y la monitorización de cada etapa en la arquitectura medallón.</p><p>En este caso, se han creado 3 tablas de control y 3 procedimientos almacenados:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/449/1*3KVjIG14DGg9245mbtThGw.png" /></figure><p>Cada etapa cuenta con una tabla de control dedicada que contiene columnas específicas adaptadas a sus propios requerimientos operativos.</p><h4>bronze_control</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xm8uftYbdPUktsk70uw_tA.png" /><figcaption>bronze_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/bronze_control.png">Ver en GitHub</a>)</figcaption></figure><p>Contiene una fila por cada indicador objetivo del que se extraen datos (se seleccionaron los indicadores más relevantes, dado que existen más de 2.000 disponibles).</p><p>Sus columnas almacenan los parámetros necesarios para realizar las llamadas a la API de ESIOS — como el ID del indicador o el parámetro <em>time_agg (</em>que indica si los valores del periodo se agrupan mediante suma o promedio<em>)</em>— , las rutas de los directorios de destino para el almacenamiento de los datos y la marca de tiempo de la última actualización (<em>last_watermark</em>), entre otros.</p><h4>silver_control</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c5WUYa0mm1l3YQVM9Vaprw.png" /><figcaption>silver_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/silver_control.png">Ver en GitHub</a>)</figcaption></figure><p>Contiene una fila por cada tabla que se creará en la capa <em>Silver</em>, partiendo de los archivos ubicados en la ruta especificada en la columna <em>source_folder_name</em>.</p><h4>gold_control</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*04TlbPR7D7h5BnkGiXl5Gg.png" /><figcaption>gold_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/gold_control.png">Ver en GitHub</a>)</figcaption></figure><p>Contiene una fila por cada tabla que se creará en el lakehouse de la capa <em>Gold</em>. La columna <em>notebook </em>indica el nombre del cuaderno encargado de actualizar sus datos.</p><p>En general, el campo <em>last_watermark </em>incluye un valor por defecto, lo que permite la recuperación de datos históricos durante la primera ejecución. El parámetro <em>is_active </em>permite activar o desactivar la actualización de los datos sin necesidad de eliminar los registros de las tablas.</p><p>Los procedimientos almacenados permiten actualizar el <em>last_watermark </em>y las estadísticas de ejecución tras cada actualización de datos.</p><h3>Lakehouses y Variable Library</h3><p>El siguiente paso consiste en <strong>crear los lakehouses</strong> donde se almacenarán los datos correspondientes a cada etapa de la arquitectura.</p><p>Una vez creados (y dejándolos inicialmente vacíos), se anotan sus identificadores únicos — junto con el del warehouse de metadatos — en un nuevo elemento de tipo<strong> variable library</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/886/1*TjANP0LVPUDYpbAJ_-JekA.png" /></figure><p>Esto evita incluir valores estáticos para los identificadores de los lakehouses de origen y destino en las actividades de los pipelines, permitiendo incorporar estas variables de forma dinámica en la ejecución de los notebooks.</p><h3>Capa Bronze</h3><p>En la capa <strong><em>Bronze</em></strong>, la información se extrae directamente de la API de ESIOS y se almacena en su formato original (JSON) dentro del lakehouse <em>LH_Bronze</em>.</p><p>Para lograrlo, se utiliza el pipeline <em>PL_Bronze</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FqpOldZ8y5nDk98D899Gng.png" /></figure><p>En el apartado de configuración del pipeline, se añade la ruta al lakehouse de la capa <em>Bronze </em>utilizando la librería de variables creada en el apartado anterior.</p><p>En cuanto a las actividades del flujo:</p><ul><li>La primera actividad establece la <strong>variable <em>end_date</em></strong><em> </em>con la fecha actual del sistema. Esta fecha es <strong>clave para la estrategia de actualización incremental mediante watermarks</strong>, ya que define el límite del rango a solicitar a la API y servirá como nuevo watermark al finalizar el guardado de los datos.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/585/1*nuxJazRx9_gfRCY8vktWDQ.png" /></figure><ul><li>La actividad de <strong>LookUp </strong>realiza una consulta SQL a la tabla <em>bronze_control </em>del warehouse de metadatos, recuperando todas las filas donde <em>is_active </em>sea igual a 1.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/603/1*Q1Hsk6zHqKqbY8K-Tr8gPw.png" /></figure><p>Se desmarca la casilla “first row only” para indicar que se requiere la tabla completa.</p><ul><li>El <strong>ForEach </strong>recibe el output de la actividad anterior con el fin de iterar sobre las filas de la tabla, donde cada fila contiene los datos de un indicador.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/962/1*W1cd_FBQvihmnuCcPPzddA.png" /></figure><ul><li>En el interior del ForEach, se invoca el cuaderno <strong><em>NB_Bronze</em></strong>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/753/1*ZVnujbUQHRrwrFceMaVJZg.png" /></figure><p>Este cuaderno <strong>recoge los parámetros específicos de cada iteración</strong> para realizar la llamada a la API.</p><p>Además, incorpora un periodo de solape (<em>overlap_minutes</em>) en la fecha de inicio para capturar posibles datos tardíos, compensando el hecho de que el origen no proporciona una fecha explícita de actualización. Finalmente, guarda los datos en el lakehouse.</p><pre># Import libraries<br>import requests<br>import json<br>import os<br>from datetime import datetime, timedelta<br><br># Define parameters that will come from pipeline during execution<br><br>## Pipeline will define end_date as base parameter for all activities in the pipeline using @utcNow()<br>end_date = None<br><br>## The next parameters will come from bronze_control table in metadata warehouse, retrieved using pipeline activities<br>esios_indicator_id = None<br>esios_indicator_short_name = None<br>locale_language = None<br>time_trunc = None<br>time_agg = None<br>target_lakehouse_path = None<br>target_landing_path = None<br>target_folder_name = None<br>file_type = None<br>last_watermark = None<br>overlap_minutes = None<br><br># Define other variables for API call<br><br>## Start and end dates in ISO8601 format with time zone and considering overlap minutes for start date<br>overlap_minutes = int(overlap_minutes)<br>end_date_dt = datetime.fromisoformat(end_date.replace(&quot;Z&quot;, &quot;+00:00&quot;))<br>start_date_dt = datetime.fromisoformat(last_watermark.replace(&quot;Z&quot;, &quot;+00:00&quot;)) - timedelta(minutes=overlap_minutes)<br><br>start_date = start_date_dt.strftime(&quot;%Y-%m-%dT%H:%M:%SZ&quot;)<br>end_date = end_date_dt.strftime(&quot;%Y-%m-%dT%H:%M:%SZ&quot;)<br><br>## Execution date for file name<br>execution_date = end_date_dt.strftime(&quot;%Y-%m-%d_%H-%M-%S&quot;)<br><br>## Other variables for the request<br>url = f&quot;https://api.esios.ree.es/indicators/{esios_indicator_id}&quot;<br>headers = {&#39;Accept&#39;: &#39;application/json; application/vnd.esios-api-v1+json&#39;,<br>           &#39;Content-Type&#39;: &#39;application/json&#39;,<br>           &#39;x-api-key&#39;: f&#39;{api_key}&#39;}<br>params = {<br>    &quot;locale&quot;: f&quot;{locale_language}&quot;,<br>    &quot;start_date&quot;: f&quot;{start_date}&quot;,<br>    &quot;end_date&quot;: f&quot;{end_date}&quot;,<br>    &quot;time_trunc&quot;: f&quot;{time_trunc}&quot;,<br>    &quot;time_agg&quot;: f&quot;{time_agg}&quot;<br>}<br><br># Make API call<br>try:<br>    response = requests.get(url, headers=headers, params=params)<br>    response.raise_for_status()<br>    data = response.json()<br>    if not data:<br>        raise ValueError(f&quot;No data returned for indicator {esios_indicator_id} between {start_date} and {end_date}&quot;)<br>except requests.exceptions.RequestException as e:<br>    raise RuntimeError(f&quot;Error fetching data from API: {e}&quot;)<br><br># Define paths<br>file_path = f&quot;{target_lakehouse_path}/{target_landing_path}/{target_folder_name}/indicator_{esios_indicator_id}_{execution_date}.{file_type}&quot;<br>directory_path = f&quot;{target_lakehouse_path}/{target_landing_path}/{target_folder_name}&quot;<br><br># Create folder if it doesnt exists and save json file <br>try:<br>    notebookutils.fs.mkdirs(directory_path)<br>    notebookutils.fs.put(file_path, json.dumps(data, indent=4), True)<br>    print(f&quot;Data successfully saved to {file_path}&quot;)<br>except Exception as e:<br>    raise RuntimeError(f&quot;Error saving file to {file_path}. Details: {e}&quot;)</pre><p>Este enfoque resulta viable gracias a que todos los indicadores de ESIOS comparten los mismos parámetros y devuelven la información bajo un formato homogéneo.</p><ul><li>Por último, si todas las iteraciones del bucle ForEach se han completado con éxito, se invoca el <strong>procedimiento almacenado</strong> <em>SP_update_bronze_watermark</em> para actualizar la fecha del campo <em>last_watermark </em>con el valor de la variable <em>end_date</em>. Servirá como punto de partida para la siguiente ejecución.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*oMjtFpnB1gSTovEw13GQ1w.png" /></figure><p>Una vez ejecutado el pipeline, los datos aparecerán automáticamente en el lakehouse, organizados dentro de las carpetas especificadas en la tabla de control:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/1*7nGaX5eQ5qwLlKhW4TDsTQ.png" /></figure><p>Si volvemos a consultar el warehouse de metadatos tras el proceso, podremos comprobar que la fecha se ha actualizado correctamente:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-0OlsUXrsWsg575lQ-Nt_w.png" /></figure><p>Con esto concluye la primera parte de esta serie.</p><p>Todo el código correspondiente a esta etapa se puede consultar en <a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture"><strong>mi repositorio de GitHub</strong></a>.</p><p><strong>La </strong><a href="https://medium.com/@martacasdelg/c%C3%B3mo-construir-una-arquitectura-medall%C3%B3n-metadata-driven-en-microsoft-fabric-parte-2-04eb13aefb93"><strong>parte 2</strong></a><strong> ya está disponible</strong>. En ella se verán las capas <em>Silver </em>y <em>Gold</em>, así como el modelo <em>Direct Lake</em> y el reporte final en Power BI.</p><p>¡Gracias por leerme!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f5a6dbfa6f20" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Build a Metadata-Driven Medallion Architecture in Microsoft Fabric (Part 1)]]></title>
            <link>https://medium.com/@martacasdelg/how-to-build-a-metadata-driven-medallion-architecture-in-microsoft-fabric-part-1-6297422fb591?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/6297422fb591</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[medallion-architecture]]></category>
            <category><![CDATA[metadata]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Sun, 08 Nov 2026 11:39:46 GMT</pubDate>
            <atom:updated>2026-11-09T17:59:02.438Z</atom:updated>
            <content:encoded><![CDATA[<p>Implementing a <a href="https://learn.microsoft.com/es-es/azure/databricks/lakehouse/medallion"><strong>Medallion architecture</strong></a> makes it possible to manage data quality clearly and in stages — ranging from raw ingestion to the creation of dimensional models ready for consumption by BI tools.</p><p>Combining this pattern with a <strong>metadata-driven approach</strong> significantly optimizes the flexibility and scalability of your pipelines. Instead of relying on static, hardcoded values, <strong>control tables </strong>dynamically govern parameters, watermarks, and execution ranges.</p><p>In this series of articles, I explain how to put this architecture into practice in <strong>Microsoft Fabric</strong> using real-world data from the Spanish electricity market, sourced from the <a href="https://www.esios.ree.es/es/pagina/api">ESIOS API</a>.</p><p>I will show you how to:</p><ul><li>Separate pipeline logic using a metadata warehouse.</li><li>Process data through the Bronze, Silver, and Gold layers using PySpark notebooks.</li><li>Deliver a data product in Power BI using <a href="https://learn.microsoft.com/es-es/fabric/fundamentals/direct-lake-overview"><em>Direct Lake</em></a> mode.</li></ul><p>This <strong>first part</strong> focuses on the solution design, the creation of the metadata warehouse, and incremental ingestion into the Bronze layer; the <strong>second part</strong> will cover the remaining stages all the way to the final report.</p><p>It is worth noting that there is no single “correct” way to design this type of architecture. These articles are not intended to be a definitive guide, but rather a practical solution designed to inspire you and adapt to the needs of your own projects.</p><h3>Architecture</h3><p>Below is the architecture diagram for this project:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ItUuH36ReaulmhaVWsKLQ.png" /><figcaption>Architecture diagram (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/architecture_diagram.png">See on GitHub</a>)</figcaption></figure><p>The following sections will explain the solution step by step.</p><h3>Metadata Warehouse</h3><p>The process is controlled by a <strong>metadata warehouse</strong> named <em>WH_Metadata</em>, which stores the control tables governing data extraction, loading, and monitoring across each layer of the medallion architecture.</p><p>In this setup, three control tables and three stored procedures have been created:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/449/1*3KVjIG14DGg9245mbtThGw.png" /></figure><p>Each stage features a dedicated control table with specific columns tailored to its operational requirements.</p><h4>bronze_control</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xm8uftYbdPUktsk70uw_tA.png" /><figcaption>bronze_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/bronze_control.png">See on GitHub</a>)</figcaption></figure><p>It contains one row for each target indicator from which data is extracted (the most relevant indicators were selected, given that there are more than 2000 available).</p><p>Its columns store the parameters needed to make the calls to the ESIOS API — such as the indicator ID or the <em>time_agg </em>parameter (which specifies whether period values are aggregated using sum or average)— , the destination directory paths for data storage, and the timestamp of the last update (<em>last_watermark</em>), among others.</p><h4>silver_control</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c5WUYa0mm1l3YQVM9Vaprw.png" /><figcaption>silver_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/silver_control.png">See on GitHub</a>)</figcaption></figure><p>It contains one row for each table to be created in the <em>Silver </em>layer, starting from the files located in the path specified in the <em>source_folder_name </em>column.</p><h4>gold_control</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*04TlbPR7D7h5BnkGiXl5Gg.png" /><figcaption>gold_control (<a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture/blob/main/assets/gold_control.png">See on GitHub</a>)</figcaption></figure><p>It contains one row for each table to be created in the <em>Gold </em>layer lakehouse. The <em>notebook </em>column indicates the name of the notebook responsible for updating its data.</p><p>In general, the <em>last_watermark </em>field includes a default value, which allows for the retrieval of historical data during the first execution. The <em>is_active </em>parameter makes it possible to enable or disable data updates without needing to delete records from the tables.</p><p>The stored procedures allow updating the <em>last_watermark </em>and execution statistics after each data refresh.</p><h3>Lakehouses and Variable Library</h3><p>The next step consists of <strong>creating the lakehouses</strong> where the data corresponding to each stage of the architecture will be stored.</p><p>Once created (and initially left empty), their unique identifiers — along with that of the metadata warehouse — are recorded in a new <strong>variable library</strong> item.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/886/1*TjANP0LVPUDYpbAJ_-JekA.png" /></figure><p>This avoids hardcoding static values for the source and target lakehouse identifiers in the pipeline activities, making it possible to incorporate these variables dynamically into notebook execution.</p><h3>Bronze Layer</h3><p>In the <strong><em>Bronze </em></strong>layer, information is extracted directly from the ESIOS API and stored in its raw format (JSON) within the <strong><em>LH_Bronze </em></strong>lakehouse.</p><p>To achieve this, the <strong><em>PL_Bronze</em></strong><em> </em>pipeline is used:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FqpOldZ8y5nDk98D899Gng.png" /></figure><p>In the pipeline configuration section, the path to the <em>Bronze </em>layer lakehouse is added using the variable library created in the previous section.</p><p>Regarding the pipeline activities:</p><ul><li>The first activity sets the <strong>end_date variable</strong> to the current system date. This date is <strong>key to the incremental refresh strategy using watermarks</strong>, as it defines the limit of the range to request from the API and will serve as the new watermark once the data saving process is complete.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/585/1*nuxJazRx9_gfRCY8vktWDQ.png" /></figure><ul><li>The <strong>LookUp </strong>activity performs a SQL query on the <em>bronze_control </em>table of the metadata warehouse, retrieving all rows where <em>is_active </em>equals 1.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/603/1*Q1Hsk6zHqKqbY8K-Tr8gPw.png" /></figure><p>The “First row only” checkbox is unchecked to indicate that the entire table is required.</p><ul><li>The <strong>ForEach </strong>receives the output from the previous activity in order to iterate over the table rows, where each row contains the data for an indicator.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/962/1*W1cd_FBQvihmnuCcPPzddA.png" /></figure><ul><li>Inside the ForEach, the <strong><em>NB_Bronze </em></strong>notebook is invoked.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/753/1*ZVnujbUQHRrwrFceMaVJZg.png" /></figure><p>This notebook <strong>captures the specific parameters of each iteration</strong> to execute the API call.</p><p>In addition, it incorporates an overlap period (<em>overlap_minutes</em>) into the start date to capture potential late-arriving data, compensating for the fact that the source does not provide an explicit update date. Finally, it saves the data into the lakehouse.</p><pre># Import libraries<br>import requests<br>import json<br>import os<br>from datetime import datetime, timedelta<br><br># Define parameters that will come from pipeline during execution<br><br>## Pipeline will define end_date as base parameter for all activities in the pipeline using @utcNow()<br>end_date = None<br><br>## The next parameters will come from bronze_control table in metadata warehouse, retrieved using pipeline activities<br>esios_indicator_id = None<br>esios_indicator_short_name = None<br>locale_language = None<br>time_trunc = None<br>time_agg = None<br>target_lakehouse_path = None<br>target_landing_path = None<br>target_folder_name = None<br>file_type = None<br>last_watermark = None<br>overlap_minutes = None<br><br># Define other variables for API call<br><br>## Start and end dates in ISO8601 format with time zone and considering overlap minutes for start date<br>overlap_minutes = int(overlap_minutes)<br>end_date_dt = datetime.fromisoformat(end_date.replace(&quot;Z&quot;, &quot;+00:00&quot;))<br>start_date_dt = datetime.fromisoformat(last_watermark.replace(&quot;Z&quot;, &quot;+00:00&quot;)) - timedelta(minutes=overlap_minutes)<br><br>start_date = start_date_dt.strftime(&quot;%Y-%m-%dT%H:%M:%SZ&quot;)<br>end_date = end_date_dt.strftime(&quot;%Y-%m-%dT%H:%M:%SZ&quot;)<br><br>## Execution date for file name<br>execution_date = end_date_dt.strftime(&quot;%Y-%m-%d_%H-%M-%S&quot;)<br><br>## Other variables for the request<br>url = f&quot;https://api.esios.ree.es/indicators/{esios_indicator_id}&quot;<br>headers = {&#39;Accept&#39;: &#39;application/json; application/vnd.esios-api-v1+json&#39;,<br>           &#39;Content-Type&#39;: &#39;application/json&#39;,<br>           &#39;x-api-key&#39;: f&#39;{api_key}&#39;}<br>params = {<br>    &quot;locale&quot;: f&quot;{locale_language}&quot;,<br>    &quot;start_date&quot;: f&quot;{start_date}&quot;,<br>    &quot;end_date&quot;: f&quot;{end_date}&quot;,<br>    &quot;time_trunc&quot;: f&quot;{time_trunc}&quot;,<br>    &quot;time_agg&quot;: f&quot;{time_agg}&quot;<br>}<br><br># Make API call<br>try:<br>    response = requests.get(url, headers=headers, params=params)<br>    response.raise_for_status()<br>    data = response.json()<br>    if not data:<br>        raise ValueError(f&quot;No data returned for indicator {esios_indicator_id} between {start_date} and {end_date}&quot;)<br>except requests.exceptions.RequestException as e:<br>    raise RuntimeError(f&quot;Error fetching data from API: {e}&quot;)<br><br># Define paths<br>file_path = f&quot;{target_lakehouse_path}/{target_landing_path}/{target_folder_name}/indicator_{esios_indicator_id}_{execution_date}.{file_type}&quot;<br>directory_path = f&quot;{target_lakehouse_path}/{target_landing_path}/{target_folder_name}&quot;<br><br># Create folder if it doesnt exists and save json file <br>try:<br>    notebookutils.fs.mkdirs(directory_path)<br>    notebookutils.fs.put(file_path, json.dumps(data, indent=4), True)<br>    print(f&quot;Data successfully saved to {file_path}&quot;)<br>except Exception as e:<br>    raise RuntimeError(f&quot;Error saving file to {file_path}. Details: {e}&quot;)</pre><p>This approach is viable because all ESIOS indicators share the same parameters and return information in a homogeneous format.</p><ul><li>Finally, if all iterations of the ForEach loop complete successfully, the <strong>stored procedure</strong> <em>SP_update_bronze_watermark </em>is invoked to update the <em>last_watermark </em>field with the value of the <em>end_date </em>variable. This will serve as the starting point for the next execution.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*oMjtFpnB1gSTovEw13GQ1w.png" /></figure><p>Once the pipeline is executed, the data will automatically appear in the lakehouse, organized within the folders specified in the control table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/333/1*7nGaX5eQ5qwLlKhW4TDsTQ.png" /></figure><p>If the metadata warehouse is queried again after the process, it can be verified that the date has been updated correctly:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-0OlsUXrsWsg575lQ-Nt_w.png" /></figure><p>This concludes the first part of this series.</p><p>All code corresponding to this stage can be consulted in <a href="https://github.com/MartaCasdelg/ESIOS-Fabric-Medallion-Architecture"><strong>my GitHub repository</strong></a>.</p><p><a href="https://medium.com/@martacasdelg/how-to-build-a-metadata-driven-medallion-architecture-in-microsoft-fabric-part-2-c2a3fbab4021"><strong>Part 2</strong></a><strong> is now available. </strong>It covers the <em>Silver </em>and <em>Gold </em>layers, as well as the <em>Direct Lake</em> model and the final Power BI report.</p><p>Thank you for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6297422fb591" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[CTEs Recursivas en SQL]]></title>
            <link>https://medium.com/@martacasdelg/ctes-recursivas-en-sql-28c7d9f61dd8?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/28c7d9f61dd8</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[cte]]></category>
            <category><![CDATA[hierarchical-data]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[common-table-expressions]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Thu, 06 Nov 2025 21:00:42 GMT</pubDate>
            <atom:updated>2026-11-04T08:49:38.417Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zs5S5j3-GTFLRf_PQKUpsQ.jpeg" /><figcaption>Foto de <a href="https://unsplash.com/es/@adibbehrooz?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Mohammad Bagher Adib Behrooz</a> en <a href="https://unsplash.com/es/fotos/mujer-en-la-construccion-XHI-S_xWK28?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>En un post anterior expliqué que una <strong>CTE </strong>(Common Table Expression) es un conjunto de resultados temporal que podemos utilizar dentro de una instrucción SELECT, INSERT, UPDATE o DELETE.</p><p>También mencioné que las CTE nos aportan muchos beneficios, entre ellos, el de crear <strong>consultas recursivas</strong>. Esto es, consultas en las que la CTE se hace referencia a sí misma.</p><p>¿Para qué nos sirve esto? Pues resulta que es especialmente útil cuando trabajamos con datos jerárquicos. Lo veremos con un caso práctico.</p><h4>SINTAXIS</h4><p>Empecemos repasando la <strong>sintaxis de una</strong> <strong>CTE no recursiva</strong>. Recordemos que:</p><ul><li>Siempre comienza con la palabra WITH + un nombre para luego poder referenciarla + AS. Opcionalmente, se puede escribir el nombre de las columnas entre paréntesis después del nombre.</li><li>Se escribe la consulta que conformará la CTE entre paréntesis.</li><li>Hecho lo anterior, se escribe otra consulta, en la cual hacemos referencia a la CTE.</li></ul><pre>WITH cte_name [(column_name,...)] AS<br>    (CTE_definition)<br><br>SELECT ... FROM cte_name;</pre><p>Ahora bien, ¿en qué cambia la<strong> sintaxis de una</strong> <strong>CTE recursiva</strong>? Pues no varía demasiado, como verás a continuación:</p><pre><br>WITH RECURSIVE cte_name [(column, ...)] AS (<br>    [non-recursive_term] -- Anchor member<br>    UNION ALL<br>    [recursive_term] -- Recursive member<br>)<br><br>SELECT ... FROM cte_name;</pre><p>La consulta vuelve a comenzar con WITH, pero debemos añadir la palabra clave <strong>RECURSIVE</strong>. Luego sigue con AS y entre paréntesis se define la consulta, que esta vez tiene <strong>dos partes</strong>.</p><p>La primera parte (<strong>“anchor member”</strong> o “ancla”) es la consulta base de la CTE, mientras que la segunda (<strong>“recursive member”</strong>) es la consulta en la que se hace referencia a la propia CTE.</p><p>Ambas se unen por medio de <strong>UNION ALL</strong>.</p><p>Por fuera de la CTE, y al igual que en las no recursivas, tenemos la consulta en la que se referencia la CTE.</p><blockquote><strong>¡Importante! Si estás usando SQL Server</strong> <strong>no tienes que usar RECURSIVE </strong>en la consulta. De hacerlo, saltará un error.</blockquote><h4>CÓMO FUNCIONA</h4><p>Antes de ver el caso práctico, quiero mostrar un ejemplo sencillo en el que usaremos las CTE recursivas para crear la secuencia de Fibonacci (una secuencia de números descrita por Leonardo de Pisa que comenzaba por los términos 0 y 1, y continuaba con la suma de los dos términos anteriores).</p><p>Tenemos la siguiente consulta:</p><pre>WITH RECURSIVE fib (x, y) AS (<br>    SELECT 0, 1<br>    UNION ALL<br>    SELECT y, (x+y) FROM fib <br>)<br><br>SELECT x FROM fib LIMIT 10;</pre><p>Aquí hemos creado una CTE llamada “fib” formada por dos columnas “x” e “y”.</p><p>Dentro encontramos los dos miembros de la CTE recursiva unidos por un UNION ALL, y al final tenemos la consulta que traerá de vuelta los valores de “x” de la CTE, pero vamos a olvidarnos de esta parte de momento.</p><p>En su lugar, vamos a tratar de imaginar cómo se irá creando la tabla resultante de la CTE recursiva.</p><p>Empezamos por el “anchor member”, que sirve de base de la CTE y sabemos que tiene el valor 0 para “x” y 1 para “y”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/176/1*i30S2iV0iwHFM798AY_Nxw.png" /></figure><p>Ahora debemos mirar el “recursive member”, analizar cuál sería el resultado, y añadirlo a la tabla anterior (ya que ambos miembros están unidos por UNION ALL).</p><p>En este caso, “x” sería igual al valor de “y” (1) e “y” sería la suma de “x” e “y” del “anchor member” (0+1=1).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/178/1*JC54IulKWVqS8zs_jY0w6A.png" /></figure><p>¿Y ya está? Pues no, <strong>aquí empieza lo interesante</strong>. Fíjate en que el miembro recursivo se está refiriendo a la propia CTE de la que forma parte en la cláusula FROM.</p><p>¿En qué influye esto exactamente? Influye en que la tabla de la cual se origina el cálculo del miembro recursivo está formada por el propio resultado que genera por sí mismo.</p><p>Es decir, <strong>se forma un bucle</strong> en el que el miembro recursivo genera un resultado y vuelve a ejecutarse sobre los resultados obtenidos, generando más y más filas.</p><p>Volviendo a la tabla anterior, esto significa que ahora el miembro recursivo se volverá a ejecutar sobre esas dos filas, añadiendo una fila más de resultados, y así sucesivamente.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/316/1*ilf2cWj7ImZkUf4LwzN3Ew.png" /></figure><p>Entonces, tenemos una tabla en la que se crean registros infinitos. <strong>¿Cómo rompemos el bucle?</strong></p><p>En este caso usando LIMIT 10 al final de la consulta para delimitar el resultado a solo las primeras 10 filas (si usas SQLServer deberías usar SELECT TOP 10 en lugar de LIMIT).</p><p>Lo importante es que <strong>siempre hay que incluir algún elemento que frene el bucle.</strong></p><p>Por tanto, el resultado de la consulta al completo es:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/105/1*j9OUyqQ6Z5i9MdYmNcLufw.png" /></figure><p>También se puede utilizar la opción <strong>MAXRECURSION </strong>para especificar el <strong>número máximo de recursiones (repeticiones)</strong> permitidas para la CTE.</p><p>Se incluiría de la siguiente manera, donde “n” debe ser un entero positivo entre 0 y 32767. El valor 0 indica que no hay límite de repercusiones, lo que puede producir un bucle infinito.</p><pre>[recursive query]<br><br>SELECT ... FROM cte_name OPTION(MAXRECURSION n);</pre><p>El proceso que acabamos de ver se podría resumir en el siguiente esquema:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/372/1*NdxumF3wQ32sfB1EomsmEA.png" /><figcaption>esquema de sqlservertutorial.net</figcaption></figure><p>Espero que este ejemplo te haya ayudado a entender cómo funciona la CTE recursiva.</p><p>Pasemos al caso práctico.</p><h4>CASO PRÁCTICO</h4><p>Es habitual utilizar CTEs recursivas cuando nos encontramos ante <strong>datos jerárquicos</strong>, por ejemplo, una tabla en la que se muestra una lista de empleados y sus respectivos jefes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/321/1*4YcxfbGDnCnktaKD_zxlAA.png" /></figure><p>La columna “jefe_id” provee el identificador del jefe del empleado en cuestión, que se puede encontrar en la propia tabla.</p><p>Por ejemplo, el jefe de Lucía Fernández tiene id 3. Ese id corresponde a Mauro González.</p><p>Por otra parte, si en “jefe_id” aparece el valor NULL, podemos concluir que esa persona no tiene jefe, por lo que ocupa la primera posición del organigrama de la empresa. Es el caso de Carlos Ortega.</p><p>A continuación <strong>os mostraré cómo se puede utilizar una CTE recursiva para asignar niveles de jerarquía</strong> a cada empleado.</p><pre>WITH RECURSIVE organigrama AS (<br> SELECT<br>  id,<br>  nombre,<br>  jefe_id,<br>  0 AS nivel_jerarquico<br> FROM<br>  empleados<br> WHERE<br>  jefe_id IS NULL<br><br> UNION ALL<br><br> SELECT<br>  e.id,<br>  e.nombre,<br>  e.jefe_id,<br>  nivel_jerarquico + 1<br> FROM<br>  empleados e<br>  INNER JOIN organigrama o<br>  ON e.jefe_id = o.id<br>)<br><br>SELECT<br> o.id,<br> o.nombre,<br> e.nombre AS jefe,<br> o.nivel_jerarquico<br>FROM<br> organigrama o<br> LEFT JOIN empleados e <br> ON o.jefe_id = e.id<br>ORDER BY<br> o.nivel_jerarquico,<br> o.jefe_id</pre><p>Para entender qué sucede en esta consulta comenzaré analizando el código de la CTE y, por último, veremos qué resultados obtenemos al ejecutar la consulta al completo.</p><p>En primer lugar, el <strong>“anchor member”</strong> <strong>devolverá los datos del</strong> <strong>presidente o dueño de la compañía</strong>, asignándole el valor 0 en una nueva columna llamada “nivel jerárquico”.</p><p>Para seleccionarlo ponemos la condición WHERE, que devolverá únicamente los datos de la persona que no tiene jefe (jefe_id IS NULL).</p><p>Si aislamos esta parte de la consulta, obtendremos la siguiente tabla:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/436/1*lHrVSuzyssqJFfpL2s1iRg.png" /></figure><p>A partir de este dato, el<strong> “recursive member” devolverá la información de los</strong> <strong>empleados que están bajo su mando</strong> y les asignará el siguiente nivel en la jerarquía (0 + 1 = 1).</p><p>¿Cómo lo hace? Lo hace seleccionando los empleados en los que el código de su jefe (jefe_id) aparece como id en “anchor member”.</p><p>Por eso se hace un INNER JOIN entre “empleados” (que es la tabla original del problema) y la propia CTE. Con eso obtendremos el siguiente nivel de jerarquía:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*33xU0otkfnI-V3iXzoY0og.png" /></figure><p>Ahora bien, como esto es una CTE recursiva, se ha creado un <strong>bucle </strong>en el que el miembro recursivo sigue devolviendo resultados (sigue añadiendo subordinados a la tabla y asignándoles nuevos niveles de jerarquía a partir de los datos obtenidos en la recursión anterior) hasta que ya no hay más coincidencias en la condición del INNER JOIN (e.jefe_id = o.id).</p><p><strong>Hasta aquí el análisis de lo que sucede dentro de la CTE</strong>.</p><p>Entendido esto, solo queda ver la parte final de la consulta, en la que seleccionamos las columnas id, nombre y nivel jerárquico de la CTE y, además, añadimos el nombre del jefe haciendo un LEFT JOIN entre la tabla “empleados” y la CTE (que solo tiene su id, no su nombre).</p><p>Este sería el <strong>resultado final</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/487/1*kWwcxoroUM7yLbB3qBZwag.png" /></figure><p>¡Muchas gracias por leerme! Espero que os haya servido de ayuda.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=28c7d9f61dd8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Recursive CTE in SQL]]></title>
            <link>https://medium.com/@martacasdelg/ctes-recursivas-in-sql-1b74e4b34c13?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/1b74e4b34c13</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[cte]]></category>
            <category><![CDATA[hierarchical-data]]></category>
            <category><![CDATA[common-table-expressions]]></category>
            <category><![CDATA[data-modeling]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Thu, 06 Nov 2025 20:53:38 GMT</pubDate>
            <atom:updated>2026-11-04T08:49:08.740Z</atom:updated>
            <content:encoded><![CDATA[<h3>Recursive CTE in SQL: A Practical Guide</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zs5S5j3-GTFLRf_PQKUpsQ.jpeg" /><figcaption>Photo from <a href="https://unsplash.com/es/@adibbehrooz?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Mohammad Bagher Adib Behrooz</a> in <a href="https://unsplash.com/es/fotos/mujer-en-la-construccion-XHI-S_xWK28?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>In a previous post, I explained that a <strong>CTE </strong>(Common Table Expression) is a temporary result set that we can use within a SELECT, INSERT, UPDATE, or DELETE statement.</p><p>I also mentioned that CTEs provide many benefits, including the ability to create <strong>recursive queries</strong>. That is, queries in which the CTE references itself.</p><p>What is this useful for? It turns out to be especially helpful when working with hierarchical data. We’ll take a look at it with a practical example.</p><h4>SINTAXIS</h4><p>Let’s start by reviewing the <strong>syntax of a</strong> <strong>non-recursive CTE</strong>. Let’s remember that:</p><ul><li>It always begins with the word WITH + a name we can later reference + AS. Optionally, the column names can be written in parentheses after the name.</li><li>The query that forms the CTE is written inside parentheses.</li><li>Once that’s done, another query is written, in which we reference the CTE.</li></ul><pre>WITH cte_name [(column_name,...)] AS<br>    (CTE_definition)<br><br>SELECT ... FROM cte_name;</pre><p>Now then, how does the <strong>syntax of a recursive CTE</strong> change? Well, it doesn’t vary too much, as you’ll see below:</p><pre>WITH RECURSIVE cte_name [(column, ...)] AS (<br>    [non-recursive_term] -- Anchor member<br>    UNION ALL<br>    [recursive_term] -- Recursive member<br>)<br><br>SELECT ... FROM cte_name;</pre><p>The query begins again with WITH, but we must add the <strong>RECURSIVE </strong>keyword. Then it continues with AS, and inside the parentheses the query is defined, which this time has <strong>two parts</strong>.</p><p>The first part (<strong>“anchor member”</strong>) is the base query of the CTE, while the second (<strong>“recursive member”</strong>) is the query in which the CTE references itself.</p><p>Both parts are joined by <strong>UNION ALL</strong>.</p><p>Outside of the CTE, just as with non-recursive ones, we have the query in which the CTE is referenced.</p><blockquote><strong>Important! If you’re using SQL Server, you don’t need to use RECURSIVE </strong>in the query. Doing so will throw an error.</blockquote><h4>HOW IT WORKS</h4><p>Before looking at the practical case, I want to show a simple example in which we’ll use recursive CTEs to create the Fibonacci sequence (a sequence of numbers described by Leonardo of Pisa that begins with the terms 0 and 1, and continues with the sum of the two previous terms).</p><p>We have the following query:</p><pre>WITH RECURSIVE fib (x, y) AS (<br>    SELECT 0, 1<br>    UNION ALL<br>    SELECT y, (x+y) FROM fib <br>)<br><br>SELECT x FROM fib LIMIT 10;</pre><p>Here we’ve created a CTE called “fib” made up of two columns, “x” and “y”.</p><p>Inside, we find the two members of the recursive CTE joined by a UNION ALL, and at the end we have the query that will return the values of “x” from the CTE — but let’s ignore that part for now.</p><p>Instead, let’s try to imagine how the result table of the recursive CTE will be built.</p><p>We start with the anchor member, which serves as the base of the CTE and we know it has the value 0 for “x” and 1 for “y”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/176/1*i30S2iV0iwHFM798AY_Nxw.png" /></figure><p>Now we need to look at the recursive member, analyze what its result would be, and add it to the previous table (since both members are joined by UNION ALL).</p><p>In this case, “x” would be equal to the value of “y” (1), and “y” would be the sum of “x” and “y” from the anchor member (0+1=1).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/178/1*JC54IulKWVqS8zs_jY0w6A.png" /></figure><p>And that’s it? Well, no — <strong>this is where things get interesting</strong>. Notice that the recursive member is referring to the very same CTE it belongs to in the FROM clause.</p><p>How does this affect things exactly? It affects the fact that the table from which the recursive member’s calculation originates is made up of the result it generates itself.</p><p>In other words, <strong>a loop is formed</strong> in which the recursive member generates a result and then runs again over the results obtained, generating more and more rows.</p><p>Going back to the previous table, this means that now the recursive member will run again over those two rows, adding an additional row of results, and so on.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/316/1*ilf2cWj7ImZkUf4LwzN3Ew.png" /></figure><p>So, we end up with a table in which infinite records are created. <strong>How do we break the loop?</strong></p><p>In this case, by using LIMIT 10 at the end of the query to restrict the result to only the first 10 rows (if you’re using SQL Server, you should use SELECT TOP 10 instead of LIMIT).</p><p>The important thing is that <strong>you must always include some element that stops the loop</strong>.</p><p>Therefore, the result of the full query is:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/105/1*j9OUyqQ6Z5i9MdYmNcLufw.png" /></figure><p>You can also use the <strong>MAXRECURSION </strong>option to specify the <strong>maximum number of recursions (repetitions)</strong> allowed for the CTE.</p><p>It would be included as follows, where “n” must be a positive integer between 0 and 32,767. A value of 0 indicates that there is no recursion limit, which can cause an infinite loop.</p><pre>[recursive query]<br><br>SELECT ... FROM cte_name OPTION(MAXRECURSION n);</pre><p>The process we’ve just seen can be summarized in the following diagram:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/372/1*NdxumF3wQ32sfB1EomsmEA.png" /><figcaption>diagram from sqlservertutorial.net</figcaption></figure><p>I hope this example has helped you understand how the recursive CTE works.</p><p>Let’s move on to the practical case.</p><h4>PRACTICAL CASE</h4><p>It’s common to use recursive CTEs when working with <strong>hierarchical data</strong> — for example, a table that shows a list of employees and their respective managers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/321/1*4YcxfbGDnCnktaKD_zxlAA.png" /><figcaption>(Apologies for the table and column names being in spanish — I originally wrote this blog in that language)</figcaption></figure><p>The “jefe_id” column provides the identifier of the manager of the employee in question, which can be found in the same table.</p><p>For example, the manager of Lucía Fernández has id 3. That id corresponds to Mauro González.</p><p>On the other hand, if the value NULL appears in “jefe_id”, we can conclude that this person has no manager, and therefore occupies the top position in the company’s organizational chart. This is the case for Carlos Ortega.</p><p>Next, <strong>I’ll show you how a recursive CTE can be used to assign hierarchy levels</strong> to each employee.</p><pre>WITH RECURSIVE organigrama AS (<br> SELECT<br>  id,<br>  nombre,<br>  jefe_id,<br>  0 AS nivel_jerarquico<br> FROM<br>  empleados<br> WHERE<br>  jefe_id IS NULL<br><br>UNION ALL<br><br> SELECT<br>  e.id,<br>  e.nombre,<br>  e.jefe_id,<br>  nivel_jerarquico + 1<br> FROM<br>  empleados e<br>  INNER JOIN organigrama o<br>  ON e.jefe_id = o.id<br>)<br><br>SELECT<br> o.id,<br> o.nombre,<br> e.nombre AS jefe,<br> o.nivel_jerarquico<br>FROM<br> organigrama o<br> LEFT JOIN empleados e <br> ON o.jefe_id = e.id<br>ORDER BY<br> o.nivel_jerarquico,<br> o.jefe_id</pre><p>To understand what happens in this query, I’ll start by analyzing the CTE code, and finally we’ll look at the results we obtain when executing the full query.</p><p>First, <strong>the anchor member will return the data of the president or owner of the company</strong>, assigning the value 0 in a new column called “hierarchy level.”</p><p>To select this person, we use the WHERE condition, which will return only the data of the person who has no manager (jefe_id IS NULL).</p><p>If we isolate this part of the query, we’ll obtain the following table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/436/1*lHrVSuzyssqJFfpL2s1iRg.png" /></figure><p>Starting from this data, the <strong>recursive member will return the information of the employees who are under their supervision</strong> and will assign them the next level in the hierarchy (0 + 1 = 1).</p><p>How does it do this? It selects the employees whose manager code (jefe_id) appears as an id in the anchor member.</p><p>That’s why an INNER JOIN is performed between “empleados” (which is the original table in this problem) and the CTE itself. With that, we’ll obtain the next hierarchy level:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*33xU0otkfnI-V3iXzoY0og.png" /></figure><p>Now then, since this is a recursive CTE, a <strong>loop </strong>is created in which the recursive member keeps returning results (it keeps adding subordinates to the table and assigning them new hierarchy levels based on the data obtained in the previous recursion) until there are no more matches in the INNER JOIN condition (e.jefe_id = o.id).</p><p><strong>That’s the analysis of what happens inside the CTE.</strong></p><p>With that understood, all that’s left is to look at the final part of the query, where we select the id, name, and hierarchy level columns from the CTE, and we also add the manager’s name by performing a LEFT JOIN between the “empleados” table and the CTE (because the CTE only contains the id, not the name).</p><p>This is the <strong>final result</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/487/1*kWwcxoroUM7yLbB3qBZwag.png" /></figure><p>Thank you very much for reading! I hope you found it helpful.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1b74e4b34c13" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Common Table Expression (CTE) en SQL]]></title>
            <link>https://medium.com/@martacasdelg/common-table-expression-cte-en-sql-f2c79ad691ee?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/f2c79ad691ee</guid>
            <category><![CDATA[cte]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Tue, 02 Sep 2025 20:35:13 GMT</pubDate>
            <atom:updated>2025-09-02T20:35:13.856Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*frlPPQa8P5uy5WyJsGYoDw.jpeg" /><figcaption>Foto de <a href="https://unsplash.com/es/@connorxwang?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Connor Wang</a> en <a href="https://unsplash.com/es/fotos/hombre-con-abrigo-negro-sentado-en-un-banco-JJTTTCa2qFo?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><p>Una <strong>CTE </strong>(Common Table Expression) es un conjunto de resultados temporal que podemos utilizar dentro de una instrucción SELECT, INSERT, UPDATE o DELETE.</p><p>Ese conjunto de resultados no se almacena en la base de datos, por lo que solo se puede utilizar dentro de la propia consulta en la que se crea la CTE. No obstante, nos aportan muchos beneficios, entre ellos:</p><ul><li>Nos permiten hacer referencia al mismo conjunto de resultados temporales varias veces en la consulta. Esto es, las CTE<strong> son reusables</strong>. En lugar de tener que declarar la misma subconsulta cada vez que la necesitemos, podemos crear una CTE y hacer referencia a la misma las veces que precisemos.</li><li><strong>Permiten crear consultas recursivas</strong>, esto es, consultas en las que la CTE se hace referencia a sí misma. Esto es especialmente útil cuando trabajamos con datos jerárquicos.</li><li><strong>Hacen que nuestro código sea más fácil de leer y entender</strong>, pues permiten separar consultas complejas en varias partes. Además, como las CTE son reusables, podemos escribir menos código al evitar el uso de subconsultas.</li></ul><p>En este artículo <strong>me centraré en las</strong> <strong>CTE no recursivas</strong>, pero planeo publicar un artículo sobre <strong>CTE recursivas próximamente</strong>.</p><p>¡No olvides suscribirte si te interesa leerlo!</p><h4><strong>SINTAXIS</strong></h4><p>La sintaxis de una CTE es bastante simple:</p><ul><li>Siempre comienza con la palabra WITH + un nombre para luego poder referenciarla + AS. Opcionalmente, se puede escribir el nombre de las columnas entre paréntesis después del nombre.</li><li>Se escribe la consulta que conformará la CTE entre paréntesis.</li><li>Hecho lo anterior, se escribe otra consulta, en la cual hacemos referencia a la CTE.</li></ul><pre>WITH cte_name [(column_name,...)] AS<br>    (CTE_definition)<br><br>SQL_statement;</pre><p>También podemos usar <strong>múltiples CTE</strong> en la misma consulta, separándolas con una coma:</p><pre>WITH cte_name_1 [(column_name,...)] AS<br>    (CTE_definition_1),<br><br>cte_name_2 [(column_name,...)] AS<br>    (CTE_definition_2),<br><br>cte_name_n [(column_name,...)] AS<br>    (CTE_definition_n)<br><br>SQL_statement;</pre><h4><strong>EJEMPLO: Cómo eliminar duplicados con la ayuda de una CTE</strong></h4><p>Hasta aquí todo ha sido muy teórico. Veamos un ejemplo práctico sobre cómo eliminar duplicados de una tabla usando una CTE.</p><p>Supongamos que tenemos la siguiente tabla con los datos de los empleados de una compañía:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/259/1*3hhDg8yxTzH4xBTG5srj4A.png" /><figcaption>Imagen del autor</figcaption></figure><p>Primero<strong> identificamos si hay registros duplicados</strong> en la tabla. Para ello, podemos escribir esta consulta:</p><pre>WITH cte_emp AS (<br> SELECT<br>  id,<br>  nombre,<br>  salario,<br>  ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num<br> FROM<br>  empleados<br>)<br><br>SELECT<br> *<br>FROM<br> cte_emp;</pre><p>Como podrás observar, estamos creando una CTE llamada “cte_emp” en la que seleccionamos todas las columnas de la tabla anterior y usamos ROW_NUMBER() para numerar todas las filas según su id. De esta manera, cualquier valor de la columna “row_num” superior a 1 es un duplicado.</p><p>En la parte final de la consulta seleccionamos todas las columnas de “cte_emp”, obteniendo la siguiente tabla:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/343/1*wRPQ06ZNx_zSKP8NGz5g0Q.png" /><figcaption>Imagen del autor</figcaption></figure><p>A simple vista vemos que<strong> </strong>hay valores superiores a 1<strong> </strong>en la columna “row_num”, por lo que<strong> hay duplicados</strong>.</p><p>Ahora escribiremos una consulta para <strong>seleccionar únicamente los duplicados</strong> de la tabla. Para ello, incluiremos la cláusula WHERE en la consulta anterior.</p><pre>WITH cte_emp AS (<br> SELECT<br>  id,<br>  nombre,<br>  salario,<br>  ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num<br> FROM<br>  empleados<br>)<br><br>SELECT<br> *<br>FROM<br> cte_emp<br>WHERE<br> row_num &gt; 1;</pre><p>De la cual obtenemos:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/338/1*dqB1qkBh5MJyUdkq0s6cMA.png" /><figcaption>Imagen del autor</figcaption></figure><p>Ahora que los tenemos seleccionados, pasaremos a <strong>eliminar los duplicados</strong> cambiando la instrucción SELECT por DELETE.</p><pre>WITH cte_emp AS (<br> SELECT<br>  id,<br>  nombre,<br>  salario,<br>  ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num<br> FROM<br>  empleados<br>)<br><br>DELETE<br>FROM<br> cte_emp<br>WHERE<br> row_num &gt; 1;</pre><p>Al ejecutar la consulta se habrán eliminado los registros duplicados.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/366/1*NccT3BJ51QvkBJrsGqXU1w.png" /><figcaption>Imagen del autor</figcaption></figure><p>Podemos <strong>comprobar</strong> que se han eliminado correctamente volviendo a ejecutar la consulta que devolvía los registros repetidos.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/232/1*vnF2OLxJhFBxNoWjtaHt9Q.png" /><figcaption>Imagen del autor</figcaption></figure><p>No aparece ningún resultado, por lo que ya no hay duplicados en la tabla.</p><h4>REFERENCIAS</h4><p><a href="https://towardsdatascience.com/the-ultimate-guide-to-sql-ctes-12a065187a15">The Ultimate Guide to SQL CTEs. Master common table expressions in 5… | by Skyler Dale | Towards Data Science</a></p><p><a href="https://towardsdatascience.com/sql-for-data-analysis-subquery-vs-cte-699ef629d9eb">SQL for Data Analysis: Subquery vs. CTE | by Aaron Zhu | Towards Data Science</a></p><p><a href="https://medium.com/@arjunuttamsharma561/cte-in-sql-server-3d1eb3c3e7ac">https://medium.com/@arjunuttamsharma561/cte-in-sql-server-3d1eb3c3e7ac</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f2c79ad691ee" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Common Table Expression (CTE) in SQL]]></title>
            <link>https://medium.com/@martacasdelg/common-table-expression-cte-in-sql-c66403242ee0?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/c66403242ee0</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[cte]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Tue, 02 Sep 2025 20:34:49 GMT</pubDate>
            <atom:updated>2025-09-02T20:34:49.812Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*frlPPQa8P5uy5WyJsGYoDw.jpeg" /><figcaption>Foto de <a href="https://unsplash.com/es/@connorxwang?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Connor Wang</a> en <a href="https://unsplash.com/es/fotos/hombre-con-abrigo-negro-sentado-en-un-banco-JJTTTCa2qFo?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><p>A <strong>CTE </strong>(Common Table Expression) is a temporary result set that we can use within a SELECT, INSERT, UPDATE or DELETE statement.</p><p>This result set is not stored in the database, which means it can only be used within the query where the CTE is defined. However, CTEs provide several benefits, such as:</p><ul><li>They allow us to reference the same temporary result set multiple times within a query. In other words, <strong>CTEs are reusable</strong>. Instead of writing the same subquery every time, we can define a CTE once and reference it as many times as needed.</li><li><strong>They enable recursive queries</strong>, meaning the CTE can reference itself. This is particularly useful when working with hierarchical data.</li><li><strong>They make our code easier to read and understand</strong>, since they let us break down complex queries into smaller parts. And because CTEs are reusable, we can write less code by avoiding repeated subqueries.</li></ul><p>In this article, <strong>I’ll focus on non-recursive CTEs</strong>, but I plan to publish a separate article about <strong>recursive CTEs soon</strong>.</p><p>Don’t forget to subscribe if you’re interested in reading it!</p><h4>SYNTAX</h4><p>The syntax of a CTE is quite simple:</p><ul><li>It always begins with the keyword WITH + a name (to reference it later) + AS. Optionally, you can write the column names in parentheses right after the name.</li><li>The query that defines the CTE is written inside parentheses.</li><li>After that, another query is written, in which we reference the CTE.</li></ul><pre>WITH cte_name [(column_name,...)] AS<br>    (CTE_definition)<br><br>SQL_statement;</pre><p>We can also use <strong>multiple CTE</strong> in the same query, separating them with a comma:</p><pre>WITH cte_name_1 [(column_name,...)] AS<br>    (CTE_definition_1),<br><br>cte_name_2 [(column_name,...)] AS<br>    (CTE_definition_2),<br><br>cte_name_n [(column_name,...)] AS<br>    (CTE_definition_n)<br><br>SQL_statement;</pre><h4>EXAMPLE: How to Remove Duplicates Using a CTE</h4><p>So far, everything has been quite theoretical. Let’s look at a practical example of how to remove duplicates from a table using a CTE.</p><p>Suppose we have the following table with the employee data of a company:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/259/1*tebkN4v4aG6y86P0tCIorA.png" /><figcaption>Image by author</figcaption></figure><p>First, we <strong>identify if there are duplicate records</strong> in the table. To do this, we can write the following query:</p><pre>WITH cte_emp AS (<br> SELECT<br>  id,<br>  name,<br>  salary,<br>  ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num<br> FROM<br>  employees<br>)<br><br>SELECT<br> *<br>FROM<br> cte_emp;</pre><p>As you can see, we are creating a CTE called “cte_emp” in which we select all columns from the previous table and use ROW_NUMBER() to number all the rows based on their id. In this way, any value in the “row_num” column greater than 1 is a duplicate.</p><p>At the end of the query, we select all columns from “cte_emp”, obtaining the following table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/342/1*M2IExpRmp5Xht9QvMOSVCA.png" /><figcaption>Image by author</figcaption></figure><p>At first glance, we can see that there are values greater than 1 in the “row_num” column, which means <strong>there are duplicates</strong>.</p><p>Now we will write a query to <strong>select only the duplicates from the table</strong>. To do this, we will include a WHERE clause in the previous query.</p><pre>WITH cte_emp AS (<br> SELECT<br>  id,<br>  name,<br>  salary,<br>  ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num<br> FROM<br>  employees<br>)<br><br>SELECT<br> *<br>FROM<br> cte_emp<br>WHERE<br> row_num &gt; 1;</pre><p>From which we get:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/341/1*wNZ0E31krIEEb2TRIbLqAA.png" /><figcaption>Image by author</figcaption></figure><p>Now that we have them selected, we will proceed to <strong>delete the duplicates</strong> by replacing the SELECT statement with DELETE.</p><pre>WITH cte_emp AS (<br> SELECT<br>  id,<br>  name,<br>  salary,<br>  ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num<br> FROM<br>  employees<br>)<br><br>DELETE<br>FROM<br> cte_emp<br>WHERE<br> row_num &gt; 1;</pre><p>By executing the query, the duplicate records will have been deleted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/366/1*NccT3BJ51QvkBJrsGqXU1w.png" /><figcaption>Image by author</figcaption></figure><p>We can <strong>verify </strong>that they have been correctly deleted by re-running the query that returned the duplicate records.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/232/1*vnF2OLxJhFBxNoWjtaHt9Q.png" /><figcaption>Image by author</figcaption></figure><p>No results appear, which means there are no more duplicates in the table.</p><h4>REFERENCES</h4><p><a href="https://towardsdatascience.com/the-ultimate-guide-to-sql-ctes-12a065187a15">The Ultimate Guide to SQL CTEs. Master common table expressions in 5… | by Skyler Dale | Towards Data Science</a></p><p><a href="https://towardsdatascience.com/sql-for-data-analysis-subquery-vs-cte-699ef629d9eb">SQL for Data Analysis: Subquery vs. CTE | by Aaron Zhu | Towards Data Science</a></p><p><a href="https://medium.com/@arjunuttamsharma561/cte-in-sql-server-3d1eb3c3e7ac">https://medium.com/@arjunuttamsharma561/cte-in-sql-server-3d1eb3c3e7ac</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c66403242ee0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Translytical Task Flows: ¡marca, comenta y edita datos desde Power BI!]]></title>
            <link>https://medium.com/@martacasdelg/translytical-task-flows-marca-comenta-y-edita-datos-desde-power-bi-03416594b04f?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/03416594b04f</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[fabric]]></category>
            <category><![CDATA[translytical-task-flows]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Tue, 27 May 2025 21:04:28 GMT</pubDate>
            <atom:updated>2025-05-28T22:05:20.166Z</atom:updated>
            <content:encoded><![CDATA[<h4>Aprende a usar Translytical Task Flows en Power BI para escribir datos desde el informe con funciones personalizadas y botones interactivos.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/1*maznHsrbZVZ3ZUMtdlW2Og.gif" /><figcaption>image by author</figcaption></figure><p>Una de las novedades más destacadas de la actualización de mayo de 2025 para Power BI son los <strong>Translytical Task Flows</strong>, actualmente en versión preliminar pública.</p><p>Esta nueva funcionalidad habilita el uso de <strong>User Data Functions</strong> en los botones de acción, lo que permite a los usuarios realizar acciones personalizadas y automatizadas directamente desde el informe, como por ejemplo:</p><ul><li><strong>Modificar datos.</strong></li><li><strong>Añadir comentarios al reporte.</strong></li><li><strong>Enviar notificaciones dinámicas.</strong></li><li><strong>Activar flujos de aprobación.</strong></li><li><strong>Aplicar lógica personalizada basada en IA.</strong></li></ul><p>En este artículo, explicaré paso a paso cómo utilizar esta herramienta para habilitar <em>write-back </em>sobre una base de datos, permitiendo que los usuarios puedan:</p><ul><li>Marcar o desmarcar productos como favoritos o destacados.</li><li>Crear o eliminar comentarios.</li></ul><p>¡Vamos a ello!</p><h3>Requisitos</h3><p>Para poder utilizar los <strong>Translytical Task Flows</strong>, se deben cumplir con los siguientes requisitos:</p><ul><li>Contar con una licencia de<strong> Microsoft Fabric</strong> (puedes comenzar con la versión de prueba).</li><li>Tener activada la opción <strong>“User data functions (preview)”</strong>, disponible en el portal de administración de Power BI, dentro de la sección <strong>Tenant settings</strong>.</li></ul><h3>Limitaciones</h3><p>Al tratarse de una funcionalidad en vista previa, actualmente existen algunas restricciones importantes:</p><ul><li>Solo los nuevos visuales de segmentador (botón, lista y texto) son reconocidos automáticamente como inputs para los parámetros de función.</li><li>No es compatible con los formatos PBIR (<em>Power BI enhanced report</em>) y PBIP (<em>Power BI Project</em>).</li><li>No está disponible para Power BI embed.</li></ul><h3>Ejemplo Práctico</h3><p>En este caso práctico, quiero mostrar cómo utilizar los <strong>Translytical Task Flows </strong>para permitir que los usuarios interactúen activamente con el reporte.</p><p>En concreto, el objetivo es que puedan:</p><ul><li>Marcar o desmarcar productos como favoritos o destacados (estos se visualizarán mediante reglas condicionales).</li><li>Añadir y eliminar comentarios sobre productos directamente desde el informe.</li></ul><p>Para ello, comenzaré creando una <strong>base de datos con los datos de muestra</strong> que ofrece Microsoft Fabric al crear una nueva base de datos SQL.</p><p>A partir de ahí, generaré <strong>tablas adicionales</strong> donde se registrarán los productos marcados y los comentarios ingresados por los usuarios.</p><p>Después, desarrollaré cuatro <strong>User Data Functions</strong>:</p><ul><li>Una para marcar o desmarcar un producto como favorito.</li><li>Otra para marcar o desmarcar un producto como destacado.</li><li>Una función que reciba el texto ingresado por el usuario para guardar un comentario sobre un producto.</li><li>Y, finalmente, una función para eliminar comentarios existentes.</li></ul><p>Cada función incluirá las <strong>validaciones necesarias</strong> para garantizar un comportamiento lógico y consistente, como por ejemplo:</p><ul><li>Evitar sobrescribir un comentario si ya existe uno para ese producto.</li><li>No permitir que un mismo producto esté marcado simultáneamente como favorito y destacado.</li><li>Impedir que se ejecute la acción si el usuario no ha seleccionado ningún producto, o si ha seleccionado más de uno.</li></ul><p>Vamos paso a paso:</p><h4>1. Creamos una base de datos con datos de prueba</h4><p>Creamos una base de datos SQL de Microsoft Fabric (la llamaré “<em>DB_TranslyticalTaskFlows</em>” en este ejemplo) y seleccionamos la opción para usar datos de prueba.</p><p>Cuando termine de cargar tendremos algo así:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/261/0*LqwefoJ8flMDjRwU" /><figcaption>image by author</figcaption></figure><h4>2. Crear las tablas adicionales</h4><p>Creamos tres tablas nuevas para registrar la información que los usuarios introducirán desde el informe:</p><ul><li>Productos marcados como favoritos</li><li>Productos marcados como destacados</li><li>Comentarios asociados a productos</li></ul><p>La tabla de favoritos, por ejemplo, se crearía de la siguiente manera:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/479/1*1eaGUKJMFpuc-kOaoDJnAQ.png" /><figcaption>image by author</figcaption></figure><h4>3. Crear el contenedor de funciones</h4><p>Dentro del espacio de trabajo de Fabric, creamos un nuevo elemento del tipo <strong>User data function</strong>.</p><p>En mi caso lo denominaré “<em>UDF_sqlwriteback</em>”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/291/0*s0Ayl08tvX3U0zSx" /><figcaption>image by author</figcaption></figure><h4>4. Configurar la conexión a la base de datos</h4><p>En la parte superior seleccionamos <strong>“Manage connections” </strong>y añadimos la conexión a la base de datos que creamos en el paso 1.</p><p>El alias asignado será necesario en el script de cada función.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/0*dM79Ij6s_emSAKhT" /><figcaption>image by author</figcaption></figure><h4><strong>5. Crear las funciones</strong></h4><p>A continuación, desarrollamos las funciones necesarias para escribir datos en nuestras tablas.</p><p>Cada función se encargará de una acción específica y contiene validaciones para evitar errores lógicos (como marcar un producto como favorito si ya está destacado, o sobrescribir un comentario ya existente).</p><p>El script comienza importando la librería fabric.functions y creando una instancia UserDataFunctions:</p><pre>import fabric.functions as fn<br><br>udf = fn.UserDataFunctions()</pre><p>⚙️ <strong>toggle_favorite</strong></p><p>Marca un producto como favorito o lo desmarca si ya lo está.</p><p>Incluye validaciones para:</p><ul><li>Evitar marcar un producto que esté destacado.</li><li>Impedir que la acción se ejecute si el usuario no ha seleccionado un único producto.</li></ul><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def toggle_favorite(sqlDB: fn.FabricSqlConnection, productModelID: int, productCount: int) -&gt; str:<br><br>    # Verificar que se ha seleccionado un único producto<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Por favor, seleccione un único producto para realizar esta acción.&quot;)<br><br>    # Crear conexión con la bbdd<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    # Verificar si el producto está marcado como destacado<br>    check_flagged_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FlaggedProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_flagged_query, (productModelID,))<br>    is_flagged = cursor.fetchone()[0] &gt; 0<br><br>    if is_flagged:<br>        cursor.close()<br>        connection.close()<br>        raise fn.UserThrownError(&quot;No puede marcar este producto como favorito porque está marcado como destacado.&quot;)<br><br>    # Verificar si el producto ya está marcado como favorito<br>    check_fav_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FavoriteProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_fav_query, (productModelID,))<br>    is_favorite = cursor.fetchone()[0] &gt; 0<br><br>    if is_favorite:<br>        delete_query = &quot;DELETE FROM [SalesLT].[FavoriteProduct] WHERE ProductModelID = ?&quot;<br>        cursor.execute(delete_query, (productModelID,))<br>        result_message = &quot;El producto fue desmarcado como favorito.&quot;<br>    else:<br>        insert_query = &quot;INSERT INTO [SalesLT].[FavoriteProduct] (ProductModelID, IsFavorite) VALUES (?, 1)&quot;<br>        cursor.execute(insert_query, (productModelID,))<br>        result_message = &quot;El producto fue marcado como favorito.&quot;<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return result_message</pre><p>⚙️ <strong>toggle_flaggedproduct</strong></p><p>Marca un producto como destacado o lo desmarca.</p><p>Incluye validaciones para:</p><ul><li>Evitar destacar un producto ya favorito.</li><li>Impedir que la acción se ejecute si el usuario no ha seleccionado un único producto.</li></ul><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def toggle_flaggedproduct(sqlDB: fn.FabricSqlConnection, productModelID: int, productCount: int) -&gt; str:<br><br>    # Verificar que se ha seleccionado un único producto<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Por favor, seleccione un único producto para realizar esta acción.&quot;)<br><br>    # Crear conexión con la bbdd<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    # Verificar si el producto está marcado como favorito<br>    check_fav_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FavoriteProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_fav_query, (productModelID,))<br>    is_favorite = cursor.fetchone()[0] &gt; 0<br><br>    if is_favorite:<br>        cursor.close()<br>        connection.close()<br>        raise fn.UserThrownError(&quot;No puede marcar este producto como destacado porque está marcado como favorito.&quot;)<br><br>    # Verificar si el producto ya está destacado<br>    check_flagged_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FlaggedProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_flagged_query, (productModelID,))<br>    is_flagged = cursor.fetchone()[0] &gt; 0<br><br>    if is_flagged:<br>        delete_query = &quot;DELETE FROM [SalesLT].[FlaggedProduct] WHERE ProductModelID = ?&quot;<br>        cursor.execute(delete_query, (productModelID,))<br>        result_message = &quot;El producto fue desmarcado como destacado.&quot;<br>    else:<br>        insert_query = &quot;INSERT INTO [SalesLT].[FlaggedProduct] (ProductModelID, IsFlagged) VALUES (?, 1)&quot;<br>        cursor.execute(insert_query, (productModelID,))<br>        result_message = &quot;El producto fue marcado como destacado.&quot;<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return result_message</pre><p>⚙️ <strong>add_product_comment</strong></p><p>Permite guardar un comentario para un producto.</p><p>Incluye validaciones para:</p><ul><li>Evitar sobrescribir comentarios existentes.</li><li>Impedir que la acción se ejecute si el usuario no ha seleccionado un único producto.</li></ul><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def add_product_comment(sqlDB: fn.FabricSqlConnection, productModelID: int, comment: str, productCount: int) -&gt; str:<br><br>    # Verificar que se ha seleccionado un único producto<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Por favor, seleccione un único producto para realizar esta acción.&quot;)<br><br>    # Crear conexión con la bbdd<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    # Verificar si ya existe un comentario para este producto<br>    check_query = &quot;SELECT COUNT(*) FROM [SalesLT].[ProductComment] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_query, (productModelID,))<br>    exists = cursor.fetchone()[0] &gt; 0<br><br>    if exists:<br>        cursor.close()<br>        connection.close()<br>        raise fn.UserThrownError(<br>            &quot;Este producto ya tiene un comentario. Si desea modificarlo, elimine el comentario existente primero.&quot;<br>        )<br><br>    # Si no existe, insertar nuevo comentario<br>    insert_query = &quot;&quot;&quot;<br>    INSERT INTO [SalesLT].[ProductComment] (ProductModelID, Comment)<br>    VALUES (?, ?)<br>    &quot;&quot;&quot;<br>    cursor.execute(insert_query, (productModelID, comment))<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return &quot;Comentario guardado correctamente.&quot;</pre><p>⚙️ <strong>delete_product_comment</strong></p><p>Elimina el comentario asociado a un producto.</p><p>Incluye validaciones para evitar que la acción se ejecute si el usuario no ha seleccionado un único producto.</p><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def delete_product_comment(sqlDB: fn.FabricSqlConnection, productModelID: int, productCount: int) -&gt; str:<br><br>    # Verificar que se ha seleccionado un único producto<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Por favor, seleccione un único producto para realizar esta acción.&quot;)<br><br>    # Crear conexión con la bbdd<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    delete_query = &quot;DELETE FROM [SalesLT].[ProductComment] WHERE ProductModelID = ?&quot;<br>    cursor.execute(delete_query, (productModelID,))<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return &quot;Comentario eliminado correctamente.&quot;</pre><p>Una vez terminado el código lo <strong>publicamos </strong>y se verán las funciones en el <strong>explorador de funciones</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/265/0*4hctE7aVTw0vaCkF" /><figcaption>image by author</figcaption></figure><h4>6. (Opcional) Compartir funciones con otros usuarios</h4><p>Desde el botón “Compartir”, podemos otorgar permisos a otros usuarios, sin necesidad de dar acceso completo a la edición del código.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9TFhtVdRFGZgqEN3" /><figcaption>image by author</figcaption></figure><h4>7. Creamos el informe en Power BI Desktop</h4><p>A continuación, conectamos Power BI a la base de datos que contiene nuestras tablas personalizadas: FavoriteProduct, FlaggedProduct y ProductComment, además de las tablas originales de productos (provenientes de los datos de muestra).</p><p>En la vista de informe:</p><ul><li>Creamos un visual de tabla para mostrar los productos.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*WSi8L5ZX9ahbGOrL" /><figcaption>image by author</figcaption></figure><ul><li>Agregamos formatos condicionales que aplican color si un producto está marcado como favorito (amarillo) o como destacado (rojo).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/625/0*JnqJ1tLFPUxcqReY" /><figcaption>image by author</figcaption></figure><p>Al principio, la tabla no mostrará ningún color ni tendrá valores en la columna de comentarios porque nuestras tablas personalizadas están vacías.</p><h4>8. Configuración de botones de acción</h4><p>Importante, para configurar los botones <strong>es necesario activar la opción preview “User data functions (preview)”</strong> en Power BI Desktop.</p><p>Con eso habilitado:</p><ul><li>Añadimos los botones al informe.</li><li>Vamos a la sección “Acción” y elegimos “Función de datos” como tipo de acción.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/231/0*xatPqBs5N82ncijY" /><figcaption>image by author</figcaption></figure><ul><li>Seleccionamos el área de trabajo, el conjunto de funciones y la función que queremos asignar.</li></ul><p>Cuando lo hacemos, <strong>los parámetros definidos en la función se detectan automáticamente y aparecen como campos a rellenar</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/369/1*ZPzvr3Q5REmFCov_s_2tUQ.png" /><figcaption>image by author</figcaption></figure><p>En el caso de productModelID y productCount, podemos usar las siguientes métricas DAX:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/571/1*zbyyVydsaQwz2GvB1seOYQ.png" /><figcaption>image by author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/499/1*rt5Kd4fQd5a-t1XOTajvvw.png" /><figcaption>image by author</figcaption></figure><p>Para la función add_product_comment, necesitamos que el usuario pueda <strong>introducir texto</strong>.</p><p>Para ello, usamos el nuevo visual de <strong>“segmentador de texto”</strong> (también en preview), el cual es compatible con las acciones de función de datos y se detecta automáticamente como input.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/366/1*Gk9RXKgfwbPolysZ6cKqog.png" /><figcaption>image by author</figcaption></figure><p>Por otro lado, dejamos habilitada la casilla de “Actualizar el informe después de un resultado correcto” para poder visualizar el cambio una vez esté aplicado.</p><h4>9. Resultado final</h4><p>Una vez completado todo el proceso, diseñamos el informe en Power BI Desktop y lo publicamos.</p><p>El resultado es un informe completamente interactivo, donde los usuarios pueden marcar productos, destacar elementos y dejar comentarios, todo desde la interfaz del reporte.</p><p>A continuación, muestro el informe final que he diseñado:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/1*maznHsrbZVZ3ZUMtdlW2Og.gif" /><figcaption>image by author</figcaption></figure><h3>Conclusiones</h3><p><strong>Translytical task flows</strong> es una herramienta muy potente que habilita escenarios avanzados de interacción en Power BI.</p><p>Como toda funcionalidad nueva, requiere ser evaluada y utilizada con criterio, especialmente en contextos sensibles o de producción.</p><p>Aun así, su incorporación representa un avance significativo hacia experiencias más interactivas y dinámicas dentro del ecosistema Power BI.</p><p><strong>¡Gracias por leerme!</strong></p><h3>Referencias</h3><ul><li><a href="https://powerbi.microsoft.com/en-us/blog/announcing-translytical-task-flows-preview/">Translytical task flows (Preview)</a></li><li><a href="https://learn.microsoft.com/es-es/power-bi/create-reports/translytical-task-flow-overview">Documentación Oficial</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=03416594b04f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Translytical Task Flows: mark, comment, and edit data from Power BI!]]></title>
            <link>https://medium.com/@martacasdelg/translytical-task-flows-mark-comment-and-edit-data-from-power-bi-bc653718c874?source=rss-30b331a2e646------2</link>
            <guid isPermaLink="false">https://medium.com/p/bc653718c874</guid>
            <category><![CDATA[fabric]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[translytical-task-flows]]></category>
            <dc:creator><![CDATA[Marta Castrillo]]></dc:creator>
            <pubDate>Tue, 27 May 2025 21:00:28 GMT</pubDate>
            <atom:updated>2025-05-28T22:05:23.072Z</atom:updated>
            <content:encoded><![CDATA[<p>Learn how to use Translytical Task Flows in Power BI to write back data from the report using custom functions and interactive buttons.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/1*JJcjlR3n-AedJLvPukQoGg.gif" /><figcaption>gif by author</figcaption></figure><p>One of the most notable features in the May 2025 Power BI update is <strong>Translytical Task Flows</strong>, currently in public preview.</p><p>This new functionality enables the use of <strong>User Data Functions</strong> on action buttons, allowing users to perform custom and automated actions directly from the report, such as:</p><ul><li><strong>Modify data.</strong></li><li><strong>Add comments to the report.</strong></li><li><strong>Send dynamic notifications.</strong></li><li><strong>Trigger approval flows.</strong></li><li><strong>Apply custom AI-based logic.</strong></li></ul><p>In this article, I will explain step-by-step how to use this tool to enable <em>write-back</em> on a database, allowing users to:</p><ul><li>Mark or unmark products as favorites or flagged.</li><li>Create or delete comments.</li></ul><p>Let’s get started!</p><h3>Requirements</h3><p>To use Translytical Task Flows, the following requirements must be met:</p><ul><li>Have a Microsoft Fabric license (you can start with the trial version).</li><li>Have the “User data functions (preview)” option enabled, available in the Power BI admin portal under Tenant settings.</li></ul><h3>Limitations</h3><p>As this is a preview feature, there are currently some important restrictions:</p><ul><li>Only the new slicer visuals (button, list, and text) are automatically recognized as inputs for function parameters.</li><li>It is not compatible with PBIR (Power BI enhanced report) and PBIP (Power BI Project) formats.</li><li>It is not available for Power BI embedded.</li></ul><h3>Practical Example</h3><p>In this practical example, I want to show how to use <strong>Translytical Task Flows</strong> to allow users to actively interact with the report.</p><p>Specifically, the goal is that they can:</p><ul><li>Mark or unmark products as favorites or flagged (these will be visualized via conditional formatting rules).</li><li>Add and delete comments about products directly from the report.</li></ul><p>To do this, I will start by <strong>creating a database using the sample data</strong> that Microsoft Fabric provides when creating a new SQL database.</p><p>From there, I will create <strong>additional tables </strong>where the marked products and user-entered comments will be stored.</p><p>Next, I will develop four <strong>User Data Functions</strong>:</p><ul><li>One to mark or unmark a product as favorite.</li><li>Another to mark or unmark a product as flagged.</li><li>A function that receives text entered by the user to save a comment about a product.</li><li>Finally, a function to delete existing comments.</li></ul><p>Each function will include the <strong>necessary validations</strong> to ensure logical and consistent behavior, such as:</p><ul><li>Avoid overwriting a comment if one already exists for that product.</li><li>Prevent a product from being marked simultaneously as favorite and flagged.</li><li>Prevent the action from executing if the user has not selected any product, or if more than one has been selected.</li></ul><p>Let’s go step by step:</p><h4>1. Create a database with sample data</h4><p>Create a Microsoft Fabric SQL database (I will call it “<em>DB_TranslyticalTaskFlows</em>” in this example) and select the option to use sample data.</p><p>Once it finishes loading, it will look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/261/0*LqwefoJ8flMDjRwU" /><figcaption>image by author</figcaption></figure><h4>2. Create additional tables</h4><p>Create three new tables to record the information users will enter from the report:</p><ul><li>Products marked as favorites</li><li>Products marked as flagged</li><li>Comments associated with products</li></ul><p>The favorites table, for example, would be created as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/479/1*1eaGUKJMFpuc-kOaoDJnAQ.png" /><figcaption>image by author</figcaption></figure><h4>3. Create the function container</h4><p>Within the Fabric workspace, create a new item of type User Data Function.</p><p>In my case, I will name it “<em>UDF_sqlwriteback</em>”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/291/0*s0Ayl08tvX3U0zSx" /><figcaption>image by author</figcaption></figure><h4>4. Configure the database connection</h4><p>At the top, select <strong>“Manage connections”</strong> and add the connection to the database created in step 1.</p><p>The assigned alias will be necessary in each function script.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/0*dM79Ij6s_emSAKhT" /><figcaption>image by author</figcaption></figure><h4><strong>5. Functions</strong></h4><p>Next, develop the functions needed to write data to our tables.</p><p>Each function handles a specific action and contains validations to avoid logical errors (such as marking a product as favorite if it is already flagged, or overwriting an existing comment).</p><p>The script begins by importing the fabric.functions library and creating a UserDataFunctions instance:</p><pre>import fabric.functions as fn<br><br>udf = fn.UserDataFunctions()</pre><p>⚙️ <strong>toggle_favorite</strong></p><p>Marks a product as favorite or unmarks it if already marked.</p><p>Includes validations to:</p><ul><li>Avoid marking a product that is flagged.</li><li>Prevent the action if the user has not selected exactly one product.</li></ul><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def toggle_favorite(sqlDB: fn.FabricSqlConnection, productModelID: int, productCount: int) -&gt; str:<br><br>    # Verify that a single product has been selected<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Please select a single product to perform this action.&quot;)<br><br>    # Create connection to the database<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    # Check if the product is already marked as flagged<br>    check_flagged_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FlaggedProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_flagged_query, (productModelID,))<br>    is_flagged = cursor.fetchone()[0] &gt; 0<br><br>    if is_flagged:<br>        cursor.close()<br>        connection.close()<br>        raise fn.UserThrownError(&quot;You cannot mark this product as favorite because it is already flagged.&quot;)<br><br>    # Check if the product is already marked as favorite<br>    check_fav_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FavoriteProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_fav_query, (productModelID,))<br>    is_favorite = cursor.fetchone()[0] &gt; 0<br><br>    if is_favorite:<br>        delete_query = &quot;DELETE FROM [SalesLT].[FavoriteProduct] WHERE ProductModelID = ?&quot;<br>        cursor.execute(delete_query, (productModelID,))<br>        result_message = &quot;The product was unmarked as favorite.&quot;<br>    else:<br>        insert_query = &quot;INSERT INTO [SalesLT].[FavoriteProduct] (ProductModelID, IsFavorite) VALUES (?, 1)&quot;<br>        cursor.execute(insert_query, (productModelID,))<br>        result_message = &quot;The product was marked as favorite.&quot;<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return result_message</pre><p>⚙️ <strong>toggle_flaggedproduct</strong></p><p>Marks a product as flagged or unflags it.</p><p>Includes validations to:</p><ul><li>Avoid flagging a product that is already favorite.</li><li>Prevent the action if the user has not selected exactly one product.</li></ul><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def toggle_flaggedproduct(sqlDB: fn.FabricSqlConnection, productModelID: int, productCount: int) -&gt; str:<br><br>    # Verify that a single product has been selected<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Please select a single product to perform this action.&quot;)<br><br>    # Create connection to the database<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    # Check if the product is marked as favorite<br>    check_fav_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FavoriteProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_fav_query, (productModelID,))<br>    is_favorite = cursor.fetchone()[0] &gt; 0<br><br>    if is_favorite:<br>        cursor.close()<br>        connection.close()<br>        raise fn.UserThrownError(&quot;You cannot flag this product because it is already marked as favorite.&quot;)<br><br>    # Check if the product is already flagged<br>    check_flagged_query = &quot;SELECT COUNT(*) FROM [SalesLT].[FlaggedProduct] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_flagged_query, (productModelID,))<br>    is_flagged = cursor.fetchone()[0] &gt; 0<br><br>    if is_flagged:<br>        delete_query = &quot;DELETE FROM [SalesLT].[FlaggedProduct] WHERE ProductModelID = ?&quot;<br>        cursor.execute(delete_query, (productModelID,))<br>        result_message = &quot;The product was unflagged.&quot;<br>    else:<br>        insert_query = &quot;INSERT INTO [SalesLT].[FlaggedProduct] (ProductModelID, IsFlagged) VALUES (?, 1)&quot;<br>        cursor.execute(insert_query, (productModelID,))<br>        result_message = &quot;The product was flagged.&quot;<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return result_message</pre><p>⚙️ <strong>add_product_comment</strong></p><p>Allows saving a comment for a product.</p><p>Includes validations to:</p><ul><li>Avoid overwriting existing comments.</li><li>Prevent the action if the user has not selected exactly one product.</li></ul><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def add_product_comment(sqlDB: fn.FabricSqlConnection, productModelID: int, comment: str, productCount: int) -&gt; str:<br><br>    # Verify that a single product has been selected<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Please select a single product to perform this action.&quot;)<br><br>    # Create connection to the database<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    # Check if a comment already exists for this product<br>    check_query = &quot;SELECT COUNT(*) FROM [SalesLT].[ProductComment] WHERE ProductModelID = ?&quot;<br>    cursor.execute(check_query, (productModelID,))<br>    exists = cursor.fetchone()[0] &gt; 0<br><br>    if exists:<br>        cursor.close()<br>        connection.close()<br>        raise fn.UserThrownError(<br>            &quot;This product already has a comment. If you want to modify it, please delete the existing comment first.&quot;<br>        )<br><br>    # If it doesn&#39;t exist, insert new comment<br>    insert_query = &quot;&quot;&quot;<br>    INSERT INTO [SalesLT].[ProductComment] (ProductModelID, Comment)<br>    VALUES (?, ?)<br>    &quot;&quot;&quot;<br>    cursor.execute(insert_query, (productModelID, comment))<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return &quot;Comment saved successfully.&quot;</pre><p>⚙️ <strong>delete_product_comment</strong></p><p>Deletes a comment associated with a product.</p><p>Includes validations to prevent the action if the user has not selected exactly one product.</p><pre>@udf.connection(argName=&quot;sqlDB&quot;, alias=&quot;DBTranslyticalT&quot;)<br>@udf.function()<br>def delete_product_comment(sqlDB: fn.FabricSqlConnection, productModelID: int, productCount: int) -&gt; str:<br><br>    # Verify that a single product has been selected<br>    if productCount != 1:<br>        raise fn.UserThrownError(&quot;Please select a single product to perform this action.&quot;)<br><br>    # Create connection to the database<br>    connection = sqlDB.connect()<br>    cursor = connection.cursor()<br><br>    delete_query = &quot;DELETE FROM [SalesLT].[ProductComment] WHERE ProductModelID = ?&quot;<br>    cursor.execute(delete_query, (productModelID,))<br><br>    connection.commit()<br>    cursor.close()<br>    connection.close()<br><br>    return &quot;Comment deleted successfully.&quot;</pre><p>Once the code is complete, we <strong>publish it</strong>, and the functions will appear in the<strong> function explorer</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/265/0*4hctE7aVTw0vaCkF" /><figcaption>image by author</figcaption></figure><h4>6. (Optional) Share functions with other users</h4><p>From the “Share” button, we can grant permissions to other users without giving full access to edit the code.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9TFhtVdRFGZgqEN3" /><figcaption>image by author</figcaption></figure><h4>7. Create the report in Power BI Desktop</h4><p>Next, we connect Power BI to the dataset that contains our custom tables: FavoriteProduct, FlaggedProduct, and ProductComment, along with the original product tables (from the sample data).</p><p>In the report view:</p><ul><li>I create a table visual to display the products.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*WSi8L5ZX9ahbGOrL" /><figcaption>image by author</figcaption></figure><ul><li>Add conditional formatting that applies color if a product is marked as favorite (yellow) or flagged (red).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/625/0*JnqJ1tLFPUxcqReY" /><figcaption>image by author</figcaption></figure><p>At first, the table will not show any color or values in the comments column, since our custom tables are still empty.</p><h4>8. Action Button Configuration</h4><p>To configure the buttons, you must<strong> enable the “User data functions (preview)” option</strong> in Power BI Desktop.</p><p>Once that’s enabled:</p><ul><li>Add the buttons to the report.</li><li>Go to the “Action” section and choose “Data function” as the action type.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/176/1*K3jKcr0V2yh4p0wlx2oV9w.png" /><figcaption>image by author</figcaption></figure><ul><li>Select the workspace, the function group, and the specific function we want to assign.</li></ul><p><strong>The parameters defined in the function are automatically detected and appear as fields to be filled in</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/314/1*IWutvPPZOnkQynXYKLc4DA.png" /><figcaption>image by author</figcaption></figure><p>In the case of productModelID andproductCount , we can use the following DAX measures:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/571/1*zbyyVydsaQwz2GvB1seOYQ.png" /><figcaption>image by author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/499/1*rt5Kd4fQd5a-t1XOTajvvw.png" /><figcaption>image by author</figcaption></figure><p>For the add_product_comment function, we need the user to be able to <strong>enter text</strong>.</p><p>To achieve this, we use the new &#39;<strong>text slicer</strong>&#39; visual (also in preview), which is compatible with data function actions and is automatically detected as input.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/315/1*HJ57ttAp8If1Q8A5OlnbWg.png" /><figcaption>image by author</figcaption></figure><p>On the other hand, we leave the ‘Refresh the report after a successful autcome’ checkbox enabled so that the change can be seen once applied.</p><h4>9. Final Result</h4><p>Once the entire process is completed, we design the report in Power BI Desktop and publish it.</p><p>The result is a fully interactive report where users can mark products as favorites, flag items, and leave comments — all directly from the report interface.</p><p>Here is the final report I designed:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/1*JJcjlR3n-AedJLvPukQoGg.gif" /><figcaption>gif by author</figcaption></figure><blockquote>In the GIF showcasing my report, the interface is displayed in Spanish. Although the code and explanations in this article have been translated into English, the original final product was created in Spanish, which is why the report visuals appear in that language.</blockquote><h3>Conclusions</h3><p><strong>Translytical task flows</strong> is a very powerful tool that enables advanced interaction scenarios in Power BI.</p><p>Like any new feature, it needs to be evaluated and used with discretion, especially in sensitive or production environments.</p><p>Still, its incorporation represents a significant step forward towards more interactive and dynamic experiences within the Power BI ecosystem.</p><p><strong>Thank you for reading!</strong></p><h3>References</h3><ul><li><a href="https://powerbi.microsoft.com/en-us/blog/announcing-translytical-task-flows-preview/">Translytical task flows (Preview)</a></li><li><a href="https://medium.com/r?url=https%3A%2F%2Flearn.microsoft.com%2Fes-es%2Fpower-bi%2Fcreate-reports%2Ftranslytical-task-flow-overview">Oficial documentation</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bc653718c874" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>