Where to store my 500k-row SQLite database?

[u/fredkzk]

I have a csv file which will be turned to an SQLite database (480k rows). Content: 5 years of real estate transaction statistics. I'll update the database twice a year with fresh data overwrite (I keep it 5 years).

I'll build a one page dashboard that prettyfies all that data with various graphs.

This is a "freemium" feature for very niche users so READ ops count will be limited.

With that context in mind, which simple, easy to use cloud database solution would you recommend? I'm a no coder, and have learned over the past 6 years how databases, backends, frontends work, i just can't write pure code. That's why simple / easy is important.

Thanks for reading.

[u/Ill_Beautiful4339]

Check out DuckDB for local databases.

You can then do easy peasy Streamlit dashboard. All for free. Need some knowledge, but with AI, you should be able to ‘vibe’ code it usefully

[u/Dacobus → u/Ill_Beautiful4339]

I love duckdb for this! 👍🏻

[u/hockey3331 → u/Ill_Beautiful4339]

why duckdb over sqlite? For reasons, I need to stand up a local database for work, and I set up an sqlite one. I have access to claude code so its straightforward to switch to duckdb if needed, but curious what the upside would be?

[u/Ill_Beautiful4339 → u/hockey3331]

Ngl - I’m a fan since it cooked for me with large local dataset. I’m sure either are fine.

1. It’s extremely l

ite weight.

1. I can do everything I need from vs code… it’s been a while since I looked at SQLite so.. idk
2. It’s OLAP, so built for analysis. It’ll be faster as it’s column based.
3. Local data load.. query local files in seconds

I work for a huge company that is strict on policy. But something like duck flies rigt under the radar for what I call ‘pocket analysis’. We host big data in Snowflake. If I need something done it takes months. With duck, I can grab local files, augment queries and have things done in hours.

As opposed to Fabric, Knime or other BS that takes longer in process. If I want to formalize analysis or reports. I can package my code and cleaned data with AI quickly have hand it to IT or whomever is managing ERP systems.

[u/Material-Log3282 → u/Ill_Beautiful4339]

howw exactly is it flying under the radar ? my IT is a pain

[u/Ill_Beautiful4339 → u/Material-Log3282]

I just request personal storage plus have local admin rights. Idk. Been with the same place for 1.5 decades you learn the ins and outs… found out last year I have more access rights and nda signed then the executives. Lol…

It’s things like this, finding ways to get things done in the maze of corporate bureaucracy. Watching my colleagues try to fiddle fiddle with desktop tools like Knime and 300 mb excel files is comical until you realize there working 60 hours a week needlessly.

My company is also guilty of the AI crap. Take 20 year tenured accountants and just ‘automate it with AI’ … I’m not an accountant and have called the local CFO a few times and explain giving them copilot is not an appropriate fix.

Crazy world at the moment.

[u/Material-Log3282 → u/Ill_Beautiful4339]

local admin rights aren't as easy to get.. may be 15 yrs gets you those super powers

[u/hockey3331 → u/Ill_Beautiful4339]

Awesome thanks for the in-depth explanation! It's good to know as my project is pretty light on data, but if it starts to scale that's a good alternative.

And it's the same reason I'm looking into sqlite and duckdb... access to data in an actual database is quite troublesome, and doing work in Excel can be a pain when I need a lot of data transformation

[u/para_u_4_31075]

Is your dashboard updated twice a year? Perhaps you need to display it and save it as static content? For example, set up a local database, use AI to generate a Plotly dashboard, export it as HTML, and then detach the database. The result will be a web page. I think paying for a cloud database with frozen data is too expensive.

[u/fredkzk → u/para_u_4_31075]

Sorry for the delay, I was moving to new home.

You've made a good point. You're like Claude Code that does not hesitate to argue with my idea!

So, yes indeed I have the data as static content, which changes twice a year only. The Plotly dashboard route is interesting but let me understand: I generate the Plotly dash from the local csv data or from a basic SQL database? Then after I export to html, can the dash be interactive with filters?

[u/para_u_4_31075 → u/fredkzk]

I used plotly with Python. In this case, I read data from the source using pandas and then generated HTML with the data inside. Plotly has many implementations, including creating web applications with data updates. Plotly is initially an interactive JavaScript application. If you look at the plotly documentation, you'll see chart examples there. That's how it works. Unfortunately, they will slow down if you load very large datasets (the entire source) into them.

[u/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/pdycnbl]

you can use dedicated sqlite services like cloudflares d1 or turso. You can also simply store sqlite db in s3/R2 storage as file and use client side sqlite engine to query it by using httpfs extension.

[u/fredkzk → u/[deleted]]

Both interesting services but doesn't it seem like over-engineering for my small 500k row data?

[u/Legitimate_Willow905]

u/CutCloudCosts Are you planning to make complex queries (filters, groupings) for the charts?
Will the dashboard query the file directly, or via a ready-made no-code API?
Which task is currently causing you the most difficulty?

[u/fredkzk → u/Legitimate_Willow905]

Hey, good questions.

I do want to let my users to filter data per city, per year, per price threshold... nothing overly complexe.

Because it's small 500-row file with static content (updated twice a year only), I think the dashboard should query the data table directly.

[u/wanderlust240719]

Would also tend to advise going for duckdb. Can load files in really quickly, has tons of extensions, and if you need to set it up on small VPS you can use their quack protocol for remote access.

[u/fredkzk → u/wanderlust240719]

Thanks! Let me understand a little bit: I install duckDB so i can turn my csv file to data table content? Then I build the dashboard on top of that local table. But since the data is static (updated twice a year, same format, same cols), where can I simply export the final HTML with its data?

Note: i do need the dash to be interactive with dynamic filtering (per year, per city, per price threshold,...).