<?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 Fran Lozano on Medium]]></title>
        <description><![CDATA[Stories by Fran Lozano on Medium]]></description>
        <link>https://medium.com/@franloza?source=rss-ce22534f84f2------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*CXExIbOi_2--0_YEsl6hSw.jpeg</url>
            <title>Stories by Fran Lozano on Medium</title>
            <link>https://medium.com/@franloza?source=rss-ce22534f84f2------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 22:10:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@franloza/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[How I Automated a Manual Data Process Using LLMs]]></title>
            <link>https://franloza.medium.com/how-i-automated-a-manual-data-process-with-llms-07ead9bbd700?source=rss-ce22534f84f2------2</link>
            <guid isPermaLink="false">https://medium.com/p/07ead9bbd700</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[prompt-engineering]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[automation]]></category>
            <dc:creator><![CDATA[Fran Lozano]]></dc:creator>
            <pubDate>Mon, 26 May 2025 15:02:15 GMT</pubDate>
            <atom:updated>2025-05-26T15:17:15.899Z</atom:updated>
            <content:encoded><![CDATA[<p>It all started with an <a href="https://circuitocarrerascuenca.franloza.com/">application</a> I built to display statistics for a local running race series held annually in Cuenca, a small province in Spain. I talked about it in this <a href="https://medium.com/@franloza/how-to-create-a-data-web-app-for-free-with-evidence-c67ebaf91909">post</a>.</p><p>The data update process was almost automatic. Data is extracted from the official timekeeping company (Cronomancha). It’s transformed using a <a href="https://github.com/franloza/running-races-insights/blob/main/elt/transform.py">query</a> (I didn’t want to use dbt because it was an extra dependency and not really necessary). The <a href="https://github.com/franloza/running-races-insights/blob/main/sources/race_results/race_results.duckdb">database</a>, also hosted on Git, is updated (it’s only 4MB, so not an issue). Changes are pushed to the main branch, and Netlify handles the web deployment. Done. Easy.</p><p>But there was a CATCH. One small step was manual. The timekeeper enters race names however they see fit, which might not match the official name listed in the <a href="https://www.circuitocarrerasdiputacioncuenca.com/images/stories/2025/REGLAMENTO%20%20XXI%20CIRCUITO%20CCPP%202025%20definitivo.pdf">official regulations</a>.</p><p>Here’s an example:</p><ul><li>Official Calendar: XVI 10K LA PITA</li><li>Timekeeper’s Race Name: XVI 10K LA PITA - 2025</li></ul><p>Another example where there’s no clear pattern:</p><ul><li>Official Calendar: X CARRERA POPULAR DE LA GUITARRA ESPAÑOLA (X Popular Race of the Spanish Guitar)</li><li>Timekeeper’s Race Name: CASIMARRO - CIRCUITO CCPP 2025 (Casasimarro is a small town in Cuenca, known for its guitar-making tradition. The timekeeper used the town name.)</li></ul><p>And so on with a few more races.</p><p>As you can see, this isn’t a problem you can solve with simple <a href="https://en.wikipedia.org/wiki/Approximate_string_matching">fuzzy matching</a> or similar tricks. It requires context (like knowing what the race was called in previous years) to resolve.</p><p>For this reason, I have to maintain a <a href="https://github.com/franloza/running-races-insights/blob/main/data/circuit_races.csv">CSV file</a> to manage this mapping. This file links the timekeeper’s race names to the official calendar, which specifies location, date, and race number. At the beginning of the year, I know the order and location of all races from the calendar. So, I can fill in all the CSV rows except for the “official” race name used by the timekeeper, which I don’t know until they publish the results online.</p><p>The context to solve this problem can be extracted from the mapping file itself, which already contains how this classification was done in previous years. If I see that last year, the “La Pita” race was in El Herrumblar (another small town in the Cuenca region), it’s very likely to be in the same place this year. If I see the race name contains “CASIMARRO,” it’s very likely to be in Casasimarro, even if in other years it was called “Carrera popular de la guitarra española.” See where I’m going with this? It’s a problem that can be solved with a bit of context, but it’s difficult to solve algorithmically.</p><p>And this is where LLMs come into play.</p><p>To solve it, I wanted an LLM to handle this task: fetching the names of the races run in the current year after updating the database, and then trying to fill in the missing race name in the CSV.</p><p>For this, I used <a href="https://openrouter.ai/">OpenRouter</a> because I wasn’t sure which model to use (it’s a seemingly simple task, not needing an advanced LLM), and I wanted the flexibility to switch to a cheaper, yet still effective, model in the future.</p><p>After about an hour of <em>vibe coding</em>, I had it working. Incredible. I don’t want to get too into the technical details of this process, but I’ll summarize it in a few points:</p><ul><li>I didn’t write a single line of code myself. Everything was an interaction with Cursor’s AI agent. I used Gemini-2.5 Pro, which has been giving me great results lately.</li><li>If you don’t set good guardrails for the prompt, the LLM tends to hallucinate. I spent a lot of my time iterating with the AI to provide <strong>clear rules</strong> in the prompt to prevent it from inventing race names that hadn’t happened yet. It was also key to give enough clues in the prompt so it understood that locations in the race name were relevant. If a race was called XVII SUBIDA A LOS MOLINOS DE MOTA DEL CUERVO (XVII Ascent to the Windmills of Mota del Cuervo - Mota del Cuervo being another town in Cuenca), it&#39;s very likely in Mota del Cuervo, not in the city of Cuenca, for example. If the AI wasn&#39;t sure, clear instructions were also needed for it to return null if it couldn&#39;t find a match.</li><li>I opted to build a broader prompt including historical mapping data instead of calling the LLM for each CSV row to be filled. LLM context windows are getting larger, and when you’re not dealing with huge volumes of data, I think it’s better to give all the context at once rather than making multiple smaller calls.</li><li>I defined a clear output format for the LLM: a JSON array of objects, which allowed for programmatic updating of the CSV.</li><li>I initially tried the free <a href="https://openrouter.ai/deepseek/deepseek-chat-v3-0324:free">DeepSeek V3</a> model, but it didn’t give good results. I finally settled on <a href="https://openrouter.ai/google/gemini-2.0-flash-001">Gemini 2.0 Flash</a>, which solved it correctly, quickly, and at a very low cost per call.</li></ul><figure><img alt="Cost per request to DeepSeek V3 and Gemini 2.0 Flash from OpenRouter" src="https://cdn-images-1.medium.com/max/1024/1*ZU7TrENJZN4xi9fNlb-0Pw.png" /><figcaption>Cost per request to DeepSeek V3 and Gemini 2.0 Flash from OpenRouter</figcaption></figure><p>My AI assistant in Cursor was essential in this process. I would describe the problem (“The LLM isn’t prioritizing location enough”), and it would suggest modifications to the prompt’s structure or wording. Then we’d apply the changes directly in the script and test again. It was a completely natural process of iteration and improvement, like having a programmer helping me with this process.</p><p>If you want to see the result, you can find it <a href="https://github.com/franloza/running-races-insights/blob/main/scripts/intelligent_race_updater.py">here</a>. This script contains the prompt and the CSV update code. The bot has already created its <a href="https://github.com/franloza/running-races-insights/pull/2">first Pull Request</a>, and I’m confident about putting it in fully automatic mode. This will be possible once I validate in the coming weeks that it performs the task correctly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iuHzxa-nvUh3tS2LyU9xjw.png" /><figcaption>Pull Request created by a fully-automated process (Github Actions)</figcaption></figure><p>This <em>vibe coding</em> process was more than just a simple coding exercise. It was an iterative process with a travel companion who understands me and helps me solve a problem. A deep dive into prompt engineering and a realization that AI will enable use cases that were previously not so trivial to solve algorithmically. If you have a task involving “fuzzy matching” or maintaining a data mapping that requires some context to resolve, I think it’s worth considering using an LLM guided by a well-structured prompt. For my part, I’m convinced this travel companion is here to stay and will accompany me in future challenges.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=07ead9bbd700" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Vibe Simulation: AI, Monte Carlo, and a Casino Game]]></title>
            <link>https://franloza.medium.com/vibe-simulation-ai-monte-carlo-and-a-casino-game-5f2a7dd56529?source=rss-ce22534f84f2------2</link>
            <guid isPermaLink="false">https://medium.com/p/5f2a7dd56529</guid>
            <category><![CDATA[monte-carlo]]></category>
            <category><![CDATA[simulation]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[chatgpt]]></category>
            <dc:creator><![CDATA[Fran Lozano]]></dc:creator>
            <pubDate>Thu, 15 May 2025 15:50:17 GMT</pubDate>
            <atom:updated>2025-05-15T15:57:54.772Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y8pikXVTpM4vvuqLzb6blg.png" /><figcaption>Generated with AI by me using Microsoft Designer</figcaption></figure><p>While my usual focus is on data engineering, I recently stumbled upon a fascinating problem that pulled me into the world of simulations and AI-driven probability.</p><p>It all started, as it <a href="https://franloza.medium.com/what-is-the-easiest-way-to-move-data-from-stripe-to-postgres-54d5e489bda8">often does for me</a>, with an interesting find on Twitter/X. This particular <a href="https://x.com/hamptonism/status/1922538415842558309">tweet</a> presented a seemingly simple card game:</p><iframe src="https://cdn.embedly.com/widgets/media.html?type=text%2Fhtml&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;schema=twitter&amp;url=https%3A//x.com/hamptonism/status/1922538415842558309&amp;image=" width="500" height="281" frameborder="0" scrolling="no"><a href="https://medium.com/media/2abb0ef00ac603334f6abd06ed90ddf3/href">https://medium.com/media/2abb0ef00ac603334f6abd06ed90ddf3/href</a></iframe><h3>Solving the Problem with AI</h3><p>I decided to tackle this problem with ChatGPT (You can see the code in this <a href="https://x.com/franloza93/status/1922584855662981325">tweet</a>).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eJk37bOxEirC3kEynFmC4A.png" /><figcaption>Solution given by ChatGPT to the problem</figcaption></figure><p>The AI not only provided the code to simulate the game but also gave me the solution after running it: it wasn’t worth paying a single dollar to play.</p><p>A stimulating discussion with a quant led me to two conclusions:</p><p>1. The AI didn’t provide a <a href="https://x.com/franloza93/status/1922698516427575441">correct answer</a> when I asked for a mathematical explanation of the problem.</p><p>2. The quant <a href="https://x.com/franloza93/status/1922715200739758274">was mistaken in his answer</a>, and the AI’s result was, in fact, correct: all simulations ended in a tie, so it was impossible to win.</p><p>Math has never been my strong suit, and perhaps that’s why I enjoy solving these problems with the Monte Carlo method so much. During my time in the financial sector, I was fascinated to see how quants used simulations to calculate probabilities for all sorts of strategies. So, with this problem as an excuse, I wanted to explore its variables and see how AI could lend me a hand.</p><h3>What If We Change the Rules a Bit?</h3><p>In the original problem, winning was impossible: the game always ended in a tie. But what if we change the rules? Imagine that pairs of different colors (one red card and one black card) are returned to the deck instead of being discarded. Using Cursor, I modified the code to simulate this new scenario:</p><pre>def simulate_game_with_replacement():<br>    &quot;&quot;&quot;Simulation where mixed pairs are added back to the deck.&quot;&quot;&quot;<br>    # Create initial deck<br>    deck = [&#39;R&#39;] * 26 + [&#39;B&#39;] * 26<br>    random.shuffle(deck)<br>    <br>    # Initialize counts<br>    X = 0  # Red-red pairs<br>    Y = 0  # Black-black pairs<br>    <br>    # Process cards until deck is empty<br>    while len(deck) &gt;= 2:<br>        # Draw a pair<br>        card1 = deck.pop(0)<br>        card2 = deck.pop(0)<br>        <br>        if card1 == &#39;R&#39; and card2 == &#39;R&#39;:<br>            X += 1<br>        elif card1 == &#39;B&#39; and card2 == &#39;B&#39;:<br>            Y += 1<br>        else:<br>            # Mixed pair - add cards back to the deck and shuffle<br>            deck.extend([card1, card2])<br>            random.shuffle(deck)</pre><p>The result was this:</p><pre>Probability of winning (X &gt; Y): 0.000<br>Probability of losing (X &lt; Y): 0.000<br>Probability of tying (X = Y): 1.000<br>Expected value: $0.00</pre><p>Still nothing! This variation also ended in a tie, so we still couldn’t win. But what if we complicate things a little more? What would happen if we remove the first card from the deck, play the game (discarding mixed pairs as before), and also discard the last card remaining in the deck?</p><p>I only needed to modify a couple of lines of code:</p><pre>    # Discard one card before starting<br>    deck.pop(0)<br>    <br>    # Process cards until only one card is left<br>    while len(deck) &gt; 1:</pre><p>The result was this:</p><pre>Probability of winning (X &gt; Y): 0.245<br>Probability of losing (X &lt; Y): 0.245<br>Probability of tying (X = Y): 0.510<br>Expected value: $24.51</pre><p>It seems that with this variation, we could actually win some money. The question now is: how much would you be willing to pay to play? If you pay about $24.50 (the “fair bet”), in the long run, you would neither win nor lose money. To have a chance of beating the casino, you’d have to pay less. If you pay more (which is usually the case), you’d end up losing money over time.</p><p>To make it more interesting, let’s say the casino sets a price of $20, and you’ve noticed the bet is in your favor. You have $200 in your wallet to wager. How much money would you expect to have after 1000 games? With the help of AI and a bit of <em>vibe coding</em>, I generated these images:</p><figure><img alt="Balance evolution in the first 1000 games over 100 simulations with an initial capital of $200" src="https://cdn-images-1.medium.com/max/1024/1*ITaE3prnVPUkwxiVRaqfbA.png" /><figcaption>Balance evolution in the first 1000 games over 100 simulations with an initial capital of $200</figcaption></figure><figure><img alt="Balance distribution in the first 1000 games over 100 simulations with an initial capital of $200" src="https://cdn-images-1.medium.com/max/1024/1*mK-76vvD033W519SnXv6pg.png" /><figcaption>Balance distribution in the first 1000 games over 100 simulations with an initial capital of $200</figcaption></figure><p>These simulations reveal something interesting: out of the 100 performed, in just over 30, we lost all our money. Wow! All that glitters is not gold. Even knowing the odds are in our favor, if we start with $200, we’ll lose our entire bankroll approximately 30% of the time.</p><h3>Strategy and Bankroll Management</h3><p>At this point, feeling like I was falling down a rabbit hole, I wanted to ask one last question: how much money should I start with to have a 95% probability of not losing it all after 1000 games?</p><p>After several iterations with Cursor, I obtained these results:</p><figure><img alt="Evolution of the probability of not losing all money after 1000 games based on the initial amount." src="https://cdn-images-1.medium.com/max/1024/1*stHD_utRo4hba15nK425xw.png" /><figcaption>Evolution of the probability of not losing all money after 1000 games based on the initial amount</figcaption></figure><p>The simulation gave me the answer: to have a 95% probability of keeping my capital after 1000 games, I needed to start with $560.</p><p>I reran the balance simulation with this new amount, and the results were more encouraging:</p><figure><img alt="Balance evolution in the first 1000 games over 100 simulations with an initial capital of $560" src="https://cdn-images-1.medium.com/max/1024/1*5bUcj6cw_M21tARAR0k50g.png" /><figcaption>Balance evolution in the first 1000 games over 100 simulations with an initial capital of $560</figcaption></figure><figure><img alt="Balance distribution in the first 1000 games over 100 simulations with an initial capital of $560" src="https://cdn-images-1.medium.com/max/1024/1*M4YJGctUMOvpZO5O-CthhA.png" /><figcaption>Balance distribution in the first 1000 games over 100 simulations with an initial capital of $560</figcaption></figure><p>In this scenario, the expected winnings were around $5000 after 1000 games, with a 5% risk of losing everything. Doesn’t sound too bad, right?</p><h3>Final Thoughts</h3><p>This problem is straightforward, and my only goal was to satisfy my curiosity. Nevertheless, we are witnessing <a href="https://venturebeat.com/ai/meet-alphaevolve-the-google-ai-that-writes-its-own-code-and-just-saved-millions-in-computing-costs/">breathtaking advances</a> in the development of agents capable of devising new algorithms for complex mathematical problems.</p><p>My reflection is that while AI may not yet solve complex mathematical problems analytically, it is a formidable tool for generating code. And this, in turn, allows it to develop algorithms or simulations to tackle such problems. That’s where, for now, I believe much of its value lies: in giving us the necessary code to simulate and analyze these issues. I am convinced that the future will bring very interesting advances by combining these two facets — mathematical reasoning and computation — which will complement each other.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5f2a7dd56529" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Operational vs. Analytical Data Teams: A Framework for Focus and Scale]]></title>
            <link>https://franloza.medium.com/operational-vs-analytical-data-teams-60a2cf412f6f?source=rss-ce22534f84f2------2</link>
            <guid isPermaLink="false">https://medium.com/p/60a2cf412f6f</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[productivity]]></category>
            <dc:creator><![CDATA[Fran Lozano]]></dc:creator>
            <pubDate>Sun, 02 Mar 2025 11:50:05 GMT</pubDate>
            <atom:updated>2025-03-02T11:50:05.425Z</atom:updated>
            <content:encoded><![CDATA[<p>If you’ve been on a data team, you might have experienced the crunch: one minute you’re racing to fix a pipeline that keeps the product alive, the next you’re scrambling to deliver insights that execs are breathing down your neck for. It’s a common scenario in growing companies — starting with internal reporting support, only to find priorities shifting as the business scales. Suddenly, because you “know how to work with data,” you’re thrust into high-stakes operational challenges. It’s not inevitable, but it happens often enough to be worth talking about. Early in my career, I witnessed a startup data team face a critical billing pipeline failure. Instead of crumbling under pressure, the company pivoted effectively from analytical to operational focus. This experience inspired me to explore the benefits of separating these functions — here’s how it works, why it matters, and how you can pull it off to keep your team sharp and your product humming.</p><h3>Operational vs. Analytical: Drawing the Line</h3><p>So, what’s the difference? Operational data teams are the backbone of the product — they keep it running smoothly. Their pipelines are tied to the customer experience, and when they break, the impact hits fast. Imagine a SaaS company — call it Acme Corp — where the billing pipeline processes usage logs using a microservice, stores the output in S3, and serves aggregated data via an API backed by ClickHouse. That system has to be bulletproof, or customers might face billing errors — overcharges, undercharges, or worse. That’s operational: real-time, high-stakes, and customer-facing. The team behind it — think software engineers, backend devs, and data engineers — lives for reliability and speed.</p><p>Analytical data teams play a different game. They’re the strategists, digging into data to uncover insights that guide the business. They load data into a data warehouse, model trends, and build dashboards that execs use to chart the course. Picture a hypothetical startup with a lean crew and big ambitions. Their analytical team starts with invoice data in Snowflake, tracking accounts receivable to forecast cash flow — classic analytics work. But then growth kicks in, and suddenly that invoice data needs to feed a financing partner three times a day to unlock working capital. The stakes jump, and what was once “chill” analytics becomes mission-critical. Here’s where the split shines: the operational team builds a high-frequency pipeline in Apache Airflow to share the data with the third-party tool, using the same sources as the previous pipelines but with custom retry logic and extra monitoring to ensure timely invoice delivery. This setup ensures that the operational pipeline is robust and reliable, while the analytical team can continue running dbt at a lower frequency, analyzing payment trends without interference. Same source, separate missions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PzSEI1wbLH7fpknUR7sATg.png" /></figure><p>Here’s my rule of thumb: if the data directly powers the product or critical business operations, and its failure would significantly disrupt customer experience or business continuity, it’s operational. If it’s for internal decisions — like forecasting, finance reporting or product analytics — it’s analytical. One tricky spot? Customer dashboards. If they’re real-time and embedded in the product, they’re operational. If they’re internal tools for strategy, they’re analytical. Take Reverse ETL, like syncing data to Salesforce. If it’s for sales reps to prioritize leads internally, it’s analytical. If it triggers customer-facing actions — like sending an automated email, or updating service levels — it’s operational. It’s all about the use case.</p><h3>Why This Split Matters</h3><p>Focus is the big win here. When operational teams own the pipelines that keep the product alive, they can zero in on uptime and performance without getting sidetracked by analytics requests. Analytical teams, free from the grind of on-call duties, can dive deep into the data that drives decisions. I’ve seen this clarity cut through the noise — no more endless debates over priorities or confusion about who’s fixing what. And the payoff isn’t just peace of mind; it’s tangible. Operational reliability goes up, insights get sharper, and your team doesn’t burn out. I’ve known engineers who quit because they couldn’t handle another weekend lost to a failing pipeline. This split keeps them in the fight.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*vhFrF3De27yOG1k-l2lS8g.jpeg" /></figure><p>Back at Acme Corp, the operational team owned the S3-ClickHouse pipeline, fortifying it with error handling and alerts. The analytical team tapped its S3 outputs, modeling data in Snowflake with dbt to track usage and revenue. In this hypothetical, they even spotted an overage billing glitch, saving Acme from customer backlash. By processing the same data differently, they cross-checked it, catching issues a single pipeline might miss. The split didn’t just avoid overlap — it bolstered data integrity.</p><h3>Pulling It Off: Making It Work</h3><p>Think this only works for big teams? Think again. It’s less about size and more about intent. On a small team, you might carve out mornings for operational pipelines and afternoons for analytics — or alternate sprints. As you scale to, say, a 50-engineer org, you can dedicate specialists or full squads. This approach bends to fit your headcount, but the principle holds: protect each team’s mission.</p><p>One must-do: keep the lines open. Operational and analytical teams rely on each other — analytical work often feeds off operational data — so they need to sync regularly. Quick standups to align on schema changes or flag quality issues aren’t overhead; they’re essential. Without them, you’re asking for drift and last-minute scrambles. Clear communication isn’t just nice to have — it’s how you make this stick.</p><h3>Your Team Deserves This</h3><p>If your data team’s stuck juggling firefights while analytics sits on the back burner, I’ve been there. Splitting operational and analytical work is a fix born in the data trenches. It’s kept teams sane, shipped rock-solid products, and unlocked insights we’d never have reached otherwise. Whether you’re a startup CTO or a data lead at a growing firm, take a step back. Are you chasing emergencies when you could be driving breakthroughs? Draw that line — your team will thank you.</p><p><em>Special thanks to </em><a href="https://emilie.substack.com/"><em>Emilie</em></a><em>, the data lead who expertly navigated the transition from analytical to operational focus, proving this framework’s value in the trenches. And to Paige, whose insights three years ago sparked this article — better late than never!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=60a2cf412f6f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What is the Easiest Way to Move Data from Stripe to Postgres?]]></title>
            <link>https://franloza.medium.com/what-is-the-easiest-way-to-move-data-from-stripe-to-postgres-54d5e489bda8?source=rss-ce22534f84f2------2</link>
            <guid isPermaLink="false">https://medium.com/p/54d5e489bda8</guid>
            <category><![CDATA[stripe]]></category>
            <category><![CDATA[dlt]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Fran Lozano]]></dc:creator>
            <pubDate>Thu, 27 Jun 2024 16:04:53 GMT</pubDate>
            <atom:updated>2024-06-27T16:35:51.533Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*p9nDRo0v37rcUmWa" /><figcaption>Generated with AI by me using Microsoft Designer</figcaption></figure><p>Lately, I find quite a few opportunities to satisfy my curiosity on Twitter/X. The latest one I found in this <a href="https://x.com/xoelipedes/status/1801622607956992376">tweet</a>.</p><blockquote>What is the best way to sync data from Stripe to Postgres? I don’t want to write any code or pay anything if possible.</blockquote><blockquote>Ideally, something that automatically creates the necessary tables and syncs with a single command, and if it’s open source, even better.<br> — Xoel López</blockquote><p>I believe extracting data from Stripe is a quite common use case in many digital businesses that want to track metrics like their MRR (Monthly Recurring Revenue), or analyze payment data by connecting it with their CRM and/or product backend. In this case, this request could be generalized to “I want to move data from &lt;Insert SaaS here&gt; to &lt;Insert database here&gt;&quot; with the following requirements:</p><ol><li>It should be free.</li><li>Without writing a single line of code.</li></ol><p>For this case, I assumed real-time data was not needed.</p><p>I have been moving data for the last 5 years, and to this day, I’m still not sure which tool to recommend. I’ve developed ad-hoc integrations with Stripe and have used <a href="https://meltano.com/">Meltano</a> and <a href="https://airbyte.com/">Airbyte</a> for similar tasks, but never with the Stripe integration. Both Meltano and Airbyte seem like good tools to me, but I recommended Airbyte because it had a <a href="https://airbyte.com/product/airbyte-cloud">cloud</a> that allowed for a quick proof of concept in a free and visual way through the browser.</p><p>After recommending it (mistake, I should have tested the connector first 🤦🏻), I created a trial account in Airbyte Cloud and set up a new connection, but it failed to create with a 502 error. I tried it on different days, so I assumed it wasn’t a temporary issue.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GiRf6JYztg_ljUK8DWr-dA.gif" /><figcaption>502 error returned by Airbyte Cloud when creating the connection</figcaption></figure><p>It seems that for this use case, Airbyte (or at least Airbyte Cloud) wasn’t the best option. <a href="https://x.com/Alejandrogbu/status/1801625388239487403">Another comment in the thread</a> suggested dlt, so I thought it would be a good opportunity to try it out and draw my own conclusions.</p><h4>Testing dlt</h4><p><a href="https://dlthub.com/">dlt</a> is a Python library for extracting data from multiple sources and loading it into a database or file system. In fact, its name (dlt = data load tool) is very reminiscent of another well-known library for data engineers: <a href="https://www.getdbt.com/">dbt</a> (data build tool). dlt aims to be the EL part of the ELT (Extract-Load-Transform) paradigm, leaving the T to dbt.</p><p>One of the things that surprised me was its simplicity and lightness. With dlt init stripe_analytics postgres, I had a fully functional skeleton where I only had to configure a secrets.toml file to make it work. However, this broke one of the initial problem requirements: Not writing a single line of code. Even though it&#39;s configuration, you need to clone the repository, create a virtual environment, install dependencies... and then automate its execution on a remote machine. Certainly a lot of work for a no-code tool.</p><p>So, I set out to develop an application to deploy an automated pipeline that works simply using the browser.</p><p>To start, I modified the main script generated by dlt with a much more configurable interface: Load type (<a href="https://dlthub.com/docs/general-usage/full-loading">full load</a> and <a href="https://dlthub.com/docs/general-usage/incremental-loading">incremental</a>) and time intervals (start and end date). I don’t like incremental pipelines that use a state because they make it difficult to do backfilling of data when, for example, the data wasn’t ready at the source (Very well explained in <a href="https://juhache.substack.com/p/debugging-data-pipelines">this article</a>).</p><p>I also added a Docker image so it could be run without needing to create a virtual environment or install dependencies and could be configured with environment variables. This is very useful when running jobs in Kubernetes (<a href="https://franloza.medium.com/10-short-rules-for-a-data-engineer-ef5a958627e7">point 5 of my 10 rules for a data engineer</a>).</p><p>To meet the first requirement (it should be free), I used GitHub Actions, which gives you 2000 minutes per month <a href="https://github.com/pricing">for free</a>, to automate the loading process. To host the PostgreSQL database, I chose <a href="http://tembo.io/">Tembo</a>, which offers <a href="https://tembo.io/pricing/">free</a> PostgreSQL instances with 0.25 vCPU and 1 GB of memory and supports the <a href="https://github.com/hydradatabase/hydra">Hydra</a> extension in its <a href="https://tembo.io/docs/product/stacks/analytical/data-warehouse">DataWarehouse stack</a> (because a data warehouse can be “<a href="https://www.linkedin.com/posts/tayloramurphy_just-had-a-great-conversation-with-a-data-activity-7206699277425754113-R0ob">just Postgres</a>”).</p><p>With all this, you could automate the extraction and loading of Stripe to PostgreSQL without leaving the browser simply by:</p><ol><li>Forking <a href="https://github.com/franloza/stripe-to-postgres/tree/main">this repository on Github</a>.</li><li>Configuring <a href="https://github.com/franloza/stripe-to-postgres/blob/5bdb7627eed3053346a6588deefc879fededc0f9/.github/workflows/load_data.yaml#L20">the environment variables</a> as GitHub secrets.</li></ol><p>I have to admit that I cheated a bit, and this works well as a proof of concept, but if you want to tweak something in the pipeline, you already have to get your hands dirty and touch some code. Even so, dlt is so well designed that just by <a href="https://github.com/franloza/stripe-to-duckdb/commit/0769dcfaaa08092090793508d46eac5c04ba4997">changing a couple of variables</a>, you can make the pipeline write to a different database (like DuckDB). A real discovery and one that I will probably recommend from now on.</p><p>See you soon!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=54d5e489bda8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create a Data Web App for Free with Evidence]]></title>
            <link>https://franloza.medium.com/how-to-create-a-data-web-app-for-free-with-evidence-c67ebaf91909?source=rss-ce22534f84f2------2</link>
            <guid isPermaLink="false">https://medium.com/p/c67ebaf91909</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[web-app-development]]></category>
            <category><![CDATA[running]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Fran Lozano]]></dc:creator>
            <pubDate>Sat, 08 Jun 2024 11:08:00 GMT</pubDate>
            <atom:updated>2024-06-10T12:56:42.972Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Image of runners being monitored by a data application" src="https://cdn-images-1.medium.com/max/1024/1*-QGSNnKMlMIwEUdR9ji1MA.jpeg" /><figcaption>Generated with AI by me using Microsoft Designer</figcaption></figure><p>Recently, I had the privilege of meeting <a href="https://x.com/davidgasquez">David Gasquez</a> and learning about his project, <a href="https://github.com/datonic/datadex">Datadex</a>. This initiative collects and shares a catalog of open datasets, making them accessible for anyone to analyze. Inspired to <a href="https://github.com/datonic/datadex/pull/61">contribute</a>, David and I had several discussions about data and how to easily share discoveries and analyses from public datasets. David introduced me to several tools, including <a href="https://evidence.dev/">Evidence</a>, which I realized could be a fantastic way to go beyond simply creating catalogs of open data and actually <strong>share stories and insights derived from the data</strong>.</p><p>One of the ideas I loved most about Datadex was how David managed to automate the ingestion, transformation, and publication of open data without spending a single euro. He used GitHub Actions to automate <a href="https://github.com/datonic/datadex/blob/31413d4d28cd13c6cf041fde5473b0aee5fccf02/.github/workflows/ci.yml#L36">workflows</a> and Hugging Face to expose the <a href="https://huggingface.co/datonic">datasets</a>. This concept of creating an end-to-end flow — from ingestion to visualization — at <strong>zero cost</strong> and with easy sharing capabilities, inspired me to start a new proof of concept using Evidence. Before we dive into the details, let me provide a bit of context.</p><h3>What is Evidence?</h3><p><a href="https://evidence.dev/">Evidence</a> is a JavaScript framework for creating data apps with Markdown and SQL. You can build a web application with pre-existing components like bar charts and dropdowns, and integrate them using SQL within your markdown. The final output is a static website that can be hosted on platforms like Netlify or Vercel.</p><p>The three things I liked most about Evidence are:</p><ol><li><a href="https://evidence.dev/blog/why-we-built-usql/">Universal SQL</a>: A query engine powered by DuckDB’s WebAssembly that allows you to query different data sources quickly and directly from your browser.</li><li><a href="https://docs.evidence.dev/deployment/netlify/#evidence-main-article">Static site</a>. Evidence generates a static site by running all the queries at build time, and it caches the data in parquet files that are queried from the browser, which is extremely fast.</li><li><a href="https://docs.evidence.dev/core-concepts/templated-pages/">Templated pages</a>. You can generate pages from data, so one single markdown file could generate a page for every data category (e.g. customer, city, etc).</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DnqorAo7oKXhq6XBUj1s5A.png" /><figcaption>How page templates work in the <a href="https://circuitocarrerasdiputacioncuenca.netlify.app/2024/">web application</a></figcaption></figure><p>For this project, I opted for Evidence over other options like <a href="https://streamlit.io/">Streamlit</a> or <a href="https://www.rilldata.com/">Rill</a> because I wanted to deploy something without having to set up a virtual machine on a cloud provider or use the cloud services of those libraries. Evidence was the only one that offered me that possibility by <strong>deploying as a static website</strong>. I also liked the fact that I could develop the application in Markdown and use SQL for data preparation.</p><p>I believe Evidence is an excellent option for analysts who lack Python skills or for individuals like myself who prefer not to delve into frontend development. To create more complex applications, the developer has the flexibility to enhance its functionality using <a href="https://docs.evidence.dev/components/custom-components/">custom components</a> through Svelte.</p><h3>Use case</h3><p>One of my favorite hobbies is running. Apart from training, I love participating in races to test my current fitness level and get that adrenaline rush from running alongside others who share the same hobby. I live in a region of Spain called Cuenca, which hosts a series of 26 races in different towns throughout the region. The athletics club in my town organizes one of these 26 races, and they’re always keen to know the metrics and statistics of participants in each race to see if they’ve improved compared to the previous year. The company that provides the timing chips publishes the final times, but participation statistics aren’t public. So, I decided to develop an application that could help all the towns access the race statistics they organize.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lqNmozw10fhXG0nLbo0p9g.jpeg" /><figcaption>Advertising poster of the <a href="https://www.circuitocarrerasdiputacioncuenca.com/">competition</a>, organized by the regional government.</figcaption></figure><p>With a <a href="https://github.com/franloza/running-races-insights/blob/main/elt/extract.py">Python script</a>, I extracted raw data from the website that publishes the results, <a href="https://github.com/franloza/running-races-insights/blob/main/elt/transform.py">transformed</a> it (actually, using dbt for processing a single model isn’t necessary, right?), and created a DuckDB database with the cleaned data. You can query the data using SQL <a href="https://shell.duckdb.org/#queries=v0,LOAD-httpfs~%0AATTACH-&#39;https%3A%2F%2Fraw.githubusercontent.com%2Ffranloza%2Frunning%20races%20insights%2Fmain%2Fsources%2Frace_results%2Frace_results.duckdb&#39;-AS-race_results-(READ_ONLY)~%0A">here</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*214_qygIudzZ-zvbVjPytA.png" /><figcaption>Thanks to <a href="https://duckdb.org/docs/api/wasm/overview.html">DuckDB Wasm</a> and <a href="https://shell.duckdb.org/">DuckDB Web Shell</a>, it is possible to query databases stored anywhere on the internet. Credit to David for showing me this tool.</figcaption></figure><p>The database is quite small, occupying only 3.26 MB, so I decided to publish it directly on GitHub because:</p><ol><li>It wouldn’t be updated very frequently (at most once a week).</li><li>It would only be read at build time to generate the Evidence cache layer.</li><li>It allowed deploying the application without the need to prepare the data or run any scripts.</li></ol><p>There are free alternatives for storing these databases such as <a href="https://huggingface.co/pricing">HuggingFace</a> (with 50GB of ephemeral space) or <a href="https://motherduck.com/product/pricing/">Motherduck</a> (with 10 GB of Storage), which would allow storing larger datasets with a higher update frequency. With <a href="https://www.netlify.com/pricing/">Netlify’s starter plan</a>, any website developed with Evidence can be published for free and quite easily (Be aware of the 100GB bandwidth limit!) . If data updates are needed, these can be automated with <a href="https://docs.github.com/en/billing/managing-billing-for-github-actions/about-billing-for-github-actions#included-storage-and-minutes">GitHub Actions</a>, which include 2000 minutes per month for free.</p><p>Everything that is necessary to generate the webpage is stored in GitHub: Queries, markdown files and data (as Duckdb database). Thanks to templated pages, I was able to generate one page for every race with just one single markdown file. Dropdowns work really well to filter the data, and I used them to filter by age category.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g5FFABAbBDK8ax_15FBwjA.png" /><figcaption>Dropdowns are very useful to filter the data dynamically.</figcaption></figure><p>You can find the web application <a href="https://circuitocarrerascuenca.franloza.com/">here</a>.</p><h3>Conclusion</h3><p>In my latest <a href="https://franloza.medium.com/building-a-data-application-with-duckdb-43823070730">project to visualize data from coches.net</a>, I missed the part of sharing the results in a web application. Sure, you could download the data locally, set up a Dash server on your machine, and then visualize the data. But let’s be realistic, if you truly want the data to be used and explored, you need to provide a web application for it (whether through a BI tool or an ad-hoc application).</p><p><strong>With Evidence, it’s very easy to create data products using the versatility of DuckDB, the simplicity of Markdown, and the power of SQL</strong>. And all without breaking the bank!</p><p>You can find the full code of the application on <a href="https://github.com/franloza/running-races-insights/tree/main">Github</a>. Do you think Evidence was a good choice for this use case? I would love to read your opinions.</p><p>Stay curious!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c67ebaf91909" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a data application with DuckDB]]></title>
            <link>https://franloza.medium.com/building-a-data-application-with-duckdb-43823070730?source=rss-ce22534f84f2------2</link>
            <guid isPermaLink="false">https://medium.com/p/43823070730</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[modern-data-stack]]></category>
            <category><![CDATA[app-development]]></category>
            <dc:creator><![CDATA[Fran Lozano]]></dc:creator>
            <pubDate>Sun, 04 Dec 2022 20:22:47 GMT</pubDate>
            <atom:updated>2022-12-04T22:06:18.739Z</atom:updated>
            <content:encoded><![CDATA[<h4>Visualizing the car and motorcycle market in Spain from my computer.</h4><figure><img alt="Diagram of the data flow of the application, using dbt, duckDB, dagster and Dash." src="https://cdn-images-1.medium.com/max/1024/1*iCWl9YRYi5ckTa-FCmSwOQ.png" /><figcaption>Data flow of the application</figcaption></figure><h4>Introduction</h4><p><a href="https://duckdb.org/">DuckDB</a> is a technology that has gained a lot of attention in recent months. I myself discovered its full potential a few months ago when I was able to query some parquet files from my terminal in a very simple way.</p><h3>Fran Lozano on Twitter: &quot;I just discovered DuckDB (And its CLI) and I could not be more excited about it. Querying parquet and CSV files is easy as pie. https://t.co/JaufIOF99D pic.twitter.com/WQNhYzW09g / Twitter&quot;</h3><p>I just discovered DuckDB (And its CLI) and I could not be more excited about it. Querying parquet and CSV files is easy as pie. https://t.co/JaufIOF99D pic.twitter.com/WQNhYzW09g</p><p>Used to working with parquet files in my day-to-day work, using the <a href="https://duckdb.org/docs/api/cli.html">DuckDB CLI</a> significantly increased my productivity. I could check that the schema of the tables was correct, add data in a matter of seconds, and even read the files directly from S3. Quite a discovery.</p><h4><strong>But why is DuckDB so revolutionary?</strong></h4><p>Apart from its command line utility for querying <a href="https://duckdb.org/docs/data/csv">CSV</a>, <a href="https://duckdb.org/docs/data/parquet.html">Parquet</a>, and <a href="https://duckdb.org/docs/extensions/json.html">JSON</a>, <strong>DuckDB enables embedded interactive analytics and can serve data to interactive visualization tools</strong>.</p><p>As Kojo explains in their <a href="https://kojo.blog/duckdb/">blog</a>, DuckDB fills the gap in embedded databases for online analytical processing (OLAP).</p><figure><img alt="Quadrant of 4 type of databases: Transactional/Analytical versus Embedded/Stand-Alone. DuckDB is located in the Analytical-Embedded cell." src="https://cdn-images-1.medium.com/max/1024/1*g2sj_MLPytmBVn_ywc2bQg.png" /><figcaption>Image by <a href="https://kojo.blog/about/">Kojo Osei</a> on <a href="https://kojo.blog/duckdb/">Kojo Blog</a></figcaption></figure><p>It is no longer necessary to use a stand-alone data warehouse like Snowflake or BigQuery to develop an application that performs complex analytical queries. <strong>With DuckDB you have the simplicity of SQLite and the functionality of Snowflake, on your local machine</strong>. This opens up a whole new world of possibilities for developing data applications with open-source tools.</p><p>This trend towards the creation of data applications has been reflected recently this year with the <a href="https://www.snowflake.com/blog/snowflake-to-acquire-streamlit/">acquisition of Streamlit by Snowflake</a>. DuckDB is not only limited to the development of embedded data applications, but can also be used for <a href="https://tech.gerardbentley.com/python/data/intermediate/2022/04/26/holy-duck.html">rapid prototyping with tools such as Streamlit</a>, and then migrate to Snowflake or another data warehouse to serve a web application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0kE-ixcGARCginWYBftGGA.png" /><figcaption>Screenshot from <a href="https://streamlit-demo-uber-nyc-pickups-streamlit-app-456wus.streamlit.app/?pickup_hour=8#nyc-uber-ridesharing-data">NYC Uber Ridesharing Data Streamlit App</a>. Find more examples in their <a href="https://streamlit.io/gallery">gallery</a>.</figcaption></figure><p>On the plus side, <a href="https://rmoff.net/2022/10/20/data-engineering-in-2022-exploring-dbt-with-duckdb/">dbt has integration with DuckDB</a>, and take my word for it, <strong>it is blazing fast</strong>. This will allow you to use dbt to transform data without having to run a local database like PostgreSQL. In addition, dbt has the ability <a href="https://hightouch.com/blog/dbt-snapshots-guide">to snapshot your data</a>, which allows you to keep the history of changes in a table to analyze data over time. You can even <a href="https://medium.com/@danthelion/analyzing-bigger-files-locally-in-seconds-with-duckdb-and-datagrip-8753486a3da9">query data from DataGrip</a> (or PyCharm Professional), which makes it especially attractive for data application development.</p><h4><strong>Use case</strong></h4><p>In the middle of the year, I felt the need to buy my first car. In some big cities like Madrid, the regulation for polluting cars is getting stricter and stricter, so I opted to buy a hybrid car. But there was a problem: The second-hand market for hybrid cars is not as developed as the traditional fuel market, and that meant that there were not many good opportunities. I hypothesized that there was a lack of supply of hybrid second-hand cars, so I wanted to analyze it and see if it was possible to find opportunities.</p><p>One of the main portals for selling second-hand cars in Spain is coches.net. Using a <a href="https://gist.github.com/franloza/dcaaf90151b7b59898b34b1ddfd11dca">Bash script</a>, I downloaded the data and analyzed it with <a href="https://pandas.pydata.org/">Pandas</a> and <a href="https://plotly.com/python/">Plotly</a>.</p><figure><img alt="Image of a distribution of car classified ads in coches.net by type of fuel" src="https://cdn-images-1.medium.com/max/837/1*CxouwfE2ROYDLCe8rt6i5g.png" /><figcaption>Distribution of car classified ads in coches.net by type of fuel</figcaption></figure><p>Here I could see that the percentage of new hybrid cars (green and purple, second and third charts), was greater than used hybrid cars (first chart), which barely reached 5%.</p><p>At this point, I wanted to go a little further and see if it was possible to build an application to track the change in car prices over time. I managed to convince my friend <a href="https://www.linkedin.com/in/jorge-sanchez-cremades/">Jorge</a>, who was interested in buying a vehicle (a motorcycle instead of a car), and I presented him with a design of what I wanted the application to look like.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iZmyfVPKIlHm638yc8zK5w.png" /><figcaption>Mockup of the application that I shared with Jorge</figcaption></figure><p>Jorge, always willing to learn and work on new things, was fine with it, so we got down to work.</p><h4>Application development</h4><p>When we started, we only had a Bash script, and a Jupyter notebook with some charts, but we needed to create an interactive application. This is when we decided to use DuckDB as the application database because of its speed and simplicity.</p><p>In addition, we needed to have a snapshot of prices every day so we could chart their evolution and spot opportunities. With these requirements, I decided to use Dagster instead of Airflow because I wanted to test this platform, which was also well integrated with dbt and Jorge already had experience with it. We decided to use dbt for the creation of the tables that were going to be read by the application as well as the snapshots.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HNez_Z8s8E7pkhki70DQYw.png" /><figcaption>Overview of the DAG that described the relation between tasks in Dagster.</figcaption></figure><p>We migrated the Bash script to a <a href="https://github.com/franloza/coches-net-dashboard/blob/master/orchestration/data_manager/resources/coches_net.py">Python module</a> and Jorge adapted it to also download the motorcycle dataset. Using <a href="https://github.com/franloza/coches-net-dashboard/blob/master/transformation/macros/get_fct_prices_model.sql">macros</a> from dbt, we reused a lot of code in the data modeling since the model was practically identical. The dependencies between tasks we defined in Dagster, by creating a <a href="https://github.com/franloza/coches-net-dashboard/blob/master/orchestration/data_manager/jobs.py">job</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-G-LUIn3y-Y-cLdPTc0kxQ.png" /><figcaption>After downloading the data, the data were snapshotted and then transformed to the final models.</figcaption></figure><p>Jorge was in charge of the development of the visualization application. For this purpose, he used <a href="https://dash.plotly.com/">Dash</a>, a framework for building data apps in Python. With this tool, we were able to explore from different perspectives the car and motorcycle market in Spain, and track changes in prices.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*n19S68u3et41GzGuNjFw6Q.png" /><figcaption>Screenshot of the application running in my browser</figcaption></figure><p>Finally, we prepared the application so that it could be deployed with a single command using Docker Compose. We also tried to deploy it on an EC2 machine so that it would refresh the database data daily in an automated way, but we ran into a problem: AWS IPs were blocked in coches.net, so we couldn’t automate this last step.</p><h4><strong>Conclusion</strong></h4><p>Thanks to open-source tools and especially DuckDB, It was possible to build a data application in a simple way. I also discovered that the second-hand market for hybrid cars in Spain still has to grow and lacks of supply compared to fuel cars, which made it difficult to find good opportunities.</p><p>You can find the full code of the application on <a href="https://github.com/franloza/coches-net-dashboard">Github</a>. See you soon!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=43823070730" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[10 Short Rules for a Data Engineer]]></title>
            <link>https://franloza.medium.com/10-short-rules-for-a-data-engineer-ef5a958627e7?source=rss-ce22534f84f2------2</link>
            <guid isPermaLink="false">https://medium.com/p/ef5a958627e7</guid>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[pipeline]]></category>
            <category><![CDATA[rules]]></category>
            <dc:creator><![CDATA[Fran Lozano]]></dc:creator>
            <pubDate>Sat, 26 Jun 2021 08:03:22 GMT</pubDate>
            <atom:updated>2021-07-01T14:31:36.778Z</atom:updated>
            <content:encoded><![CDATA[<h4>They will save you headaches and your co-workers will be grateful.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XaDrGhTRSj6R0lM5ECNqng.jpeg" /><figcaption>Credit: MichaelGaida/Pixabay</figcaption></figure><blockquote>— Hey, yesterday’s job failed. How can I recover the data?</blockquote><blockquote>— Oh! That’s bad. Well, first you need to get a list of all the files that have been partially updated in S3, delete the files and their corresponding rows in the table with a “DELETE FROM … WHERE” command. Then, you can rerun the job.</blockquote><blockquote>— Why can’t I just rerun the job?</blockquote><blockquote>—Because you could create duplicates in the table.</blockquote><p>It was just an example, but you should never have a conversation like this with a co-worker. As a data engineer, I have dealt with thorny pipelines that have taught me valuable lessons. Here are ten rules that have helped me to develop scalable and resilient jobs. I have learned these rules from experience, breaking things, triggering alarms, and spending long hours debugging. These rules are biased to my experience with Python, Airflow, and Kubernetes, but they still may be helpful in your career as a data engineer.</p><ol><li>Decouple configuration from implementation. <a href="https://medium.com/buildit/build-once-deploy-everywhere-part-1-706d7affaf0f">Build Once, Deploy Anywhere</a>. You don’t need two different pieces of code to bring two tables from PostgreSQL to Redshift. Projects like <a href="https://meltano.com/">Meltano</a> and <a href="https://airbyte.io/">Airbyte</a> are good open-source integration tools to not reinvent the wheel.</li><li>Make your jobs <a href="https://medium.com/ssense-tech/lets-get-idempotence-right-59f227178bb8">idempotent</a>. Other people will use your pipelines. If they run your jobs multiple times (there are countless reasons to do that), make sure they will produce the same results as running them just once.</li><li>Isolate development and production environments. This will allow you and your team to test the pipelines safely. Do you want to overwrite a production table when developing a new version of a job?.</li><li>Keep scalability always in mind. If you work in a start-up, this is very important. Kubernetes + Airflow is an <a href="https://medium.com/bluecore-engineering/were-all-using-airflow-wrong-and-how-to-fix-it-a56f14cb0753">excellent combination</a> when the number of jobs starts to grow.</li><li>Learn how to code good CLIs. Implement user-friendly CLIs and use arguments and environment variables to configure them. Tools like <a href="https://towardsdatascience.com/typer-build-powerful-clis-in-one-line-of-code-using-python-321d9aef3be8">Typer</a> make it even funnier.</li><li>Isolate job dependencies. Don’t fall into the <a href="https://medium.com/knerd/the-nine-circles-of-python-dependency-hell-481d53e3e025">dependency hell</a>. Virtual environments and containers are great tools to avoid conflicts between packages. And don’t forget to pin your dependencies!.</li><li>Make your pipelines easy to configure. Give visibility to your pipelines by moving configuration parameters to a human-readable format. This will add a metadata layer to your pipelines and allow non-Python users to configure them. YAML or JSON files are easier to configure than an Airflow DAG.</li><li>Measure resource usage. If you can’t measure it, you can’t improve it. Rely on tools and services like Grafana or Datadog to monitor your jobs.</li><li>Don’t expose your secrets. Limit permissions and keep your sensitive variables secured. Do not pass sensitive information around when possible. Kubernetes secrets or a cloud secret manager are better alternatives than Airflow variables.</li><li>Document your pipelines, sources, and transformations. Tools like <a href="https://docs.getdbt.com/docs/building-a-dbt-project/documentation">dbt</a> allow you to document and let other people understand your datasets and pipelines. Did you know that you can <a href="https://geoffruddock.com/markdown-documentation-in-airflow-dags">embed markdown documentation into your Airflow DAGs</a>?.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ef5a958627e7" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>