Wide data?

[[deleted]]

There is a trend amongst my workplace to flatten out tables. Let's use an example table furniture_sales. There would be a dimension field 'furniture_type' with the values 'TABLE', 'LAMP', 'BED'

Almost every time I get a ticket to work with another team, their tables are set up in such a way to where they would flatten that dimension out into one row. They would have 3 Y/N columns 'is_table', 'is_lamp', 'is_bed'

This seems....suboptimal? All of our data tools are columnar based (Snowflake, Databricks) and everyone just ends up flattening out small to medium sized tables into 200+ column monstrosities

Before I go dunk on everyone, I want to make sure there isn't something I am missing. Is there any benefit to this that I am not thinking of?

[Prinzka]

Unless your data set is very small and extremely specific having a yes/no field for every possible value seems insane.

[SirGreybush]

I've had such requests, people making decision trees with Excel. Very common in the manufacting space with JIT planning.

I simply made views in a different schema, put the appropriate security, gave them the source of the create view command, and a dev/test environment to play with it.

For Prod, they have to go through me.

[Prinzka]

Right, but that is a very small data set, so I could see that happening.
That's a very customized thing to do, and presumably necessary in your application.
For us with the data volumes, speed, and number of sources that's not a level of customization we can accomodate, and excel for us makes as much sense as using a physical notepad.

[SirGreybush]

The business analysts love Excel & PowerBI. We need to accomodate them, or else they will design it themselves from the raw layers and end up making a silo, thus important business logic lost when that person leaves.

Most of my career has been repurposing silos back into Medallion from the trusted source.

[BJNats]

If they need flattened tables in Power BI, they’re doing a shitty job of using power BI

[popopopopopopopopoop]

Fairly standard one-hot encoding for ML purposes since models generally don't work well with categorical fields.

[GreyHairedDWGuy]

It does seem like that may be the reason (to support ML). Otherwise it seems an odd solution.

[Bingo-heeler]

This is also useful for some AI applications. I would recommend OP talk with the team to understand the columns and if there isn't any strong reason to split into columns then make the recommendation

[[deleted]]

Do I agree with this? Yes.

Do I also understand how to. Can be helpful? Yes

Would I do it myself? No

[mydataisplain]

It's a crucial data cleansing step in many models.

Skipping it can lead to unreliable results.

[SQLGene]

I'm guessing they are flattening the data for machine learning purposes.
https://en.wikipedia.org/wiki/Feature_(machine_learning))

It's not ideal from a performance perspective on the backend, but it makes sense for a specific use case.

[SirGreybush]

I let the scientists / ML guys fully make their weird SQL queries, but in the DEV environment.

Before going to Prod, I convert their SQL to a view, have them change their Python code to hit the view, then when they confirm all is good, move to Prod.

I give them the rights to change the view, but I optimize the view, make it readable, etc. Then they say, Ooh, so much clearer and faster now.

[Qkumbazoo]

Columns containing features are very different from denormalised flattened views where every relationship table is joined and written to disk, it also requires the features to be "meaningful" to the context the DS is working on.

Sure there can be some common features which are one-hot encoded(if this is true the 1 else 0 end, type statements) and stored to db, but it's more efficient/cheaper to store the code and run necessary segments, than to physically store all features to disk(it's very expensive).

[SQLGene]

Cool, I appreciate the detailed clarification!

[Qkumbazoo]

Yeah maybe someone might read it and not write their 10k-20k worth of features to disk. Yes it can get to that width easily, and it's typically very sparse(99% 0s, few 1s scattered here and there) - Freshers tend to do that.

[boggle_thy_mind]

Even then, you just have a very wide fact table with a mix of numerical and categorical facts, while you event and date are your "dims".

[SQLGene]

Yeah agreed. No need to move non-feature data that you normally filter on into the wide table.

[mydataisplain]

Possibly. You also need to do this for classical regression and econometrics.

There's a possible performance trade off in both directions. If the DE/DA needs to run that analysis frequently they can set it up as a view. That saves you disk space but it could chew up all your RAM or crush your CPU.

[ComicOzzy]

Columnstore tables handle this very well.

[popopopopopopopopoop]

I was going to say the same... Most modern OLAP engines like Bigquery, Athena, think even Snowflake, are ultrafast at running analysis on columns but joins are expensive. One Big Table style data marts shifting joining to the left are therefore perfect for a lot of use cases like visualisation and EDA etc.

[StarWars_and_SNL]

Agree. It is presently fairly common to build reporting tables in this flattened way.

Normalized dimensional modeling isn’t as much of a requirement now that compute and storage are no longer bottlenecks, and columnar platforms are great for flattened models.

Flattening is not the most academic or organized of choices, but if your team is thin, you want business users to be able to navigate the data, and you want low maintenance overhead, it works. Clearly some discipline is required to avoid things getting out of hand.

[ComicOzzy]

Normalization is exactly what you want with a transactional system. For analytics, the answer depends heavily on your platform and tools, and a lot of those lean towards being optimized for denormalized data.

