Claude's Strength in Structured Coding for APIs and Backend Logic

Most data engineers lint their Python. Nobody lints their SQL. You've got flake8 on every commit, formatters on your notebooks, type checkers on your APIs. But the SQL that actually touches production data? That gets eyeballed in a PR and merged with a "looks good to me." I started running SQLFluff on a client's dbt project almost by accident. Someone mentioned it in a thread, I added it to the pre-commit config, and within a week I couldn't imagine working without it. It's open source. It supports most major warehouses - Redshift, BigQuery, Snowflake, Postgres - and has a dbt templater that actually handles Jinja SQL without losing its mind. Basic setup takes minutes. Tuning it for your team's conventions takes a bit longer, but that's time well spent. What it catches isn't dramatic. Inconsistent casing across models. Ambiguous joins that pass review because everyone reads them differently. Implicit column references that'll break the next time someone touches the schema. None of it is catastrophic on its own. But messy SQL accumulates. And accumulated mess is where bugs hide. The real value isn't even the linting itself. It's that your team stops arguing about SQL style in pull requests. The linter decides. You move on. And when you onboard someone new, they don't have to reverse-engineer your conventions from 50 different models - the rules are in the config. Run it in CI and it prevents style drift over time, not just during review. That matters more as your team grows. We treat SQL like it's somehow exempt from the standards we apply to every other language in the stack. It shouldn't be. It's the language closest to your data, and it deserves at least the same rigor as the Python wrapping it. SQLFluff is free. There's no reason not to try it.

✅ #PythonJourney | Day 152 — All API Endpoints Tested & Production Ready Today: Comprehensive endpoint testing. The entire URL Shortener API is now fully operational! Key accomplishments: ✅ Tested 4 critical endpoints:   • POST /api/v1/urls → Creates shortened URL with auto-generated short code   • GET /api/v1/urls → Returns user's URL list (ordered by newest first)   • GET /api/v1/urls/{url_id} → Retrieves specific URL details   • GET /{short_code} → Redirects to original URL + tracks click in database ✅ Fixed SQLAlchemy Click model:   • Issue: Composite primary key (id + clicked_at) prevented autoincrement   • Solution: Made id the sole primary key, clicked_at just a timestamp   • Result: Click tracking now works perfectly ✅ Verified full request/response cycle:   • Authentication: API key validation ✓   • Input validation: Pydantic models ✓   • Database operations: CRUD complete ✓   • Click tracking: Events recorded correctly ✓   • Response serialization: JSON output perfect ✓ ✅ Data flow confirmed:   1. User creates URL → Stored in PostgreSQL   2. User accesses short code → Redirect happens   3. Click event → Recorded in clicks table   4. URL counter → Incremented automatically   5. JSON response → Properly formatted What I learned today: → Comprehensive testing reveals edge cases early → SQLAlchemy's primary key behavior affects autoincrement → Docker image caching can hide recent code changes → Click tracking requires careful database schema design → Manual testing validates the entire architecture The API is now: - ✅ Accepting requests from multiple sources - ✅ Storing data reliably in PostgreSQL - ✅ Returning proper JSON responses - ✅ Tracking user behavior - ✅ Handling redirects correctly - ✅ Managing database transactions safely Endpoints remaining to test: - GET /api/v1/urls/{url_id}/analytics (analytics aggregation) - DELETE /api/v1/urls/{url_id} (soft delete) Status: API Core is production-ready. Ready for comprehensive test suite (pytest) next. This is what backend development looks like: build → test → debug → iterate → victory! #Python #FastAPI #API #Testing #Backend #PostgreSQL #Docker #SoftwareDevelopment #StartupLife

