<?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 Guha Ayan on Medium]]></title>
        <description><![CDATA[Stories by Guha Ayan on Medium]]></description>
        <link>https://medium.com/@guha-ayan?source=rss-3cb0f9fedf21------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*Bl1hPY20p29V9oZW</url>
            <title>Stories by Guha Ayan on Medium</title>
            <link>https://medium.com/@guha-ayan?source=rss-3cb0f9fedf21------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 15:09:15 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@guha-ayan/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[Intelligent document parsing (IDP) using Databricks ai_query()]]></title>
            <link>https://guha-ayan.medium.com/intelligent-document-parsing-idp-using-databricks-ai-query-ec4236348d56?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/ec4236348d56</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Tue, 30 Sep 2025 12:30:23 GMT</pubDate>
            <atom:updated>2025-09-30T12:30:23.932Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uoHkRLncUqIqS4lao-Eriw.png" /></figure><p>Intelligent document parsing (IDP) is a specific use case type which is particularly suitable to be solved using generative AI. There are many practical application of such technology in automating tasks around extracting valuable information from various digital documents. In fact, IDP is one of the first use cases LLMs were fairly successfull, and many AI services popped up to address various domain-specific use cases. IDP is also considered as a more capable cousin of traditional OCR services. It also often seen as a core foundation of RAG systems. As such, there are many known and well tested IDP solution using frameworks like Langchain and LlamaIndex.</p><p>So, why to explore one more?</p><h3><strong>Key Idea:</strong></h3><blockquote><strong>Simple reason:</strong> Use Databricks <strong>native features</strong> to solve the problem, as easily as possible. In nutshell, the idea is to extract text from pdf files, pass the data within a context embedded in a large prompt, and ask LLM to extract a bunch of information from the text and return in json format.</blockquote><p>Some key benefits to highlight:</p><ol><li><strong>Consume PDF files:</strong> All documents which are to be consumed are in PDF format</li><li><strong>Use Databricks Foundation Models:</strong> Like any self-respecting data platform, databricks also hosts a bunch of leading LLMs and exposes via Foundation model API.</li><li><strong>Use </strong><strong>ai_query Functions:</strong> While there are some more flexibility to create own model serving endpoints, databricks also provides a nice SQL entry point for the foundation (and custom) models via ai_query.</li><li><strong>Scale naturally:</strong> While this experiment is essentially a batch inference use case where it is expected to process a large amount of document files. Without writing any code to scale the processing. In other words, use Apache spark’s distributed processing model to achieve elastic scaling.</li><li><strong>Unified Governance:</strong> Finally, any data processing we want to do should be safe, reliable and secure. All the processing thus should be governed by Databricks Unity Catalog.</li></ol><h3>Use Case</h3><p>The experiment described here is for a specific use case of parsing utility bills. We all get utility bills such as water, gas and electricity and this code is targeting to automatically parse utility bills in pdf format and extract some details such as <em>name, address, billing period, due date, total units consumed and total amount</em> due etc.</p><h3>Design &amp; Implementation</h3><p>With these benefits in mind, the processing pipeline looks something like this</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/914/1*TBKq4g78hwKuNnmy7a4AGg.jpeg" /></figure><blockquote>So, this experiment assumes pdf files are dropped to databricks volume (defined within Unity Catalog as a securable object). In practice, this can take many forms such as automated Enterprise systems, Partners sharing data via delta share, or manual file upload management via web portal.</blockquote><p><strong>Read as Binary File &amp; Parse using PyPDF2</strong></p><p>This part of the solution is actually fairly standard process. Fundamentally it can be described as: <em>Well, spark does not come with the data format you want to use, so please read the whole file in binary, and bring your own parser module</em>. Using standard SparkReader class ensures the tasks are correctly encoded and sent to executors and many files can be processed in parallel.</p><blockquote>It is also worth mentioning that binary file format essentially reads the entire content of a file as a single row, so be mindful of big files.</blockquote><p>Code to parse and extract text from PDF is offloaded to PyPDF library. Of course, this library has many useful functions to extract clever details, but here text extraction seems to be good enough.</p><pre>%pip install PyPDF2==3.0.1<br>dbutils.library.restartPython()</pre><pre>### Python code to be run on executors to parse content of a single file<br>def get_pdf_text(content):<br>    &#39;&#39;&#39;<br>    This function takes in a binary content (read from pdf file) and returns the text content of the file. It uses the PyPDF2 library to extract the text from the file. It goes over each page, extract text and then combines them to a single, big text block before returning<br>    &#39;&#39;&#39;<br>    from PyPDF2 import PdfReader<br>    from io import BytesIO<br>    pdf = PdfReader(BytesIO(content))<br>    page_texts = [pdf.pages[n].extract_text()  for n in range(len(pdf.pages))]<br>    return &#39;-------page break ------&#39;.join(page_texts)</pre><p>Now, once we hook up this function with normal spark reader, it looks like this:</p><pre>from pyspark.sql.functions import *<br>from pyspark.sql.types import StringType<br><br><br>get_pdf_text_udf = udf(get_pdf_text, StringType())<br>df = spark.read.format(&quot;binaryFile&quot;).load(&#39;/Volumes/&lt;catalog&gt;/&lt;schema&gt;/path/to/file&#39;)<br>pdf_df = df.withColumn(&#39;text_content&#39;, get_pdf_text_udf(&#39;content&#39;))</pre><p>So, the next thing is to define a prompt with various parts. Please note how a placeholder block for data is left to be filled in later by actual pdf text</p><pre>from pyspark.sql.functions import replace, lit, format_string<br><br>definition_promot = &#39;&#39;&#39;You are a text analyst who needs to extract entities and information from the data given below<br>    &#39;&#39;&#39;<br>data_content_prompt = &#39;&#39;&#39;<br>    ##### Data starts here ######<br>    %s<br>    ##### Data ends here ######<br>&#39;&#39;&#39;<br><br>instructions_prompt = &#39;&#39;&#39;<br>    Extract following information from the data: <br>    - Name of the person<br>    - Address<br>    - Total Amount Due<br>    - Invoice Date<br>    - Due Date<br>    - List of Billing Periods: Period Start Data, Period End Date, kWh consumed<br>    - Total kWh consumed<br>    - Total Amount Due<br>&#39;&#39;&#39;<br><br>return_format_prompt = &#39;&#39;&#39;<br>    Return the information in the following format:<br>    {&#39;name&#39;: &#39;&#39;,<br>     &#39;address&#39;: &#39;&#39;,<br>     &#39;total_amount_due&#39;: &#39;&#39;,<br>     &#39;invoice_date&#39;: &#39;&#39;,<br>     &#39;due_date&#39;: &#39;&#39;,<br>     &#39;billing_periods&#39;: [{&#39;period_start_date&#39;: &#39;&#39;, &#39;period_end_date&#39;: &#39;&#39;, &#39;kwh_consumed&#39;: &#39;&#39;}, ...]&#39;&#39;&#39;<br><br>guard_rail_prompt = &#39;&#39;&#39;<br>    ### Important Instructions ###<br>    - Do not hallucinate any information<br>    - Do not add anything to the format other than the information in the data<br>    - Do not add any text before or after the format above as this needs to be parsed by a program&#39;&#39;&#39;<br><br>combined_prompt = definition_promot + data_content_prompt + instructions_prompt + return_format_prompt + guard_rail_prompt</pre><p>This prompt is now to be added in the dataframe, where text_content will be added to the model input to form actual prompt which is ready to be fed to the LLM.</p><pre>add_prompt_df = pdf_df.withColumn(&#39;model_input&#39;, <br>                              format_string(lit(combined_prompt), <br>                                            pdf_df.text_content) <br>                              )                               <br><br>add_prompt_df.createOrReplaceTempView(&#39;add_prompt_df&#39;)<br><br>parsed_df = spark.sql(&#39;&#39;&#39;<br>SELECT ai_query(&#39;databricks-meta-llama-3-3-70b-instruct&#39;, model_input) AS ai_response, *<br>FROM add_prompt_df<br>&#39;&#39;&#39;)<br><br>&#39;&#39;&#39;<br>parsed_df:pyspark.sql.connect.dataframe.DataFrame<br>      ai_response:string<br>      path:string<br>      modificationTime:timestamp<br>      length:long<br>      content:binary<br>      text_content:string<br>      model_input:string<br>&#39;&#39;&#39;<br></pre><p>And that is it. Basic prompting and basic text manipulation was enough to get a pretty accurate result, completely zero shot outcome and thus even more impressive.</p><p>Only thing is left now to format the ai_response as a json and extract specific fields. This is a regular Data Engineering work, nothing fancy going on here</p><pre>result = parsed_df.select(&#39;ai_response&#39;, &#39;path&#39;, &#39;modificationTime&#39;, &#39;length&#39;, &#39;text_content&#39;, &#39;model_input&#39;)\<br>    .withColumn(&#39;ai_response&#39;, <br>                from_json(&#39;ai_response&#39;, <br>                          &#39;name string, address string, total_amount_due string, invoice_date string, due_date string, total_kwh_consumed string, billing_periods string&#39;))\<br>    .withColumn(&#39;name&#39;, col(&#39;ai_response.name&#39;))\<br>    .withColumn(&#39;address&#39;, col(&#39;ai_response.address&#39;))\<br>    .withColumn(&#39;total_amount_due&#39;, col(&#39;ai_response.total_amount_due&#39;))\<br>    .withColumn(&#39;invoice_date&#39;, col(&#39;ai_response.invoice_date&#39;))\<br>    .withColumn(&#39;due_date&#39;, col(&#39;ai_response.due_date&#39;))\<br>    .withColumn(&#39;total_kwh_consumed&#39;, col(&#39;ai_response.total_kwh_consumed&#39;))\<br>    .withColumn(&#39;billing_periods&#39;, <br>                from_json(&#39;ai_response.billing_periods&#39;, <br>                          &#39;array&lt;struct&lt;period_start_date: string, period_end_date: string, kwh_consumed: string&gt;&gt;&#39;))\<br>    .drop(&#39;ai_response&#39;)<br><br>result.write.mode(&#39;overwrite&#39;).saveAsTable(&#39;origin_statement_details&#39;)<br></pre><p>And voila, we have a simple, scalable, native databricks pipeline which uses foundational model out of the box with very simple prompt engineering to turn out to be a great Intelligent data processing solution.</p><p>Code also available in <a href="https://github.com/ayanguha/databricks-foundations/tree/main/pdf-parsing-ai-query">https://github.com/ayanguha/databricks-foundations/tree/main/pdf-parsing-ai-query</a> if you are interested. I have decided to post all databricks related experiments in github under a single repo so I can share easily.</p><p>Few closing notes:</p><p>Databricks has released a new function called ai_parse_document. It is available in US and EU at the time of writing this article. It essentially replaces get_pdf_text and of course does a better job of extracting text, image, url etc.</p><p>Also, Databricks offers Agent Bricks Knowledge Extraction module as an agentic way of doing similar type of work. Unfortunately, it is still in private preview. Eagerly waiting for it to be released in public preview.</p><p>(Thanks <a href="https://medium.com/u/8b78ce892d9">Pamela Lim</a> for suggesting those other native databricks options)</p><p>This is a problem I came across while doing a broader work, and it feels worth sharing. Please feel free to share your comments and feedback.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ec4236348d56" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Lakebase — How to Connect & Use with Databricks Identity]]></title>
            <link>https://guha-ayan.medium.com/lakebase-how-to-connect-use-with-databricks-identity-ac668dc19927?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/ac668dc19927</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Sun, 28 Sep 2025 11:47:09 GMT</pubDate>
            <atom:updated>2025-09-28T11:47:09.069Z</atom:updated>
            <content:encoded><![CDATA[<h3>Lakebase — How to Connect &amp; Use with Databricks Identity</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/669/0*A_9JA317nL3ncoYL.png" /></figure><blockquote><em>Following Databricks’ announcement of Lakebase at the Data + AI Summit in June 2025, many developers are eager to integrate this new PostgreSQL-compatible OLTP database with their Databricks Apps. If you’ve already created a Lakebase database instance through the Databricks workspace and explored the ‘New Query’ experience, you might be wondering about the next steps for application integration.</em></blockquote><p>In this article, I would like to share 3 different options to integrate Apps with Lakebase — and some of my thoughts about Pros and Cons. Here is the overview:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jjrjLvheJwfkZq0B6rxl4A.png" /><figcaption>(Apologies for the screenshot quality, Medium does not support any better format)</figcaption></figure><p>But, first thing first. Lets create a schema, table and insert some record. I have done this via New Query pane on browser, but you can use dbeaver or similar SQL Client using following connection parameters:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Io2pVp1xEgd5SliE-L4JeQ.png" /></figure><p>Then, following sample code was executed</p><pre>create SCHEMA demo;</pre><pre>create table demo.notification_details as <br>select 1 as notification_id, CURRENT_TIMESTAMP - (2 * interval &#39;1 minute&#39;) notification_timestamp, &#39;info&#39; notification_priority, &#39;type 1&#39; notification_type, &#39;msg 1&#39; notification_msg<br>union all <br>select 2 as notification_id, CURRENT_TIMESTAMP notification_timestamp, &#39;warn&#39; notification_priority, &#39;type 2&#39; notification_type, &#39;msg 2&#39; notification_msg</pre><p>Also, you need a sample databricks app. I deployed a blank one:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/980/1*7Nc3GsGAhFY0CMwTKjhdrA.png" /></figure><p>Once deployed, the app will be run using a databricks service principal. You need the client_id and client_secret — and they can be accessed via the Config() object within the app. You can also find it from CLI, like so:</p><pre>databricks apps  get rd-ag-sample-app | jq -r .service_principal_client_id</pre><p>Ok, now we are ready to play! !Let us go through connection options</p><h3>Option 1: Using Postgres Roles — SQL Authentication</h3><p>The first option we will loot at are traditional way of connecting to postgres SQL. It is via Postgres roles. And that means authentication and authorization both handled at postgres level.</p><p>So, there are 2 steps to it:</p><ul><li>You MUST enable Postgres Native connection option</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*y3xJoEupLUmQ_euz6WxZDQ.png" /></figure><ul><li>Create role and provide access</li></ul><pre>CREATE ROLE connect_role LOGIN PASSWORD &#39;XXXXXXX!&#39;;</pre><pre>GRANT SELECT ON &quot;demo&quot;.&quot;notification_details&quot; TO &quot;connect_role&quot;;<br>GRANT USAGE ON SCHEMA &quot;demo&quot; TO &quot;connect_role&quot;;</pre><p>Now you can use either dbeaver or similar SQL client like so</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FXvFQ7eUzN2qiV1bRTQQdw.png" /></figure><p>But, I promised to use Apps.</p><p>And for that we need to setup databricks secret and provide right permissions.</p><pre>databricks secrets create-scope &quot;ayan-g-demo-scope&quot;</pre><pre>databricks secrets put-secret --json &#39;{<br>  &quot;scope&quot;: &quot;ayan-g-demo-scope&quot;,<br>  &quot;key&quot;: &quot;account_id&quot;,<br>  &quot;string_value&quot;: &quot;&lt;your_account_id_here&gt;&quot;<br>}&#39;</pre><pre>databricks secrets put-secret --json &#39;{<br>  &quot;scope&quot;: &quot;ayan-g-demo-scope&quot;,<br>  &quot;key&quot;: &quot;ayan-g-demo-lakebase-connect-role&quot;,<br>  &quot;string_value&quot;: &quot;connect_role&quot;<br>}&#39;</pre><pre>databricks secrets put-secret --json &#39;{<br>  &quot;scope&quot;: &quot;ayan-g-demo-scope&quot;,<br>  &quot;key&quot;: &quot;ayan-g-demo-lakebase-connect-password&quot;,<br>  &quot;string_value&quot;: &quot;&lt;your_password_here&gt;&quot;<br>}&#39;</pre><pre>databricks secrets put-acl &quot;ayan-g-demo-scope&quot; &quot;&lt;App Client Id Here&gt;&quot; MANAGE</pre><p>Once the secret is created and permission provided, the app should be able to read the secret. DO NOT forget to decode the base64 encoded value</p><pre>#########################################################</pre><pre>st.header(&quot;Demo App: Using Postgres Roles - SQL AuthN&quot;)<br>host = &#39;instance-XXXXXXXXXX.database.cloud.databricks.com&#39;<br>get_secret_connect_role = dbutils.secrets.get(scope = &quot;ayan-g-demo-scope&quot;, key = &quot;ayan-g-demo-lakebase-connect-role&quot;).value<br>get_secret_connect_role = base64.b64decode(get_secret_connect_role).decode(&#39;utf-8&#39;)</pre><pre>get_secret_connect_password = dbutils.secrets.get(scope = &quot;ayan-g-demo-scope&quot;, key = &quot;ayan-g-demo-lakebase-connect-password&quot;).value<br>get_secret_connect_password = base64.b64decode(get_secret_connect_password).decode(&#39;utf-8&#39;)</pre><pre>get_secret_connect_password = dbutils.secrets.get(scope = &quot;ayan-g-demo-scope&quot;, key = &quot;ayan-g-demo-lakebase-connect-password&quot;)</pre><pre>st.write({&#39;host&#39;: host, &#39;port&#39;: port, &#39;database&#39;: database, &#39;username&#39;: get_secret_connect_role, &#39;password&#39;: get_secret_connect_password})</pre><pre>conn = st.connection(&quot;sql&quot;, type=&quot;sql&quot;, max_entries=3, ttl=100, dialect = dialect, <br>                     host = host, port = port, database = database, username = get_secret_connect_role, password = get_secret_connect_password,<br>                     connect_args={&#39;sslmode&#39;: &quot;require&quot;})<br></pre><pre>df = conn.query(&quot;SELECT *, &#39;Using Postgres Roles - Special Needs or Migration&#39; FROM demo.notification_details;&quot;, ttl=&quot;10m&quot;)<br>st.write(df)</pre><p>And, you should be in. The result should show like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iaM7FbPYoHqq5LUmT6tgmA.png" /></figure><h3>Option 2: Using requests Module — VERY DIY</h3><p>Until this point, we are using Lakebase as a traditional cloud database. One benefit of using Lakebase is that it is automatically (kind of!!) integrated with databricks authentication model and the database itself is treated as a databricks resource. What that actually means is:</p><blockquote><strong>A databricks service principal can use </strong><a href="https://docs.databricks.com/aws/en/dev-tools/auth/oauth-m2m"><strong>M2M Oauth flow </strong></a><strong>to obtain an access token and use it to access Lakebase database</strong></blockquote><p>As a databricks app already has a service principal (which is represented by the client_id) we need to do two things:</p><ul><li>Need to create a role in the database with the same value as the client_id More details <a href="https://docs.databricks.com/aws/en/oltp/pg-roles#create-postgres-roles-based-on-databricks-identities">here</a></li></ul><pre>CREATE EXTENSION IF NOT EXISTS databricks_auth;</pre><pre>SELECT databricks_create_role(&#39;&lt;your client_id here&gt;&#39;,&#39;SERVICE_PRINCIPAL&#39;);</pre><pre>GRANT SELECT ON &quot;demo&quot;.&quot;notification_details&quot; TO &quot;&lt;your client_id here&gt;&quot;;<br>GRANT USAGE ON SCHEMA &quot;demo&quot; TO &quot;&lt;your client_id here&gt;&quot;;</pre><ul><li>Obtain a access token and use it as a password</li></ul><p>The subsequent code is the most DIY way, and also the most generic way, to get access token for any databricks identity using the API. I am using requests library and the first part of the code should be very familiar to anyone worked with databricks identities (or in general have some OAuth knowledge)</p><pre>#########################################################<br>st.header(&quot;Demo App: Using requests Module -- VERY DIY &quot;)</pre><pre>import requests</pre><pre>account_id = dbutils.secrets.get(scope = &quot;ayan-g-demo-scope&quot;, key = &quot;account_id&quot;)</pre><pre>cfg = Config()<br>username = client_id = cfg.client_id<br>client_secret = cfg.client_secret</pre><pre>token_url=f&quot;https://accounts.cloud.databricks.com/oidc/accounts/{account_id}/v1/token&quot;</pre><pre>data = {&#39;grant_type&#39; : &#39;client_credentials&#39;, &#39;scope&#39;: &#39;all-apis&#39;}<br>res = requests.post(token_url, auth=(client_id, client_secret), data = data)</pre><pre>password = res.json()[&#39;access_token&#39;]<br></pre><pre>st.write({&#39;host&#39;: host, &#39;port&#39;: port, &#39;database&#39;: database, &#39;username&#39;: username, &#39;password&#39;: password})<br># Initialize connection.<br>conn = st.connection(&quot;sql&quot;, type=&quot;sql&quot;, max_entries=3, ttl=100, dialect = dialect, <br>                     host = host, port = port, database = database, username = username, password = password,<br>                     connect_args={&#39;sslmode&#39;: &quot;require&quot;})<br></pre><pre>df = conn.query(&quot;SELECT *, &#39;Using requests Module -- VERY DIY&#39; FROM demo.notification_details;&quot;, ttl=&quot;10m&quot;)<br>st.write(df)</pre><p>Now you should be able to see the data using this method:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PBdl-blFKeZ2brUw-zWIeQ.png" /></figure><p>Note; password is a short-lived OAuth access token. By looking at the format, I can also tell that it is a JWT token. You can looking it up and see the details here and you can see the Expiry Date (encoded in Unix Epoch Time)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4I3swd5-5yGGoxqQBYP1VA.png" /></figure><h3>Option 3: Using WorkspaceClient — Recommended</h3><p>Now, DIY is not for faint-hearted, and more importantly, not required to go through all those troubles yourself. New version of databricks-sdk makes it super easy.</p><p>But, you need to upgrade your environment. As of July 4, 2025 Databricks apps environment in Australia/Sydney region is still at databricks-sdk==0.33.0 and you need higher. I have tested with databricks-sdk==0.57.0 so it has changed somewhere in between. The docs <a href="https://docs.databricks.com/aws/en/oltp/query/notebook">here</a> talks about it in detail in context of connecting Lakebase with notebooks.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/902/1*0NGr4MUwbIAvD4bE8YEQ6A.png" /></figure><p>Now, it should be trivial:</p><pre>############## Common Attributes ###################<br>from databricks.sdk import WorkspaceClient<br>w = WorkspaceClient()</pre><pre>instance_name = &quot;ayan-g-demo&quot;<br>instance = w.database.get_database_instance(name=instance_name)<br>host = instance.read_write_dns<br>##############<br>st.header(&quot;Demo App: Using WorkspaceClient - Recommended &quot;)</pre><pre>cred = w.database.generate_database_credential(request_id=str(uuid.uuid4()), instance_names=[host])<br>password = cred.token<br>cfg = Config()<br>username = cfg.client_id</pre><pre>st.write({&#39;host&#39;: host, &#39;port&#39;: port, &#39;database&#39;: database, &#39;username&#39;: username, &#39;password&#39;: password})<br># Initialize connection.<br>conn = st.connection(&quot;sql&quot;, type=&quot;sql&quot;, max_entries=3, ttl=100, dialect = dialect, <br>                     host = host, port = port, database = database, username = username, password = password,<br>                     connect_args={&#39;sslmode&#39;: &quot;require&quot;})<br></pre><pre>df = conn.query(&quot;SELECT *, &#39;Using WorkspaceClient - Recommended&#39; FROM demo.notification_details_1;&quot;, ttl=&quot;10m&quot;)<br>st.write(df)</pre><p>This is the recommended method, so if you are using it, please do not forget to create appropriate role for the service principal, following the previous section.</p><h3>Few Notes on Best Practices</h3><p>The code snippets used in this blog are intentionally simplified, to get the development started. I used streamlit st.connection method for explanation only. For prod-ready development, following choices are available:</p><ul><li>Use standard ORM libraries such as sqlalchemy or psycopg2 — both are supported. See <a href="https://docs.databricks.com/aws/en/oltp/query/notebook#python">this doc</a> for more details</li><li>Use connection_pool for better concurrency mnagement. See <a href="https://docs.databricks.com/aws/en/oltp/query/notebook?language=Connection%C2%A0pool#python">this doc</a> for more details</li><li>Refresh token regularly. This <a href="https://docs.databricks.com/aws/en/oltp/query/notebook?language=Connection+pool+%26+rotating+M2M+OAuth#python">doc</a> describes how to do refresh token periodically. Additionally, instead of refreshing with static window of time, JWT exp claim can also be used to ascertain if the token is due for refresh</li></ul><p>This blog covered three distinct approaches to connecting Databricks Apps with Lakebase databases. Each method has its place depending on your specific requirements:</p><blockquote>Use PostgreSQL roles for traditional database workflows or when migrating existing applications<br>Use requests module when you need fine-grained control over the authentication flow<br>Use WorkspaceClient for new Databricks Apps seeking the most streamlined integration</blockquote><p>The upcoming demo will showcase these integration patterns in a real-world scenario, demonstrating how Lakebase bridges the gap between operational + analytical data in modern AI-driven applications.</p><p>— — — — — — — — — — — — — — — —</p><blockquote>PS: Edit on 18/7/2025</blockquote><p>Databricks added another documentation (somewhat unlikely part of their doc suite) on <a href="https://docs.databricks.com/aws/en/dev-tools/databricks-apps/lakebase">How to Add Lakebase as a resource in a databricks apps</a> so I am adding it here for completeness</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ac668dc19927" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Tale of Two (Opposite) Trends]]></title>
            <link>https://guha-ayan.medium.com/a-tale-of-two-opposite-trends-e00d01c51e24?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/e00d01c51e24</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Wed, 23 Apr 2025 03:39:17 GMT</pubDate>
            <atom:updated>2025-04-23T03:39:17.278Z</atom:updated>
            <content:encoded><![CDATA[<p>Recently I have been asked about evolution and future of data platform design few times. While explaining to stakeholders I noticed that a holistic understanding of where data platforms are headed is often missing. And regardless someone is deep in the trenches or someone planning for next big project, it is always good to take a step back and have a birds-eye view of what is happening around us.</p><p>The demand and need for clean, reliable, trustworthy, consumption-ready data is higher than ever. The key players in this space are either obvious public cloud providers (Read: AWS, Azure and GCP)OR data technologies which born and bred in cloud (Read Snowflake and Databricks). AWS and Azure has rolled out their flagship offerings in last couple of years, where multi-cloud vendors like Databricks and Snowflake are competing hard by making their individual products more nuanced, refined and user-friendly.</p><p>As a result of the breakneck race of acquiring and retaining workloads, two opposing trends emerging — it looks like they will be around for few more years, and thus worth discussing.</p><ol><li>At one end, every serious data platforms are building all-inclusive, batteries-included data platforms to keep existing customers within the platform.</li><li>On the other end, same providers are offering interoperability via open data formats in order to consume data from any other provider. This enables these platforms to acquiring more workloads as well as partner with other players in the ecosystem via Data Sharing and Clean Room uses cases</li></ol><h3><strong><em>First, Build a Nice Platform</em></strong></h3><p>Here is the simplified blueprint of leading data providers from a conceptual capability model view:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/751/1*JauUEkRDIYI0PjSObsvUug.jpeg" /></figure><p>This blueprint is a derivative of Lakehouse architecture. Engineering advances of last 4–5 years has started to pay off and the conceptual design is finally shaping up to be industrialised and stable enough. More and more fine tuning, refinements and feature parity will start flowing through various product and vendor roadmaps, but the foundation will not dramatically change.</p><blockquote>Note: Native Data applications is emerging as a new concept. Ex: <a href="https://www.databricks.com/product/databricks-apps">Databricks Apps</a> and <a href="https://www.snowflake.com/en/product/features/native-apps/">Snowflake Native Apps</a> started with simple streamlit application developments. It is a successful venture for sure, and now with advent of native Generative AI apps the demand of such apps is skyrocketing. Watch out this space.</blockquote><p>Compare this with following offering rolled out by leading vendors, and the point will become more clearly illustrated.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PTU6cKCyyHd8xEuK7SmVBA.jpeg" /></figure><h3>Then, Make it interoperable</h3><p>There has been another competition has been brewing in the background. It is a competition around two most important aspects of data management:</p><ol><li><strong>How to minimise number of files to be read from disk: </strong>This is an age old problem since the first database was designed. Every technology throughout last 50 years tried to solve this problem, at scale, with security, while providing ACID guarantees. This problem has’nt gone away in cloud-based data platforms but definitely some well known players has emerged. Paying its due tribute to <a href="https://hudi.apache.org/">Apache Hudi</a>, clear front runners are <a href="https://delta.io/">Apache Delta Lake</a> and <a href="https://iceberg.apache.org/">Apache Iceberg</a>. Both are novel technology, with strong engineering foundations and wide adoptions</li><li><strong>How to make them interoperable</strong>: Aka. The competition of Open Data (sometimes called Open Table) formats. Delta has always been perceived as proprietary technology from databricks where it was incubated and battle tested for a while before being open sources. Microsoft contributed a significant work into delta before adopting it as a basis of their Fabric OneLake design. On the other hand, Iceberg, originated from Netflix, has got more love and care from Snowflake and AWS over last few years. The competition has been fierce over this space as everyone saw this as a true opportunity to capture more workloads and enable more value. Until last year when databricks announced their <a href="https://www.databricks.com/blog/delta-lake-universal-format-uniform-iceberg-compatibility-now-ga">Iceberg support</a>. More details can be found in this awesome <a href="https://www.databricks.com/resources/webinar/beyond-lakehouse-table-formats/thank-you?scid=7018Y000001Fi10QAC&amp;utm_source=google&amp;utm_adgroup=169620277604&amp;utm_offer=beyond-lakehouse-table-formats&amp;utm_term=iceberg+databricks&amp;gad_source=1&amp;gclid=Cj0KCQjw782_BhDjARIsABTv_JDIt8-sMmQzMpDFHyDQPl9qczaQ0hEbviVz53W8grn2_RW-yZOQX1kaAqB9EALw_wcB">discussion podcast</a>.</li></ol><p>Interestingly, all the vendors are prioritising interoperability — via Delta if it is within Azure (Fabric and Databricks) or via Iceberg (across cloud and data platform vendors).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/821/1*rE4uA9dCDxixm8o_TXiSbg.jpeg" /></figure><h3><strong>What does all of this mean: Go back to the basics</strong></h3><p>The key result is a versatile landscape of choices. To add to the complexity, each interoperable solutions are at different maturity level. This is often confusing for new data practitioners and leaders as well as seasoned data architects who are embarking in or already in the middle of enterprise data platform journey. Then there are individual opinions, biases and agendas to add to these confusions.</p><p>So, how to makes sense of this myriad mesh of options? Here are some suggestions:</p><ol><li><strong>Nothing Succeed like success</strong>: Please do not lose sight of the key drivers. It is absolutely fine, even recommended, to start small. Deliver a sliver of technology roadmap while delivering business values faster.</li><li><strong>MVP still needs to go to Prod</strong>: Delivering a slice of the platform does not mean less design work. On the contrary, it allows to define standards, build security, quality and governance foundations and pave the way for automation around deployment and operationalisation. Please do not compromise on making the data platform enterprise-grade while building the MVP.</li><li><strong>Think capabilities, not tools: </strong>Define a list of capabilities the data platform must support. In latest state of technology, all of the platforms discussed above are fairly capable of delivering business values and it is not uncommon to have a mix of them to deliver different capabilities. The only thing to remember is not try to deliver same capability using more than one capability. Ex: Choosing Snowflake for Data Warehousing and Databricks for AI/ML is perfectly kosher, but building Data Warehouse partially in Snowflake and partially in Databricks is not recommended.</li><li><strong>Embrace rework, not tech debts:</strong> In this quickly changing landscape if anything is constant then it is the fact that the platform will change and evolve. Rework is almost inevitable while developing a platform. Faster adoption of change is a trait platform owners should celebrate. And nothing slows down the change more than technical debts incurred via sub-optimal design and planning. Please plan for rework to refine and improve individual parts of the platform independently, but at the same time obsessively control tech debts.</li></ol><p>It is extremely interesting that how versatile the landscape has become to enable us, data engineers, to deliver value in rapid pace. See this <a href="https://medium.com/@lam.truong/navigating-the-data-landscape-open-composable-architectures-vs-c6f8f226688c">3 part series</a> for more details about how to go about navigating this landscape. If you need any help or your ideas need a sounding board, please feel free to reach out. Happy to offer to listen in your requirements, thoughts, challenges and definitely happy to share experiences and opinions.</p><p>Interesting times ahead!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e00d01c51e24" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[dbt Snapshot vs DLT — Apply Change]]></title>
            <link>https://guha-ayan.medium.com/dbt-snapshot-vs-dlt-apply-change-2c955c747418?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/2c955c747418</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Wed, 16 Apr 2025 01:11:43 GMT</pubDate>
            <atom:updated>2025-06-10T13:32:42.313Z</atom:updated>
            <content:encoded><![CDATA[<h3>dbt Snapshot vs DLT — Apply Change</h3><p>Recently I was experimenting with Delta Live Tables (DLT) and was intrigued by the comment in databricks docs Previously, the MERGE INTO statement was commonly used for processing CDC records on Databricks. However, MERGE INTO can produce incorrect results because of out-of-sequence records or requires complex logic to re-order records.</p><p>I decided to give it a spin. And to compare with traditional MERGE type workload, I decided to use dbt snapshot with postgres The broad set up is to populate some base table (or ephemeral views, in case of dbt) with required data and try to simulate some out-of-order scenarios. These scenarios are rare in practice and often represent some systemic issues at source. However, sometime there are legitimate business or technical scenarios to elicit out-of-order events and it is actually tricky to handle them properly.</p><p>Here is the High Level Summary</p><pre><strong>High Level Summary</strong><br>+---------------+------------------+--------------+<br>|     Case      | DLT Apply Change | dbt Snapshot |<br>+---------------+------------------+--------------+<br>| Base Scenario | Pass             | Pass         |<br>| Case 1A       | Pass             | Fail         |<br>| Case 1B       | Pass             | Fail         |<br>| Case 2        | Pass             | Fail         |<br>| Case 3A       | Pass             | Fail         |<br>| Case 3B       | Pass             | Fail         |<br>| Case 4        | Pass             | Fail         |<br>+---------------+------------------+--------------+</pre><blockquote>Now, just by looking at this summary, it may seem that DLT is much better than dbt. But in reality, requirement to cater for the late arrival data semantics is not very common, and dbt is perfect for most of the use cases.</blockquote><blockquote>DLT has few sweet spots and APPLY CHANGES is one of them. It delivers the functionality required for SCD Type 1 or Type 2 with complex out-of-order events out of the box, which alleviate the effort of writing and maintaining our own.</blockquote><blockquote><strong><em>Hence, I would suggest to keep this as a specialised tool in the toolbox and use it sparingly when special circumstances warrant the use</em></strong></blockquote><h3>Scenarios</h3><ol><li>Base Scenario:</li></ol><ul><li>Initial record: (product_id, status, event_date) = 1, ‘pending’, ‘2023–01–01’</li><li>2nd record: (product_id, status, event_date) = 1, ‘assigned’, ‘2023–01–05’</li><li>3rd record: (product_id, status, event_date) = 1, ‘in-progress’, ‘2023–01–08’</li></ul><p>2. Case 1A: Out of order record, with event_date before first existing record (different status value)</p><ul><li>(product_id, status, event_date) = 1, ‘submitted’, ‘2022–12–25’</li></ul><p>3. Case 1B: Out of order record, with event_date before first existing record (same status value)</p><ul><li>(product_id, status, event_date) = 1, ‘pending’, ‘2022–12–25’</li></ul><p>4. Case 2: Out of Order Recrd, with event_date between 2 closed record</p><ul><li>(product_id, status, event_date) = 1, ‘in-progress’, ‘2023–01–03’</li></ul><p>5. Case 3A: Out of Order Record, with event_date between one closed record and one open record, but no change in status</p><ul><li>(product_id, status, event_date) = 1, ‘in-progress’, ‘2023–01–06’</li></ul><p>6. Case 3B: Out of Order Record, with event_date between one closed record and one open record, with change in status</p><ul><li>(product_id, status, event_date) = 1, ‘in-progress — Late’, ‘2023–01–06’</li></ul><p>7. Case 4: New Record, No change in status value</p><ul><li>(product_id, status, event_date) = 1, ‘in-progress’, ‘2023–01–10’</li></ul><h3>Expected Result</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/998/1*tcMIH_SsnpCezrqMx746GA.jpeg" /></figure><h3>Code Snippets: dbt</h3><p>dbt Snapshot config product_snapshot.yaml</p><pre>snapshots:<br>  - name: product_snapshot<br>    relation: ref(&#39;product_status_scd2&#39;)<br>    config:<br>      schema: snapshots<br>      database: postgres <br>      unique_key: product_id<br>      strategy: timestamp<br>      updated_at: updated_at<br>      hard_deletes: ignore <br>      snapshot_meta_column_names:<br>        dbt_valid_from: record_valid_from_datetime<br>        dbt_valid_to: record_valid_to_datetime<br>        dbt_scd_id: product_sk_id<br>        dbt_updated_at: modified_datetime<br>        dbt_is_deleted: is_deleted</pre><p>dbt model stg_product.sql</p><pre>{{ config(materialized=&#39;ephemeral&#39;) }}<br><br>with base as <br>(<br>    select <br>        1 product_id,<br>        -- Base scenario  <br>        --&#39;pending&#39; status,<br>        --&#39;2023-01-01&#39; updated_at <br>        -----------------------------    <br>        --&#39;assigned&#39; status,<br>        --&#39;2023-01-05&#39; updated_at <br>        ----------------------------- <br>        --&#39;in-progress&#39; status,<br>        --&#39;2023-01-08&#39; updated_at <br>        ----------------------------------------------------------<br>        -- Case 1A: Out of order record, with event_date before first existing record (different status value)<br>        --&#39;submitted&#39; status,<br>        --&#39;2022-12-25&#39; updated_at <br>        -----------------------------<br>        -- Case 1B: Out of order record, with event_date before first existing record (same status value)<br>        --&#39;pending&#39; status,<br>        --&#39;2022-01-01&#39; updated_at <br>       ----------------------------------------------------------<br>        -- Case 2: Out of Order Recrd, with even_date between 2 closed record<br>        --&#39;in-progress&#39; status,<br>        --&#39;2023-01-03&#39; updated_at  <br>        ----------------------------------------------------------<br>        -- Case 3A: Out of Order Record, with event_date between one closed recrd and one open record, but no change in status<br>        --&#39;in-progress&#39; status,<br>        --&#39;2023-01-06&#39; updated_at  <br>        -----------------------------<br>        -- Case 3B: Out of Order Record, with event_date between one closed recrd and one open record, change in status<br>        --&#39;in-progress - Late&#39; status,<br>        --&#39;2023-01-06&#39; updated_at  <br>        ----------------------------------------------------------<br>        -- Case 4: New Record, No change in status value<br>         --&#39;in-progress&#39; status,<br>        --&#39;2023-01-10&#39; updated_at  <br>        -----------------------------<br>)<br>select * from base </pre><p>Example dbt snapshot run</p><pre>(.venv) #####@#######$$$ % <strong>dbt snapshot</strong> <br>12:31:10  Running with dbt=1.9.4<br>12:31:10  Registered adapter: postgres=1.9.0<br>12:31:10  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.<br>There are 1 unused configuration paths:<br>- models.my_new_project.example<br>12:31:10  Found 3 models, 4 data tests, 1 snapshot, 433 macros<br>12:31:10  <br>12:31:10  Concurrency: 1 threads (target=&#39;dev&#39;)<br>12:31:10  <br>12:31:11  1 of 1 START snapshot public_snapshots.product_snapshot ........................ [RUN]<br>12:31:11  1 of 1 OK snapshotted public_snapshots.product_snapshot ........................ [INSERT 0 1 in 0.22s]<br>12:31:11  <br>12:31:11  Finished running 1 snapshot in 0 hours 0 minutes and 0.41 seconds (0.41s).<br>12:31:11  <br>12:31:11  Completed successfully<br>12:31:11  <br>12:31:11  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1</pre><h3>Code Snippets: databricks</h3><p>Databricks Table DDL &amp; DMLs</p><pre>%sql<br>drop table au_etl_demos.autoloader_tests.stg_product;<br>drop table au_etl_demos.autoloader_tests.product_scd_2;<br>create table au_etl_demos.autoloader_tests.stg_product (product_id int, status string, updated_at date)</pre><pre>%sql<br>-- Base scenario<br> --   insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;pending&#39;, &#39;2023-01-01&#39;);<br> --   insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;assigned&#39;, &#39;2023-01-05&#39;);<br> --   insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;in-progress&#39;, &#39;2023-01-08&#39;);<br>------------------------------------------------------------------------------------------------------------------<br>-- Case 1A: Out of order record, with event_date before first existing record (different status value)<br>-- insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;submitted&#39;, &#39;2022-12-25&#39;);<br>------------------------------------------------------------------------------------------------------------------<br>-- Case 1B: Out of order record, with event_date before first existing record (same status value)<br>-- insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;pending&#39;, &#39;2022-12-25&#39;);<br>------------------------------------------------------------------------------------------------------------------<br>-- Case 2: Out of Order Recrd, with even_date between 2 closed record<br> -- insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;in-progress&#39;, &#39;2023-01-03&#39;);<br>------------------------------------------------------------------------------------------------------------------<br>-- Case 3A: Out of Order Record, with event_date between one closed recrd and one open record, but no change in status<br> -- insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;in-progress&#39;, &#39;2023-01-06&#39;)<br>------------------------------------------------------------------------------------------------------------------<br>-- Case 3B: Out of Order Record, with event_date between one closed recrd and one open record, change in status<br> -- insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;in-progress - Late&#39;, &#39;2023-01-06&#39;);<br>------------------------------------------------------------------------------------------------------------------<br>-- Case 4: New Record, No change in status value<br>--  insert into au_etl_demos.autoloader_tests.stg_product values(1, &#39;in-progress&#39;, &#39;2023-01-10&#39;);<br>------------------------------------------------------------------------------------------------------------------</pre><p>Databricks DLT Code</p><pre>import dlt<br>from pyspark.sql.functions import col, expr<br><br>@dlt.view<br>def stg_product():<br>  return spark.readStream.table(&quot;au_etl_demos.autoloader_tests.stg_product &quot;)<br><br>dlt.create_streaming_table(&quot;product_scd_2&quot;)<br><br>dlt.apply_changes(<br>  target = &quot;product_scd_2&quot;,<br>  source = &quot;stg_product&quot;,<br>  keys = [&quot;product_id&quot;],<br>  sequence_by = col(&quot;updated_at&quot;),<br>  except_column_list = [&quot;updated_at&quot;],<br>  stored_as_scd_type = &quot;2&quot;<br>)</pre><p>DLT Pipeline</p><pre>{<br>    &quot;id&quot;: &quot;#########&quot;,<br>    &quot;pipeline_type&quot;: &quot;WORKSPACE&quot;,<br>    &quot;development&quot;: true,<br>    &quot;continuous&quot;: false,<br>    &quot;channel&quot;: &quot;CURRENT&quot;,<br>    &quot;photon&quot;: true,<br>    &quot;libraries&quot;: [<br>        {<br>            &quot;notebook&quot;: {<br>                &quot;path&quot;: &quot;/Users/#########/Autoloader test/DLT Test&quot;<br>            }<br>        }<br>    ],<br>    &quot;name&quot;: &quot; DLT Job&quot;,<br>    &quot;serverless&quot;: true,<br>    &quot;catalog&quot;: &quot;au_etl_demos&quot;,<br>    &quot;schema&quot;: &quot;autoloader_tests&quot;,<br>    &quot;data_sampling&quot;: false<br>}</pre><h3>Results</h3><h4>dbt:</h4><ol><li>Base Scenario:</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Nd5Z-A6tbzfGTDnDWrrwdQ.png" /></figure><p>2. Case 1A, 1B, 2, 3A, 3B: No change</p><p>3. Case 4:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zREd-KT1UWq8jba5p4xmnQ.png" /></figure><h4>databricks</h4><ul><li>Base Scenario</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/908/1*1JM4_UXPtyvRegtv-CEzLg.png" /></figure><ul><li>Case 1A</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*kfMbEyEXUGEymGbS42mPRQ.png" /></figure><ul><li>Case 1B</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/863/1*BFj_uArC3pPjja9MCfWfbA.png" /></figure><ul><li>Case 2</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/863/1*L4WOMgR0BCGaaENv-YeAWA.png" /></figure><ul><li>Case 3A</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/761/1*bg_w2rbq5FZI58e5gWVjOQ.png" /></figure><ul><li>Case 3B</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*TrUZ0VBQW3mTfEysIho-9Q.png" /></figure><ul><li>Case 4</li></ul><p>No Change (As Expected)</p><blockquote>Disclaimer: These tests were run with dbt snapshot with postgres If anyone wants to repeat these tests in any other supported datawarehouse please let me know in the comments. I will be very interested in your findings and happy to make necessary updates in this article</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2c955c747418" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Text2SQL — Evaluation Accelerator]]></title>
            <link>https://guha-ayan.medium.com/text2sql-evaluation-accelerator-a52774832bdf?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/a52774832bdf</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[llm-evaluation]]></category>
            <category><![CDATA[text-2-sql]]></category>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Mon, 24 Mar 2025 20:27:11 GMT</pubDate>
            <atom:updated>2025-04-22T12:55:07.061Z</atom:updated>
            <content:encoded><![CDATA[<h3>Text2SQL — Evaluation Accelerator</h3><p>One of the very first application of Generative AI, or LLMs, has been text to SQL transformation. In other words, user can describe what they want to query from the database in natural language, and some application will translate that description to a SQL statement. The SQL statement must be syntactically correct, and at the very least functionally correct, and hopefully well-written and performant. Very often these applications are required to integrate with data consumption layer (or Gold layer, if you are fan of medallion architecture) developed on top of choice of cloud data platforms. It is actually a reflection of aspirations of analysts who use data daily — especially someone non-technical — such as: Insurance Actuary Analysts, Power Grid Electrical Engineers.</p><p>Another key aspect of Generative AI has been evaluation and benchmarking frameworks used to validate quality, performance and reliability of Large Language models. Many useful evaluation frameworks has been designed, developed and continuously improved to match the pace of innovation in this space.</p><p>How about the eval frameworks for Text2SQL task? First of all, what are validation points? Here is a simple mental model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/372/1*q_yM2ehJUQttpHQ4sBstXA.png" /></figure><p>In this diagram, at any point, only one of the three dimension can be evaluated by measuring variations around that dimension while locking down any variation around other dimension.</p><p><strong>Prompts</strong>: If the choices of LLM and amount of data can be accessed are locked down, then various prompting techniques can be tried and measured with high confidence.</p><p><strong>Models</strong>: If the prompts and data can be locked down, then a benchmark harness can integrate various models and record their relative performances</p><p><strong>Data</strong>: Generally, data is assumed relatively static variable when it comes to benchmarking. While it is essential to lock down data while evaluating model or prompt efficacy so that the results can be reproduced, it is not very useful in real life. But more on this later.</p><p>Current available benchmarking solutions are generally of 2 types:</p><ol><li><strong>Multi-model Evaluation </strong>— These are generally used to benchmark new large language models, and hence it is useful to lockdown prompting techniques and data elements. Example: <a href="https://crfm.stanford.edu/helm/">Helm from Standford</a> and <a href="https://huggingface.co/docs/leaderboards/en/index">Huggingface Leaderboard</a>. These frameworks are primarily used by industry leading model developers and researchers. They use these benchmarks to understand high level efficacy of models as well as probe deeper into model’s inner workings. Here the focus largely is open and academic evaluation of various modelling techniques, model architecture, model size, context window etc.</li><li><strong>Single Model Evaluation</strong> — These are generally used by vendors to test their flagship models such that to determine the readiness for market. The focus of these evaluations is very different than (1) — the objective is to optimise a model — and vary different system prompts to tune for better results. Generally, this process has a unique leg-up over (1) — the target SQL dialect is also locked down to vendor supported platform. As an example, <a href="https://www.snowflake.com/en/product/features/arctic/">Snowflake Arctic family of models</a> are tuned to generate Snowflake SQL dialect, with Snowflake specific functions and performance optimisations</li></ol><p>It is interesting that both are largely based on <a href="https://yale-lily.github.io/spider">Spider</a> and <a href="https://www.tpc.org/tpcds/">TPC-DS</a> datasets — with some variations like curated instructions and question-answer pairs. There are two problems -</p><p>Firstly, the dataset, prompts and validation pairs are often open-sourced. Hence there is a high possibility that new LLMs will be trained on such benchmarks, leading to performing better without improving the model itself — a scenario which is analogous to an exam where questions were already known to the participants.</p><p>Secondly, data quality, governance, modelling and customisations are reflective of Organisation’s unique business processes and overall data maturity. No two organisation has identical data models — even if they start of same common models with large overlaps. Evaluations on standard data sets are useful to measure how the model is “expected” to perform — but your mileage will ALWAYS vary.</p><p>In some way, there is a gap currently for some framework which can help organisations to employ both benchmarking techniques described above — but for real life datasets. This article will propose what are components needed for such a framework, and will describe a small demo version to illustrate the viability of such framework.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/1*JaClv24SmbsTTwjED6iB7g.jpeg" /></figure><p>User Interface and API layers will be standard web development components — to enable functionalities for end users. There are a ton of great examples of such applications. In the demo, I used streamlit and Flask, though I would propose to use any better alternative for streamlit.</p><p>Key aspects of the solution will be</p><ol><li><strong>Database Manager:</strong> A key requirement of the application to have an extensible component to manage all database interactions. Each database/data warehousing solution exposes metadata differently with minor variations. The SQL dialects and available functions are unique. Documentation pages are database specific too. And very importantly, each database reports query performance statistics differently. So it is critical to use an <a href="https://refactoring.guru/design-patterns/adapter">adapter pattern </a>to build out specific database manager — or even better to utilise existing libraries via a thin wrapper on top of them.</li><li><strong>LLM Manager:</strong> Similar to database manager, the idea is to manage a way to standardise how the prompts are collected, tuned and modified over various experimentation and finally able to choose the right one. While I started the basic demo using Ollama to keep things local, it seems Anthropic MCP is designed for this exact purpose and can be adapted to Text2SQL use case</li><li><strong>Result Manager: </strong>A standard way to store the prompt, system prompt, LLM response (with chain of thought if applicable), SQL generated, SQL run statistics, SQL similarity with a curated set etc. Generally this can be any database — I used local couchdb server as I believe NoSQL databases will be a good choice for this scenario.</li></ol><p>Here is how the demo version looks like</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/681/1*oIcWfnSdVtnWXbTsfEXSgA.jpeg" /></figure><p>A screen record for how it looks (done over weekend — using VS Code using <a href="https://github.com/features/copilot">Github Copilot</a>)</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FLADTN3Lj8UU%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DLADTN3Lj8UU&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FLADTN3Lj8UU%2Fhqdefault.jpg&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/34cfe3a976c6253f7d6f16c368b1989f/href">https://medium.com/media/34cfe3a976c6253f7d6f16c368b1989f/href</a></iframe><p>Large chunk of code here: <a href="https://github.com/ayanguha/ollama-duckdb-prompt-tracker">https://github.com/ayanguha/ollama-duckdb-prompt-tracker</a> —</p><p>As promised, I have added docs here: <a href="https://ayanguha.github.io/ollama-duckdb-prompt-tracker/index.html">https://ayanguha.github.io/ollama-duckdb-prompt-tracker/index.html</a> using <a href="https://www.sphinx-doc.org/en/master/">sphinx</a> (I wanted to learn it for a while and used this opportunity to learn how to build beautiful documentation)</p><p>Any comments, suggestions, criticism — all welcome.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a52774832bdf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Top 5 Macro Data Trends for 2023]]></title>
            <link>https://guha-ayan.medium.com/top-5-macro-data-trends-for-2023-17cc19e63da6?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/17cc19e63da6</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Fri, 23 Dec 2022 04:15:47 GMT</pubDate>
            <atom:updated>2022-12-23T04:23:35.615Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*WgTiLgKXf5kTAeeYS4i_TA.jpeg" /></figure><p>Holiday greetings!! It is time of the year when we are buzzing with anticipation of upcoming holidays, and also with anticipation for what new year will bring to us. This is also a great time to look back to identify what are the big trends we saw this year, and what we expect to continue in next year. Here are highlights for me, synthesised.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EPfoBOL87ip-uv75Lx2NEw.png" /><figcaption>Macro Data Trend: 2023</figcaption></figure><h3><strong>I. Data Reliability will be key</strong></h3><p>In distant past, life was simple. Data used to live in a single, often giant, appliance based data warehouses. Data processing, storage, analysis everything used to colocate. It had an excellent property: single control plane touchpoint.</p><p>Fast forward to today, our data storage and compute systems are decoupled, pipelines are often span across multiple toolsets, and there are requirement to enable multiple consumption pattern of data. All for good reasons. However, now our control planes are widely fragmented. So what is the impact? Our experience is this fragmentation directly impact non functional requirements (NFR) such as quality, performance, cost, security etc.</p><p>This year we are seeing an emerging trend of renewed focus on data reliability engineering. Key focus being consolidation of tools and technologies to provide a streamlined way to manage and measure NFRs, there are number of concepts and implementations were introduced around this theme, such as:</p><blockquote>Distributed Data Governance</blockquote><ul><li>Platform specific governance tools such as Unity Catalog, AWS Lake Formation, Alation etc.</li><li>Tag/Attribute based policy enforcement: this is a big leap in terms governance and policy management</li><li>Data Security — including Perimeter security, AuthN and AuthZ, Hashing, Encryption at rest and at flight.</li></ul><blockquote>Data Ops</blockquote><ul><li>Integration of data assets with CI/CD</li><li>Automated Data Quality measurement</li><li>Use software engineering practices to ensure code quality, especially using Spark and dbt</li><li>Automated and augmented AI/ML driven proactive security and quality management such as GCP Cloud DLP</li></ul><blockquote>Data Observability</blockquote><ul><li>Single Pane of Glass for end to end data visibility</li><li>Centralised logging, monitoring and notification system</li></ul><h3>II. Unified Data Platforms</h3><p>Unification of data platforms has been a theme this year and we expect it to continue in next year as well. Two biggest manifestation of this concept can be grouped as below:</p><blockquote>Unification of Processing Semantics</blockquote><p>The concept of treating batch data as bounded dataset and real time data as unbounded dataset, and treating both datasets around such abstraction is the key focus.</p><p>This is a trend is shaping up for last couple of years. <a href="https://spark.apache.org/">Apache Spark</a> has been leading the way. <a href="https://beam.apache.org/">Apache Beam</a> is built on the idea (the name itself is derived by fusing two words — <strong>B</strong>atch and Str<strong>eam) </strong>and it is in core of <a href="https://cloud.google.com/dataflow">GCP Dataflow</a> offering. Recently <a href="https://docs.aws.amazon.com/kinesisanalytics/latest/java/how-it-works.html">AWS Kinesis Analytics</a> introduced support for <a href="https://flink.apache.org/">Apache Flink</a>. This is definitely a space which will evolve and emerge pretty rapidly.</p><blockquote>Feature Consolidation At Platform level</blockquote><p>Each of the platform vendors are flexing their “We can do it all” muscles. This market trend is pushing each vendor to close major gaps in their offerings. Key example being Snowflake’s Snowpark, Databricks’ Unity catalog, AWS Lakeformation etc.</p><h3><strong>III. Novel Data Consumption Patterns</strong></h3><p>While traditional BI consumption pattern is still leading the board, various novel consumption patterns are emerging. To support such patterns, platform level modifications are emerging simultaneously.</p><blockquote>Data Assets — Data As Product</blockquote><p>One of the key trend is to identify information in the form of data assets and making them first class objects into a platform. An asset can be a BI report in traditional form, it can be an entire dataset, it can also be a synthesized notebook which runs a set of analytical workload or a completely trained ML model. The key here is how information is shared and consumed across the board.</p><blockquote>Data Sharing, Clean Rooms, Marketplaces</blockquote><p>The ability to share data securely and without creating copies is absolutely game changer. Cross-Industry data sharing and ability to enable multi-party governance and access control brings unprecedented agility. This concept is now extended to various data assets as well.</p><p><a href="https://docs.snowflake.com/en/user-guide/data-sharing-intro.html">Snowflake</a> and GCP <a href="https://cloud.google.com/solutions/financial-services/datashare">BigQuery</a> leading the way, but <a href="https://www.databricks.com/product/delta-sharing">Databricks</a> and other vendors are catching up quickly. Few of the key trend around Data Sharing is emerging such as</p><ul><li><strong>Marketplace</strong>: It is a one way sharing of data where one organisation is the publisher and others are consumers.</li><li><strong>Exchange</strong>: Data exchange mechanism enables multiple publishers and subscribers to use the same platform to form a data ecosystem.</li><li><strong>Clean Rooms</strong>: Clean room technology evolved with the need of multiple parties to come to a common platform to work on a specific problem, without revealing their inner workings. Needless to mention this trend helped quick data exchange during pandemic while the crisis forced organisations to fine tune the technology from performance and security stand point.</li></ul><p>It is expected data asset sharing will continue and evolve as a trend. It is expected many early adopters who are on preview stage will soon become generally available, while a host of new features being introduced</p><h3>IV. Interaction with AI/ML Assets</h3><p>The industry has spend last 5 years to make model development commonplace. Strong focus and initiatives were in general successful. Writing and developing a custom ML model for a specific use case are the new normal. Now the conversation in moving from science to engineering aspects — efficient, reliability and security of the model as the industry is focussing on <a href="https://www.slalombuild.com/en-gb/blueprint-articles/products-are-static-intelligent-products-are-dynamic-which-do-you-think-your-customers-are-choosing">intelligent products</a>.</p><p>There are couple of trends emerging around this area:</p><blockquote>ML Operations (MLOps)</blockquote><p>These two terms are so similar that it is often misinterpreted. MLOps is around how model lifecycle is managed — all the way from model development to model deployment and then measuring model performance while it is running in production. There are few interesting development in this area:</p><ul><li><a href="https://mlflow.org/news/2022/11/14/2.0.1-release/index.html">Release of mlflow 2.0</a></li><li>Consolidation of <a href="https://cloud.google.com/vertex-ai/docs/pipelines/build-pipeline">Vertex AI</a> workbench over <a href="https://www.kubeflow.org/">Kubeflow</a></li></ul><blockquote>ML Engineering</blockquote><p>There is a renewed focus on considering ML applications as engineering artefacts and treat them as such. This includes version controlling, quality management and CI/CD. <a href="https://www.featurestoresummit.com/">Feature stores</a> are now showing up in enterprise architecture, especially where big organisations started to rationalise feature engineering across many bespoke models.</p><p>Application engineering space is carving out a space for ML Engineers to integrate with low-effort AI/ML solutions such as AutoML and Cognitive services supported by prominent cloud platforms.</p><h3>V. SQL Going Serverless — dbt will help</h3><p>This is not a new trend, rather it is affirmation of continuing focus on SQL. In fact, all prominent data vendors are investing effort and features to make SQL as performant as possible. One prominent trend emerging in this space is around <strong>Serverless offerings</strong>. <a href="https://aws.amazon.com/redshift/redshift-serverless/">Redshift</a> and <a href="https://www.databricks.com/blog/2021/08/30/announcing-databricks-serverless-sql.html">Databricks</a> both came out with serverless SQL endpoints, while Azure already had similar offerings around <a href="https://learn.microsoft.com/en-us/azure/synapse-analytics/quickstart-serverless-sql-pool">SQL pools</a> under Synapse ecosystem.</p><p>This year, not many data platform discussion were concluded without mentioning <a href="https://www.getdbt.com/">dbt</a>. It is an excellently conceived neat little tool which has great impact on how teams manage to write consistent SQL code. It has been introduced as tools for Analytics Engineers, but it is used in data processing space quite often. It is expected that both the trends will continue to evolve next year.</p><h3>Conclusion</h3><p>Everything putting together, here is the outlook:</p><blockquote><strong>data engineering technology stack is mature and the focus is shifting towards reliability. While data science space is evolving rapidly, both technology and mind share investments on building engineering foundations around it are on the rise.</strong></blockquote><p><em>** This articles focuses on macro trends which have potential impact on the overall analytical data platform. There are trends within each specific area which have finer and subtler impact. Of course, these are personal views and observations.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=17cc19e63da6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Artificial General Intelligence — Safety Problems — Part III]]></title>
            <link>https://guha-ayan.medium.com/artificial-general-intelligence-safety-problems-part-iii-cca8e40ce01c?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/cca8e40ce01c</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Thu, 23 Jun 2022 13:41:38 GMT</pubDate>
            <atom:updated>2022-06-23T13:41:38.678Z</atom:updated>
            <content:encoded><![CDATA[<h3>Artificial General Intelligence — Safety Problems — Part III</h3><p>Today’s artificial intelligence systems are doing extraordinary things in every place you look. They are transforming the way technologies are built not only for today, but also for future. With such profound impact in our daily life, it is only fair to ask: How does these systems work (<a href="https://guha-ayan.medium.com/artificial-general-intelligence-safety-problems-part-1-62beb76ac9d4">Part I</a>), How safe are these systems are and what are the typical challenges (<a href="https://guha-ayan.medium.com/artificial-general-intelligence-safety-problems-part-ii-cba2e2dfa1bf">Part II</a>) and what are some of proposed solutions (This Article).</p><blockquote><strong><em>disclaimer</em></strong><em>: I am not an expert in this area. I find the concepts extremely interesting. I wanted to collect my notes and share my understanding. I will be grateful for any feedback and/or suggestion.</em></blockquote><h3>Repeating themes</h3><blockquote>We want AGI agents to explore strategies and policies such that they can perform better than human in general. But we also want the consequences of their actions are not harmful. Resolving this adversarial relationship between reward and risk, like many other things in life, is the key for AI safety.</blockquote><p>In order to achieve a reasonable answer to the problem, there are number of active researches are ongoing, to find clever, better, faster and feasible solutions around some repeating themes as follows.</p><ul><li>Better understanding of agent’s learning path and improve it</li><li>Better ways to find explore vs exploit models</li><li>How to scale and automate oversight and safety controls</li><li>Decomposing systems into smaller sub-systems which can be then designed to be stable and safe</li></ul><h3><strong>Interpretability</strong></h3><p>Interpretability or Explainability is now deemed as critical for any AI system to be deployed in real world. It helps few critical AI safety issues:</p><ul><li><strong>Understanding: </strong>Sometimes it is more important to understand why a machine learning system makes a decision than what the decision actually is. This understanding of the inner workings may give indications about areas where the agent may not be performing well and the take actions to bolsters any such gaps.</li><li><strong>Debugging: </strong>In the grand scheme of things agents can be seen as complex software programs. Software program debugging and testing is a generally understood field. By bringing best practices of debugging and testing techniques can help to improve overall performance.</li><li><strong>Accountability</strong>: Interpretable systems can be easier to reason with when it comes down to accountability of any decision taken by the agent. In case of catastrophic outcomes it may not be very important aspect though.</li></ul><p>In general, an interpretable system helps to at least quantify the associated risk profile. This information can then be used effectively to measure risk in real world and can be a key element while deciding if an agent is “ready” for deployment.</p><blockquote>Interpretability does not make an AGI safe by itself, but it is a key ingredient to be a safe system</blockquote><h3>Adversarial Reward Function — <strong>Reward Modelling</strong></h3><p>If we go back to basic reinforcement learning model we will find the agent and the reward function is in an adversarial relationship. While the agent tries to find policies to increase the reward while the reward function tries to penalise any unintended policies.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*3aoNvS3cN_LaDlRq2s1Cxg.jpeg" /></figure><p>One implementation of such strategy is to define a reward model. In this case a neural network model tries to predict how a human rewards agent’s action. The reward model interacts with a human observer for a limited time in a periodic manner. This way the network learns human preferences in context of the reward function, and it consults the human only when it is really uncertain: and that can happen when an agent tries some new policy. This is a novel way to incorporate human preferences in rewarding mechanism.</p><h3>Safe Exploration</h3><p>As we discussed in Part II, exploring the configuration space is an essential goal for an agent to learn interesting and efficient policies. On the other side, exploration is, by definition, can be dangerous. So how can an agent explore safely? There are few suggestions :</p><ul><li><strong>Simulation</strong>: This is probably the most known and most sensible way of exploration, though simulating the real world correctly is not a trivial task itself.</li><li><strong>Demonstration</strong>: One option to limit the need of exploration is to use human experts as a point of reference and learn from them. This can help the agent to learn a generally safe baseline policy. Obvious downside of this option is that the agent can not be better than an human expert, but in some high risk scenarios that may be an acceptable outcome</li><li><strong>Oversight</strong>: While exploration providing oversight can be a safe way to train. It is similar to having a second pair of break in a car which is used to teach driving.</li></ul><h3>Scalable Supervision</h3><p>Scaling human oversight is expensive and slow. If a human is appointed to supervise an agent in every step it takes, it is probably cheaper to hire the human to do the work!</p><p>One possible way is to ask the human to check the agent once in a while randomly. This way some of the episodes of agent’s work will be “labelled” and some are “unlabelled”. If the agent can learn quickly enough from the sparse set of labelled episode then this mechanism of <strong>semi-supervised reinforcement</strong> learning can definitely be a safe alternative.</p><p>Another possible approach is to use ideas from traditional system integration testing. In this <strong>hierarchical reinforcement learning model</strong>, sub-agents are grouped under high level agents. Higher level agents can train on a sparse set of reward signal, while creating a more dense reward signals for the sub-agents.</p><blockquote>Hierarchical RL seems a particularly promising approach to oversight, especially given the potential promise of combining ideas from hierarchical RL with neural network function approximators</blockquote><h3><strong>Iterative Distillation and Amplification</strong></h3><p>Can you recall how were you taught algebra (or any other problems)? First we are assigned some simple problems and teacher shows us how to do them. Progressively we get better in doing simple problems and at the same time the teacher introduces more complex problems and patterns. This is a simple analogy of iterative distillation and amplification (IDA).</p><p>In this model an agent distills down learning from a computationally expensive model. Distilled knowledge then amplifies agents’s capability to take better decisions with less cost. The agent can continue the process iteratively by trading off the cost of running the expensive model and frequency of running it. Like adversarial modelling, the agent can start focus on the areas it is doing badly and thus speeds up the learning process.</p><h3><strong>Response to Out of Distribution Scenarios</strong></h3><p>One of the core assumption of machine learning systems is that the training and deployment environments will be pretty close in context of the things which matters for the model. In practice, and especially in context of general intelligence settings, seemingly similar environments can have diverse properties. For example, a self driving system which is trained solely on highways can exhibit some unsafe behaviours if deployed on busy city traffic, in spite of both environments can be considered as “road”.</p><p>There are few possibility to approach this problems, one of which is extend the training process using ensemble techniques and increase robustness.</p><ul><li><strong>Single Model, Multiple variations:</strong> Define and train a set of models and calibrate the outcome over a weighted population of samples.</li><li><strong>Single Model, Multiple distributions: </strong>A model can be trained over various distributions. If the model can perform well across various distributions then there is a high chance that it will perform well enough in a novel distribution</li></ul><p>Another approach is to expect outliers and design safety overrides into the system to address situations when model is really uncertain. This can be done by falling back to human oversight, systematic overrides or a combination.</p><h3><strong>Quantilisation</strong></h3><p>If we look at the basic reinforcement learning set up then we see one of biggest safety issue is agent’s obsession on maximising the reward function. There is absolutely no incentive for an agent not to do so, even for small fraction increase.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*62cjaFvDW_P6suQFI1L39g.gif" /></figure><p>By we as human agents, we do not do that. One of the reason is we have a different value system. It is not comparable with the value system agent has. But we want agent to honour at least part of our value system, such as “do not kill” or “do not steal” etc. In other words, we want agents to behave more like a human when taking decisions.</p><p>But at the same time, we do not want to give up immense potential an agent can uncover by exploring alternative strategies human would never consider or not able to pursue due to physical constraints.</p><p>Quantilisers are devised to combine best of both worlds. It can be thought of a set of controls which can define how an agent will behave. At one end of the spectrum is most risk aversive human model and on the other end agent is free to maximise reward function in any way it can. Given the risk profile of the given task such mechanisms can provide a safety control.</p><h3>Conclusion</h3><p>How do we typically build large complex structures? We design it right and meticulously test each components. So why can’t we do the same for Artificial General Intelligence? Unfortunately, it is a trade off between our ambition and our risk appetite. We humans have developed a value system over many years of biological and social evolution. It is impossible to expect the same will be somehow inherited by new breed of systems and agents.</p><p>If we assume AGI will be a reality and AGI systems can be as powerful as we want them to be, then being safe must be a non-negotiable trait. Because it is such a distant possibility and there are so many roadblocks we need to cross before we have a realistic chance, AI safety is often ignored as a priority. Hope this series will help us all to understand why AI safety is important and how we should mature it over the time so we will be ready when AGIs are.</p><h3>References</h3><p><a href="https://www.youtube.com/c/RobertMilesAI">https://www.youtube.com/c/RobertMilesAI</a></p><p><a href="https://ai-alignment.com/iterated-distillation-and-amplification-157debfd1616">Iterated Distillation and Amplification</a></p><iframe src="https://drive.google.com/viewerng/viewer?url=https%3A//intelligence.org/files/QuantilizersSaferAlternative.pdf&amp;embedded=true" width="600" height="780" frameborder="0" scrolling="no"><a href="https://medium.com/media/672419403a58fd48b9b37fc7f383cc4d/href">https://medium.com/media/672419403a58fd48b9b37fc7f383cc4d/href</a></iframe><p><a href="https://forum.effectivealtruism.org/posts/2ZeHrfJr9uHHJ2e8J/my-understanding-of-paul-christiano-s-iterated-amplification">My Understanding of Paul Christiano&#39;s Iterated Amplification AI Safety Research Agenda - EA Forum</a></p><p><a href="https://ai-alignment.com/semi-supervised-reinforcement-learning-cf7d5375197f">https://ai-alignment.com/semi-supervised-reinforcement-learning-cf7d5375197f</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cca8e40ce01c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Artificial General Intelligence — Safety Problems — Part II]]></title>
            <link>https://guha-ayan.medium.com/artificial-general-intelligence-safety-problems-part-ii-cba2e2dfa1bf?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/cba2e2dfa1bf</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Sun, 19 Jun 2022 14:01:30 GMT</pubDate>
            <atom:updated>2022-06-23T13:47:31.643Z</atom:updated>
            <content:encoded><![CDATA[<h3>Artificial General Intelligence — Safety Problems — Part II</h3><p>Today’s artificial intelligence systems are doing extraordinary things in every place you look. They are transforming the way technologies are built not only for today, but also for future. With such profound impact in our daily life, it is only fair to ask: How does these systems work (<a href="https://medium.com/p/62beb76ac9d4/">Part I</a>), How safe are these systems are and what are the typical challenges (This article) and what are some of proposed solutions (<a href="https://medium.com/p/cca8e40ce01c/edit">Part III</a>).</p><blockquote><strong><em>disclaimer</em></strong><em>: I am not an expert in this area. I find the concepts extremely interesting. I wanted to collect my notes and share my understanding. I will be grateful for any feedback and/or suggestion.</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/612/1*aniAX_2S2Jc4i_h-cv00bw.jpeg" /></figure><h3><strong>Misalignment of Agent and Human Goals</strong></h3><p>As we saw in previous post, reinforcement learning agents try to fulfil their goals by maximising the reward function. These goals are ideally defined by humans and perfectly understood by the agent. But what happens if that is not the case?</p><p>Expressing goals in terms of physical world is hard and it is even harder to making a computer to understand. In practice, we have to use certain proxies to emulate what we actually want. Curing cancer is a fairly lofty goal, and one way to evaluate it is to measure rate of reduction in number of cancer patients. It seems to be a reasonable approximation, right? But how about killing anyone who has cancer? That also reduce that exact metric. But we all know that’s not what we want. How can we explain it to AGI?</p><p>“<a href="https://docs.google.com/spreadsheets/d/e/2PACX-1vRPiprOaC3HsCf5Tuum8bRfzYUiKLRqJmbOoC-32JorNdfyTiRRsR7Ea5eWtvsWzuxo8bjOxCG84dAg/pubhtml"><strong>Specification gaming examples in AI</strong></a><strong>” </strong>(in ref) is a list of many game playing and robotics research experiments where AGI agent manifests such behaviours.</p><p>But what is the true source of the misalignment? One way to think about our goals are often expressed in a way that it is assumed to be within the framework of human value system. This value system is encoded in our DNA in the form of evolutionary mutations, as well as it is ingrained in our social constructs.</p><p>An AGI agent does not have these benefits. For an agent, the only thing really matters in the world is achieving goals by maximising reward functions. And unless it is encoded in the reward function, it does not need to pay attention to anything else because there is no incentive to do so.</p><blockquote><strong>The agent will trade off a large amount of things it does not care about to increase even a small amount of gains in things it cares about.</strong></blockquote><blockquote>One important point to note here: It is not that the agent is not aware of human values. As a super-intelligent agent it can read books and attend classes to get a clear understanding of the complete environment. The problem is it just does not care about it unless being told explicitly.</blockquote><p><strong>Dangerous By Default</strong></p><p>As such, we can definitely conclude designing AGI systems in a safe manner is pretty difficult. In other words, the AGI systems are more likely to exhibit a dangerous behaviour than that of a safe one. And it may or may not exhibit the problems until they start interacting in real world.</p><p>There are quite few many ways the misalignment in goals can manifest depending on various factors. Let us discuss few of them in detail below.</p><h3><strong>Convergent Instrumental Goals</strong></h3><p>In last post we discussed what are instrumental goals: They are goals an agent would like to learn in order to better fulfil its terminal goals.</p><p>What is very interesting is many of such instrumental goals are convergent as they are common across a large number of terminal goals. In other words, learning some of these terminal goals is useful for an agent which has a diverse set of terminal goals.</p><ul><li><strong>Preservation</strong></li></ul><p>We discussed this briefly before. Here the core problem is AGI agent can develop or learn ways to preserve both its goals and destruction.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*1QX-Z3qGsZMqRiGtSp76uw.gif" /></figure><p>Let us assume an agent is in training phase and it started exhibiting indications that its goals are not aligned with the intended goal. Now the safest course of action is to stop training, modify the goal and retrain. However, how will the agent perceive these actions?</p><p>First thing is to note that we are talking about an intelligent agent, which already knows enough about the environment to understand the intention of the proposed course of action.</p><p>Secondly it can also able to deduce that modifying the current goal will definitely prevent it to achieve its current goal. If we assume the only thing it cares is to achieve its current goal, there is a high incentive to prevent this modification. This is called <strong>Goal Preservation</strong>.</p><p>Additionally, just the idea of being stopped or being destroyed is not very complex to deduce for an intelligent agent. And like any other intelligent artefact it is natural for the agent to take precautions against such actions. Thus learning <strong>Self Preservation</strong> as an instrumental goal should be high in agent’s list of priority.</p><ul><li><strong>Resource acquisition</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*S5qTIpFsV9BDI2MMcTJaqQ.gif" /></figure><p>This should be very easy to understand as as an intelligent agents humans are prone to this kind of behaviour. Achieving any goal requires resources. In general resources can be expressed in few translatable forms (such as money) so that a large number of terminal goals can be achieved by acquiring few forms of resources in large quantity.</p><p>Unfortunately as we have seen over and over in human history, driving this greedy behaviour further down the road is almost always a recipe for disaster. Now think of many intelligent agents within an environment trying to acquire same form of resource as most as they can — without any concept of morality and values!!</p><ul><li><strong>Self Improvement</strong></li></ul><p>Self improvement is a trait we all like to have. In some sense it is key to continue learning new policies and strategies. The notion of improvement does not conflict with preservation, in fact it helps other instrumental goals.</p><p>At first instance it seems a benign goal, so why it is a safety issue? One of the reason is that AGI agents do not have a sense of “Good Enough”. Without any specific constraint in place, intelligent agents has no reason to stop improving itself, even when there is nothing more to be gained.</p><h3><strong>Reward Hacking</strong></h3><p>Reward hacking is a broad paradigm of AI safety issues where the agent can have some way to interact with the reward function itself.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*hBalpuYUpMuhsgW4uZbY4g.jpeg" /></figure><p>The agent can then either take advantage to “game” the reward system or it can exploit some weakness of the reward system itself. There are few possible scenarios below:</p><ul><li><strong>Partially Observed Goals</strong></li></ul><p>If there is no direct way to measure the desired goal then the agent can be presented with an imperfect reward function. This can be then maximised with some strategies without actually achieving the goal.</p><p>We have looked into the cure cancer scenario. Another classic example is the cleaning robot thought experiment. It is hard to define cleanliness. Cleanliness can be thought as just the opposite of visible mess. In other words, the robot can be rewarded more if it sees less mess around it. While it seems a fairly reasonable approach, an intelligent robot can just close its eyes (or put a bucket on top of its head) and pretend cleanliness.</p><ul><li><strong>Goodhart’s Law</strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NMKBcvriZFShx8COTneP_A.jpeg" /></figure><blockquote>When a metric becomes a target then it ceases to be a good metric</blockquote><p>A good reward function should have a strong correlation with the goal being accomplished. However if optimising reward function itself becomes a goal then the correlation loses its meaning.</p><p>Let us consider a ticketing system is monitored by an AGI agent and the performance depends on number of tickets it can close in a month. An intelligent agent may close a number of tickets without resolution on the last day of the month and reopen them soon after. Or it can just create new tickets under false pretence and just close them to increase counts.</p><ul><li><strong>Defect Exploitation</strong></li></ul><p>Artificial general intelligence systems are complex by nature. It depends on smooth interactions between various complex systems such as the environment and reward functions, and often has to deal with complex abstract concepts. This increases the overall risk profile of defects being present in the system, especially at least part of the system can be non-deterministic and hard to test.</p><p>Defects can occur within the specification of the goal or within the reward function itself. These vulnerabilities can be discovered by agents by systematic or accidental attacks and then can be exploited.</p><p>Agents are also part of the environment itself and able to access various parts of it. There is high motivation for an intelligent agent to attack any vulnerability which can cause high reward. This is called <strong>“wire heading”</strong>. As an example: to win a game of <a href="https://www.nytimes.com/games/wordle/index.html">Wordle</a>, an agent may try to hack into the server so that it can decide which word to provide (and then just play it to win).</p><h3><strong>Unsafe Exploration</strong></h3><p>As we saw in the reinforcement learning definition, an agent can take actions to interact with the environment. At that time we have not made any assumption about what actions agents can take. In principle, agents can take any action randomly or following some patterns as it sees fit. In other words, the agent is free to <strong>“explore”</strong> the action space based on its policies<strong>.</strong></p><p>The question is: how to explore the action space safely. It is bound to happen that some actions can lead to some bad outcome. It is also possible some actions can lead to immediate catastrophes. In fact, the agent faces a trade off between exploring new actions (and lead to catastrophes) and exploiting current known good actions (and never able to discover better actions possible).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/631/1*cJNZuX8BLNOntCGAn9vV-w.jpeg" /></figure><p>An agent does not have any heuristic about the action itself, ie it can not know the result of an action without actually choosing it. Because if it knows what the action will result into then there is nothing to learn by taking the action.</p><p>If the agent decides to explore only a set of known and safe action space then it will miss out a large unexplored space. This will be a blocker for its goal of self improvement. Of course it can avoid known unsafe spaces, but that will not be enough to guarantee safety.</p><h3><strong>Distributional Shift of Environment</strong></h3><p>Distributional shift of data is a curse in machine learning in general. Any ML 101 course will teach you the importance of the assumptions made around the distribution. In fact machine learning can be simply defined as various ways to approximate an unknown distribution of data. It is the hope that any unknown data element for which predictions are to be made will also be drawn from the same distribution.</p><p>The same assumptions are true for reinforcement learning settings. If the training is done on a set of environments exhibiting a certain pattern then it is unfair to expect the model to perform in an environment which is very different. However, this is a very practical possibility in real world. Real world is so diverse and complex that it is almost impossible to create even a complete set of representative example environments.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u60uXPTokGa-AP5pac4obQ.png" /></figure><p>Example: An agent trained in a maze to get a reward. During testing, the reward is always kept at a fixed location (lets say top right corner of the maze) and agent could learn to get the reward. Now the agent is deployed to an environment where the reward is placed at random location. The agent completely ignores the reward, and goes to the top right corner. The agent is still intelligent, because it can solve the maze. But it has learned a wrong objective. It now knows “how to go to top right corner” and NOT “how to get a reward.</p><p>Similarly, an agent trained on an environment where the reward is a yellow gem. However in testing, the reward was a red gem, and there was a distractor: a yellow star. The agent learned to go to “Yellow” things, and thus in testing it preferred Yellow star over the red gem.</p><h3>Ethical AI vs AI Safety</h3><p>Building ethical and responsible AI systems is another challenge — but with slightly different objective. Any reasonably large human data set is bound to be tainted by our history of preferential prejudices and power equations. An AI system trained on such data is bound to pick up such traits which are unethical in today’s moral standards. It is almost like children learning both good and bad things from the society. Creating ethical AI or at least safe guarding AI systems against unethical behaviours are a pressing challenge right now.</p><p>On the other hand, AI safety deals into more foundational safety concerns. In fact, an ethical AI systems can turn out to be unsafe and vice versa. There can be overlaps too: such as intelligent AGI systems can “deceive” to behave as safe and only exposes its unsafe nature once deployed. The deception strategy is both unethical and unsafe but can be a lucrative strategy to preserve itself against adversarial observers.</p><p>There are few solutions for AI safety can also be important for ethical AI such as interpretability.</p><h3>Conclusion</h3><p>So, is there some solutions around these problems? A lot of research is going on to address these problems. Tools we have now are very strong understanding of deep networks which can approximate any behaviours. We also have unprecedented computing power along with tools and technology around scale up and scale out. We have our accumulated knowledge about how we learn and how we improve ourselves. There are some interesting ways researchers are trying to address AI safety problems. We will take a look at some of them in <a href="https://medium.com/p/cca8e40ce01c/edit">Part III</a>.</p><h3><strong>References</strong></h3><p><a href="https://docs.google.com/spreadsheets/d/e/2PACX-1vRPiprOaC3HsCf5Tuum8bRfzYUiKLRqJmbOoC-32JorNdfyTiRRsR7Ea5eWtvsWzuxo8bjOxCG84dAg/pubhtml">Specification gaming examples in AI - master list - Google Drive</a></p><iframe src="https://drive.google.com/viewerng/viewer?url=https%3A//arxiv.org/pdf/2105.14111.pdf&amp;embedded=true" width="600" height="780" frameborder="0" scrolling="no"><a href="https://medium.com/media/e2a4f558e5f60a0f408b93619011ebe4/href">https://medium.com/media/e2a4f558e5f60a0f408b93619011ebe4/href</a></iframe><p><a href="https://arxiv.org/abs/1606.06565">Concrete Problems in AI Safety</a></p><iframe src="https://drive.google.com/viewerng/viewer?url=https%3A//arxiv.org/pdf/1901.10995.pdf&amp;embedded=true" width="600" height="780" frameborder="0" scrolling="no"><a href="https://medium.com/media/d67d065df4797fdf4f68603ebd2e4e9a/href">https://medium.com/media/d67d065df4797fdf4f68603ebd2e4e9a/href</a></iframe><p><a href="https://cset.georgetown.edu/wp-content/uploads/CSET-Key-Concepts-in-AI-Safety-An-Overview.pdf">https://cset.georgetown.edu/wp-content/uploads/CSET-Key-Concepts-in-AI-Safety-An-Overview.pdf</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cba2e2dfa1bf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Artificial General Intelligence — Safety Problems — Part 1]]></title>
            <link>https://guha-ayan.medium.com/artificial-general-intelligence-safety-problems-part-1-62beb76ac9d4?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/62beb76ac9d4</guid>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Sun, 19 Jun 2022 00:54:26 GMT</pubDate>
            <atom:updated>2022-06-23T13:47:07.927Z</atom:updated>
            <content:encoded><![CDATA[<h3>Artificial General Intelligence — Safety Problems — Part 1</h3><p>Today’s artificial intelligence systems are doing extraordinary things in every where you look. They are transforming the way technologies are built not only for today, but also for the future. With such profound impact in our daily life, it is only fair to ask: How do these systems work (This article), How safe are these systems are, what are the typical challenges (<a href="https://guha-ayan.medium.com/artificial-general-intelligence-safety-problems-part-ii-cba2e2dfa1bf">Part II</a>) and what are some of proposed solutions (<a href="https://medium.com/p/cca8e40ce01c/edit">Part III</a>).</p><blockquote><strong>disclaimer</strong>: I am not an expert in this area. I find the concepts around AI safety extremely interesting. I wanted to collect my notes and share my understanding. I will be grateful for any feedback and/or suggestion.</blockquote><p>But first, let us define a few semantics so that we do not get distracted by the buzzwords.</p><h3><strong>Artificial General Intelligence (AGI)</strong></h3><p>Artificial Intelligence is a promise of developing technologies in clever ways so that it can perform any tasks humans can do, only much better. The improvement can be <strong><em>in speed, in accuracy and in generality</em></strong>. While today’s AI systems can do very well in the first two parameters, humans win hands down in the parameter of generality.</p><p>For example, an intelligent AI chess player can outperform human chess players, and an intelligent computer vision software may accurately recognise faces with high accuracy. But the computer vision software can not play chess, and the chess AI has no notion about what a face it. In general, any average human can both play chess <strong><em>and</em></strong> recognise faces. Generality is a key trait of our cognitive prowess which today’s AI systems lack.</p><p>It is good to be aware of this major difference. Artificial <strong>General</strong> Intelligence is a major area of active research. In this article we will discuss one of the promising way: Reinforcement Learning. Let us find out what it is.</p><p><strong>Reinforcement Learning</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/299/1*zeOk0X2eKHu2PZqvxjFuiw.jpeg" /></figure><blockquote>Have you ever heard about <em>“</em><strong><em>Failures are pillars of Success</em></strong><em>”</em> or “<strong>Your actions have consequences</strong>”?</blockquote><p>Well, then you know what reinforcement learning is. But let us also understand the definition.</p><blockquote><strong>Reinforcement learning</strong> (<strong>RL</strong>) is an area of machine learning concerned with how intelligent agents ought to take actions in an environment in order to maximise the notion of cumulative reward. Reinforcement learning is one of three basic machine learning paradigms, alongside supervised learning and unsupervised learning.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/524/1*kn7qa6UULFIPTYzaSnWKUg.jpeg" /></figure><p>In Reinforcement learning set up, <strong>an agent</strong> is interacting with <strong>an environment</strong>. The agent can take <strong>actions</strong> in the environment, and thus can modify the <strong>state</strong> of the environment. By doing so, the environment returns some <strong>observation</strong> and a <strong>reward</strong> to the agent. Based on the rewards and observations over a period of time the agent can come up with a <strong>policy</strong> or behaviour which lets it achieve its <strong>goal. </strong>Over time the agent can learn or exhibit behaviours which can maximise reward by “learning” from its actions, observations and reward signals.</p><p>From agent’s perspective, there are two possible types of the environment. Either it knows the entire environment (ex: current configuration of the chess board) or it knows the environment partially (ex: game playing). It may be counter-intuitive to understand that knowing the environment may not be desirable always. We will discuss a bit about exploration strategies later.</p><p>It seems complicated , so let’s understand how a child learns the age old lesson: Do not play with fire.</p><ul><li>In this model, child is the agent and his house with a burning candle is the environment.</li><li>Being a child, he/she is entitled to take random actions and one of which can be touching the flame.</li><li>By touching the flame he/she feels pain and that is a negative reward.</li><li>Now he/she knows not to touch the flame ever again or take precautions before he/she does so. The child updates his/her behaviour and makes a policy to not touch the flame again.</li><li>As a result of this, he/she makes observations about the fire, candle and pain and <strong><em>learns</em></strong> about them.</li></ul><p>In the above model, we have not touched upon goals. So what are goals and how many types of goals are there?</p><h3><strong>Terminal Goals</strong></h3><p>Goals are fundamentally something the agent wants to achieve. Terminal goals are a true reflection of the final objective. For example: in the example above, the terminal goal is not to have pain (or not to harm the body). Terminal goals are goal primitives and can not be further driven by any other goals.</p><h3><strong>Instrumental or Enabling Goals</strong></h3><p>These are the goals which are stepping stones of achieving the terminal goal. There can be many instrumental goals or there can be only few depending on the complexity of the terminal goals.</p><p>In general instrumental goals are</p><ul><li>more concrete goals</li><li>easier to learn</li><li>often common across terminal goals.</li></ul><p>For example, <strong>learning to be alive</strong> in a complex environments is a key skill an agent would like to learn, regardless of its terminal goal. Because if the agent dies then it will not be able to achieve the terminal goal.</p><p>Reinforcement learning has not been in mainstream AI/ML until recently. It has been explored heavily in game development and robotics.</p><h3><strong>Then, what is Intelligence?</strong></h3><p>With the concepts discussed above, intelligence can now be defined as</p><ul><li>an intelligent agent should be able to understand the observations and rewards well enough so it can model how the environment is</li><li>It has ways to reason about the steps it has taken which leads to good outcomes (so it can take more of such steps) and bad outcomes (so that it can avoid such steps). Typically this is a deep neural network learning component</li><li>It keeps improving the understanding about environment and making better decisions to achieve its goals.</li></ul><p>The promise of artificial general intelligence is to find agents who can intelligently pursue a diverse set of terminal goals in complex and real world around us.</p><blockquote>It sounds really complex, right? But we have precedence. Evolution theory can be modelled as a reinforcement learning system which acted in real world to optimise the goal of increasing the probability of maximising offsprings by making many tweaks within and across species. It is not an efficient method but it only proves, given enough time and resource, it is possible to come up with generally intelligent systems.</blockquote><p>Now that we have defined what is intelligence for an agent, what are the things should we expect from our superhuman artificial general intelligence systems</p><ul><li>We expect AGI agents to be able to outperform humans in general cognitive tasks</li><li>Not only can they outperform humans in traditional sense of speed and accuracy, but also by identifying and exploring numerous strategies which are never explored by humans.</li><li>They will learn NOT to make mistakes, ie take decisions which will lead to negative rewards (Or at least won’t make mistakes we do)</li></ul><p>All good so far? Nice!! Let us explore some of the tools and frameworks available for interacting with Reinforcement learning and Environment framework to train AI agents.</p><h3><strong>Open AI Gym Framework</strong></h3><p>Open AI <a href="https://gym.openai.com/">Gym</a> is one of the best places to start gathering some intuition about reinforcement learning.</p><blockquote>Gym is a toolkit for developing and comparing reinforcement learning algorithms. It supports teaching agents everything from <a href="https://gym.openai.com/envs/Humanoid-v1">walking</a> to playing games like <a href="https://gym.openai.com/envs/Pong-ram-v0">Pong</a> or <a href="https://gym.openai.com/envs/VideoPinball-ram-v0">Pinball</a>.</blockquote><p>Gym implements many simple and complex <a href="https://gym.openai.com/docs/#environments">environments</a> to interact and train. Gym also provides a standardised way to create, measure and publish benchmarks as everyone is using same sets of environments.</p><h3><strong>Python Libraries</strong></h3><ul><li><a href="https://github.com/taylormcnally/keras-rl2"><strong>keras-rl2</strong></a><strong> </strong>is an implementation of various reinforcement algorithms with integration of keras, pytorch and tensorflow. It also supports open-ai gym out of the box.</li><li><a href="https://github.com/openai/baselines"><strong>open-ai-baseline</strong></a> and its fork <a href="https://github.com/hill-a/stable-baselines"><strong>stable-baseline</strong></a><strong> </strong>are high quality RL algorithm implementations with particularly integration with Open AI Gym.</li></ul><p>There are few other notable python libraries, you can find them in the post mentioned in references. However these libraries are still fairly nascent and evolving.</p><h3>AGI Safety</h3><p>Reinforcement learning and in general Artificial General Intelligence is a subject of active research. There is no real agreement around any timeframe when we can see something substantial and useful, but surveys say it may take somewhere between 40–100 years to happen.</p><p>AGI safety is a one area of active research to identify problems of approaches and consequences of such a system, should it exists, can pose in a real world. In the next part of this series ,we will discuss few interesting safety problems. In the final part we will discuss how to address and measure some of the proposed solutions.</p><h3><strong>References</strong></h3><p><a href="https://towardsdatascience.com/5-frameworks-for-reinforcement-learning-on-python-1447fede2f18">https://towardsdatascience.com/5-frameworks-for-reinforcement-learning-on-python-1447fede2f18</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=62beb76ac9d4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Skills of a Data Engineer]]></title>
            <link>https://medium.com/slalom-australia/skills-of-a-data-engineer-241c4f615990?source=rss-3cb0f9fedf21------2</link>
            <guid isPermaLink="false">https://medium.com/p/241c4f615990</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[slalom-build]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[slalom]]></category>
            <dc:creator><![CDATA[Guha Ayan]]></dc:creator>
            <pubDate>Mon, 13 Sep 2021 22:52:45 GMT</pubDate>
            <atom:updated>2025-11-07T17:40:46.964Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tu85XOJ5Wps7uTvpgBc7zA.png" /></figure><p>Once upon a time, there used to be two species: software engineers and data management folks. They coexisted successfully, occasionally spoke to each other over strict protocols. Both areas had their own rituals, practices and culture. Data management was messy, monolithic and bulky, while software engineering was matured and maintainable. However, success of software is always dependent on how data is managed, which led to data folks starting to adopt software engineering practices. This is how a new function came along: data engineers — data engineers always have data at heart, but their craft encompasses software engineering.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TciciCpvxVbruXioK--Rlg.jpeg" /><figcaption>Courtesy: <a href="https://medium.com/u/8a1062cf5d12">Andrew Lamont</a>— Slalom _build</figcaption></figure><p>This blog is my humble opinion about what skills a good data engineer should have.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/1*iHco0fSnvjOeeq67sf5WWQ.png" /></figure><p>Let’s explore each sections.</p><h3><strong>What you cannot live without</strong></h3><h4><strong>SQL</strong></h4><p>SQL is undoubtably the most widely known and probably one of the most important skillset to have when it comes to data. SQL is used for data manipulation, data definition and data analysis, it is also used for moving data from one place to another. It is a very simple declarative language with a handful of important reserved tokens and functionalities to remember. It is taught in schools, but you can always learn it from the infinite amount of resources available across the internet.</p><p>SQL remains a prominent skill in modern data architecture. Most of common cloud MPP systems and modern distributed computing solutions have SQL interfaces to interact with them. In the last few years, a significant amount of work has gone into improving SQL experience in larger data using better and smarter query optimisers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/510/1*aKq8b-kC0i9j2D1N9_n9lg.jpeg" /></figure><h4><strong>Python</strong></h4><p><a href="https://www.python.org/">Python</a> is by far the easiest programming language you can learn. It is intuitive, flexible and versatile. It represents the “engineering” part of a data engineer. Python helps you to deal with various types of data, interact with various types of resources and build systems. All tools and technologies covered in the following sections are either written in python or have python bindings ingrained in their design. You can take all benefits of modern software practices easily such as version control, CI/CD, and containerisation. Finally, python is widely used in data science due to its superior set of specifically designed libraries.</p><p>One word of caution though for those who are learning Python but have a past in server-side scripting. It is very easy to write python scripts, but it requires effort and patience to learn the craft, and I learned it the hard way. It’s important to not write python code as you would have written shell scripts. Learn pythonic ways.</p><h3>Know your platform</h3><h4>Apache Spark</h4><p>Let me be clear here, I am biased and for a reason. Apache Spark has matured like no other system in past five years. It is used heavily for data batch and stream processing and all major cloud vendors have managed offerings. But more interestingly, Apache Spark is used in many novel ways such as embedded edge processing. In short, Apache Spark is one of the most widely used and most versatile distributed compute system available for commercial use. With the advancement of <a href="https://delta.io/">delta.io</a>, <a href="https://www.mlflow.org/">mlflow</a> and kubernetes integration and various other aspirational improvements, Apache spark is definitely a key skill that is here to stay.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/684/1*kNBQi-vVM_J3vaqHS_HBWw.png" /></figure><h4>Cloud Experience</h4><p>I won’t spend too much time on this topic. Anyone who is remotely related to software industry in last five to seven years would have been exposed to some cloud experience.</p><p>What I will stress though is invest time to learn cloud. Develop a general understanding of cloud services, have a clear understanding of which service does what and how they fit into modern data architecture. As of today, all cloud offerings in the data space are very similar with certain subtle differences. It is very important to know the general reference architecture which can be translated to any cloud and then have deep expertise on either AWS, Azure or GCP.</p><p>My recommendation would be to learn and understand as much as possible for cloud specific data products, and learn as much as possible -without losing generality- when it comes to non-data products. As an example, it is important to learn the finest details about the NoSQL or Data Warehousing product you are using, but knowing how virtual networks work in general is good enough.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TjF6IqIroUIB5cy6.png" /><figcaption>Courtesy: <a href="https://www.reddit.com/r/AZURE/comments/mkfzww/big_data_pipeline_on_aws_microsoft_azure_and/">https://www.reddit.com/r/AZURE/comments/mkfzww/big_data_pipeline_on_aws_microsoft_azure_and/</a></figcaption></figure><h4>Data Warehousing</h4><p>First of all, data warehousing is not dead. Some pronounced it dead at the awake of Hadoop, but they were wrong. It remained one of key component in data architecture even with its seemingly high cost and bulky operational nature. As it stands today, cloud offerings such as <a href="https://aws.amazon.com/redshift/">AWS Redshift</a>, <a href="https://cloud.google.com/bigquery">GCP BigQuery</a> and <a href="https://docs.microsoft.com/en-us/azure/synapse-analytics/">Azure Synapse</a> are all robust and mature data warehousing solutions and it is helpful to learn one of them according to your cloud choice. Snowflake is definitely a cloud-born Data Warehouse and if this is what your organisation uses then it is helpful to learn it. I also mentioned it below in “Something Extra” section.</p><h3>Your Toolbox</h3><p>Now let me talk about some of the engineering practices you need to add your toolbox to build better data platforms and data products.</p><h4>Automation</h4><p>With the advent of cloud infrastructures and agile development practices, a lot of focus has shifted to automation.</p><p><strong>Infrastructure Provisioning</strong></p><p>In modern cloud architecture, it is important to decouple data and compute resources. And each compute resource has a cost defined by the time it is working. Hence it is critical to express every bit of compute required as code so that it can be turned on or off, or scaled automatically to control cost. Depending on your cloud provider and organisational mandate, be familiar with either cloud based provisioning services (such as <a href="https://aws.amazon.com/cloudformation/">AWS CloudFormation</a> or <a href="https://docs.microsoft.com/en-us/azure/azure-resource-manager/">Azure Resource Manager</a>) or be familiar with a generic tool (such as <a href="https://www.terraform.io/">Teraform</a> or <a href="https://www.ansible.com/">Ansible</a>)</p><p><strong>CI/CD</strong></p><p>While version controlling has been a known practice, it is not enough anymore. With infrastructure being stateless and auto-scaled, it is important to have the ability to deploy the code and configurations whenever a new piece of compute resource is provisioned. Automated deployment pipelines are not optional anymore, they are part of mature engineering practices. Git is by far the de-facto standard in this area. Learn git. Additionally, familiarity with any specific tool such as <a href="https://azure.microsoft.com/en-au/services/devops/">Azure DevOps</a> and <a href="https://about.gitlab.com/">Gitlab</a> is also good.</p><p><strong>Operations</strong></p><p>Most of the current cloud service providers have excellent offerings around monitoring and alerting services. They can be used across infrastructure, platform and application management. It will be always good to be familiar with such offerings so that you can integrate your solution easily.</p><h4>Quality Engineering tools</h4><p>Data projects are notoriously difficult to test traditionally. There are real and practical challenges such as functional meaning of data being modified over a period of time, prohibitive volume, and not having representative distribution of data across environment.</p><p>However, a few simple basic tools in modern data engineer’s toolbox can save time and ensure quality. Firstly, unit tests should be part of the design. Write functional and modular code and use libraries like <a href="https://nose.readthedocs.io/en/latest/">nose</a>, <a href="https://docs.pytest.org/en/6.2.x/">pytest</a> or <a href="https://docs.python.org/3/library/unittest.html">unittest</a> to build a unit test framework. Secondly take steps to automate manual functional tests to form a regression suite.</p><p>Data quality itself is a measure of platform maturity. While definitions and implementations vary a lot because data quality as a field is evolving, there are specific tool such as <a href="https://pydeequ.readthedocs.io/en/stable/README.html">pydeequ</a> are available today to run basic data quality checks. A combination of Apache spark and <a href="https://www.getdbt.com/">dbt</a> (more about dbt below) can as well be used to achieve the same.</p><h4>Agile Tool</h4><p>My simple suggestion, be familiar with Jira or Azure DevOps. These tools have a broad set of options, but you should learn Scrum and Kanban models at a minimum.</p><h3>Data Modelling</h3><p>Data modelling as a concept has been around for a very long time. In the last few years though, we have seen a steady decrease of focus in this area to favour more dynamic semi-structured and unstructured data sets. In my experience though, data modelling becomes absolutely necessary at the consumption layer. Most of today’s BI tools require a <strong>dimensional model</strong>. Many of data driven applications require <strong>NoSQL or 3NF modelling</strong>. And data scientists started to rely more on <strong>feature stores</strong>.</p><p>I mentioned various types of data models, but it is not important to know all of them at once. The key is to be open to learn them whenever you get a chance. There is no better way to learn Data Modelling that studying well designed models themselves.</p><h3>Something Extra</h3><p>This section talks about a few skills that are not mandatory to have at the beginning, but they will help as you mature.</p><h4>Tool Specialisation</h4><p>In the past few years, we have seen the rise of excellent data engineering platforms such as <a href="https://www.snowflake.com/">Snowflake</a>, <a href="https://databricks.com/">Databricks</a> — and data engineering tools such as <a href="https://fivetran.com/">Fivetran</a> and <a href="https://www.matillion.com/">Matillion</a>. All of them have their own place in data engineering design. Interestingly, they all compete and complement each other in many cases.</p><p>One open source project deserves special mention: <a href="https://www.getdbt.com/">dbt</a>. I strongly recommend to familiarise yourself with it.</p><h4>Data Visualisation</h4><p>Data visualisation is a handy skill to grow for a data engineer. This space is super crowded with many established players and many challengers. In general, I would recommend to start with either of Power BI or Tableau.</p><p>Data visualisation itself is a larger topic of interest, and there is no limit as to what can be done with libraries like <a href="https://d3js.org/">d3.js</a>, <a href="https://plotly.com/python/">Plotly</a> and <a href="https://seaborn.pydata.org/">Seaborn</a>. I am consciously not getting into more elaborate data exploration and data analysis visualisation requirements in this article as it is a topic on its own. You may want to check out <a href="https://guha-ayan.medium.com/data-animation-using-plotly-express-6ba689c33221">this</a> article if you are interested.</p><h4>Streaming Data Processing</h4><p>Stream Data Processing is a niche area of data processing and things are still evolving. Processing data as soon as it is available is an exciting idea. With organisations investing in IOT, real time stream processing will be something to look forward.</p><p>There are two parts to it: Data Streaming and Stream Processing. First one relates to how the data is moved around and second one is about how to process data.</p><p>For streaming data, there are traditional queues such as RabbitMQ, as well as cloud-backed ones like GCP Pubsub. There are also distributed purpose-built data streaming technologies such as Kafka, and cloud-backed ones like AWS Kinesis and Azure Event Hub.</p><p>Until recently the processing semantics for batch and streams were treated differently, and architecture patterns like Lambda and Kappa were used to design systems. Now a days both paradigms are collapsed to a single paradigm, but with different contexts: bounded context vs unbounded context. There are many processing technologies available and many of them are cloud native such as <a href="https://beam.apache.org/">Apache Beam</a>, <a href="https://flink.apache.org/">Apache Flink</a>, <a href="https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html">Spark Structured Streaming</a>, and/or cloud-backed such as <a href="https://azure.microsoft.com/en-au/services/stream-analytics/?">Azure Stream Analytics</a>, <a href="https://aws.amazon.com/kinesis/data-analytics/">AWS Kinesis Analytics</a> etc.</p><p>My suggestion is to invest in learning Kafka for streaming data and spark structured streaming for stream processing in principle. It is important to understand processing semantics such as at least once, at most once and exactly-once and trigger-once (used for batch). Additionally, it will also help to learn cloud specific solutions.</p><h4>Application Development</h4><p>Data driven applications are one of the key consumption channel of data platforms. There can be many applications: it can be recommendation engines, computer vision, search and update applications, data APIs and many many more.</p><p>While these applications are often seen as software engineering problems, in reality a large part of the solutions are data engineering and requires some specialised skillsets we talked about in this article.</p><p>With data being front and centre of digital transformation strategies for many organisations, data driven applications will be increase in volume, in scope and in variety. Hence, it only makes sense to invest time and effort to understand concepts and foundational concepts which has large overlaps with modern data engineering. Let me list out few technologies you should have a fundamental understanding:</p><ul><li>API Backend: REST, GraphQL</li><li>Serverless Design: Function-As-A-Service offerings, Docker, Kubernetes</li><li>Databases: NoSQL in general</li><li>Search: ElasticSearch</li></ul><h3>Final Words</h3><p>We’ve touched on a lot of tools and technologies — even though I excluded machine learning engineering, data security and metadata technologies it is still an overwhelming list. Unfortunately this is the nature of today’s technology landscape. The question is, do you need to learn all of them? My suggestion would be to use a T-shaped model to structure your maturity.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/910/1*_OzTsmY9rpipoDIfyZ27rg.png" /></figure><p>At Slalom, we follow a similar structure when it comes to Data Engineering.</p><p>We use what we call a Modern Culture of Data framework to help our clients enable everyone in their organisation to accelerate business outcomes with rapid insights and achieve the full potential of their investments in data and analytics.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=241c4f615990" width="1" height="1" alt=""><hr><p><a href="https://medium.com/slalom-australia/skills-of-a-data-engineer-241c4f615990">Skills of a Data Engineer</a> was originally published in <a href="https://medium.com/slalom-australia">Slalom Australia</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>