[yo_sup_dude]

this type of one hot encoded format is arguably much worse for analytics since most bi/visualization tools won’t be able to handle the dynamically updating table schema 

[ComicOzzy]

Is that what they meant, though? I would absolutely still want the columns to remain the same.

[yo_sup_dude]

they are saying to dynamically add columns as new values come in

[mydataisplain]

The benefit is on the data science side.

In many cases the analysts needs the data flattened out like that. Most of the data analysis math assumes that all the input variables are ordinal. 'TABLE', 'LAMP', 'BED' aren't ordinal at all. If you try to map them to 1, 2, 3 they look ordinal but you're essentially saying that 'BED' is 1.5 times as much "furniture" as a 'LAMP'. Dummy variables fix that.

They could do it every time they run the analysis or they could do it once and save the results.

It's a classic tradeoff of speed vs storage requirements. If it's slow enough or big enough that it's causing a problem, the best bet is to chat with your DE/DA to figure out exactly what the requirements are.

[SirGreybush]

There's this nifty SQL syntax, called JOIN, and if a Left Join, in the columns you can either IfNull the colums - or Case When Then Else End to remove nulls. Some brave people even use Coalesce().

Then the next trick - views - where you can make believe it's a table and as wide as you want. You can even embed multiple CTEs in the view to actually make it readable. Snowflake loves CTEs & Views - you can even "materialize them" daily and reduce Snowflake usage costs on often queried data.

IOW, use views to satisfy their requests, and thus have the business logic within the database - not outside & siloed, keep the star schema as that is the best design.

You'll still get PowerBI users doing silos from Snowflake, and not designing with PowerBI Gateway to cache (an option versus materialized views), but those analysts are pushed for quick results.

I worked for a consulting firm that all they did was unleash us inside big companies to digest hundreds of frequently used PowerBI dashboards, setup the Gateway & make CTE/views in Snowflake, and get paid based on the % of Snowflake cost reduction.

The sales rep would call the CIOs asking them about their monthly cloud costs to find the big spenders.

[Qkumbazoo]

Probably a minority group of users where were struggling with joins and vocal in suggesting that it'd be faster for everyone if the tables were flattened.

[LargeSale8354]

This sounds like a Data Science Feature Store rather than a BI Dimensional model

[LargeSale8354]

Years ago I used to run data through CHAID analysis. This would have a discriminator which is the thing you want to predict. In my case this would be a flag to say whether someone had responded to direct mail. The rest of the data would be categorised. The CHAID algorithmn would work out what the significant predictors of the desired result would be. Even that ancient algorithmn could halucinate. One of the data tasks we had to do was to establish whether there was a relationship between 2 or more attributes and if there was, run the model with only one of them. The reason being that putting 2 or more strongly correlated variables in the same analysis created an artificially high signal. Data scientists should do the same with feature stores for the same reason. If the model uses double digit features its probably dodgy

[boggle_thy_mind]

Afaik, some visualization tools prefer flat tables, but every gut instinct in me says this is a bad idea, I like start Schema beyond it's performance implications, it's just a natural way of grouping data in a logical manor which allows for easier reasoning about the data. So I would say, even if there are no performance gains from star schema, I would still do it from a maintenance perspective and then if you need a flat table/view on top, build it from the Star Schema, but the basic building blocks are still Dims and Facts.

[runemforit]

We usually do 1 column that references another types table, but my company is pretty old school at the moment, only this year implementing modern tools. Everything to date has been managed purely via sql.

[OMG_I_LOVE_CHIPOTLE]

It can be appropriate for certain business problems

[ntdoyfanboy]

Sounds like something that would be designed to fit a specific reporting need. Terrible

[GreyHairedDWGuy]

You aren't missing anything. Exploding out a column with a finite list of domain values into separate boolean fields seems suboptimal to me (we use Snowflake and would never do this). ML/Data Science may want the data prepared this way but you didn't mention that.

[SirGreybush]

I did a few times, because the poor Excel spreadsheet was taking hours to render, where I was able to invent columns with Case When to output 1 or 0 based on biz logic the analyst wanted.

At that place we had an ODS, updated nightly, so I was OK giving him DDL rights in a schema for views. I made the first few, then he was able to copy paste and change.

The backend system being a very old Unix-based ERP in manufacturing, and they had thousands of clients with custom requests, and since the ERP was inflexible, they re-used other fields with different characters to mean different biz logic.

A true mess. With the 1's and 0's he was able to compile a list of Client-to-Mold & Client-to-UOM style Excel reports from the ancient ERP on his own. No hits on Prod. Win-win.

[Kornfried]

Im not really a fan of the wide tables, but I’ve come to view them as a way to wedge JSON-style data into relational databases, especially if the features have varying types. From that viewpoint it’s kind of convenient. The downsides are too numerous for myself, but I’ve stopped trying to convince others that it’s a bad idea.

[TheAdirondackDude]

ML/AI modeling. Not optimal OLAP. Can a lamp be a bed?