dbt-gizmosql — a month of new capabilities Six releases shipped for dbt-gizmosql, the dbt adapter for GizmoSQL (an Apache Arrow Flight SQL engine backed by DuckDB). The headline: the adapter now supports a lot of things it just... didn't before. → Python models (brand new!) Write dbt transformations in Python. dbt.ref() / dbt.source() pull upstream tables as Arrow; return a DuckDB relation, pandas DataFrame, or PyArrow Table and the result is shipped back to the server via ADBC bulk ingest. Incremental Python models supported too. → session.remote_sql() — server-side pushdown for Python models Python models run client-side, so dbt.ref('big_table') streams the whole upstream table across the wire before your code sees it. The new remote_sql() escape hatch runs SQL directly on GizmoSQL and returns only the result — the filter executes server-side: def model(dbt, session): schema = dbt.this.schema return session.remote_sql( f"select * from {schema}.big_table where name = 'Joe'" ) → External materialization — write straight to files A new 'external' materialization issues a server-side COPY to Parquet, CSV, or JSON. Anywhere the server's DuckDB can reach: local disk, s3://, gs://, azure://, MinIO. Supports partitioning, codecs, and format inference. The result is ref()-able downstream. → Microbatch incremental strategy Time-windowed incrementals via dbt's microbatch strategy — reprocess a recent event_time window each run, with automatic batching. → Snapshot merge rewritten around MERGE BY NAME Snapshots now use DuckDB's native MERGE ... UPDATE / INSERT BY NAME — more robust to column reordering, far clearer than a hand-rolled merge. → Much faster seed loading Seeds are now read with DuckDB's CSV reader (correct null handling, proper type inference) and bulk-ingested as Arrow via ADBC instead of row-by-row INSERTs. A shout-out to ADBC None of this would be practical without ADBC (Arrow Database Connectivity). It gives the adapter a columnar, zero-copy path to the server: seeds and Python-model results ship as Arrow record batches, ref() pulls land as Arrow tables, and remote_sql() streams Arrow results back. It's the reason the adapter can move real data volumes without the usual row-by-row ODBC/JDBC tax. Huge thanks to the Apache Arrow community and the adbc-driver-flightsql maintainers. And finally — the best GizmoSQL features come from the user community. GizmoData thanks our users for their great feedback and engagement! pip install dbt-gizmosql https://lnkd.in/ewKGMUCe #dbt #duckdb #dataengineering #apachearrow #adbc #flightsql

