<?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 Bella Jiang on Medium]]></title>
        <description><![CDATA[Stories by Bella Jiang on Medium]]></description>
        <link>https://medium.com/@bella23jiang?source=rss-6d4b6bc5adaa------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*zh9odFVJmRcGqc8Oc6E9JA.jpeg</url>
            <title>Stories by Bella Jiang on Medium</title>
            <link>https://medium.com/@bella23jiang?source=rss-6d4b6bc5adaa------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 15:31:22 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@bella23jiang/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 not to go AI-bankrupted on Snowflake]]></title>
            <link>https://medium.com/@bella23jiang/how-not-to-go-ai-bankrupted-on-snowflake-917683fd8182?source=rss-6d4b6bc5adaa------2</link>
            <guid isPermaLink="false">https://medium.com/p/917683fd8182</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[snowflake-cortex]]></category>
            <dc:creator><![CDATA[Bella Jiang]]></dc:creator>
            <pubDate>Wed, 02 Sep 2026 11:00:04 GMT</pubDate>
            <atom:updated>2026-09-02T11:00:04.146Z</atom:updated>
            <content:encoded><![CDATA[<p>After blasting Coco/Cortex AI bill on day one for a personal project, I started looking up whether it iscommon to overrun AI budget and how not to be AI bankrupted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*J74LT8x4nZKr4Ae2uwxOFg.png" /></figure><p>Different from your heavily subsidised Claude agents, for functions that generate new text using provided text (AI_COMPLETE, AI_CLASSIFY, AI_FILTER, AI_AGG, SUMMARIZE), both input and output tokens are billable.</p><p>Note that there is a managed system prompt overhead (skillls etc.), automatically added to the beginning of input text when using Cortex AI functions, meaning the real input token count is always higher than the simple prompt-length count. Check out Mate’s blog on <a href="https://blog.namilink.com/i-intercepted-snowflake-cortex-codes-system-prompt-here-s-why-it-writes-broken-sql-b7168ba7e5f7?sk=72e4303cb0d38f88b76affb1e7aca8e7">Cortext Code’s context problem</a> for more.</p><p>*A token is the smallest unit of text processed by Snowflake Cortex AI functions, approximately equal to four characters.</p><p>Found a useful <a href="https://www.reddit.com/r/snowflake/comments/1uj55r6/comment/oularbr/?utm_source=share&amp;utm_medium=web3x&amp;utm_name=web3xcss&amp;utm_term=1&amp;utm_content=share_button">Reddit post</a>, Angela mentioned a few points which are very helpful on AI budgeting. My key takeaways:</p><p><strong>1. Start fresh each time</strong><br> Old conversations carry all their previous text forward as input tokens every time you send a new message. Starting a new conversation for each new task keeps that baggage from piling up and costing you money for context you don’t actually need anymore.</p><p><strong>2. Use Plan Mode and don’t let it act on its own</strong><br> Plan Mode makes the agent propose what it’s going to do <em>before</em> doing it, instead of just doing it. That gives you a chance to catch something wrong or wasteful before it runs, rather than after you’ve already paid for it. Be aware that Plan Mode toggle is default off, it needs to be switched on in every new conversation.</p><p><strong>3. Cap how long any single action can run (for example, 30 seconds)</strong><br> Long-running actions are usually where costs quietly spike. This rule forces the agent to pause and check with you before doing anything that would take longer, so you’re deciding on the expensive stuff instead of finding out afterward.</p><p><strong>4. Get test syntax back instead of having the agent run tests itself</strong><br> Running and interpreting tests inside the conversation burns tokens. Instead, have the agent hand you the test <em>code</em>, and run it yourself on regular compute (which is much cheaper than token-based usage).</p><p>The query to check AI usage</p><pre>SELECT<br>    USAGE_DATE,<br>    SERVICE_TYPE,<br>    CREDITS_USED,<br>    CREDITS_BILLED<br>FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY<br>WHERE SERVICE_TYPE ILIKE &#39;%AI%&#39;<br>   OR SERVICE_TYPE ILIKE &#39;%CORTEX%&#39;<br>ORDER BY USAGE_DATE DESC</pre><blockquote>AI_FUNCTIONS includes <em>AI_COMPLETE</em>, generates a response (completion) from text or an image using a supported language model. A poorly-scoped AI_COMPLETE call in a query that runs against millions of rows can get expensive fast.</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=917683fd8182" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Make Data-Informed Real Estate Choices?]]></title>
            <link>https://medium.com/@bella23jiang/how-to-make-data-informed-real-estate-choices-288021ccf050?source=rss-6d4b6bc5adaa------2</link>
            <guid isPermaLink="false">https://medium.com/p/288021ccf050</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[real-estate]]></category>
            <category><![CDATA[property-investment]]></category>
            <category><![CDATA[insights-and-data]]></category>
            <dc:creator><![CDATA[Bella Jiang]]></dc:creator>
            <pubDate>Tue, 25 Jun 2024 10:35:38 GMT</pubDate>
            <atom:updated>2024-07-01T06:38:02.740Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*SM2tGrj6zpD4EL8d" /><figcaption>Photo by <a href="https://unsplash.com/@purzlbaum?utm_source=medium&amp;utm_medium=referral">Claudio Schwarz</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Depending on the purpose, whether for investment or personal living, the focus can vary. Generally, good real estate investments not only offer inflation resilience but also generate passive income.</p><p>One of the best free resources for Australian investment property research is <a href="http://heatmaps.com.au/">HEATMAPS</a>, an online application that uses heatmaps to visualize real estate and related data in Australia. It is frequently updated and provides data transparency, primarily sourced from government websites. Most insights below are using <a href="http://heatmaps.com.au/">HEATMAPS</a>.</p><p>Kudos to the creator, Victor. Be sure to check out his <a href="https://www.youtube.com/@heatmapsaustralianinvestme5608">YouTube</a> channel for more insights.</p><h4>Key Metrics</h4><p><strong><em>Growth Rate</em></strong></p><p>This represents the relative median house price change in percent. For instance, if last year&#39;s median price was $ 1M and this year it is $ 1.1M then the Growth is $ 0.1M and the Growth Rate is 10%.</p><p><strong><em>Gross Yield Rate</em></strong></p><p>A key indicator in property investment, calculated by dividing annual rent by the property’s value. While important, yield alone isn’t a comprehensive measure of a good investment, as it doesn’t account for property price growth and equity accumulation. Many ‘blue-chip’ areas may have low yields but show significant value growth.</p><p>A strategic approach is to seek locations offering both high yield and high growth.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/345/1*SIc49yyjtITSeXH2LJZU4g.png" /><figcaption>Navigation on HEATMAPS</figcaption></figure><p>In the top navigation section, under <em>RANKINGS</em> by selecting <em>TOP GROWTH/YIELD SUBURBS,</em> you can find the top 50 suburbs for growth and yield in 2023 compared to 2022. Percentage-wise, the top-ranking suburbs are mostly in rural areas, such as the mining town Collinsville. Some Sydney suburbs stand out in terms of dollar growth.</p><p>Regarding the <em>Gross Yield Rate</em>, neither urban nor suburban areas made it into the top 50 due to high house prices.</p><blockquote><strong>Does high yield and high growth mean high investment return?</strong></blockquote><p>Without considering risks like rental vacancy, crime, and natural disasters, it’s difficult to definitively state that locations with high yield and high growth will guarantee high investment returns.</p><p>Being data-informed in the property purchasing process involves understanding the financial climate and market demand in addition to property metrics research.</p><blockquote><strong>How to cross validate the metrics?</strong></blockquote><p>Metric variations can occur due to different calculation methods, coverage periods, and update frequencies. A discrepancy of ±5% is generally acceptable.</p><p>For example, according to HEATMAPS, 79 houses sold in Croydon, NSW 2132, had a median price of $2.25M in 2023.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-7ebAU3i2lWRVFouqEYYAg.png" /><figcaption>Search on HEATMAPS</figcaption></figure><p>On realestate.com.au, the median price over the past 12 months is $2.348M from 65 sold houses.</p><p>The periods covered differ slightly: HEATMAPS covers Jan 2023 to Dec 2023, while realestate.com.au covers Jun 2023 to May 2024. Higher median prices are expected in more recent periods.</p><p>To find suburb insights on realestate.com.au, search for the suburb name and click “Find out how much it costs to buy/rent in SUBURB_NAME” in the top right corner.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aSjVrmSsN5rcLvQLqwSvrw.png" /><figcaption>Search on realestate.com.au</figcaption></figure><p>Hover over the time series graph to match periods. For Jan 2023 to Dec 2023, realestate.com.au shows a median price of $2.28M from 75 sales, close to HEATMAPS’ $2.25M from 79 sales, within the ±5% range.</p><p>The difference might be due to how outliers are treated on each website.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_E3BlrKoIG3L13npm6JC5w.png" /><figcaption>Suburb Insights on realestate.com.au</figcaption></figure><h4><strong>Other Metrics</strong></h4><p><strong><em>Owner Occupied Rate</em></strong></p><p>Investors often prefer areas with predominantly owner-occupied dwellings because these neighborhoods typically exhibit greater stability, lower crime rates, and better property maintenance.</p><p>Homeowners are more likely to invest in their property’s upkeep, enhancing neighborhood appeal and property values.</p><p>Additionally, such areas often have stronger community ties and better schools, further attracting long-term residents. This stability can lead to more predictable and higher returns on investment, making these areas attractive for investors seeking lower risk and sustainable growth.</p><p><strong><em>Crime Rate</em></strong></p><p>Similarly, people prefer to buy/rent a house in a safe area.</p><p><strong><em>New Buildings Approvals</em></strong></p><p>This layer visualizes the number of recently approved development applications (DAs) from local councils. Supply and demand define the price and the perspective. A higher number of recent development applications might indicate an increased supply on the local market. Investors might want to avoid investing into the area with an oversupply of houses/units.</p><h4>Red flags</h4><p><strong><em>Oversupply</em></strong></p><p>Victor discussed this in his HEATMAPS YouTube channel, part 8&amp;9. Using Alexandria, Waterloo, and Zetland as examples, nearly 2,000 new units were approved for construction two years before 2021. The assumption was that the supply would far exceed demand, leading to lower rental prices and low yields with high strata fees for homeowners.</p><p>Let’s see how things have unfolded three years after the video was posted. Yield initially decreased by 0.3% since mid-2021, followed by a gradual increase, peaking at 4.92% in Q1 2023, and then rising significantly to 5.61% in the last quarter of 2023.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m0LxNqBrcw-T4Bnnapa4Tg.png" /><figcaption>Units Gross Yield in Postcode 2017 on HEATMAPS</figcaption></figure><p>Why hasn’t it been as bad as expected?</p><p>Several factors come into play, the most significant being the unprecedented influx of migrants to Australia post-COVID, sustaining high demand for housing and apartments. Additionally, rising interest rates since mid-2022 have shifted home loan pressure from homeowners to renters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cxHbpXiypQ1sBGK1xencnQ.png" /><figcaption>Overseas migration on ABS</figcaption></figure><p>Is O<em>versupply</em> still a concern? I believe it is. In the long run, rental prices may decrease with tighter immigration policies and fewer migrants moving in.</p><p>Historical data and metrics reveal overall trends but aren’t absolute. It helps buyers make data-informed decisions. The choice of where to buy and whether to invest in certain properties should be considered on a case-by-case basis.</p><p>Thanks for reading, and good luck with the property investment journey :)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=288021ccf050" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[I tried Quicksight Q for a month and here is what I found…]]></title>
            <link>https://medium.com/learning-data/i-tried-quicksight-q-for-a-month-and-here-is-what-i-found-b51b0d5b7816?source=rss-6d4b6bc5adaa------2</link>
            <guid isPermaLink="false">https://medium.com/p/b51b0d5b7816</guid>
            <category><![CDATA[generative-ai-tools]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[quicksight]]></category>
            <category><![CDATA[data-analytics-tools]]></category>
            <dc:creator><![CDATA[Bella Jiang]]></dc:creator>
            <pubDate>Mon, 17 Jun 2024 02:44:19 GMT</pubDate>
            <atom:updated>2024-06-24T21:20:40.272Z</atom:updated>
            <content:encoded><![CDATA[<p>I recently learned about Generative BI at a tech conference, and the presentation on Quicksight Q was impressive. I decided to try it for a month and write a detailed review from a data analyst’s point of view.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FuBG7lFXV6II%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DuBG7lFXV6II&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FuBG7lFXV6II%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/7d57857ee8da87ab08be210de615d391/href">https://medium.com/media/7d57857ee8da87ab08be210de615d391/href</a></iframe><blockquote><strong><em>What’s Generative BI?</em></strong></blockquote><p>Generative Business Intelligence (BI) refers to the integration of generative artificial intelligence (AI) techniques with traditional BI tools to enhance data analysis, visualization, and reporting capabilities. This integration leverages AI models, such as large language models (LLMs), to enable users to interact with data more intuitively, often using natural language.</p><p>In essence, Generative BI refers to the application of AI in the realm of data analytics.</p><h3>Amazon Q in QuickSight</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/788/0*2f24YzDCCG8YrXqs.png" /><figcaption>image by author</figcaption></figure><p>It costs an additional $300 per month on top of QuickSight subscriptions but luckily it is available as part of the 30-day Amazon QuickSight Enterprise Edition free trial. To try Amazon Q in QuickSight, a Pro user role needs to be invited and assigned.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*IUqIC6sfuoamHygo.png" /><figcaption>image by author</figcaption></figure><p>In the beginning, I thought only by providing data to AI and it would understand everything and do the analysis. It turns out that some prep work are required to make it happen.</p><p><strong><em>Prepare Q</em></strong></p><blockquote><em>1. Create a topic, from a dataset or an existing analysis</em></blockquote><blockquote><em>2. Create named entities and curate other topic metadata</em></blockquote><blockquote><em>3. Start asking questions, and share with business users</em></blockquote><p>According to guidelines, topic preparation is needed to provide context to the data provided. It’s not all magic after all, it requires human input set-ups to produce reports.</p><p>Adding <em>Friendly name</em> and <em>Synonyms</em> to let AI understand different words</p><p><strong><em>Named entity</em></strong></p><p>Named Entities are groupings of data fields that collectively represent a business concept and are used to enhance the Q&amp;A experience.</p><p>In my case, the real estate dataset that includes property listing details and lister information, I have put alternative names and chosen data columns that I want to be included.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*i_jSNkj6fxw01wBG.png" /><figcaption>image by author</figcaption></figure><p>As you can see, most suggested questions could have been more insightful…</p><h3>My two cents</h3><ol><li>Lack of contextual knowledge is no joke, dump questions are given even after the Data Topic is prepared. Not all business models are like the marketing dataset used in the demo. I guess that’s why human input is important here, training Q to understand the contextual knowledge is a bit time-consuming than I thought.</li><li>Some plot type is not integrated, and impossible to be presented by Q no matter how many times it’s trained. For instance, unlike the world map, it seems hard for Amazon Q to understand Australian suburbs only by providing postcodes. I attempted to update the tree map to a regional map using alternative plot types manually but failed.</li><li>Some might wonder if Quicksight Q is worth the investment. In my opinion, it is not. I found it more troublesome than useful, and it is also quite expensive, given the monthly subscription only costs $24 per author, spending $300 for what feels like a cumbersome Gen BI tool doesn’t seem justified. I found that ChatGPT offers better data analysis and insights.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*C5tqojo-9k1OrHNB.png" /><figcaption>image by author</figcaption></figure><p>I do like the executive summary at the top, it can discover trends like <em>no sales were found after 14 May</em>, which can be useful for anomaly detection.</p><p>Another “more advanced” Gen BI feature by Quicksight is Paginated Reports, a fully managed cloud-based business intelligence (BI) solution for creating, scheduling, and sharing reports and data exports. Unfortunately, there is no Free Tier option. It costs $500 for 500 reports, or $1 per report unit, which isn’t a terrible deal, but it’s beyond my budget.</p><p>Amazon Quicksight isn’t the only tool with Gen BI integration; nearly all popular visualization tools have it. Here’s an overview of their pros and cons.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ImtGAJNKxKns9g-7.png" /><figcaption>image by author</figcaption></figure><blockquote><strong><em>Can generative BI replace data analysts?</em></strong></blockquote><p>Here is an answer AI gives — <em>Generative BI is unlikely to completely replace business/data analysts, but it will significantly augment their capabilities. The future will likely see a collaborative relationship where AI handles data-intensive tasks and analysts provide the strategic oversight, contextual understanding, and creative problem-solving needed to drive business success.</em></p><p>Agreed, Gen BI is a tool that enhances analyst work efficiency but contextual understanding and strategic guidance are still required from human. At least, for now.</p><p>Maybe in 10 years, analysts will be replaced by new roles — “BI Model Validator,” and “BI Strategy Consultant”, just as new technologies have historically led to the creation of new jobs, we expect that generative BI will contribute to the emergence of novel job opportunities designed to harness this technology.</p><p><em>The contents of external submissions are not necessarily reflective of the opinions or work of </em><a href="http://mavenanalytics.io/"><em>Maven Analytics</em></a><em> or any of its team members.</em></p><p><em>We believe in fostering lifelong learning and our intent is to provide a platform for the data community to share their work and seek feedback from the Maven Analytics data fam.</em></p><p><a href="https://medium.com/learning-data/how-to-get-your-work-published-by-learning-data-with-maven-analytics-7df21e466a3e?source=friends_link&amp;sk=020dfac485597d602e218968d9ffb395"><em>Submit your own writing here</em></a><em> if you’d like to become a contributor.</em></p><p><em>Happy learning!</em></p><p><em>-Team Maven</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b51b0d5b7816" width="1" height="1" alt=""><hr><p><a href="https://medium.com/learning-data/i-tried-quicksight-q-for-a-month-and-here-is-what-i-found-b51b0d5b7816">I tried Quicksight Q for a month and here is what I found…</a> was originally published in <a href="https://medium.com/learning-data">Learning Data</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to build a Data Pipeline with AWS Glue and Terraform]]></title>
            <link>https://blog.dataengineerthings.org/how-to-build-a-data-pipeline-with-aws-glue-and-terraform-ac1ace165d29?source=rss-6d4b6bc5adaa------2</link>
            <guid isPermaLink="false">https://medium.com/p/ac1ace165d29</guid>
            <category><![CDATA[aws-glue]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[terraform]]></category>
            <category><![CDATA[etl]]></category>
            <dc:creator><![CDATA[Bella Jiang]]></dc:creator>
            <pubDate>Fri, 07 Jun 2024 04:14:18 GMT</pubDate>
            <atom:updated>2024-06-12T01:54:02.827Z</atom:updated>
            <content:encoded><![CDATA[<h4>A step-by-step guide to an ETL project that explores Australian property price</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G1Ef1SjRga7nkmYiC4Nuew.png" /><figcaption>Project infrastructure created by the author</figcaption></figure><h4>Preface ✨</h4><p>One feedback I got from a friend on my previous <a href="https://medium.com/@bella23jiang/navigating-sydneys-property-market-a-data-driven-approach-with-dlt-dbt-core-ca1938cfb45c">real estate ETL project</a>: the dataset is incomplete.</p><p>He is right, it turns out the API provider sources data by web crawling <a href="https://www.realestate.com.au/">realestate.com.au</a>, and any results over page 50 are not returned due to the website’s anti-crawler mechanism.</p><p>To resolve the data source issue, I found that bulk property sales data is accessible on the <a href="https://valuation.property.nsw.gov.au/embed/propertySalesInformation">NSW government website</a>, which I believe is a more trustworthy data source that includes all property sales data across New South Wales.</p><p>Except… the bulk data is nested ZIP files by week and region, in a mysterious DAT format.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c5GXHrLuFStEqV1c5KOYkw.png" /><figcaption>nested zipped bulk data</figcaption></figure><p>This official <a href="https://www.valuergeneral.nsw.gov.au/__data/assets/pdf_file/0015/216402/Current_Property_Sales_Data_File_Format_2001_to_Current.pdf">documentation</a> helps a lot for me to make sense of the data.</p><p>After unzipping and briefly cleaning the data (rows with Record type ’B’ ‘C’ ‘D’ &amp; ‘Z’ were removed because these are unnecessary information), I got a list of CSV files containing daily sales in different NSW regions (6,374 separate files for the year 2023).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/1*7lNA3IXUiyOx6u3R9L6lnQ.png" /><figcaption>briefly cleaned data</figcaption></figure><p>With <em>briefly cleaned data</em> uploaded into S3 for storage, I decided to build an ETL pipeline using the AWS services.</p><p>Although the pipeline can be constructed directly in the AWS console, using Infrastructure as Code (IaC) tools to deploy it offers several advantages. IaC allows for testing before deployment, which is especially beneficial for complex projects requiring intricate configurations. Additionally, IaC makes it much easier to shut down all relevant resources, reducing the risk of unexpected charges from forgotten resources.</p><h4>Overview</h4><p>Our goal is to extract data from S3, transform it, and load it into Redshift Serverless for further analysis, and Athena is also set up for ad-hoc light analyses. The pipeline will:</p><ul><li>Use AWS Glue crawlers to infer schema and extract metadata from the source database</li><li>Create destination data warehouse Redshift Serverless</li><li>Use AWS Glue crawlers to infer schema and extract metadata from the Redshift Serverless</li><li>Setup AWS Glue jobs for ETL from Source S3 to Destination Redshift Serverless</li><li>Orchestrate jobs into a workflow to load processed data into the Redshift Serverless (TBD)</li></ul><h4>Prerequisites</h4><p>An AWS account and AWS CLI installed.</p><h4>Tech Stack</h4><ul><li>Python</li><li>Terraform</li><li>AWS Glue Crawler</li><li>AWS Glue Catalog</li><li>AWS Glue Job</li><li>S3</li><li>AWS Athena</li><li>AWS Redshift</li></ul><h4>From S3 to Glue Catalog</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*m8cK5SWKgnuAAb5-jyNmQQ.png" /><figcaption>Image by the author</figcaption></figure><p>This section explains how to make raw data in S3 usable by creating a Crawler that will determine the schema of the data in the raw layer and subsequently create a Glue catalog table.</p><p>Initially, an IAM role for the Glue Crawler is created by using the <em>iam_for_glue</em> <em>module</em>, which has sufficient permissions. A Terraform module is a tool that contains multiple resources used together. It allows resource configurations to be packaged for reuse.</p><pre>data &quot;aws_iam_policy_document&quot; &quot;glue_assume_role&quot; {<br>  statement {<br>    effect = &quot;Allow&quot;<br>    principals {<br>      type        = &quot;Service&quot;<br>      identifiers = [&quot;glue.amazonaws.com&quot;]<br>    }<br>    actions = [&quot;sts:AssumeRole&quot;]<br>  }<br>}<br><br>resource &quot;aws_iam_role&quot; &quot;iam_for_glue&quot; {<br>  name               = &quot;iam_for_glue&quot;<br>  assume_role_policy = data.aws_iam_policy_document.glue_assume_role.json<br>}<br><br>data &quot;aws_iam_policy_document&quot; &quot;glue_policy_document&quot; {<br>  statement {<br>    effect = &quot;Allow&quot;<br>    actions = [<br>      &quot;s3:GetBucketLocation&quot;,<br>      &quot;s3:ListBucket&quot;,<br>      &quot;s3:GetBucketAcl&quot;,<br>      &quot;s3:GetObject&quot;,<br>      &quot;s3:PutObject&quot;,<br>      &quot;s3:DeleteObject&quot;<br>    ]<br>    resources = [&quot;*&quot;] #to be specify later<br>  }<br>  statement {<br>    effect = &quot;Allow&quot;<br>    actions = [<br>      &quot;glue:*&quot;<br>    ]<br>    resources = [&quot;*&quot;]<br>  }<br>  statement {<br>    effect = &quot;Allow&quot;<br>    actions = [<br>      &quot;logs:CreateLogGroup&quot;,<br>      &quot;logs:CreateLogStream&quot;,<br>      &quot;logs:PutLogEvents&quot;<br>    ]<br>    resources = [<br>      &quot;arn:aws:logs:*:*:*:/aws-glue/*&quot;<br>    ] #to be specify later<br>  }<br>}<br><br>resource &quot;aws_iam_policy&quot; &quot;glue_policy&quot; {<br>  name        = &quot;glue-policy&quot;<br>  description = &quot;allow glue to get and list object from s3 bucket&quot;<br>  policy      = data.aws_iam_policy_document.glue_policy_document.json<br>}<br><br>resource &quot;aws_iam_role_policy_attachment&quot; &quot;attach_getObject&quot; {<br>  role       = aws_iam_role.iam_for_glue.name<br>  policy_arn = aws_iam_policy.glue_policy.arn<br>}</pre><p>Then, a Glue Catalog Database is created to store the inferred schema table. With the IAM Role and Catalog Database in place, the Crawler is ready to go.</p><pre># create iam role for glue crawler<br>module &quot;iam_for_glue&quot; {<br>  source = &quot;./modules/glue_iam&quot;<br>}<br>#create glue catalog database<br>resource &quot;aws_glue_catalog_database&quot; &quot;glue_catalog_database&quot; {<br>  name = var.glue_catalog_database_name<br>}<br>#create glue crawler<br>resource &quot;aws_glue_crawler&quot; &quot;glue_crawler&quot; {<br>  database_name = var.glue_catalog_database_name<br>  name          = var.glue_crawler_name<br>  role          = module.iam_for_glue.glue_iam_arn<br>  s3_target {<br>    path = format(&quot;s3://%s&quot;, var.s3_bucket_source_name)<br>  }<br>}</pre><h4>Deploy Redshift Serverless</h4><p>Redshift Serverless can manage both small and large datasets. It automatically scales to accommodate bursts, providing flexibility for varying workloads. Compared to the Redshift Cluster, it is a more suitable option for our project. This is because it’s fully managed by AWS, which lessens our workload and allows us to concentrate on the ETL part.</p><p>Here’s what we’ll need to deploy Redshift Serverless:</p><ol><li>Create a <strong>VPC and 3 Subnets at different Availability Zones</strong></li></ol><pre># AWS Availability Zones data<br>data &quot;aws_availability_zones&quot; &quot;available&quot; {}<br><br>######################################<br><br># Create the VPC<br>resource &quot;aws_vpc&quot; &quot;redshift-serverless-vpc&quot; {<br>  cidr_block           = var.redshift_serverless_vpc_cidr<br>  enable_dns_hostnames = true<br><br>  tags = {<br>    Name = &quot;nsw-properties-redshift-serverless-vpc&quot;<br>  }<br>}<br><br>######################################<br><br># Create the Redshift Subnet AZ1<br>resource &quot;aws_subnet&quot; &quot;redshift-serverless-subnet-az1&quot; {<br>  vpc_id            = aws_vpc.redshift-serverless-vpc.id<br>  cidr_block        = var.redshift_serverless_subnet_1_cidr<br>  availability_zone = data.aws_availability_zones.available.names[0]<br><br>  tags = {<br>    Name = &quot;nsw-properties-redshift-serverless-subnet-az1&quot;<br>  }<br>}<br><br># Create the Redshift Subnet AZ2<br>resource &quot;aws_subnet&quot; &quot;redshift-serverless-subnet-az2&quot; {<br>  vpc_id            = aws_vpc.redshift-serverless-vpc.id<br>  cidr_block        = var.redshift_serverless_subnet_2_cidr<br>  availability_zone = data.aws_availability_zones.available.names[1]<br><br>  tags = {<br>    Name = &quot;nsw-properties-redshift-serverless-subnet-az2&quot;<br>  }<br>}<br><br># Create the Redshift Subnet AZ3<br>resource &quot;aws_subnet&quot; &quot;redshift-serverless-subnet-az3&quot; {<br>  vpc_id            = aws_vpc.redshift-serverless-vpc.id<br>  cidr_block        = var.redshift_serverless_subnet_3_cidr<br>  availability_zone = data.aws_availability_zones.available.names[2]<br><br>  tags = {<br>    Name = &quot;nsw-properties-redshift-serverless-subnet-az3&quot;<br>  }<br>}</pre><p>2. Create a <strong>Security Group</strong></p><pre># Create a Security Group for Redshift Serverless<br>resource &quot;aws_security_group&quot; &quot;redshift-serverless-security-group&quot; {<br>  depends_on = [aws_vpc.redshift-serverless-vpc]<br><br>  name        = &quot;${var.app_name}-redshift-serverless-security-group&quot;<br>  description = &quot;${var.app_name}-redshift-serverless-security-group&quot;<br><br>  vpc_id = aws_vpc.redshift-serverless-vpc.id<br><br>  ingress {<br>    description = &quot;allow traffic from all IP&quot;<br>    from_port   = 5439<br>    to_port     = 5439<br>    protocol    = &quot;tcp&quot;<br>    cidr_blocks = [&quot;0.0.0.0/0&quot;] // update to secure IP<br>  }<br><br>  tags = {<br>    Name = &quot;${var.app_name}-redshift-serverless-security-group&quot;<br>  }<br>}<br>#create endpoint gateway to allow traffic between s3 and redshift VPC<br>resource &quot;aws_vpc_endpoint&quot; &quot;s3_redshift&quot; {<br>  vpc_id            = aws_vpc.redshift-serverless-vpc.id<br>  service_name      = &quot;com.amazonaws.us-east-1.s3&quot;<br>  vpc_endpoint_type = &quot;Gateway&quot;<br><br>  route_table_ids = [aws_vpc.redshift-serverless-vpc.default_route_table_id]<br>}</pre><p>3. Create an <strong>AWS IAM Role and IAM Policy</strong></p><pre># Create an IAM Role for Redshift<br>resource &quot;aws_iam_role&quot; &quot;redshift-serverless-role&quot; {<br>  name = &quot;nsw-properties-redshift-serverless-role&quot;<br><br>  assume_role_policy = &lt;&lt;EOF<br>{<br>  &quot;Version&quot;: &quot;2012-10-17&quot;,<br>  &quot;Statement&quot;: [<br>    {<br>      &quot;Action&quot;: &quot;sts:AssumeRole&quot;,<br>      &quot;Principal&quot;: {<br>        &quot;Service&quot;: &quot;redshift.amazonaws.com&quot;<br>      },<br>      &quot;Effect&quot;: &quot;Allow&quot;,<br>      &quot;Sid&quot;: &quot;&quot;<br>    }<br>  ]<br>}<br>EOF<br><br>  tags = {<br>    Name = &quot;nsw-properties-redshift-serverless-role&quot;<br>  }<br>}<br># Create and assign an IAM Role Policy to access S3 Buckets<br>resource &quot;aws_iam_role_policy&quot; &quot;redshift-s3-full-access-policy&quot; {<br>  name = &quot;nsw-properties-redshift-serverless-role-s3-policy&quot;<br>  role = aws_iam_role.redshift-serverless-role.id<br><br>  policy = &lt;&lt;EOF<br>{<br>   &quot;Version&quot;: &quot;2012-10-17&quot;,<br>   &quot;Statement&quot;: [<br>     {<br>       &quot;Effect&quot;: &quot;Allow&quot;,<br>       &quot;Action&quot;: &quot;s3:*&quot;,<br>       &quot;Resource&quot;: &quot;*&quot;<br>      }<br>   ]<br>}<br>EOF<br>}<br># Get the AmazonRedshiftAllCommandsFullAccess policy<br>data &quot;aws_iam_policy&quot; &quot;redshift-full-access-policy&quot; {<br>  name = &quot;AmazonRedshiftAllCommandsFullAccess&quot;<br>}<br><br># Attach the policy to the Redshift role<br>resource &quot;aws_iam_role_policy_attachment&quot; &quot;attach-s3&quot; {<br>  role       = aws_iam_role.redshift-serverless-role.name<br>  policy_arn = data.aws_iam_policy.redshift-full-access-policy.arn<br>}</pre><p>4. Create the <strong>AWS Redshift Serverless Namespace and Workgroup</strong></p><pre># Create the Redshift Serverless Namespace<br>resource &quot;aws_redshiftserverless_namespace&quot; &quot;serverless&quot; {<br>  namespace_name      = var.redshift_serverless_namespace_name<br>  db_name             = var.redshift_serverless_database_name<br>  admin_username      = var.redshift_serverless_admin_username<br>  admin_user_password = var.redshift_serverless_admin_password<br>  iam_roles           = [aws_iam_role.redshift-serverless-role.arn]<br><br>  tags = {<br>    Name = var.redshift_serverless_namespace_name<br>  }<br>}<br><br># Create the Redshift Serverless Workgroup<br>resource &quot;aws_redshiftserverless_workgroup&quot; &quot;serverless&quot; {<br>  depends_on = [aws_redshiftserverless_namespace.serverless]<br><br>  namespace_name = aws_redshiftserverless_namespace.serverless.id<br>  workgroup_name = var.redshift_serverless_workgroup_name<br>  base_capacity  = var.redshift_serverless_base_capacity<br><br>  security_group_ids = [aws_security_group.redshift-serverless-security-group.id]<br>  subnet_ids = [<br>    aws_subnet.redshift-serverless-subnet-az1.id,<br>    aws_subnet.redshift-serverless-subnet-az2.id,<br>    aws_subnet.redshift-serverless-subnet-az3.id,<br>  ]<br>  publicly_accessible = var.redshift_serverless_publicly_accessible<br><br>  tags = {<br>    Name = var.redshift_serverless_workgroup_name<br>  }<br>}</pre><h4>From Redshift to Glue Catalog (optional)</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/641/1*875QMmygr3-htGPendbsEA.png" /><figcaption>Image by the author</figcaption></figure><p>Before we set up the Redshift crawler, which infers Redshift database schema, we need to create a table in Redshift. In my case, it’s a table of property prices.</p><pre>create table if not exists nsw_properties(<br>  id INTEGER,<br>  unit_num INTEGER,<br>  street_num INTEGER,<br>  street_name VARCHAR(255),<br>  suburb_name VARCHAR(255),<br>  suburb_postcode INTEGER,<br>  area_size DOUBLE PRECISION,<br>  area_unit VARCHAR(255),<br>  contract_date DATE,<br>  settlement_date DATE,<br>  sold_price INTEGER,<br>  zoning VARCHAR(255),<br>  property_nature VARCHAR(255)<br>);</pre><p>Data in Redshift is safeguarded within a VPC, making it inaccessible without proper permissions. To access Redshift data from AWS Glue, you need not only sufficient IAM permissions (e.g., DataAPIPermissions) for the Glue service role but also a JDBC connector. This JDBC connector must navigate through the VPC security group, a designated subnet, and its associated availability zone.</p><pre>#create jdbc connection<br>resource &quot;aws_glue_connection&quot; &quot;glue_jdbc_conn&quot; {<br>  connection_properties = {<br>    JDBC_CONNECTION_URL = &quot;jdbc:redshift://${aws_redshiftserverless_workgroup.serverless.endpoint[0].address}:${aws_redshiftserverless_workgroup.serverless.port}/dev&quot;<br>    PASSWORD            = var.redshift_serverless_admin_password<br>    USERNAME            = var.redshift_serverless_admin_username<br>  }<br>  name = var.glue_jdbc_conn_name<br>  physical_connection_requirements {<br>    availability_zone = aws_subnet.redshift-serverless-subnet-az1.availability_zone<br>    security_group_id_list = [<br>      aws_security_group.redshift-serverless-security-group.id,<br>    ]<br>    subnet_id = aws_subnet.redshift-serverless-subnet-az1.id<br>  }<br>}</pre><p>With the connector configured, we can create the crawler and database.</p><pre>#create redshift catalog database<br>resource &quot;aws_glue_catalog_database&quot; &quot;redshift_catalog_database&quot; {<br>  name = var.redshift_glue_catalog_database_name<br>}<br>#create redshift to glue crawler<br>resource &quot;aws_glue_crawler&quot; &quot;redshift_crawler&quot; {<br>  database_name = var.redshift_glue_catalog_database_name<br>  name          = var.redshift_glue_crawler_name<br>  role          = module.iam_for_glue.glue_iam_arn<br>  jdbc_target {<br>    connection_name = aws_glue_connection.glue_jdbc_conn.name<br>    path            = &quot;dev/public/nsw_properties&quot; #db schema and table name<br>  }<br>}</pre><h4>Define the Glue Job</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J2q7Wm7EKDnObnNCH49tGg.png" /><figcaption>Image by the author</figcaption></figure><p>Based on the Redshift catalog table, we can add steps in a Glue job to transform the data. Common transformations include filtering, converting formats, appending columns, and more.</p><p>For example, we can:</p><ul><li>Filter records to include only those after a certain date.</li><li>Convert columns to different data types.</li><li>Add new columns with calculated values.</li></ul><p>In my case, I need to convert <em>contract_date</em> from a string in the format YYYYMMDD to a date type. While this seems straightforward, it’s challenging to achieve using the built-in change schema functions in Visual ETL with DynamicFrame. Instead, it can be accomplished by converting the DynamicFrame to a DataFrame. Here is the Glue job script.</p><pre>import sys<br>from awsglue.transforms import *<br>from awsglue.utils import getResolvedOptions<br>from pyspark.context import SparkContext<br>from awsglue.context import GlueContext<br>from awsglue.job import Job<br>from awsglue import DynamicFrame<br>from pyspark.sql.functions import to_date, col<br><br>args = getResolvedOptions(sys.argv, [&#39;JOB_NAME&#39;]) <br>sc = SparkContext()<br>glueContext = GlueContext(sc)<br>spark = glueContext.spark_session<br>job = Job(glueContext)<br>job.init(args[&#39;JOB_NAME&#39;], args) <br><br># Create dynamic frame from glue s3 catalog<br>inputDF = glueContext.create_dynamic_frame.from_catalog(database=&quot;nsw_properties_s3_database&quot;, table_name=&quot;nsw_properties_source_bucket&quot;, transformation_ctx=&quot;inputDF&quot;)<br><br># Convert DynamicFrame to DataFrame<br>inputDF_df = inputDF.toDF()<br># Convert contract_date and settlement_date from string to date format<br>inputDF_df = inputDF_df.withColumn(&quot;contract_date&quot;, to_date(col(&quot;contract_date&quot;).cast(&quot;string&quot;), &quot;yyyyMMdd&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;settlement_date&quot;, to_date(col(&quot;settlement_date&quot;).cast(&quot;string&quot;), &quot;yyyyMMdd&quot;))<br># Apply other transformations and type changes<br>inputDF_df = inputDF_df.withColumn(&quot;id&quot;, col(&quot;id&quot;).cast(&quot;int&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;unit_num&quot;, col(&quot;unit_num&quot;).cast(&quot;int&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;street_num&quot;, col(&quot;street_num&quot;).cast(&quot;int&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;street_name&quot;, col(&quot;street_name&quot;).cast(&quot;string&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;suburb_name&quot;, col(&quot;suburb_name&quot;).cast(&quot;string&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;suburb_postcode&quot;, col(&quot;suburb_postcode&quot;).cast(&quot;int&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;area_size&quot;, col(&quot;area_size&quot;).cast(&quot;double&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;area_unit&quot;, col(&quot;area_unit&quot;).cast(&quot;string&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;sold_price&quot;, col(&quot;sold_price&quot;).cast(&quot;int&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;zoning&quot;, col(&quot;zoning&quot;).cast(&quot;string&quot;))<br>inputDF_df = inputDF_df.withColumn(&quot;property_nature&quot;, col(&quot;property_nature&quot;).cast(&quot;string&quot;))<br><br># Convert DataFrame back to DynamicFrame<br>outputDF = DynamicFrame.fromDF(inputDF_df, glueContext, &quot;outputDF&quot;)<br>outputDF.show(5)<br># Script generated for node Amazon Redshift<br>AmazonRedshiftDF = glueContext.write_dynamic_frame.from_options(frame=outputDF, connection_type=&quot;redshift&quot;, connection_options={&quot;redshiftTmpDir&quot;: &quot;s3://aws-glue-assets-637423503968-us-east-1/temporary/&quot;, &quot;useConnectionProperties&quot;: &quot;true&quot;, &quot;dbtable&quot;: &quot;public.nsw_properties&quot;, &quot;connectionName&quot;: &quot;connect_redshift_ETL&quot;, &quot;preactions&quot;: &quot;CREATE TABLE IF NOT EXISTS public.nsw_properties (id INTEGER, unit_num INTEGER, street_num INTEGER, street_name VARCHAR, suburb_name VARCHAR, suburb_postcode INTEGER, area_size DOUBLE PRECISION, area_unit VARCHAR, contract_date DATE, settlement_date DATE, sold_price INTEGER, zoning VARCHAR, property_nature VARCHAR); TRUNCATE TABLE public.nsw_properties;&quot;}, transformation_ctx=&quot;AmazonRedshiftDF&quot;)<br><br>job.commit()</pre><p>Next, we need to create a bucket for storing the Glue job script and upload it from the local directory. The ETag (entity tag) attribute changes whenever the file is modified, making it crucial to ensure the script is updated during deployment testing.</p><pre>#create s3 bucket<br>resource &quot;aws_s3_bucket&quot; &quot;s3_utils_bucket&quot; {<br>  bucket = var.s3_utils_name<br>}<br>#add glue scripts to s3 bucket<br>resource &quot;aws_s3_object&quot; &quot;add_script&quot; {<br>  depends_on = [aws_s3_bucket.s3_utils_bucket]<br>  bucket     = var.s3_utils_name<br>  key        = var.s3_utils_key<br>  source     = &quot;${var.glue_src_path}etl.py&quot;<br>  etag       = filemd5(&quot;${var.glue_src_path}etl.py&quot;)<br>}</pre><p>After successfully executing the Glue job, the property data was moved from S3 to Redshift and formatted as desired, making it easy to query, visualize, and analyze.</p><p>Check out the full code in the repo below.</p><p><a href="https://github.com/JellyBella/s3-redshift-etl-pipeline">GitHub - JellyBella/s3-redshift-etl-pipeline: Extract data from S3, transform it, and load it into Redshift Serverless for further analysis, and Athena is also set up for ad-hoc light analyses.</a></p><h4>Final Thoughts💭</h4><p>In this project, I learned Terraform&#39;s best practices and the processes involved in securely moving data from S3 to Redshift. This included trial and error in setting up the network and IAM permissions. I enjoyed using Terraform to build a data pipeline for the first time, and I look forward to creating more!</p><p>Thanks for reading :)</p><p>Please reach out via <a href="https://www.linkedin.com/in/bellajiang/">LinkedIn</a>, <a href="https://github.com/JellyBella">GitHub</a> and <a href="https://medium.com/@bella23jiang">Medium</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ac1ace165d29" width="1" height="1" alt=""><hr><p><a href="https://blog.dataengineerthings.org/how-to-build-a-data-pipeline-with-aws-glue-and-terraform-ac1ace165d29">How to build a Data Pipeline with AWS Glue and Terraform</a> was originally published in <a href="https://blog.dataengineerthings.org/">Data Engineer Things</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Navigating Sydney’s Property Market: A Data-Driven Approach with dlt+ dbt core]]></title>
            <link>https://medium.com/@bella23jiang/navigating-sydneys-property-market-a-data-driven-approach-with-dlt-dbt-core-ca1938cfb45c?source=rss-6d4b6bc5adaa------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca1938cfb45c</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[dlt]]></category>
            <dc:creator><![CDATA[Bella Jiang]]></dc:creator>
            <pubDate>Mon, 13 May 2024 05:49:21 GMT</pubDate>
            <atom:updated>2024-06-11T00:18:45.786Z</atom:updated>
            <content:encoded><![CDATA[<h3>Navigating Sydney’s Property Market: A Data-Driven Approach with dlt + dbt core</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PSelgmm2gjaEB4BhoE9IDA.jpeg" /><figcaption>Image by AI</figcaption></figure><h3>Introduction</h3><p>As a data analyst transitioning into a data engineer, I’m intrigued by the array of tools available in the market. However, my real excitement lies in rolling up my sleeves and embarking on my inaugural cloud project.</p><p><a href="https://dlthub.com/">dlt (data load tool)</a> is a new Python library that automates the tedious part of data ingestion: Loading, Schema Management, Data Type Detection, Scalability, Scable Extraction. <a href="https://www.getdbt.com/product/what-is-dbt">dbt core</a> is an open-source tool designed to enable data teams to apply analytics engineering best practices for data transformation, and Tableau Public is a free platform to explore, create and publicly share data visualizations online.</p><p>These elements are reflected in the diagram below and further clarified in greater detail later in the article:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zwafPdr1kJ2X7lyv7Q6bWw.png" /><figcaption>Image by Author</figcaption></figure><h3>Problem</h3><p>Navigating the property market can be daunting for people seeking homes in Sydney. While <a href="https://www.realestate.com.au/">Realestate.com.au</a> is a popular platform, obtaining reliable market insights poses a challenge.</p><p>In this competitive market, which suburbs offer dream homes that fit within our budget?</p><h3>Solution</h3><p><strong><em>I. Data Ingestion</em></strong></p><p>Rather than scraping real estate websites, I opted for <a href="https://rapidapi.com/apidojo/api/realty-in-au">Realty in AU’s API</a> via Api Dojo. Because of the max page limit, I decided to retrieve sold properties for the last 12 months for a start.</p><p>With dlt <a href="https://dlthub.com/docs/general-usage/incremental-loading">Incremental loading</a>, only new or changed data will be loaded. It enables low-latency and low-cost data transfer. The schema was automatically inferred and staged in BigBucket before loading into BigQuery.</p><p>The dlt pipeline I created for ingestion is in <a href="https://github.com/JellyBella/dlt-realestate/tree/main">this repo</a>. The ingested tables are shown below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/910/1*qAg-FBC5hxBVk6W9Vi624Q.png" /><figcaption>Image by Author</figcaption></figure><p>dlt feels like the perfect solution for anyone who has scratched the surface of python development. To be able to have schemas ready for transformation in such a short space of time is truly… transformational.</p><p><strong><em>II. Clean and transform data</em></strong></p><p>Among the ten ingested tables, the focus lay on <em>realestate_analytics_tiered_results_results</em>, housing sold listings’ details like <em>sold_price</em> and <em>sold_date</em>.</p><p>Through structured modeling layers — Staging, Intermediate, and Reporting — we prepared modular pieces for a comprehensive tableau report.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0lOmEK33bY5nHh57g9AHng.png" /><figcaption>Image by Author</figcaption></figure><p>The series of transformations run helps identify potential duplicate listings among other things. Duplicates are produced by</p><ol><li>the same listings returned from different API calls as listings from surrounding suburbs(one unique).</li><li>the property listing was replaced by a new one due to an unresolved issue with the previous buyer(both unique). An intermediate model, <em>int_realestate_duplicates.sql</em>, employs logic to pinpoint unique listings. It assumes that properties with the same address and sold dates within a 7-day window are duplicates.</li></ol><p>The dbt structure I created for data transformation can be found in <a href="https://github.com/JellyBella/dbt-sydney-property">this repo</a>.</p><p><em>III. Data Visualization</em></p><p>Tableau Public, while limited in native connectors, was leveraged for data visualization. To automate the pipeline, Gsheet served as a conduit. Sheets were explored for reporting, with data refreshed on a scheduled basis and synced to the Tableau dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/614/1*iAkdK1lBYUKbfzqH1X8izg.png" /><figcaption>Image by Author</figcaption></figure><h3>Final Product</h3><p>The resulting dashboard addresses key queries, including the number of listed properties sold in the last 12 months, areas with the highest property sales, and the average cost of a 3-bedroom property. This personalized insight equips homebuyers with the knowledge to identify suburbs aligning with their budget and preferences.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/878/1*8rckAvjKHGXHiEJzohVITA.png" /><figcaption>Image by Author</figcaption></figure><p>Link to the <a href="https://public.tableau.com/app/profile/bellajiang/viz/SydneyPropertyPulse_UnveilingTrendsBeyondtheHeadlines/RealEstate">Tableau dashboard</a>.</p><h3>Further development</h3><ul><li>Realestate.com.au lacks certain sold listings, some properties were only listed on Domain.com.au (2nd largest in Australia).</li><li>By integrating NSW Property Sales Information (PSI), we can cross-reference property prices for enhanced accuracy.</li><li>This iteration primarily aimed to establish a foundation for analyzing properties, tailored to my specific needs and objectives. In terms of further development, we can expand the real estate dataset to cover NSW or even the entire Australia.</li></ul><p>Thank you for reading this far. If you find this article useful, like and share this article, and feel free to reach out via <a href="https://www.linkedin.com/in/bellajiang/"><strong>Linkedin</strong></a> and <a href="https://github.com/JellyBella"><strong>Github</strong></a>, all comments are appreciated 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca1938cfb45c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>