🔗Continuing from my last post here https://lnkd.in/gDpPdCAr As a note, I've been using KNIME to support my daily data entry tasks. Now I try to make small experiment by comparing it with Python from raw to database and verification 🗃️ 𝐃𝐚𝐭𝐚𝐬𝐞𝐭: ▪️I'm using data which have ~370k rows per day ▪️In this experiment, I used 4 days of data 📚 𝐃𝐚𝐭𝐚 𝐢𝐧𝐠𝐞𝐬𝐭𝐢𝐨𝐧 𝐟𝐥𝐨𝐰: The flow itself is not too complex: 1️⃣ 𝐏𝐮𝐭 𝐝𝐚𝐭𝐚 𝐢𝐧 𝐝𝐞𝐟𝐢𝐧𝐞𝐝 𝐟𝐨𝐥𝐝𝐞𝐫 KNIME/Python will check and extract the file(s) which have specific filename 2️⃣ 𝐃𝐚𝐭𝐚 𝐓𝐫𝐚𝐧𝐬𝐟𝐨𝐫𝐦𝐚𝐭𝐢𝐨𝐧 In this part, the transformation process is simple - Change the date format to "𝘥𝘥/𝘮𝘮/𝘺𝘺" - arrange column names - sort the data 3️⃣ 𝐑𝐮𝐧𝐧𝐢𝐧𝐠 𝐏𝐨𝐬𝐭𝐠𝐫𝐞𝐒𝐐𝐋 𝐞𝐧𝐠𝐢𝐧𝐞 𝐚𝐧𝐝 𝐜𝐨𝐧𝐧𝐞𝐜𝐭 𝐭𝐨 𝐝𝐚𝐭𝐚𝐛𝐚𝐬𝐞 Setting up the database environment and connect KNIME/Python to interact with the database 4️⃣ 𝐈𝐧𝐠𝐞𝐬𝐭𝐢𝐨𝐧 𝐝𝐚𝐭𝐚 𝐭𝐨 𝐝𝐚𝐭𝐚𝐛𝐚𝐬𝐞 Storing data in the defines tables and verifying the upload results 5️⃣ 𝐃𝐚𝐭𝐚 𝐯𝐞𝐫𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧 Make verification using line chart to check the data already import Your data is successfully ingested into database 🎉 📊 𝐑𝐞𝐬𝐮𝐥𝐭 𝐟𝐫𝐨𝐦 𝐭𝐡𝐢𝐬 𝐞𝐱𝐩𝐞𝐫𝐢𝐦𝐞𝐧𝐭 ▪️As on video, KNIME Analytics need 115s and Python need 87s to ingest data from raw to data verification. Python is ~25% more faster than KNIME ▪️The advantage of using KNIME is that we are not required to master coding because the way to operate it is simply "drag and drop" ▪️Whereas by Python, we at least need to have the ability to understand the algorithm which are then translated into coding script ---------- 🔆𝐋𝐞𝐬𝐬𝐨𝐧𝐬 𝐥𝐞𝐚𝐫𝐧𝐞𝐝 𝐟𝐫𝐨𝐦 𝐭𝐡𝐢𝐬 𝐞𝐱𝐩𝐞𝐫𝐢𝐦𝐞𝐧𝐭: The lessons learned were more focused on creating Python code. I encountered many errors, but from here we'll learn how to fix them. For example: ▪️Data types differ between the extracted results and the database. 𝑺𝒐𝒍𝒖𝒕𝒊𝒐𝒏: Match the data types. ▪️If follow method from the previous post, it takes a long time because it sends the data to the database one row at a time (or in very small chunks), which creates a lot of "network chatter" between Python and your Postgres 𝑺𝒐𝒍𝒖𝒕𝒊𝒐𝒏: Create a function that uses Postgres' built-in COPY command. This is the fastest way to move data into Postgres ▪️Found a 𝘔𝘦𝘮𝘰𝘳𝘺𝘌𝘳𝘳𝘰𝘳 message, due to converting large columns and regular expressions consumes a lot of RAM 𝑺𝒐𝒍𝒖𝒕𝒊𝒐𝒏: Use "Chunking," breaking rows into smaller "chunks". This way, your RAM only has to handle one small chunk of data at a time, but you still get the high speed of the COPY method. ---------- You can check the detail in my git https://lnkd.in/gM9MCUpb Salam Fatwa Rafiudin #DataEngineering #Python #PostgreSQL #KNIME #ETL #VSCode #JupyterNotebook

✅ #PythonJourney | Day 154 — Test Suite Complete: 14 Tests, 100% Endpoint Coverage Today: Completed the comprehensive test suite. Every API endpoint now has automated tests validating behavior, error handling, and authentication. Key accomplishments: ✅ Full test coverage (14 tests):   • Health Check: 1 test   • Create URL: 4 tests (success, invalid format, no auth, invalid auth)   • List URLs: 3 tests (empty, with data, no auth)   • Get URL Details: 2 tests (success, not found)   • Delete URL: 2 tests (success, not found)   • Get Analytics: 2 tests (success, not found) ✅ Testing patterns implemented:   • Fixture-based setup (conftest.py)   • Isolated database per test   • Mock user creation   • Authentication validation   • Error condition testing   • Status code verification ✅ All edge cases covered:   • Valid requests return proper responses   • Invalid inputs rejected with 422   • Missing auth returns 401   • Non-existent resources return 404   • Successful deletes return 204   • Analytics properly calculated ✅ Test execution:   • 14 passed in 2.51s   • Zero flaky tests   • All database operations isolated   • Clean setup and teardown What I learned today: → Comprehensive testing catches edge cases early → Fixtures reduce boilerplate and improve maintainability → Test isolation prevents hidden dependencies → Fast tests enable rapid development cycles → Good test names document expected behavior The test suite now validates: - ✅ API contract (request/response format) - ✅ Authentication (API key validation) - ✅ Authorization (users see only their data) - ✅ Error handling (proper HTTP status codes) - ✅ Business logic (URL creation, deletion) - ✅ Data persistence (database operations) This is production-grade testing: - Every endpoint tested - Every error case covered - Fast feedback on code changes - Confidence to refactor safely - Documentation through tests Current status: - ✅ Backend: Production-ready - ✅ Tests: 14/14 passing (100%) - ✅ Code coverage: All endpoints - ✅ API: Fully validated - ⏳ Deployment: Next (GCP) From zero to production-grade in 154 days. The backend is ready for real-world use. Next: Deploy to Google Cloud Platform (GCP). #Python #Testing #Pytest #Backend #API #Quality #SoftwareDevelopment #TDD #Production

🚨Stop Treating Them Like They’re the Same! 🚨 If you’ve ever looked at a dataset and felt like you were staring into a black hole of "Nothingness," you aren’t alone. But in the world of data, not all "nothings" are created equal. Is None the same as NaN? Is Null just a fancy word for zero? No. Mixing these up is a one-way ticket to buggy code and broken pipelines. Here is the "No-Nonsense" breakdown: The terms None, NaN, and Null are used to represent missing or invalid data, but they belong to different programming environments and behave differently. 1. None  (The Python Specialist) In Python, None is a built-in constant used to represent the absence of a value. None is a literal object. It represents the intentional absence of a value. Type: It is a singleton of the NoneType class. Behavior: It is not equal to 0, False, or an empty string. Comparison: You should check for it using the is operator (e.g., x is None). Usage: Commonly used as a default return value for functions that don't return anything or to initialize variables that don't have a value yet. 2. NaN (Not a Number) NaN is a special numeric value used to represent a value that is undefined or unrepresentable, particularly in floating-point calculations. Type: In Python's NumPy and Pandas libraries, it belongs to the float class. Comparison: A unique property of NaN is that it is not equal to itself (np.nan == np.nan returns False). Use special functions like pd.isna() or np.isnan() to detect it. Behavior: Mathematical operations involving NaN usually result in NaN (e.g., 5 + NaN = NaN). 3. Null Null is a keyword used in many languages (like SQL, Java, C#, and JavaScript) to indicate that a variable does not point to any object or memory address. Context: SQL: Used to represent missing or unknown values in a database. It’s a placeholder, not a value. In SQL, Null != Null, which is why we have to use IS NULL. JavaScript: Represents the intentional absence of an object value. Python: Does not have a null keyword; it uses None instead. Pandas/Polars: Modern data libraries like Polars use null as their primary indicator for any missing data across all types, whereas Pandas traditionally converts None to NaN in numeric columns. 💡 The Bottom Line: None is an object. NaN is for missing/invalid numbers. Null is for missing database entries. #DataScience #Python #Programming #SQL #DataEngineering #CodingTips

𝐖𝐨𝐫𝐤𝐢𝐧𝐠 𝐝𝐞𝐞𝐩𝐥𝐲 𝐰𝐢𝐭𝐡 𝐂𝐥𝐚𝐮𝐝𝐞? 𝐓𝐨𝐤𝐞𝐧 𝐥𝐢𝐦𝐢𝐭 𝐟𝐢𝐧𝐢𝐬𝐡𝐞𝐬 𝐟𝐚𝐬𝐭 — 𝐜𝐨𝐦𝐦𝐨𝐧 𝐩𝐫𝐨𝐛𝐥𝐞𝐦 10 tips to save your Claude tokens 𝟏: 𝐂𝐚𝐯𝐞𝐦𝐚𝐧 𝐏𝐫𝐨𝐦𝐩𝐭𝐢𝐧𝐠 Cut the polite filler. Prompt: "Direct answers only. No filler. No 'the solution is...'. Code first, explanation only if asked." → Saves ~40% per response. 𝟐/ 𝐒𝐢𝐧𝐠𝐥𝐞 𝐁𝐫𝐚𝐢𝐧 𝐃𝐮𝐦𝐩 One structured message beats five. Prompt: "Role: Senior Python dev. Context: FastAPI + PostgreSQL on AWS. Goal: Add JWT auth to /users. Constraints: No new libs. Async only. Output: Code + 2-line note." 𝟑/ 𝐑𝐢𝐠𝐡𝐭 𝐦𝐨𝐝𝐞𝐥, 𝐫𝐢𝐠𝐡𝐭 𝐣𝐨𝐛 Stop using Opus for everything. • Haiku → "Format this JSON" • Sonnet → "Refactor this module" • Opus → "Design multi-tenant architecture" 𝟒/ 𝐔𝐬𝐞 𝐏𝐫𝐨𝐣𝐞𝐜𝐭𝐬 𝐟𝐨𝐫 𝐩𝐞𝐫𝐬𝐢𝐬𝐭𝐞𝐧𝐭 𝐜𝐨𝐧𝐭𝐞𝐱𝐭 Drop style guide, schema, key files in a Project once. Then: "Using project context, add soft-delete to Orders model." → Zero re-pasting. 𝟓/ 𝐀𝐬𝐤 𝐟𝐨𝐫 𝐝𝐢𝐟𝐟𝐬, 𝐧𝐨𝐭 𝐫𝐞𝐰𝐫𝐢𝐭𝐞𝐬 Prompt: "Show only changed lines with 3 lines of context. Unified diff format. Don't reprint the file." 𝟔/ 𝐔𝐬𝐞 𝐗𝐌𝐋 𝐭𝐚𝐠𝐬 Prompt: "<context>Checkout, carts over $500 need approval.</context> <task>Write the validation function.</task> <constraints>TypeScript, no external libs.</constraints>" → Fewer retries. 𝟕/ 𝐒𝐞𝐭 𝐨𝐮𝐭𝐩𝐮𝐭 𝐥𝐢𝐦𝐢𝐭𝐬 • "Max 50 words." • "3 bullets, no intro." • "One-line summary." → Forces precision. 𝟖/ 𝐒𝐩𝐞𝐜𝐢𝐟𝐲 𝐬𝐭𝐚𝐜𝐤 𝐮𝐩𝐟𝐫𝐨𝐧𝐭 Prompt: "Stack: Python 3.11, FastAPI 0.110, SQLAlchemy 2.0 async, Pydantic v2. Don't ask about versions." → Clean code first try. 𝟗/ 𝐊𝐢𝐥𝐥 𝐩𝐨𝐢𝐬𝐨𝐧𝐞𝐝 𝐜𝐡𝐚𝐭𝐬 𝐟𝐚𝐬𝐭 If Claude keeps repeating a mistake, don't fight it. New chat. Start with: "Building Stripe webhook. Previous attempt failed on sync signature verification. Use async with raw body. Code: [paste]." 𝟏𝟎/ 𝐂𝐫𝐞𝐚𝐭𝐞 𝐚 𝐂𝐎𝐌𝐏𝐀𝐂𝐓 𝐜𝐨𝐦𝐦𝐚𝐧𝐝 Set at chat start: "When I type 'COMPACT', summarize in 5-7 points: decisions, code, open questions, next steps. Format to paste into a new chat." Then just type: COMPACT → Fresh window, full continuity. Token management isn't optional anymore. If you're serious about AI-native dev, this is the playbook. Which one are you trying first? 👇 #AI #Claude #Anthropic #PromptEngineering #AIEngineering

dbt uses Jinja. But most people never truly understand it. Here's the complete breakdown what it is, how it works, and the syntax you'll actually use every day. WHAT IS JINJA? - Jinja is a templating language for Python. It lets you write dynamic, reusable text not just static SQL. Jinja can generate any text-based format (HTML, XML, CSV, LaTeX, etc.). A Jinja template doesn’t need to have a specific extension: .html, .xml, or any other extension is just fine. In plain English? Think of it like Excel formulas inside your SQL files. Instead of hardcoding values, you write logic. dbt uses Jinja so your models can be smart, not just static. HOW DOES IT WORK? Jinja has 3 building blocks: 1️⃣ {{ ... }} → Expressions (print a value) 2️⃣ {% ... %} → Statements (logic: loops, if/else) 3️⃣ {# ... #} → Comments (not rendered in output) When dbt compiles your model, Jinja gets executed first. The output is pure SQL clean, readable, production-ready. 🎯 USE CASES IN dbt (Real Examples) ✅ Reference another model dynamically ✅ Use environment variables (dev vs prod) ✅ Loop through a list to generate repetitive SQL ✅ Apply conditional logic based on target schema ✅ Create reusable SQL macros (like functions) 💻 SYNTAX — The 5 You MUST Know 1. ref() : Reference a model {{ ref('dim_customers') }} compiles to: dim_customers Customer Model 2. if/else : Conditional logic {% if target.name == 'prod' %}   WHERE created_date >= '2020-01-01' {% else %}   WHERE created_date >= '2024-01-01' {% endif %} 3. for loop : Generate repetitive SQL {% set payment_methods = ['cash', 'upi', 'card'] %} {% for method in payment_methods %}   SUM(CASE WHEN payment_type = '{{ method }}' THEN amount END) AS {{ method }}_amount, {% endfor %} 4. set : Declare variables {% set schema_name = 'finance' %} SELECT * FROM {{ schema_name }}.transactions 5. macro : Reusable SQL functions {% macro cents_to_rupees(column_name) %}   ({{ column_name }} / 100)::numeric(16,2) {% endmacro %} {{ cents_to_rupees('order_amount') }} dbt = SQL + Jinja Master Jinja → your models become 10x more maintainable. Stop copy-pasting SQL blocks. Start writing smart, dynamic, reusable code. ♻️ Repost if this helped you understand Jinja better. What's the one Jinja feature you use most in dbt? Drop it below 👇 #dbt #DataEngineering #Jinja #Snowflake #SQL #DataWarehouse #Analytics #Python #DataEngineer

🚨 Every data team has that one Python script. You know the one. Someone wrote it "just for now" two years ago. It's still running in production. No retries. No logging. Hardcoded credentials. And every time it breaks at 3 AM, someone has to SSH into a server and pray. I just published a new article on what actually separates a script from a pipeline. Spoiler: it's not complexity. It's whether the code was designed to fail gracefully. In the article, I cover: ⚙️ Why idempotency is the single most important property your pipeline can have (and how to test it in 30 seconds) 🔁 How to handle transient vs permanent errors the right way 🔐 The Twelve-Factor config test: could you open source your codebase right now without leaking credentials? 📊 Why print() is not observability, and what to log instead 🧪 The uncomfortable truth about data testing: only 3% of tests are business logic tests 🚫 The notebook trap and other anti-patterns killing your pipelines in production If your team is stuck between "it works on my laptop" and "production grade," this one is for you. Read it here 👉 https://lnkd.in/dwMDTUSD

How to Write Better Prompts: 5 Simple Strategies for #Engineers to Get Better Results Stop thinking of the #LLM as a search engine. Treat it as part of your system. Better inputs lead to less confusion later. 1. Be Specific About What You Need Instead of vague requests, ask for clear tasks. ❌"Write a regex to validate an email address." ✅"Write a Python function using `re` that validates an email address. The domain must end in `.edu` or `.org`. It should fail if there is no `@` or if the local part contains special characters. Include test cases for these issues." 2. Provide Context The LLM doesn't know your tools, dependencies, or business logic. Give it the necessary background. ❌"How do I optimize this SQL query for speed?" ✅"I have a Postgres query scanning a 10M row `orders` table. The `customer_id` column is indexed, but the query takes 5 seconds. The execution plan shows a sequential scan. What are three optimization strategies if I cannot change the schema?" 3. Define a Role Telling the model what role to play changes the tone and depth of the response. You can ask it to be an expert, critic, or teacher. ❌"Review my Python code for bugs." ✅"Act as a Senior Systems Architect focused on high availability. Conduct a strict PR review on this code snippet. Point out any failures, concurrency issues, or possible memory leaks. Ignore style suggestions." 4. Specify the Output Format Clearly state how you want the information presented. Mention if you want tables, bullet points, emails, code blocks, or lists. ❌"List the security vulnerabilities of this system." ✅"Perform a security audit of the provided architecture. Output the results ONLY as a JSON object in this format: `{"vulnerability": "string", "severity": "low/med/high", "mitigation": "string"}`." 5. Iterate and Refine Remember, good prompting is rarely once-and-done. Treat it like debugging; it's very normal to tweak and reword an experiment to get the best results. ❌ First Try: "Fix this code." (Result is too broad or misses the edge case) ✅ Refined Prompt: "The fix you provided throws a `KeyError` on line 42 when the payload is empty. Rewrite the function to handle null payloads gracefully." What is your strategy to write a better prompt?